SQL Server Performance Tuning

How can I help identify the worst performing queries in my SQL Server database application?

This is an easy task using the SQL Server Profiler. Create a new trace in Profiler using the following configuration: Select These Event Classes SQL:BatchCompleted Select These Data Columns Groups Duration Columns EventClass TextData CPU Application Name LoginName NTUserName SPID Select These Filters (as needed) Application Name DatabaseName Duration As the above trace runs, you […]

We have two stored procedures taking five minutes to run in our development database, but the same stored procedures run quickly on our QA database. Why?

Question We have two stored procedures taking about five minutes to run in our development database. The same two stored procedures run quickly on our QA database. Both databases are on the same physical server. What can account for this difference? Answer There are several possibilities: The data is different, which can lead the query […]

When I upgraded by database to the newest version, performance is now much slower. Why?

Most likely, you need to update the statistics of all the indexes in your new SQL Server database. The easiest way to do this is to create a database maintenance plan and select the “Update statistics used by query optimizer” from the “Update Data Optimization Information” screen of the maintenance wizard, then run it now, […]

Our query used to take 30 seconds, and now it takes over 30 minutes. Why?

Question We have a SQL Server stored procedure that normally runs in less than 30 seconds. It has been running fine for over 6 months. But today, at about 11:00 AM, it started to take over 30 minutes to run. I took a Profiler trace of the stored procedure when it ran, and it performed […]

When should primary keys be used?

Question Our department is fairly new to SQL Server. I apologize if these are dumb questions, but I’ve spent hours digging on the Internet and cannot find answers to my questions. When we create our tables, we create a clustered unique index on the column(s) that best fits the table and the way it will […]

Can I programatically tell SQL Server how to allocate CPU resources?

Question My SQL Server is running on a dual processor box. What I would like to do is to use one of the CPUs for background tasks, and the other processor for online tasks. And if there are no background tasks running, I would like both processors to serve online tasks. How can I do […]

For best performance, should we install Analysis Service and the cubes as the same server as the production database?

Question We are currently running a transactional database using SQL Server. We want to set up Analysis Services and several cubes in order to get the reporting burden off the transactional database in order to boost performance. For best performance, should we install Analysis Service and the cubes as the same server, or on another […]

A view runs on our development server in 30 seconds, but it takes over 10 minutes on the production server. Why?

Question We have two SQL Server servers, both with virtually the same hardware. One is a development server, where we create and test Transact-SQL code, and the other is our production SQL Server. Here’s my problem. When a particular view runs on the development server, it takes about 30 seconds. But when I run the […]

T-SQL code in script run faster than it does as a stored procedure. Why?

Question I have some Transact-SQL code that I run as a scheduled job in SQL Server, and it seems to run much longer than I would expect. If I run the same code in Query Analyzer (as a script) it takes about 20 seconds to run. If I put the code in a stored procedure, […]

Why does the performance of some queries vary so widely?

Question I have a query that acts strangely. Most of the time it runs very quickly, in just under a few seconds. But on occasion, it takes over 20 minutes to run. This is rare, but it happens periodically. I have checked and verified that it is the exact same query each time (no difference […]