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

I am hoping that you may be able to assist me in evaluating the needs of our school district that is purchasing a software package that uses SQL Server as the backend database.

Question I am hoping that you may be able to assist me in evaluating the needs of our school district that is purchasing a software package that uses SQL Server as the backend database. Currently, we have one server per school that acts as the authentication server, printer server, file server, and application server. None […]

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

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