17 posts from: SQL Server Engine Tips
Converting from hex string to varbinary and vice versa
(Indexed 2008-07-03):
Converting hexadecimal values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005. The code samples below show how to perform the conversion(s): -- Convert hexstring value in a variable to varbinary: declare @hexstring varchar(max); set @hexstring = 'abcedf012439'; select cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)') from (select case substring(@hexstring, 1, 2) when ...(truncated)...
Converting from Base64 to varbinary and vice versa
(Indexed 2008-07-01):
Converting Base64 values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005 onwards. The code samples below show how to perform the conversion: -- Convert Base64 value in a variable to varbinary: declare @str varchar(20); set @str = '3qAAAA=='; select cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(20)'); -- Convert binary value in a variable to Base64: declare @bin varbinary(20); set @bin = 0xDEA00000; selec...(truncated)...
Differences between ISNULL and COALESCE
(Indexed 2008-06-27):
I came across a question in the SQL Server MVP newsgroup recently about ISNULL and COALESCE usage. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. Both ISNULL and COALESCE can be used to get the same results but there are some differences. 1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence 2. The N...(truncated)...
Spool operators in query plan...
(Indexed 2007-08-30):
I came across a question in the relationalserver.performance newsgroup where a customer was wondering about the spools seen in a recursive query execution plan. The query is shown below: USE Northwind;Go WITH EmpChart AS(SELECT EmployeeId, ReportsTo, 1 AS treelevelFROM EmployeesWHERE (Employees.ReportsTo = 2)UNION ALLSELECT e.EmployeeId, e.ReportsTo, treelevel +1FROM Employees eJOIN EmpChart ecON e.ReportsTo=ec.EmployeeID)SELECT * FROM EmpChart; The plan for the above query shows an index spool...(truncated)...
SQL Server 2005 SP2 Re-release and post fixes
(Indexed 2007-04-11):
Bob Ward from PSS has a wonderful blog article that explains the details about the re-release of SQL Server 2005 SP2 and fixes posted later. This is a must read for anyone deploying SQL Server 2005 SP2 to understand the various hotfixes, GDRs and procedures. Please visit his link for more details. SQL Server 2005 SP2 Re-release and post fixes -- Umachandar[Image]
SQL Server 2005 Performance Dashboard Reports
(Indexed 2007-03-29):
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SQL Server 2005 SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits fo...(truncated)...
OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2
(Indexed 2007-03-23):
SQL Server 2005 SP2 has an important enhancement to OBJECT_NAME metadata function and a new OBJECT_SCHEMA_NAME metadata function. I will first describe the old functionality to give some context and demonstrate how the new features help a lot. Please note that the examples uses DMVs that looks at metadata in each database or plan cache so performance of the queries depends on your environment, number of objects in the database, auto open/auto close setting of database, size of buffer pool or pla...(truncated)...
SQL Server Performance Engineering Team Blog
(Indexed 2007-02-28):
I would like to announce the blog from my team - SQL Server Performance Engineering. Feel free to visit our blog at http://blogs.msdn.com/sqlperf for your performance related questions and needs. Use the email link in the home page to share your feedback on performance and topics of interest. -- Umachandar Jayachandran[Image]
SQL Server 2005 SP2 has been released...
(Indexed 2007-02-19):
SQL Server 2005 Service Pack 2 has been released. You can find more information from the service pack 2 page. This service pack extends the functionality of SQL Server in many ways. The What's New document highlights the major changes in the Database Engine, Analysis Services, Integration Services and others. The service pack also has performance and manageability enhancements that will be of interest to lot of developers and database administrators. Please download the latest service pack and t...(truncated)...
Database Applications Profile Survey in http://connect.microsoft.com/sqlserver
(Indexed 2007-02-04):
My team (Data & SQL Storage Performance Team) is conducting a survey on http://connect.microsoft.com/sqlserver to better understand SQL Server workloads. The connect link below will take you directly to the survey: https://connect.microsoft.com/SQLServer/Survey/Survey.aspx?S...(truncated)... Please consider providing your workload characteristics and hardware configuration to help us better understand the workloads that run on SQL Server. This is a detailed survey so we are hoping to determine m...(truncated)...
SQL Server 2005 SP2 December CTP is available for download
(Indexed 2006-12-20):
SQL Server 2005 Service Pack 2 December CTP is available now for download. You can download it from: http://www.microsoft.com/downloads/details.aspx?FamilyID=d2d...(truncated)... Note that the December CTP has been published to the same location as the November CTP so if you come from the SQL Server 2005 CTP Home Page and get to December CTP don't be confused. Please take this opportunity to test your applications with the new service pack and provide feedback on the new features or bugs that yo...(truncated)...
Download SQL Server 2005 SP2 CTP
(Indexed 2006-11-08):
SQL Server 2005 Service Pack 2 (SP2) CTP has been released. You can provide feedback on the CTP release via http://connect.microsoft.com/sqlserver. The link for the various downloads is:http://www.microsoft.com/sql/ctp.mspxSQL Server 2005 SP2 CTP contains some new features in the database engine (vardecimal data types, object_name function enhancement, logon triggers, Common Criteria Compliance, sys.dm_exec_text_query_plan DMV, plan cache improvements among others). The list of bugs fixed in the...(truncated)...
Top 5 relational data warehouse performance improvements you would like to see in next version of SQL Server
(Indexed 2006-09-20):
Hello Everyone, We are currently looking at top performance issues for various workloads and how we can improve those in next version of SQL Server. We are collecting feedback from various customer sources. I would like to extend an invitation to readers here for feedback on top 5 relational data warehouse performance improvements you would like to see in SQL Server. Please bear the following in mind: 1. Briefly describe the relational data warehouse workload or scenario or application. More det...(truncated)...
Top 5 OLTP performance improvements you would like to see in the next version of SQL Server
(Indexed 2006-09-20):
Hello Everyone,We are currently looking at top performance issues for various workloads and how we can improve those in next version of SQL Server. We are collecting feedback from various customer sources. I would like to extend an invitation to readers here for feedback on top 5 OLTP performance improvements you would like to see in SQL Server.Please bear the following in mind:1. Briefly describe the OLTP workload or scenario or application. More details you can provide for us the better2. Prio...(truncated)...
Using catalog views in SQL Server 2005
(Indexed 2006-09-06):
Did you know that the catalog views in SQL Server 2005 exposes metadata for various objects in a database and at the server-level? This is the preferred method of accessing metadata. It is a much richer mechanism that doesn't require access to system tables or undocumented columns or status bits. If you want standard and portable access to metadata then you can still use the INFORMATION_SCHEMA views but it is limited to standard specific features, data types and views. I have posted several topi...(truncated)...
DATALENGTH optimizations for LOB data types...
(Indexed 2006-07-14):
DATALENGTH function in TSQL can be used to find the actual length in bytes of the data in a specific value. The value can be any of the data types. It is often used to determine length of LOB data type columns (text, ntext, image, varchar(max), nvarchar(max) and varbinary(max)) in a table. One of the question that comes up regarding use of DATALENGTH on LOB columns is whether it requires reading the entire value. For example, if the text or varchar(max) value is 16 MB in size does SQL Server nee...(truncated)...
New whitepapers on physical database storage and tempdb...
(Indexed 2006-06-22):
Below are links to two new whitepapers from the storage engine team. Please check it out to get better understanding of the new features in SQL Server 2005 and enhancements. Working with TempDBhttp://download.microsoft.com/download/4/f/8/4f8f2dc9-...(truncated)... Physical Database Storagehttp://download.microsoft.com/download/4/f/8/4f8f2dc9...(truncated)...