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

I am having a problem with table locking that is causing concurrency problems.

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

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

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

I need help to justify new hardware for our SQL Server.

Question I am a new DBA in my company. My department needs a new server to run SQL Server and we must make a proposal to our owner in order to justify the cost of replacing the old server. I have read every article on your web site, but I still don’t know how to […]

Will placing the MDF files and the LDF files on two different physical hard drives help performance?

QuestionI am running SQL Server on a Windows 2000 server box which has a single CPU with more than one-half GB RAM. I have a problem with SQL taking the lion’s share of RAM and making other CPU and memory intensive applications starve. My main application database is 1.5 GB and is growing at the […]

We have had some performance problems with a server that is running Terminal Services, SQL Server, and Access databases.

Question We have had some performance problems with a server that is running Terminal Services, SQL Server, and is also the repository for multiple Access and Access databases.I have had some advice from SQL Server gurus that running SQL Server and Terminal Services on the same server is a no-no. The Compaq SQL box has […]