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

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

Is there a way to control which CPU performs what tasks in SQL Server?

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

Is it normal for the buffer cache size to vary throughout the day?

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