SQL Server Performance Tuning
Question I’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 […]
My question is how do I move already existing log files in a different drive, or how can I separate data files and log files into different drives?
Question I am just starting as a DBA in a new company. We are running a single SQL server. We have about 15 databases, the largest being 2 GBs. During peak hours and weekends, there are several hits on the server that affect performance. I found out that the data files and log files are […]
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 […]
Why is it better to use RAID 1 for the operating system, SQL Server binaries, and the swap file? What advantages does RAID 1 have over RAID 5?
Both hardware RAID 1 (mirroring or duplexing) or RAID 5 (striping with parity) offer good data redundancy should a single hard disk in a RAID array fail. And as you might expect, there is some differences in performance between the two. The major difference in performance between RAID 1 and RAID 5 is that RAID […]
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 […]
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 […]
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 […]