SQL Server Performance Tuning

Our SQL Server is experiencing lots of blocking, but we don’t have any resource bottlenecks.

Question I am the DBA for a SQL Server that is running a two-node active/passive cluster with four CPU’s and 3GB or RAM on each. The server keeps up with demand most of the time. But once in a while we experience blocking issues and new connections are timing out–not a good thing. Our system […]

In Performance Monitor, there is an object called Network Interface. What does it mean and under what circumstances would you use it?

The Network Interface object available from Performance Monitor (System Monitor) is used to monitor activity in each of the network cards in your server. If you have more than one network card in your server, you will see one instance of each counter for every network card you have. While this particular performance object has […]

We have recently added a second processor to our SQL Server box. How can I configure it to take advantage of the new processor?

You don’t have to do anything, assuming none of the default SQL Server settings have been changed. SQL Server will automatically take advantage of all the CPUs in your server. To find out how your SQL Server is currently configured, right-click on the server in Enterprise Manger or Management Studio, and select “Properties”. Next, select […]

Why isn’t my SQL Server taking full advantage of all the RAM I have available in my server?

QuestionI am the DBA of a SQL Server that has the memory setting configured to “Dynamically configure SQL Server memory, and the “Maximum Memory” set to 905MB out of a possible 1151MB. When I look at the Cache Size (pages) counter, it runs about 115MB. In addition, the Buffer Cache Hit Ratio counter is fine. Why […]

Is there a practical limit to the size of a table I can pin into memory?

Question I have read about pinning frequently accessed tables into memory rather than let them being accessed from the disk all the time. But what I wish to know is if there is any cutoff limit for the size of a table that could make it a bad candidate for pinning? Answer By default, SQL […]

Is there any benefit for me in turning on the “lightweight pooling” SQL Server configuration option?

QuestionWe are running SQL Server on a server has 4 CPUs and 2GB RAM. Generally, CPU utilization rarely gets over 80%, and when it does, it’s not for more than 60 seconds at a time. Given all this, is there any benefit for me in turning on the “lightweight pooling” SQL Server configuration option? AnswerBy […]

Is it better to have one large server running multiple databases, or several smaller servers running one database each?

Assuming you have the budget to spend, you will get better performance by “scaling out” your SQL Servers onto multiple SQL Server servers than running them all on the same physical server. The reason why this is true is obvious to DBAs, but trying to justify this extra cost to managers or accountants is not […]

Is there any way to allocate processor utilization to SQL Server jobs?

Question In the past couple of weeks, we have been running some jobs that take a lot of the CPU usage. I would like to know if there is any possible way to :1. Distribute between processors, the scheduled jobs.2. Minimize the maximum amount of CPU usage that a job can take.If there are any […]

Can you tell me more about the processor queue length?

QuestionI am a student at Virginia Tech and am writing a research paper for a class of mine about database performance issues. I found an article of yours online that was very informative and I was going to use it as a reference, but I have a question that I hope you could answer for […]

Is it recommended to install SQL Server on a Windows domain controller?

QuestionIs it recommended to install SQL Server on a Windows domain controller? The DC is a Compaq ML 370 2x9Gb(RAID1) + 4x18Gb(RAID5), and is in a remote office supporting 20 users. The DC also doubles as a file server. Any assistance will be greatly appreciated. AnswerWhile SQL Server can run on a domain controller, it […]