SQL Server Performance Tuning

Is there a way to control which CPU performs what tasks in SQL Server?

Question Our SQL Server is dual processor server and I notice that overnight when we run certain jobs than one CPU is favored over the other. The favored CPU varies from day to day, but if I look at the difference between the CPU’s utilization, there is a definite trend to favor one CPU at […]

Is it normal for the buffer cache size to vary throughout the day?

Question When I monitor my SQL Server, I can see that the buffer cache size (number of pages) grows through out the day as the number of user connections increase. This cache size varies between 2-3MB to 30-35MB. The server has 256MB and is dedicated to running SQL Server. The buffer cache hit ratio is […]

Do you have a recommendation for the maximum number of databases you can place on a single SQL Server?

Question Do you have a recommendation for the maximum number of databases you can place on a single SQL Server? I understand the memory and drive size requirements are big factors, but is there a maximum recommended number of actual databases you should place on a single SQL Server instance on a single server? AnswerThe total […]

When I run a stored procedure in Query Analyzer, it runs in about two minutes. But if I execute the same stored procedure as a job, it runs several hours.

Question When I run a particular stored procedure in Query Analyzer, it runs in about two minutes.  But if I execute the same stored procedure as a scheduled job, it runs several hours before it completes. What could be the obvious reasons for this? AnswerWhen I first got this question, I didn’t have an answer. […]

We have five SQL servers running on different physical servers. What are the downfalls for combining these SQL servers onto a single physical server?

Question We have five SQL servers running on five different physical servers. What are the downfalls for combining these SQL servers together onto a single physical server? Also, is it possible to run multiple instances of SQL Server on the same server? Answer The answer to this question depends on if the single physical server has […]

Is it a good idea to run both SQL Server and IIS on the same physical server?

Like so many questions, the answer to this question is “it depends.” For ideal performance, and assuming your have a big enough budget, you should put SQL Server and IIS on their own dedicated servers (properly sized). But if you don’t have the budget for two servers, then one will have to suffice, at least […]

Is there any option to release the additional memory that has been used by SQL Server after a query is completed?

Question We are using SQL Server 2000 as our back-end and an application called VFP as our front-end. At times, the application’s performance is very poor. We used Task Manager to identify that the sqlservr.exe process appears to use up memory when running queries, but it appears not to release the memory after the queries […]

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