SQL Server Performance Tuning
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 […]
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 […]
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 […]
QuestionI’m facing a concurrency problem with SQL Server. If one user is in a transaction and is updating a particular table, then other user’s aren’t able to read any data from that table at all. The other user application’s hang and wait until the first user either commits or rollbacks the transaction. This is causing […]