Pro SQL Server 2022 Wait Statistics by 2023

Author:2023
Language: eng
Format: epub

Chapter 6 IO-related WaIt types

As I mentioned before, SQL Server will attempt to throttle the checkpoint process

to avoid overloading the storage subsystem if it believes this is necessary. It monitors the number of outstanding requests to the storage subsystem and tries to detect if there is any latency. Using this information, it will throttle the amount of IOs the checkpoint process generates so as to avoid a too-heavy load on the storage subsystem. When the checkpoint process is getting throttled, the SLEEP_BPOOL_FLUSH wait type will be

recorded.

SLEEP_BPOOL_FLUSH Example

The following example shows the impact of the SLEEP_BPOOL_FLUSH wait type on SQL

Server versions lower than SQL Server 2016. As mentioned earlier, in SQL Server 2016, the way SQL Server handles the checkpoint process has changed which means it is far

less likely for the wait type to show up in an example like the following.

Generating SLEEP_BPOOL_FLUSH waits is relatively simple, and the script in Listing

6-13, which is almost the same one as we used for the LOGBUFFER and WRITELOG

wait types, will put pressure on the checkpoint process such that SLEEP_BPOOL_FLUSH

waits will occur.

Listing 6-13. Generate SLEEP_BPOOL_FLUSH waits

USE TLog_demo;

DECLARE @i INT

SET @i = 1

WHILE @i < 100

BEGIN

INSERT INTO transactions

(t_guid)

VALUES

(newid())

SET @i = @i + 1

-- Force a checkpoint to occur within 1 second

CHECKPOINT 1

END

184

Chapter 6 IO-related WaIt types

Since we are also using the same database as in the LOGBUFFER and WRITELOG

wait types example, Listing 6-14 shows the script to create the database if it doesn’t exist already.

Listing 6-14. Create TLog_demo database

USE master;

-- Create demo database

CREATE DATABASE [TLog_demo]

ON PRIMARY (

NAME = N'TLog_demo', FILENAME = N'C:\TeamData\TLog_demo.mdf' , SIZE =

153600KB , FILEGROWTH = 10%)

LOG ON ( NAME = N'TLog_demo_log', FILENAME = N'C:\TeamData\TLog_demo.ldf'

, SIZE = 51200KB , FILEGROWTH = 10%);

-- Make sure recovery model is set to full

ALTER DATABASE [TLog_demo] SET RECOVERY FULL;

-- Perform full backup first

-- Otherwise FULL recovery model will not be affected

BACKUP DATABASE [TLog_demo]

TO DISK = N'C:\TeamData\TLog_demo_Full.bak';

-- Create a simple test table

USE TLog_demo;

CREATE TABLE transactions (

t_guid VARCHAR(50) );

What the script in Listing 6-13 will do is perform an insert of a random GUID into the transactions table inside a loop that is executed 100 times. Every time it enters a new GUID, it will issue a CHECKPOINT command with a time limit of 1 second. This forces

the checkpoint process to perform a checkpoint within the 1-second time limit.

Before running the script in Listing 6-13, I cleared the sys.dm_os_wait_stats DMV

using the DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) command.

After almost 70 seconds, the script completed on my test SQL Server. I then executed the following query to take a look at the SLEEP_BPOOL_FLUSH wait times:

SELECT *

FROM sys.dm_os_wait_stats

WHERE wait_type = 'SLEEP_BPOOL_FLUSH';

185

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.