SQL Server Performance Tuning

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 […]

Why does a query executed from VB run more slowly than when run from Query Analyzer?

Question I have a VB-based application that uses SQL Server as its back-end. I have noticed that if I run the exact Transact-SQL queries (taken from the VB app) in Query Analyzer, that they run must faster than they do from within the VB application. Why is this? Answer There can be many reasons why […]

What is the difference between a table and index scan in an execution plan?

Question When I create a graphical query execution plan of a query, I notice that there are two types of scans: Table Scans and Index Scans. How are these different? AnswerWhen the Query Optimizer is asked to optimize a query and create an execution plan for it, it tries its best to use an Index […]

What are the pros and cons of running more than one instance of SQL Server on a single physical server?

Running multiple instances of SQL Server on the same physical server should generally be avoided because each of the instances will have to fight over hardware resources, hurting the performance of all the instances. Generally speaking, there are two reasons why you might want to run multiple instances on the same physical server. 1) You […]

We run an ERP program that uses SQL Server as the backend database. Does how the ERP program is designed and written affect its performance with SQL Server?

Very much so. In fact, I would go as far as to say that if you are experiencing performance problems with your ERP application, that it is the biggest source of the performance problem, not SQL Server or your hardware (although they could be contributing to the problem). I have seen this over and over […]

The tempdb database grows so large I run out of disk space. What can I do?

Question From time to time, my tempdb database grows so large that it runs out of disk space, which causes the connection that is using the tempdb to die. The tempdb can get as large as almost 6 GB, and the server’s performance really suffers during this process, until, of course, when the connection is […]

How can I determine if a table is pinned in SQL Server memory or not?

This should be an easy task, but as far as I can tell, there is no really easy way to do this. In the following demo code, I will give an example of how to do this, although it is a lot of trouble. –First, the following code pins the “authors” table in pubs: DECLARE […]

Should I use CPU or elapsed time to measure query performance?

Question During query performance tuning, when comparing the “SQL Server Execution Times” of different runs of a query, should I be comparing the CPU Time or the Elapsed Time, or both? Answer CPU time measures the amount of CPU resources used by the query when it ran, measured in milliseconds. Elapsed Time, also measured in […]

What is the difference between SQL Server 2000 standard and enterprise editions?

Question Does SQL Server 2000 Enterprise Edition offer any performance advantages over the SQL Server 2000 Standard Edition? Answer There are several areas where SQL Server 2000 Enterprise Edition has a performance advantage of SQL Server 2000 Standard Edition. SQL Server 2000 Enterprise Edition offers these performance-related features not available in other editions of SQL […]

Why doesn’t query parellelism use all of the available CPUs?

Question I have a complex query that, according to the execution plan, uses parallelism, which is what I would expect. But when the query actually runs, it doesn’t appear to use all of the available CPUs to their full potential. For example, when I watch Performance Monitor when I run the query, it appears that […]