SQL Server Performance Improvement

This section provides insights and recommendations for improving SQL Server performance, focusing on reducing blocking and optimizing database operations.

Analyzing Blocking

To get a sum of all blocking, use the following query:

SELECT OBJECT_NAME(o.object_id), i.name, row_lock_wait_in_ms + page_lock_wait_in_ms AS "milliSecondsBlockedSinceRestart"

FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) o

JOIN sys.indexes i ON i.object_id = o.object_id AND i.index_id = o.index_id

ORDER BY row_lock_wait_in_ms + page_lock_wait_in_ms DESC

This query provides a detailed view of blocking times for each index, helping identify potential bottlenecks.

Snapshot Isolation

Snapshot isolation can significantly reduce blocking by allowing readers to access the last committed version of a row without being blocked by writers. However, writers will still block other writers, and schema changes or index creation will still cause blocking.

To enable snapshot isolation, execute the following commands:

ALTER DATABASE MyDbName SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE MyDbName SET READ_COMMITTED_SNAPSHOT ON;

This change allows sessions in the READ_COMMITTED isolation mode (the default) to automatically use snapshot isolation, requiring no application changes. It is highly recommended for reducing blocking.

Using READ_UNCOMMITTED

Many application workflows do not require transaction isolation and can be executed in READ_UNCOMMITTED mode, which eliminates most blocking. This mode allows reading uncommitted changes, which can be suitable for scenarios where absolute accuracy is not critical.

Understanding SLEEP_BPOOL_FLUSH

The SLEEP_BPOOL_FLUSH wait type should generally be ignored. It is part of SQL Server's mechanism to categorize session blocking. This wait type is not necessarily indicative of a user transaction waiting.

The buffer pool is used for caching database pages. When a page is updated, it is marked as dirty and eventually written back to disk. If the disk response time exceeds 20ms, the process throttles itself. A response time of 24ms, for example, is not considered excessive.

HADR_SYNC_COMMIT Considerations

HADR_SYNC_COMMIT waits can occur during indexing operations. The ALTER INDEX ... REBUILD command is an offline operation, making the table inaccessible during the process. It is recommended to perform such operations during off-hours.

For on-hours work, consider using online index operations, which take short-lived locks, allowing the table to remain accessible. If the HADR_SYNC_COMMIT waits are still unsatisfactory, consider discussing asynchronous commit mode.

By implementing these strategies, you can enhance SQL Server performance, reduce blocking, and ensure smoother database operations.