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

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

Should I schedule the indexes in my SQL Server databases to be rebuilt before or after the statistics on my tables are updated (or does it matter)?

If you are using SQL Server and if you have the database options “Auto Create Statistics” and “Auto Update Statistics” turned on (which they should be for best overall performance), then you normally don’t need to manually update statistics. In addition, if you rebuild a table’s indexes (which should be done often, such as once […]

We have a started experiencing a performance issue with a table that has now grown to over 600,000 rows.

Question We have a started experiencing a performance issue with a table that has now grown to over 600,000 rows. Performance has become unsatisfactory when accessing this table. I haven’t ever run DBCC INDEXDEFRAG on this table. I was wondering if after running it on this table, if performance will improve? Does the performance problem […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |