SQL Server Performance Tuning
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
We have a growing population of Access users. My question is whether there is a way of lowering these user’s priorities on our SQL Server?
QuestionWe have a growing population of Access users, much to my chagrin! My question is whether there is a way of lowering these user’s priorities on the server/database. We are hoping that if the performance using Access is bad, that this will dissuade users from using Access. AnswerAt our company, we forbid users from accessing [...]
In the past couple of weeks, my SQL Server seems to be running slower. Queries just don’t seem to run as fast. What should I do?
While this is a simple question, it is not an easy one to answer. If fact, one of the reasons I developed this website was to help answer this question. It would take a very long article to address all the potential causes of a SQL Server getting slower over time (which won’t be done [...]