SQL Server Performance Improvement

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

Recommendations

Snapshot Isolation

We highly recommend enabling both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT in your database settings. This configuration allows transactions to work with a consistent snapshot of the data, significantly reducing blocking. While writers will still block other writers, most read operations will not be blocked.

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.

Using READ_UNCOMMITTED

For many application workflows that do not require strict transaction isolation, using READ_UNCOMMITTED mode can eliminate most blocking. This mode allows reading uncommitted changes, which can be suitable for scenarios where absolute accuracy is not critical.

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.

Troubleshooting

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.

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

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.