SQL Server Engine Tips

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...

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...

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...

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...

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...

Check out the new Books Online search functionality online. The link below provides a scoped search of Books Online that returns a more precise and targeted result set. You can use it to search Books Online content quickly. http://search.live.com/macros...

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...

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...

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...

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...

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...

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=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en Note that the December CTP has been published...

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.mspx SQL Server 2005 SP2...

I have disabled anonymous comments due to the inordinate number of spam comments. And since I had email notifications for comments it was pretty bad. Hopefully this is fine.

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...

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...

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...

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...

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 TempDB http://download.microsoft.com/download/4/f/8/4f8f2dc9...

SQL Server 2005 adds new capabilities to troubleshoot deadlocks. I talked about the new trace flag #1222 in a previous post. The blog entry below from the SQL Server Storage Engine team talks about the new trace flag and provides some examples on how...

SQL Server 2005 has new optimization logic to use the SCHEMABINDING option to derive certain properties about the TSQL UDF. This can greatly improve performance of queries that use scalar UDFs in a SELECT statement for example. You can read all about...

I saw an interesting question today in the MSDN Transact-SQL forum about implementing PERCENTILE_CONT analytic function that is available in Oracle in SQL Server. The function description as noted in the Oracle docs is below: The PERCENTILE_CONT...

Index Tuning Wizard (ITW in SQL Server 2000) or Database Engine Tuning Advisor (DTA in SQL Server 2005) allow you to analyze a workload and make recommendations for the database based on the workload. The recommendations are schema changes, partitioning...

Did you know that SQL Server 2005 adds a new trace flag (-T1222) for deadlock trace output? This deadlock output has numerous improvements over older versions of SQL Server. For example, some of the enhancements of the new grace flag are: 1. XML...

I recently moved to the Central Performance Team in the SQL Server product group. My group is responsible for box-wide performance, benchmark and scalability. Given my new role I will be posting more of performance tips and techniques. I will be covering...