SQL Server Performance Tuning
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]