SQL Server Performance Tuning

Why doesn’t query parellelism use all of the available CPUs?

Question I have a complex query that, according to the execution plan, uses parallelism, which is what I would expect. But when the query actually runs, it doesn’t appear to use all of the available CPUs to their full potential. For example, when I watch Performance Monitor when I run the query, it appears that […]

Why is my Memory: pages/sec counter exceeding 20 for my SQL Server?

Question I have a server running SQL Server 2000, SP3. I recently used Performance Monitor to track the Memory: pages/sec counter to see if I have a paging problem. What surprised me what that the pages/sec were running over 200, which is way above the maximum value of 20 suggested on your website. What is […]

Are more CPUs better than fewer CPUs for the best performance from SQL Server?

Question All other things being equal, which of the following CPU options is the better choice for heavy-duty queries in SQL Server 2000: –A single Xeon CPU running at 2.4 GHz (with 512KB L2 cache)–Quad Xeon running at 550 MHz (with 1MB L2 cache) Also assume that the server would have 1GB of RAM. Answer […]

Why do I get error messages when my SQL Server is running under a heavy load.

Question I am running SQL Server 2000 on Windows 2000 with 1GB of RAM on a dedicated server, but I am getting the following error message when doing backup and running simple queries like ALTER TABLE. What can be the problem? Server: Msg 845, Level 17, State 1, Line 1Time-out occurred while waiting for buffer […]

How do I know if my table has redundant indexes?

Question I have been taking a look at the queries run against a particular table, along with the indexes on the table, and I have discovered the following: 1) Query #1 uses a composite index that includes a three column index. 2) Query #2 uses a composite index that includes a two column index. 3) […]

Each night we have to run a job that takes about 10 hours to complete. As it runs, it uses 100% of the CPU. What can I do?

Question Each night we have to run a job that takes about 10 hours to complete. As it runs, it uses 100% of the CPU. Obviously, other users are unable to access SQL Server during this time period. What can we do to speed SQL Server up? Answer I see lots of questions like this […]

DBCC INDEXDEFRAG doesn’t seem to work like it should.

Question I have a very large table, over 30 million records, that has a logical fragmentation of about 60%, and this is even if I run DBCC INDEXDEFRAG on it. Is this normal, and if not, what can I do about reducing the fragmentation? Answer In SQL Server 2000, Microsoft introduced DBCC INDEXDEFRAG to help […]

At what point should I worry about page splits, and what can I do to help reduce them?

For those not familiar with page splits, let’s first take a look at what they are. Whenever you INSERT new data into a SQL Server table, or in some cases, UPDATE data in a SQL Server table, there may not enough room for the newly INSERTed or UPDATEd row in the applicable data page. In […]

When should I include the WITH RECOMPILE option when creating a stored procedure?

Whenever a stored procedure is run in SQL Server for the first time, it is optimized and a query plan is compiled and cached in SQL Server’s memory. Each time the same stored procedure is run after it is cached, it will use the same query plan, eliminating the need for the same stored procedure […]

My application is very INSERT heavy. What can I do to speed up the performance of INSERTs?

Here are a variety of tips that can help speed up INSERTs. 1) Use RAID 10 or RAID 1, not RAID 5 for the physical disk array that stores your SQL Server database. RAID 5 is slow on INSERTs because of the overhead of writing the parity bits. Also, get faster drives, a faster controller, […]

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 |