SQL Server Performance Tuning

Using Performance Monitor

As you probably already know, SQL Server is very good at tuning itself. It has the ability to monitor itself, and through a feedback loop, it knows how to internally adjust and tune itself so that it keeps running efficiently, even when external events, such as the number of user connections or the amount of […]

General SQL Server Performance Tuning Tips

When your transaction log grows large and you want a quick way to shrink it, try this option. Change the database recovery mode of the database you want to shrink from “full” to “simple,” then truncate the log file by performing a full backup of the database, then switch back to the “full” recovery mode. […]

How to Minimize SQL Server Blocking

Blocking occurs when one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait […]

Reducing SQL Server Deadlocks

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server identifies the problem and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to […]

Reducing SQL Server Locks

if your users are complaining that they have to wait for their transactions to complete, you may want to find out if object locking on the server is contributing to this problem. To do this, use the SQL Server Locks Object: Average Wait Time (ms). You can use this counter to measure the average wait […]

SQL Server Query Execution Plan Analysis

When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query. This information if very valuable when it comes time to find out […]

Using SQL Server Query Analyzer

The Query Analyzer can be used to recommend indexes for specific tables. By entering a query into the Query Analyzer, and running the “Perform Index Analysis” option in 7.0, or the “Index Tuning Wizard” option in 2000, the query will be reviewed, and if appropriate, one or more indexes will be recommended by the Index […]

Tips for Using Performance Monitor Network Counters

Before you can use the network performance counters, the Network Monitor Agent service must be installed on your server. After installing it, you will have to reboot. Also, don’t forget to rerun the Windows Server service pack to update the files added during the installation process.   ***** One of the best ways to monitor […]

Tips for Using SQL Server Performance Monitor Counters

One cause of excess I/O on a SQL Server is page splitting. Page splitting occurs when an index or data page becomes full, and then is split between the current page and a newly allocated page. While occasional page splitting is normal, excess page splitting can cause excessive disk I/O and contribute to slow performance. […]

Tips for Using Performance Monitor Memory Counters

This counter Memory Object: Pages/Sec, measures the number of pages per second that are paged out of RAM to disk, or paged into RAM from disk. The more paging that occurs, the more I/O overhead your server experiences, which in turn can decrease the performance of SQL Server. Assuming that SQL Server is the only […]