SQL Server Performance Tuning
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 […]
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 […]
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 […]
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 […]