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

How can I capture performance data over time, and review it later?

Question What is the best way to capture Performance Monitor data and review it over time? I want to be able to produce baseline performance data for my SQL Server so I can compare past performance with current performance. Answer There are a variety of options you can consider. Each has their pros and cons.One […]

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 […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |