MVP Jonathan Kehayias brings us a short article on how to troubleshoot deadlocks in SQL Server.  Read more...

Handling the paging of results in T-SQL has been a challenge for a long time. Robert Cary presents an interesting technique in this article.  Read more...

This article describes a utility that allows you to inspect cached plans and extract information that can be useful in improving the performance of your SQL.  Read more...

Why does everyone use "it depends" as an answer to many T-SQL questions? Bob Hovious brings us a short example of how performance can change based on data loads for the same code.   Read more...

Does it make sense to set up another instance of SQL Server just for a power user? Is it a good use of resources? Steve Jones comments today on this DBA tool for enhancing performance.  Read more...

Do your system contain a time bomb in the code? David Poole looks at some potential situations where you might be experiencing performance issues and delves into why.  Read more...

Steve Jones talks a little about disk alignment for your SQL Server instances. This is something that you might to check on your systems and see if you can improve performance.  Read more...

It’s time for us to learn how to diagnose different performance and related problems in SQL Server database. Take a look at the following articles to learn the step by step process that we’ve already carried out so far.   Read more...

Even after you have applied proper indexing along with re-factoring your TSQLs with best practices, some data access routines might still be there, which would be expensive, in terms of their execution time. There must have been some smart ways to deal with these. Yes there are. SQL server offers you some rich indexing techniques that you might have not used earlier. These could surprise you with the performance benefits they possibly offer. Let us start implementing those advanced indexing techniques:  Read more...

In this article, we are going to focus on writing or refactoring the data access codes using the best practices. But, before we start playing the game, we need to prepare the ground first. So let’s do the groundwork at this very next step:  Read more...

’ll focus on the issues that you need to follow to make sure that you have done everything that you could do to optimize the data access codes you have written or you are going to write in future. The Database Administrators (DBA) also has great roles to play in optimizing and tuning the database performance. But, optimization scopes that fall into a DBA’s area are out of scope for these articles.  Read more...

There are situations in which you need to individually handle each row in a result set. SQL Server 2008 provides a minimal set of tools — cursors and WHILE loops — to accomplish this task. I'll take a closer look at each option and explain why it's difficult to pinpoint which is better in terms of performance.  Read more...

This article describes a method of gathering and analyzing performance data using SQL Trace.  Read more...

Indexing is important for improving performance, but do you know that a lack of indexes might actually cause things to stop working? Alok Dwivedi brings us a very interesting scenario where the lack of an index causes a problem with concurrency.  Read more...

The next time you are tempted to hammer a performance problem with hardware, why not instead invest that time in hammering and refining your SQL queries? There are rich rewards to be had.  Read more...

Enable Lock Pages in memory on Standard edition was ONLY an Enterprise feature until now. Read this article to see how you enable this setting in SQL Server.  Read more...

SQL WHERE clause optimization is important to get maximum performance from a query. If an index exists on a column you obviously want the query to use it...   Read more...

Change data capture is a new feature in SQL Server 2008 that provides an easy way to capture changes to data in a set of database tables so these changes can be transferred to a second system like a data warehouse. This document provides guidance on how to configure change data capture parameters to maximize data capture performance while minimizing the performance impact on the production workload. The scope of this document is limited to the capture of change data and the cleanup process. Querying the changed data is out of scope for this white paper.  Read more...

Whether a query uses Scan or Seek can have a big impact on the cost of excuting that query. This article examines when each is used and which is optimal.  Read more...

In this white paper we discuss two of the new features in SQL Server 2008, Star Join and Few-Outer-Row optimizations. We test the performance of SQL Server 2008 on a set of complex data warehouse queries designed to highlight the effect of these two features and observed a significant performance gain over SQL Server 2005 (without these two features).  Read more...