Track page splits

If you intend to fine-tune the fill factor for important tables to maximize the performance/storage space ratio, you can measure page splits in two ways: with a query on a DMV (discussed here), and with an Extended Event session (covered later in this chapter).

You can use the performance counter DMV to measure page splits in aggregate on Windows Server, as shown here:

Click here to view code image

SELECT * FROM sys.dm_os_performance_counters WHERE counter_name ='Page Splits/sec';

The cntr_value increments whenever a page split is detected. This is a bit misleading because to calculate the page splits per second, you must sample the incrementing value twice and divide by the time difference between the samples. When viewing this metric in Performance Monitor, the calculation is done for you.

You can also track page_split events alongside statement execution by adding the page_split event to sessions such as the Transact-SQL (T-SQL) template in the Extended Events wizard. You’ll see an example of this later in this chapter, in the section “Use Extended Events to detect page splits.”

• Extended Events and the sys.dm_os_performance_counters DMV are discussed in more detail later in this chapter in the section “Query performance metrics with DMVs.” This section also includes a sample session script to track page_split events.

Rebuild indexes

Performing an INDEX REBUILD operation on a rowstore index (clustered or nonclustered) physically re-creates the index B-tree leaf level. The goal of moving the pages is to make storage more efficient and to match the logical order provided by the index key. A rebuild operation is destructive to the index object and blocks other queries attempting to access the pages unless you provide the ONLINE option. Because the rebuild operation destroys and re-creates the index, it must update the index statistics afterward, eliminating the need to perform a subsequent UPDATE STATISTICS operation as part of regular maintenance.

Long-term table locks are held during the rebuild operation. One major advantage of SQL Server Enterprise edition remains the ability to specify the ONLINE option, which allows for rebuild operations that are significantly less disruptive to other queries, though not completely. This makes index maintenance feasible on SQL Servers with round-the-clock activity.

Consider using ONLINE with index rebuild operations whenever short maintenance windows are insufficient for rebuilding fragmented indexes offline. An online index rebuild, however, might take longer than an offline rebuild. There are also scenarios for which an online rebuild is not possible, including deprecated data types image, text, and ntext, or the xml data type. Since SQL Server 2012, it has been possible to perform ONLINE index rebuilds on the max lengths of the data types varchar, nvarchar, and varbinary.

For the syntax to rebuild the FK_Sales_Orders_CustomerID nonclustered index on the Sales.Orders table with the ONLINE functionality in Enterprise edition, see the following code sample:

Click here to view code image

ALTER INDEX FK_Sales_Orders_CustomerID
ON Sales.Orders
REBUILD WITH (ONLINE=ON);

It’s important to note that if you perform any kind of index maintenance on the clustered index of a rowstore table, it does not affect the nonclustered indexes. Nonclustered index fragmentation will not change if you rebuild the clustered index.

Instead of rebuilding each index on a table individually, you can rebuild all indexes on a table by replacing the name of the index with the keyword ALL. For example, to rebuild all indexes on the Sales.OrderLines table, do the following:

Click here to view code image

ALTER INDEX ALL ON [Sales].[OrderLines] REBUILD;

This is usually overkill and inefficient, however, because not all indexes may have the same level of fragmentation or need for maintenance. Remember, we should perform index maintenance as granularly as possible.

For memory-optimized tables, we recommend a manual routine maintenance step using the ALTER TABLE … ALTER INDEX … REBUILD syntax. This is not to reduce fragmentation in the in-memory data; rather, it is to examine the number of buckets in a memory-optimized table’s hash indexes. For more information on rebuilding hash indexes and bucket counts, see Chapter 15.

Aside from ONLINE, there are other options that you might want to consider for rebuild operations. Let’s look at them:

• SORT_IN_TEMPDB. Use this when you want to create or rebuild an index using tempdb for sorting the index data, potentially increasing performance by distributing the I/O activity across multiple drives. This also means that these sorting worktables are written to the tempdb database transaction log instead of the user database transaction log, potentially reducing the impact on the user database log file, and allowing for the user database transaction log to be backed up during the operation.

• MAXDOP. Use this to mitigate some of the impact of index maintenance by preventing the operation from using parallel processors. This can cause the operation to run longer, but to have less impact on performance.

• WAIT_AT_LOW_PRIORITY. First introduced in SQL Server 2014, this is the first of a set of parameters that you can use to instruct the ONLINE index maintenance operation to try not to block other operations. This feature is known as Managed Lock Priority, and this syntax is not usable outside of online index operations and partition-switching operations. (SQL Server 2022 also introduced the ability to use WAIT_AT_LOW_PRIORITY for DBCC SHRINKDATABASE and DBCC SHRINKFILE operations.) Here is the full syntax:

  Click here to view code image

  ALTER INDEX PK_Sales_OrderLines on [Sales].[OrderLines]
  REBUILD WITH (ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES,
  ABORT_AFTER_WAIT = SELF)));

  The parameters for MAX_DURATION and ABORT_AFTER_WAIT instruct the statement on how to proceed if it begins to be blocked by another operation. The online index operation will wait, allowing other operations to proceed.

  The ABORT_AFTER_WAIT parameter provides an action at the end of the MAX_DURATION wait:

  • SELF instructs the statement to terminate its own process, ending the online rebuild step.

  • BLOCKERS instructs the statement to terminate the other process that is being blocked, terminating what is potentially a user transaction. Use with caution.

  • NONE instructs the statement to continue to wait. When combined with MAX_DURATION = 0, it is essentially the same behavior as not specifying WAIT_AT_LOW_PRIORITY.

• RESUMABLE. Introduced in SQL Server 2017, this feature makes it possible to initiate an online index creation or rebuild that can be paused and resumed later, even after a server shutdown. You can also specify a MAX_DURATION in minutes when starting an index rebuild operation, which will pause the operation if it exceeds the specified duration. You cannot specify the ALL keyword for a resumable operation. The SORT_IN_TEMPDB=ON option is not compatible with the RESUMABLE option.

To leverage resumable index maintenance operations, you can see a list of resumable and paused index operations in a new DMV, sys.index_resumable_operations, where the state_desc field will reflect RUNNING (and pausable) or PAUSED (and resumable).

Here is a sample scenario of a paused/resumed index maintenance operation on a large table in the sample WideWorldImporters database:

Click here to view code image

ALTER INDEX PK_Sales_OrderLines on [Sales].[OrderLines]
REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

From another session, show that the index rebuild is RUNNING with the RESUMABLE option:

Click here to view code image

SELECT object_name = object_name (object_id), *
FROM sys.index_resumable_operations;

From a third session, run the following to pause the operation:

Click here to view code image

ALTER INDEX PK_Sales_OrderLines on [Sales].[OrderLines] PAUSE;

You can then show that the index rebuild is paused:

Click here to view code image

SELECT object_name = object_name (object_id), * FROM sys.index_resumable_operations;

This sample is on a relatively small table, and may not allow you to execute the pause before the index rebuild is completed. This will result in a disconnection of the session of the original index maintenance, and a severe error message. In the SQL Server Error Log, the event is not a severe error message, but an informative note that “An ALTER INDEX ‘PAUSE’ was executed for….”

To resume the index maintenance operation, you have two options:

• Reissue the same index maintenance operation, which will warn you it’ll just resume instead.

  Click here to view code image

  ALTER INDEX PK_Sales_OrderLines on [Sales].[OrderLines] REBUILD
  WITH (ONLINE = ON, RESUMABLE = ON);

• Issue a RESUME to the same index.

  Click here to view code image

  ALTER INDEX PK_Sales_OrderLines on [Sales].[OrderLines] RESUME;

Reorganize indexes

Performing a REORGANIZE operation on an index uses fewer system resources and is much less disruptive than performing a full rebuild, while still accomplishing the goal of reducing fragmentation. It physically reorders the leaf-level pages of the index to match the logical order. It also compacts pages to match the fill factor on the index, though it does not allow the fill factor to be changed. This operation is always performed online, so long-term table locks (except for schema locks) are not held, and queries or modifications to the underlying table or index data will not be blocked by the schema lock during the REORGANIZE transaction.

Because the REORGANIZE operation is not destructive, it does not automatically update the statistics for the index afterward as a rebuild operation does. Thus, you should always follow a REORGANIZE step with an UPDATE STATISTICS step.

• For more on statistics objects and their impact on performance, see Chapter 15.

The following example presents the syntax to reorganize the PK_Sales_OrderLines index on the Sales.OrderLines table:

Click here to view code image

ALTER INDEX PK_Sales_OrderLines on [Sales].[OrderLines] REORGANIZE;

None of the options available to rebuild that we covered in the previous section are available to the REORGANIZE command. The only additional option that is specific to REORGANIZE is the LOB_COMPACTION option. It compresses large object (LOB) data, which affects only LOB data types: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. By default, this option is enabled, but you can disable it for non-heap tables to potentially skip some activity, though we do not recommend it. For heap tables, LOB data is always compacted.

Update index statistics

SQL Server uses statistics to describe the distribution and nature of the data in tables. The Query Optimizer needs the auto create setting enabled (it is enabled by default) so it can create single-column statistics when compiling queries. These statistics help the Query Optimizer create the most optimal query plans at runtime. The auto update statistics option prompts statistics to be updated automatically when accessed by a T-SQL query. This only occurs when the table is discovered to have passed a threshold of rows changed. Without relevant and up-to-date statistics, the Query Optimizer might not choose the best way to run queries.

An update of index statistics should accompany INDEX REORGANIZE steps to ensure that statistics on the table are current, but not INDEX REBUILD steps. Remember that the INDEX REBUILD command also updates the index statistics.

The basic syntax to update the statistics for an individual table is as follows:

Click here to view code image

UPDATE STATISTICS [Sales].[Invoices];

The only command option to be aware of concerns the depth to which the statistics are scanned before being recalculated. By default, SQL Server samples a statistically significant number of rows in the table. This sampling is done with a parallel process starting with database compatibility level 150. This is fast and adequate for most workloads. You can optionally choose to scan the entire table by specifying the FULLSCAN option, or a sample of the table based on a percentage of rows or a fixed number of rows using the SAMPLE option, but these options are typically reserved for cases of unusual data skew where the default sampling may not provide adequate coverage for your column or index.

You can manually verify that indexes are being kept up to date by the Query Optimizer when auto_create_stats is enabled. The sys.dm_db_stats_properties DMF accepts an object_id and stats_id, which is functionally the same as the index_id, if the statistics object corresponds to an index. The sys.dm_db_stats_properties DMF returns information such as the modification_counter of rows changed since the last statistics update, and the last_updated date, which is NULL if the statistics object has never been updated since it was created.

Not all statistics are associated with an index, such as statistics that are automatically created. There will generally be more statistics objects than index objects. This function works in SQL Server and Azure SQL Database. You can easily tell if a statistics object (which you can gather from querying sys.stats) is automatically created by its naming convention, WA_Sys_<column_name>_<object_id_hex>, or by looking at the user_created and auto_created columns in the same view.

• For more on statistics objects and their impact on performance, see Chapter 15.

Reorganize columnstore indexes

You must also maintain columnstore indexes, but these use different internal objects to measure the fragmentation of the internal columnstore structure. Columnstore indexes need only the REORGANIZE operation. For more on designing columnstore indexes, see Chapter 15.

You can review the current structure of the groups of columnstore indexes by using the DMV sys.dm_db_column_store_row_group_physical_stats. This returns one row per row group of the columnstore structure. The state of a row group, and the current count of row groups by their states, provides some insight into the health of the columnstore index. Most row group states should be COMPRESSED. Row groups in the OPEN and CLOSED states are part of the delta store and are awaiting compression. These delta store row groups are served up alongside compressed data seamlessly when queries use columnstore data.

The number of deleted rows in a rowgroup is also an indication that the index needs maintenance. As the ratio of deleted rows to total rows in a row group that is in the COMPRESSED state increases, the performance of the columnstore index will be reduced. If delete_rows is larger than or greater than the total rows in a rowgroup, a REORGANIZE step will be beneficial.

Performing a REBUILD operation on a columnstore index is essentially the same as performing a drop/re-create and is not necessary. However, if you want to force the rebuild process, using the WITH (ONLINE = ON) syntax is supported starting with SQL Server 2019 for rebuilding (and creating) columnstore indexes. A REORGANIZE step for a columnstore index, just as for a nonclustered index, is an online operation that has minimal impact to concurrent queries.

You can also use the REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS=ON) option to force all delta store row groups to be compressed into a single compressed row group. This can be useful when you observe many compressed row groups with fewer than 100,000 rows.

Without COMPRESS_ALL_ROW_GROUPS, only compressed row groups will be compressed and combined. Typically, compressed row groups should contain up to one million rows each, but SQL might align rows in compressed row groups that align with how the rows were inserted, especially if they were inserted in bulk operations.

• We talk more about automating index maintenance in Chapter 9.

Shrink data files

Try to proactively grow database files to avoid autogrowth events altogether. You should shrink a data file only as a one-time event to solve one of three scenarios:

• A drive volume is out of space and, in an emergency break-fix scenario, you reclaim unused space from a database data or log file.

• A database transaction log grew to a much larger size than is normally needed because of an adverse condition and should be reduced back to its normal operating size. An adverse condition could be a transaction log backup that stopped working for a timespan, a large uncommitted transaction, or a replication availability group issue that prevented the transaction log from truncating.

• For the rare situation in which a database had a large amount of data deleted from the file, an amount of data that is unlikely ever to exist in the database again, a one-shrink file operation might be appropriate.

Shrink transaction log files

For the case in which a transaction log file should be reduced in size, the best way to reclaim the space and re-create the file with optimal virtual log file (VLF) alignment is to first create a transaction log backup to truncate the log file as much as possible. If transaction log backups have not recently been generated on a schedule, it may be necessary to create another transaction log backup to fully clear out the log file. Once empty, shrink the log file to reclaim all unused space, then immediately grow the log file back to its expected size in increments of no more than 8,000 MB at a time. This allows SQL Server to create the underlying VLF structures in the most efficient way possible.

• For more information on VLFs in your database log files, see Chapter 3.

The following sample script of this process assumes a transaction log backup has already been generated to truncate the database transaction log and that the database log file is mostly empty. It also grows the transaction log file backup to 9 GB (9,216 MB or 9,437,184 KB). Note the intermediate step of growing the file first to 8,000 MB, then to its intended size.

Click here to view code image

USE [WideWorldImporters];
--TRUNCATEONLY returns all free space to the OS
DBCC SHRINKFILE (N'WWI_Log' , 0, TRUNCATEONLY);
GO
USE [master];
ALTER DATABASE [WideWorldImporters]
MODIFY FILE ( NAME = N'WWI_Log', SIZE = 8192000KB );
ALTER DATABASE [WideWorldImporters]
MODIFY FILE ( NAME = N'WWI_Log', SIZE = 9437184KB );
GO

Monitor wait type aggregates

To view accumulated waits for a session, which live only until the close or reset of the session, use the sys.dm_exec_session_wait_stats DMV.

In sys.dm_exec_sessions, you can see the current wait type and most recent wait type, but this isn’t always that interesting or informative. Potentially more interesting would be to see all the accumulated wait stats for an ongoing session. This code sample shows how the DMV returns one row per session, per wait type experienced, for user sessions:

Click here to view code image

SELECT * FROM sys.dm_exec_session_wait_stats
ORDER BY wait_time_ms DESC;

There is a distinction between the two time measurements in this query and others. The value from signal_wait_time_ms indicates the amount of time the thread waited on CPU activity, correlated with time spent in the runnable state. The wait_time_ms value indicates the accumulated time in milliseconds for the wait type, including the signal_wait_time_ms, and so includes time the request spent in the runnable and suspended states. Typically, wait_time_ms is the wait measurement that we aggregate. The waiting_tasks_count is also informative, indicating the number of times this wait_type was encountered. By dividing wait_time_ms by waiting_tasks_count, you can get an average number of milliseconds (ms) each task encountered this wait.

You can view aggregate wait types at the instance level with the sys.dm_os_wait_stats DMV. This is the same as sys.dm_exec_session_wait_stats, but without the session_id, which includes all activity in the SQL Server instance without any granularity to database, query, time frame, and so on. This can be useful for getting the “big picture,” but it is limited over long spans of time because the wait_time_ms counter accumulates, as illustrated here:

Click here to view code image

SELECT TOP (25)
 wait_type
, wait_time_s = wait_time_ms / 1000.
, Pct = 100. * wait_time_ms/nullif(sum(wait_time_ms) OVER(),0)
, avg_ms_per_wait = wait_time_ms / nullif(waiting_tasks_count,0)
FROM sys.dm_os_wait_stats as wt ORDER BY Pct DESC;

Eventually, the wait_time_ms numbers will be so large for certain wait types that trends or changes in wait type accumulations rates will be mathematically difficult to see. You want to use the wait stats to keep a close eye on server performance as it trends and changes over time, so you need to capture these accumulated wait statistics in chunks of time, such as one day or one week.

Click here to view code image

--Script to set up capturing these statistics over time
CREATE TABLE dbo.usr_sys_dm_os_wait_stats
(   id int NOT NULL IDENTITY(1,1)
,   datecapture datetimeoffset(0) NOT NULL
,   wait_type nvarchar(512) NOT NULL
,   wait_time_s decimal(19,1) NOT NULL
,   Pct decimal(9,1) NOT NULL
,   avg_ms_per_wait decimal(19,1) NOT NULL
,   CONSTRAINT PK_sys_dm_os_wait_stats PRIMARY KEY CLUSTERED (id)
);
--This part of the script should be in a SQL Agent job, run regularly
INSERT INTO
Dbo.usr_sys_dm_os_wait_stats
(datecapture, wait_type, wait_time_s, Pct, avg_ms_per_wait)
SELECT
datecapture = SYSDATETIMEOFFSET()
, wait_type
, wait_time_s = convert(decimal(19,1), round( wait_time_ms / 1000.0,1))
, Pct = wait_time_ms/ nullif(sum(wait_time_ms) OVER(),0)
, avg_ms_per_wait = wait_time_ms / nullif(waiting_tasks_count,0)
FROM usr_sys.dm_os_wait_stats wt
WHERE wait_time_ms > 0
ORDER BY wait_time_s;

Using the metrics returned in the preceding code, you can calculate the difference between always-ascending wait times and counts to determine the counts between intervals. You can customize the schedule for this data to be captured in tables, building your own internal wait stats reporting table.

The sys.dm_os_wait_stats DMV is reset—and all accumulated metrics are lost—upon restart of the SQL Server service, but you can also clear them manually. Understandably, this would clear the statistics for the whole SQL Server instance. Here is a sample script of how you can capture wait statistics at any interval:

Click here to view code image

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

You can also view statistics for a query currently running in the DMV sys.dm_os_waiting_tasks, which contains more data than simply the wait_type; it also shows the blocking resource address in the resource_description field. This data is also available in sys.dm_exec_requests.

• For a complete breakdown of the information that can be contained in the resource_description field, see http://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-sql.

The query storage also tracks aggregated wait statistics for queries that it tracks. The waits tracked by the Query Store are not as detailed as the DMVs, but they do give you a quick idea of what a query is waiting on.

• For more information on reviewing waits in the Query Store, see Chapter 14.

Understand wait resources

What if you observe a query wait occurring live, and want to figure out what data the query is actually waiting on?

SQL Server 2019 delivered some new tools to explore the archaeology involved in identifying the root of waits. While an exhaustive look at the different wait resource types—some more cryptic than others—is best documented in Microsoft’s online resources, let’s review the tools provided.

The undocumented DBCC PAGE command (and its accompanying Trace Flag 3604) were used for years to review the information contained in a page, based on a specific page number. Whether trying to see the data at the source of waits or trying to peek at corrupted pages reported by DBCC CHECKDB, the DBCC PAGE command didn’t return any visible data without first enabling Trace Flag 3604. Now, for some cases, we have the pair of new functions, sys.dm_db_page_info and sys.fn_PageResCracker. Both can be used only when the sys.dm_exec_requests.wait_resource value begins with PAGE. So, the new tools leave out other common wait_resource types like KEY.

The DMVs in SQL Server 2019 and SQL Server 2022 are preferable to using DBCC PAGE because they are fully documented and supported. They can be combined with sys.dm_exec_requests—the hub DMV for all things active in SQL Server—to return potentially useful information about the object in contention when PAGE blocking is present:

Click here to view code image

SELECT r.request_id, pi.database_id, pi.file_id, pi.page_id, pi.object_id,
pi.page_type_desc, pi.index_id, pi.page_level, rows_in_page = pi.slot_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker (r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(r.database_id, prc.file_id, prc.page_id, 'DETAILED') AS
pi;

Benign wait types

Many of the waits in SQL Server do not affect the performance of user workload. These waits are commonly referred to as benign waits and are frequently excluded from queries analyzing wait stats. The following code contains a starter list of wait types that you can mostly ignore when querying the sys.dm_os_wait_stats DMV for aggregate wait statistics. You can append the following sample list WHERE clause.

Click here to view code image

SELECT * FROM sys.dm_os_wait_stats
WHERE
    wt.wait_type NOT LIKE '%SLEEP%' --can be safely ignored, sleeping
AND wt.wait_type NOT LIKE 'BROKER%' -- internal process
AND wt.wait_type NOT LIKE '%XTP_WAIT%' -- for memory-optimized tables
AND wt.wait_type NOT LIKE '%SQLTRACE%' -- internal process
AND wt.wait_type NOT LIKE 'QDS%' -- asynchronous Query Store data
AND wt.wait_type NOT IN ( -- common benign wait types
'CHECKPOINT_QUEUE'
,'CLR_AUTO_EVENT','CLR_MANUAL_EVENT' ,'CLR_SEMAPHORE'
,'DBMIRROR_DBM_MUTEX','DBMIRROR_EVENTS_QUEUE','DBMIRRORING_CMD'
,'DIRTY_PAGE_POLL'
,'DISPATCHER_QUEUE_SEMAPHORE'
,'FT_IFTS_SCHEDULER_IDLE_WAIT','FT_IFTSHC_MUTEX'
,'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
,'KSOURCE_WAKEUP'
,'LOGMGR_QUEUE'
,'ONDEMAND_TASK_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH'
,'XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
--Ignorable HADR waits
, 'HADR_WORK_QUEUE'
,'HADR_TIMER_TASK'
,'HADR_CLUSAPI_CALL');

Through your own research into your workload, and in future versions of SQL Server, as more wait types are added, you can grow this list so that important and actionable wait types rise to the top of your queries. A prevalence of these wait types shouldn’t be a concern; they’re unlikely to be generated by or negatively affect user requests.

Wait types to be aware of

This section shouldn’t be the start and end of your understanding of or research into wait types. Many of them have multiple avenues to explore in your SQL Server instance, or at the very least, names that are misleading to the DBA considering their origin. There are some, or groups of some, that you should understand, because they indicate a condition worth investigating. Many wait types are always present in all applications but become problematic when they appear in large frequency and/or with large cumulative waits. Large here is of course relative to your workload and your server.

Different instance workloads will have a different profile of wait types. Just because a wait type is at the top of the aggregate sys.dm_os_wait_stats list, it doesn’t mean that is the main or only performance problem with a SQL Server instance. It is likely that all SQL Server instances, even finely tuned instances, will show these wait types near the top of the aggregate waits list. You should track and trend these wait stats, perhaps using the script example in the previous section.

Important waits include the following, provided in alphabetical order:

• ASYNC_NETWORK_IO. This wait type is associated with the retrieval of data to a client (including SQL Server Management Studio and Azure Data Studio), and the wait while the remote client receives and finally acknowledges the data received. This wait almost certainly has very little to do with network speed, network interfaces, switches, or firewalls. Any client, including your workstation or even SSMS running locally on the server, can incur small amounts of ASYNC_NETWORK_IO as data is retrieved to be processed. Transactional and snapshot replication distribution will incur ASYNC_NETWORK_IO. You will see a large amount of ASYNC_NETWORK_IO generated by reporting applications such as Tableau, SSRS, SQL Server Profiler, and Microsoft Office products. The next time a rudimentary Access database application tries to load the entire contents of the Sales.OrderLines table, you’ll likely see ASYNC_NETWORK_IO.

  Reducing ASYNC_NETWORK_IO, like many of the waits we discuss in this chapter, has little to do with hardware purchases or upgrades; rather, it’s more to do with poorly designed queries and applications. The solution, therefore, would be an application change. Try suggesting to developers or client applications incurring large amounts of ASYNC_NETWORK_IO that they eliminate redundant queries, use server-side filtering as opposed to client-side filtering, use server-side data paging as opposed to client-side data paging, or use client-side caching.

• CXPACKET. A common and often-overreacted-to wait type, CXPACKET is a parallelism wait. In a vacuum, execution plans that are created with parallelism run faster. But at scale, with many execution plans running in parallel, the server’s resources might take longer to process the requests. This wait is measured in part as CXPACKET waits.

  When the CXPACKET wait is the predominant wait type experienced over time by your SQL Server, you should consider turning both the Maximum Degree of Parallelism (MAXDOP) and Cost Threshold for Parallelism (CTFP) dials when performance tuning. Make these changes in small, measured gestures, and don’t overreact to performance problems with a small number of queries. Use the Query Store to benchmark and trend the performance of high-value and high-cost queries as you change configuration settings.

  If large queries are already a problem for performance and multiple large queries regularly run simultaneously, raising the CTFP might not solve the problem. In addition to the obvious solutions of query tuning and index changes, including the creation of columnstore indexes, use MAXDOP as well to limit parallelization for very large queries.

  Until SQL Server 2016, MAXDOP was either a setting at the server level, a setting enforced at the query level, or a setting enforced to sessions selectively via Resource Governor (more on this toward the end of this chapter in the section “Protect important workloads with Resource Governor”). Since SQL Server 2016, the MAXDOP setting has been available as a database-scoped configuration. You can also use the MAXDOP query hint in any statement to override the database or server-level MAXDOP setting.

• IO_COMPLETION. This wait type is associated with synchronous read and write operations that are not related to row data pages, such as reading log blocks or virtual log file (VLF) information from the transaction log, or reading or writing merge join operator results, spools, and buffers to disk. It is difficult to associate this wait type with a single activity or event, but a spike in IO_COMPLETION could be an indication that these same events are now waiting on the I/O system to complete.

• LCK_M_*. Lock waits have to do with blocking and concurrency (or lack thereof). (Chapter 14 looks at isolation levels and concurrency.) When a request is writing and another request in READ COMMITTED or higher isolation is trying to lock that same row data, one of the 60+ different LCK_M_* wait types will be the reported wait type of the blocked request. For example, LCK_M_IS means that the thread wants to acquire an Intent Shared lock, but some other thread has it locked in an incompatible manner.

  In the aggregate, this doesn’t mean you should reduce the isolation level of your transactions. Whereas READ UNCOMMITTED is not a good solution, read committed snapshot isolation (RCSI) and snapshot isolation are good solutions; see Chapter 14 for more details. Rather, you should optimize execution plans for efficient access, for example, by reducing scans as well as avoiding long-running multistep transactions. Also, avoid index rebuild operations without the ONLINE option. (See the “Rebuild indexes” section earlier in this chapter for more information.)

  The wait_resource provided in sys.dm_exec_requests, or resource_description in sys.dm_os_waiting_tasks, provide a map to the exact location of the lock contention inside the database.

  • For a complete breakdown of the information that can be contained in the resource_description field in your version of SQL Server, visit https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-sql.

• MEMORYCLERK_XE. The MEMORYCLERK_XE wait type could spike if you have allowed Extended Events session targets to consume too much memory. We discuss Extended Events later in this chapter, but you should watch out for the maximum buffer size allowed to the ring_buffer session target, among other in-memory targets.

• OLEDB. This self-explanatory wait type describes waits associated with long-running external communication via the OLE DB provider, which is commonly used by SQL Server Integration Services (SSIS) packages, Microsoft Office applications (including querying Excel files), linked servers using the OLE DB provider, and third-party tools. It could also be generated by internal commands like DBCC CHECKDB. When you observe this wait occurring in SQL Server, in most cases, it’s driven by long-running linked server queries.

• PAGELATCH_* and PAGEIOLATCH_*. These two wait types are presented together not because they are similar in nature—they are not—but because they are often confused. To be clear, PAGELATCH has to do with contention over pages in memory, whereas PAGEIOLATCH relates to contention over pages in the I/O system (on the drive).

  PAGELATCH_* contention deals with pages in memory, which can rise because of the overuse of temporary objects in memory, potentially with rapid access to the same temporary objects. This can also be experienced when reading in data from an index in memory or reading from a heap in memory.

  A rise in PAGEIOLATCH_* could be due to the performance of the storage system (keeping in mind that the performance of drive systems does not always respond linearly to increases in activity). Aside from throwing (a lot of!) money at faster drives, a more economical solution is to modify queries and/or indexes and reduce the footprint of memory-intensive operations, especially operations involving index and table scans.

  PAGEIOLATCH_* contention has to do with a far more limiting and troubling performance condition: the overuse of reading from the slowest subsystem of all, the physical drives. PAGEIOLATCH_SH deals with reading data from a drive into memory so that the data can be accessed. Keep in mind that this doesn’t necessarily translate to a request’s row count, especially if index or table scans are required in the execution plan. PAGEIOLATCH_EX and PAGEIOLATCH_UP are waits associated with reading data from a drive into memory so that the data can be written to.

• RESOURCE_SEMAPHORE. This wait type is accumulated when a request is waiting on memory to be allocated before it can start. Although this could be an indication of memory pressure caused by insufficient memory available to process the queries being executed, it is more likely caused by poor query design and poor indexing, resulting in inefficient execution plans. Aside from throwing money at more system memory, a more economical solution is to tune queries and reduce the footprint of memory-intensive operations. The memory grant feedback features that are part of Intelligent Query Processing help address these waits by improving memory grants for subsequent executions of a query.

• SOS_SCHEDULER_YIELD. Another flavor of CPU pressure, and in some ways the opposite of the CXPACKET wait type, is the SOS_SCHEDULER_YIELD wait type. The SOS_SCHEDULER_YIELD is an indicator of CPU pressure, indicating that SQL Server had to share time with, or yield to, other CPU tasks, which can be normal and expected on busy servers. Whereas CXPACKET is SQL Server complaining about too many threads in parallel, SOS_SCHEDULER_YIELD is the acknowledgement that there were more runnable tasks for the available threads. In either case, first take a strategy of reducing CPU-intensive queries and rescheduling or optimizing CPU-intense maintenance operations. This is more economical than simply adding CPU capacity.

• WAIT_XTP_RECOVERY. This wait type can occur when a database with memory-optimized tables is in recovery at startup and is expected. As with all wait types on performance-sensitive production SQL Server instances, you should baseline and measure it, but be aware this is not usually a sign of any problem.

• WRITELOG. The WRITELOG wait type is likely to appear on any SQL Server instance, including availability group primary and secondary replicas, when there is heavy write activity. The WRITELOG wait is time spent flushing the transaction log to a drive and is due to physical I/O subsystem performance. On systems with heavy writes, this wait type is expected.

  You could consider re-creating the heavy-write tables as memory-optimized tables to increase the performance of writes. Memory-optimized tables optionally allow for delayed durability, which would resolve a bottleneck writing to the transaction log by using a memory buffer. For more information, see Chapter 14.

• XE_FILE_TARGET_TVF and XE_LIVE_TARGET_TVF. These waits are associated with writing Extended Events sessions to their targets. A sudden spike in these waits would indicate that too much is being captured by an Extended Events session. Usually these aren’t a problem, because the asynchronous nature of Extended Events has a much lower impact than traces.

Get started with the SQL Assessment API

To begin evaluating default or custom rules against your SQL Server, you must verify the presence of .NET Framework 4.0 and the latest versions of SMO and the SqlServer PowerShell module.

If you want to try this out on a server with an installation of SQL Server 2022 and the latest PowerShell SqlServer module, this sample PowerShell script should work:

Click here to view code image

$InstanceName='sql2022' #This should be the name of your instance
Get-SqlInstance -ServerInstance $InstanceName | Invoke-SqlAssessment | Out-GridView

For servers with installations of SQL Server, SMO should already be present on the Windows Server. For your local administration machine or a centralized server from which you’ll monitor your SQL Server environment, you’ll need to install SMO and the latest PowerShell SqlServer module.

While a developer might use Visual Studio’s Package Manager, you do not need to install Visual Studio to install the SMO NuGet package. Instead, you can use the cross-platform nuget.exe command line utility. You can also install and use the command line interface tool dotnet.exe if desired.

• More information about the various options is available at https://learn.microsoft.com/nuget/install-nuget-client-tools.

After you download nuget.exe, follow these steps:

1. Open a PowerShell window with Administrator permissions and navigate to the folder where you saved nuget.exe. Note that when calling an executable from a PowerShell window, you need to preface the executable with ./ (dot slash), which is a security mechanism carried over from UNIX systems. The command to download and install SMO via nuget.exe should take just a few seconds to complete:

   Click here to view code image

   nuget install Microsoft.SqlServer.SqlManagementObjects

   Optionally, specify an installation location for the installation:

   Click here to view code image

   nuget install Microsoft.SqlServer.SqlManagementObjects -OutputDirectory "c:
   uget
   packages"

   SMO is maintained and distributed via a NuGet package at https://nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects.

   In the installation directory, you’ll find several new folders. These version numbers are the latest at the time of writing and will likely be higher by the time you read this book.

   • System.Data.SqlClient.4.6.0

   • Microsoft.SqlServer.SqlManagementObjects.150.18147.0

2. With SMO in place, the second step to using the SQL Assessment API is to install the SqlServer PowerShell module. If you don’t already have the latest version of this module installed, launch a PowerShell console as an administrator, launch Visual Studio Code as an administrator, or use your favorite PowerShell scripting environment. The following command will download and install the latest version, even if you have a previous version installed:

   Click here to view code image

   Install-Module -Name SqlServer -AllowClobber -Force

   The AllowClobber parameter instructs Install-Module to overwrite cmdlet aliases already in place. Without AllowClobber, the installation will fail if it finds that the new module contains commands with the same name as existing commands.

   • For more information on installing and using the SqlServer PowerShell module, including how to install without Internet connectivity, see Chapter 9.

3. With SMO and the SqlServer PowerShell module installed, you are ready to compare SQL Server health with the default rule set by a PowerShell command—for example, as we demonstrated at the start of this section:

   Click here to view code image

   Get-SqlInstance -ServerInstance . | Invoke-SqlAssessment | Out-GridView

   Here, the period is shorthand for localhost, so this command executes an assessment against the API of the local default instance of SQL Server. To run the assessment against a remote SQL Server instance using Windows Authentication:

   Click here to view code image

   Get-SqlInstance -ServerInstance servername | Invoke-SqlAssessment | `
   Out-GridView

   Or, for a named instance:

   Click here to view code image

   Get-SqlInstance -ServerInstance servernameinstancename | `
   Invoke-SqlAssessment | Out-GridView

   Out-GridView (which is part of the default Windows PowerShell cmdlets) pops open a new window to make it easier to review multiple findings than reading what could be many pages of results in a scrolling PowerShell console. You can output the data any way you like using common PowerShell cmdlets.

   • For more sample syntax on assessing SQL Server instances via the Invoke-SqlAssessment cmdlet, visit https://learn.microsoft.com/powershell/module/sqlserver/invoke-sqlassessment.

The output of Invoke-SqlAssessment includes a helpful link to Microsoft documentation on every finding, the severity of each finding, the name of the check that resulted in a finding, an ID for the check for more granular review, and a helpful message. Again, all of this is provided by Microsoft’s default ruleset, on which you can base your own custom checks and probes with custom severity and messages.

To use your own customized configuration file, you can use the -Configuration parameter:

Click here to view code image

Get-SqlInstance -ServerInstance servername | Invoke-SqlAssessment `
-Configuration "C:	oolboxsqlassessment_api_config.json" | Out-GridView

Understand the variety of Extended Events targets

As mentioned, you can always watch detailed and “live” Extended Events data captured asynchronously in memory for any session through SSMS. You do this by right-clicking a session and selecting Watch Live Data from the shortcut menu. You’ll see asynchronously delivered detailed data, and you can customize the columns you see, apply filters on the data, and even create groups and on-the-fly aggregations, all by right-clicking inside the Live Data window.

The Live Data window, however, isn’t a target. The data isn’t saved anywhere outside the SSMS window, and you can’t look back at data you missed before launching Watch Live Data. You can create a session without a target, and Watch Live Data is all you’ll get, but often that is all you need for a quick observation.

Here is a summary of the Extended Event targets available to be created. Remember, you can create more than one target per session.

• Event File target (.xel). Writes the event data to a physical file on a drive asynchronously. You can then open and analyze it later, much like deprecated trace files, or merge it with other .xel files to assist analysis. (In SSMS, select the File menu, select Open, and then select Merge Extended Events Files.)

  If you are using Azure SQL Database or Managed Instance and you would like to persist Extended Event data, you can only use the Event File target to Azure Blob Storage. You also need to create a credential using a shared access signature (SAS).

  • The instructions for connecting your Azure SQL Database and Blob Storage for Extended Events can be found at https://learn.microsoft.com/azure/azure-sql/database/xevent-code-event-file.

  When you view the event file data in SSMS (right-click the event file and select View Target Data), the data does not refresh live. Data continues to be written to the file behind the scenes while the session is running. So, to view the latest data, you must close the .xel file and open it again.

  By default, .xel files are written to the instancepathMSSQLLog folder.

• Histogram target. Counts the number of times an event has occurred and bucketizes an action, storing the data in memory. For example, you can capture a histogram of sql_statement_completed broken down by the number of observed events by client-hostname action, or by the duration field.

  When configuring the histogram type target, you must choose a number of buckets (or slots, in the T-SQL syntax) that is greater than the number of unique values you expect for the action or field. If you’re bucketizing by a numeric value such as duration, be sure to provide a number of buckets larger than the largest duration you could capture over time. If the histogram runs out of buckets for new values for your action or field, it will not capture data for them.

  You can provide any number of histogram buckets, but the histogram target will round the number up to the nearest power of 2. Thus, if you provide a value of 10 buckets, you’ll see 16 buckets.

• Pair matching or Pairing target. Used to match events, such as the start and end of a SQL Server batch execution, and find occasions when an event in a pair occurs without the other, such as sql_statement_starting and sql_statement_completed. Select a start and an end from the list of actions you’ve selected.

• Ring_buffer target. Provides a fast, in-memory first in, first out (FIFO) asynchronous memory buffer to collect rapidly occurring events. Stored in a memory buffer, the data is never written to a drive, allowing for robust data collection without performance overhead. The customizable dataset is provided in XML format and must be queried. Because this data is in-memory, you should be careful how high you configure the Maximum Buffer Memory size, and never set the size to 0 (unlimited).

• Service Broker target. Used to send messages to a target service of a customizable message type.

Although the aforementioned targets are high-performing asynchronous targets, there are two synchronous targets:

• Event Tracing for Windows (ETW) target. Used to gather SQL Server data, to be combined with Windows event log data, for troubleshooting and debugging Windows applications.

• Event counter target. Counts the number of events in an Extended Events session. You use this to provide data for trending and later aggregate analysis. The resulting dataset has one row per event with a count. This data is stored in memory, so although it’s synchronous, you shouldn’t expect any noticeable performance impact.

Further, there are two session options that can affect the performance impact of an Extended Event session. The defaults are reasonably safe and are unlikely to result in noticeable performance overhead, so they don’t typically need to be changed. You might, however, want to change them if the event you’re trying to observe is rare, temporary, and outweighs your performance overhead concerns.

• EVENT_RETENTION_MODE. Determines whether, under pressure, the Extended Event session can miss a captured event. The default, ALLOW_SINGLE_EVENT_LOSS, here can let target(s) miss a single event when memory buffers used to stream the data to the target(s) are full.

  You instead specify ALLOW_MULTIPLE_EVENT_LOSS, which further minimizes the potential for performance impact on the monitored server by allowing more events to be missed.

  Or you could specify NO_EVENT_LOSS, which does not allow events to be missed, even if memory buffers are full. All events are retained and presented to the target. While not the same as using a synchronous target, it can result in the same effect: Performance of the SQL Server could suffer under the weight of the Extended Event session. Using this option is not recommended.

• MAX_DISPATCH_LATENCY. Determines the upper limit for when events are sent from the memory buffer to the target. By default, events are buffered in memory for up to 30 seconds before being sent to the targets. You could change this value to 1 to force data out of memory buffers faster, reducing the benefit of the memory buffers. A value of INFINITE or 0 allows for the retention of events until memory buffers are full or until the session closes.

Let’s look at querying Extended Events session data in T-SQL with a couple of practical common examples.

Average Disk seconds per Read or Write

Performance Monitor: PhysicalDisk:Avg. Disk sec/Read and PhysicalDisk:Avg. Disk sec/Write

DMO: sys.dm_io_virtual_file_stats offers similar metrics for each data and log file. Typically, this data is used in conjunction with the Performance Monitor data to confirm or deny behavior.

View this metric on each volume. The _Total metric doesn’t have any value here; you should look at individual volumes in which SQL Server files are present. This metric has the clearest guidance of any with respect to what is acceptable or not for a server.

Try to measure this value during your busiest workload, and also during backups. You want to see the average disk seconds per read and write operation (considering that a single query could have thousands or millions of operations) below 20 milliseconds, or .02 seconds. Below 10 milliseconds is optimal and very achievable with modern storage systems.

• This is the rare case for which we have hard and fast numbers specified by Microsoft to rely on. For more information, visit the “Monitoring Disk Usage” blog post at https://social.technet.microsoft.com/wiki/contents/articles/3214.monitoring-disk-usage.aspx.

Seeing this value spike to a very high value (such as .1 second or 100 milliseconds) isn’t a major cause for concern, but if you see these metrics sustaining an average higher than .02 seconds during peak activity, it is a fairly clear indication that the physical I/O subsystem is being stressed beyond its capacity. Low, healthy measurements for this number don’t provide any insight into the quality or efficiency of queries and execution plans, only the response from the disk subsystem. The Avg. Disk sec/Transfer counter is simply a combination of both read and write activity, unrelated to Avg. Disk Transfers/sec.

Page Life Expectancy (PLE)

Performance Monitor: MSSQL$InstanceName:Buffer Manager/Page Life Expectancy (s)

DMV: sys.dm_os_performance_counters
WHERE object_name like '%Buffer Manager%'
AND counter_name = 'Page life expectancy'

Page Life Expectancy (PLE) is a measure of time (in seconds) that indicates the age of data in memory. PLE is one of the most direct indicators of memory pressure, though it doesn’t provide a complete picture of memory utilization in SQL Server. In general, you want pages of data in memory to grow to a ripe old age; when they do, it means there is ample memory available to SQL Server to store data to serve reads without going back to the storage layer.

A dated, oft-quoted metric of 300 seconds isn’t applicable to many SQL Server instances. While 300 seconds might be appropriate for a server with 4 GB of memory, it’s far too low for a server with 64 GB of memory. Instead, you should monitor this value over time. Does PLE bottom out and stay there during certain operations or scheduled tasks? If so, your SQL Server performance might benefit from more memory during those operations. Does PLE grow steadily throughout production hours? If so, the data in memory is likely to be sufficient for the observed workload.

Page Reads

Performance Monitor: MSSQL$InstanceName:Buffer Manager/Page reads/sec

DMV: sys.dm_os_performance_counters
WHERE object_name like '%Buffer Manager%'
AND counter_name = 'Page reads/sec'

This is an average of the number of page read operations completed recently. The title is a bit misleading—these aren’t page reads out of the buffer; rather, they are out of physical pages on the drive, which is slower than data pages coming out of memory.

You should make the effort to lower this number by optimizing queries and indexing, improving efficiency of cache storage, and, of course, as a last resort, increasing the amount of server memory. Although every workload is different, a value less than 90 is a broad, overly simple guideline. High numbers indicate inefficient query and index design in read-write workloads or memory constraints in read-heavy workloads.

Memory Pages

Performance Monitor: Memory:Pages/sec

DMV: Not available.

Similar to Buffer ManagerPage Reads/sec, this is a way to measure data coming from a drive as opposed to coming out of memory. This metric is a recent average of the number of pages pulled from a drive into memory, which will be high after SQL Server startup. Although every workload is different, a value of less than 50 is a broad guideline. Sustained high or climbing levels during typical production usage indicate inefficient query and index design in read-write workloads, or memory constraints in read-heavy workloads. Spikes during database backup and restore operations, bulk copies, and data extracts are expected.

Batch Requests

Performance Monitor: MSSQL$lnstanceName:SQL StatisticsBatch Requests/sec

DMV: sys.dm_os_performance_counters WHERE object_name like '%SQL Statistics%' AND counter_name = 'Batch Requests/sec'

This is a measure of aggregate SQL Server user activity, indicating the recent average of the number of batch requests. Any command issued to the SQL Server contains at least one batch request. Higher sustained numbers are good; they mean your SQL Server instance is sustaining more traffic. Should this number trend downward during peak business hours, it means your SQL Server instance is being outstripped by increasing user activity.

Page Faults

Performance Monitor: MemoryPage Faults/sec

DMV: Not available.

A memory page fault occurs when an application seeks a data page in memory, only to find it isn’t there because of memory churn. A soft page fault indicates the page was moved or otherwise unavailable; a hard page fault indicates the data page was not in memory and must be retrieved from the drive. The Page Faults/sec metric captures both.

Page faults are a symptom, the cause being memory churn, so you might see an accompanying drop in Page Life Expectancy (PLE). Spikes in page faults, or an upward trend, indicate the amount of server memory was insufficient to serve requests from all applications, not just SQL Server.

Available Memory

Performance Monitor: MemoryAvailable Bytes or MemoryAvailable KBytes or MemoryAvailable MBytes

DMV: SELECT available_physical_memory_kb FROM sys.dm_os_sys_memory

Available memory is operating system memory currently unallocated to any application. Server memory above and beyond the SQL Server instance(s) total MAX_SERVER_MEMORY setting, minus memory in use by other SQL Server features and services or other applications, is available. This will roughly match what shows as available memory in the Windows Task Manager.

Total Server Memory

Performance Monitor: MSSQL$InstanceName:Memory ManagerTotal Server Memory (KB)

DMV: sys.dm_os_performance_counters
WHERE object_name like '%Memory Manager%'
AND counter_name = 'Total Server Memory (KB)'

This is the actual amount of memory that SQL Server is using. It is often contrasted with the next metric (Target Server Memory). This number might be far larger than what Windows Task Manager shows allocated to the SQL Server Windows NT 64 Bit background application, which shows only a portion of the memory that sqlserver.exe controls. The Total Server Memory metric is correct.

Target Server Memory

Performance Monitor: MSSQL$InstanceName:Memory ManagerTarget Server Memory (KB)

DMV: sys.dm_os_performance_counters
WHERE object_name like '%Memory Manager%'
AND counter_name = 'Target Server Memory (KB)'

This is the amount of memory to which SQL Server wants to have access and is currently working toward consuming. If the difference between Target Server Memory and Total Server Memory is larger than the value for Available Memory, SQL Server wants more memory than the Windows Server can currently acquire. SQL Server will eventually consume all memory available to it under the Max Server Memory setting, but it might take time.

View performance counters in Linux

The dynamic management view sys.dm_os_performance_counters behaves the same and delivers identical output on Windows and Linux. For example, the Performance Monitor metrics in the previous section listed as available in the DMV are also available in SQL Server on Linux.

The top command, built into Linux and with near-identical output on all distributions, launches a live full-console display of CPU and memory utilization and process metrics, not dissimilar from Windows Task Manager. The screen is data rich and starkly black and white, however, so consider the more graphical command htop. Though not present by default on all Linux distributions, it can be quickly downloaded and easily installed. This command’s output (see Figure 8-4) shows much of the same useful data with a more pleasant format and with color highlights.

Another built-in Linux tool is vmstat, which includes extended information on process memory, like runnable/sleeping processes, memory availability, swap memory use, memory I/O activity, system interrupts, and CPU utilization percentages. While vmstat returns a snapshot of the data, the syntax vmstat n appends fresh data to the console once every n seconds.

For querying items in SQL Server on Linux not available in sys.dm_os_performance_counters, such as Avg Disk sec/read and Avg Disk sec/write for each volume, different Linux tools are needed.

The iostat tool is available to install via the syststat performance monitoring tools, using the package manager on your operating system. Source code for iostat is available at https://github.com/sysstat/sysstat.

For example:

user@instance:~$ iostat -x

Using the -x parameter to return extended statistics yields basic host information, a current CPU activity utilization breakdown, and a variety of live measurements for devices, including logical disk volumes. The measures r_await and w_await are the average durations in milliseconds for read and write requests.

Other alternative packages include dtrace and nmon, the latter of which includes a simple bash-based GUI.

View data in Azure Monitor

The Azure platform’s built-in metrics tool, Azure Monitor—accessible via the Azure portal—automatically tracks several basic key performance and usage metrics in any Azure SQL Database. Azure Monitor Logs is one half of the data platform that supports Azure Monitor. The other is Azure Monitor Metrics, which stores numeric data in a time-series database. Some of these metrics are configured for you as part of the service (like metrics) while others require you to configure them (for example, SQL Diagnostics).

You query this data via the Azure Monitor Metrics pane, where you can drill down to an Azure resource and choose a metric to generate visualizations. Azure Monitor supports pinning generated visualizations to Azure portal dashboards, allowing you to create and monitor key database metrics at a glance.

When using Azure Monitor Metrics for an Azure SQL Database, for example, you can add metrics for DTU usage, or for percentages of the measures that make up a DTU. In the example in Figure 8-5, the Azure Monitor Metrics pane displays both DTU used and the average Log IO percentage on the same graph.

You can do more complicated charting via Azure Monitor by adding more metrics, which allows for visualization of multiple dimensions of interrelated data simultaneously, such as service request count per hour versus database CPU or DTU utilization.

You use filtering or splitting to further break down metrics with more than one value—for example, disk metrics. You can either filter on specific LUNs when viewing Data Disk Read Bytes/sec, for example, or you can split the data into different graph series, one for each LUN. If this sounds familiar, filtering and splitting using the Azure portal is not dissimilar from the same in Windows Performance Monitor. For example, this is similar to the selection of volumes when adding the Physical DiskAvg. Disk sec/Read counter.

Leverage Azure Monitor logs

Azure Monitor is built on the Azure Log Analytics platform, using the same data storage and query mechanisms. Azure Log Analytics is itself a separate platform built to aggregate and query big data of varying schemas in near-real time. Azure Monitor log data is stored in a Log Analytics workspace, but is distinctly under the Azure Monitor product name, which also includes Application Insights.

Many Microsoft Azure resource types natively support syncing varying diagnostic and metric information to Azure Log Analytics. Azure SQL Database natively supports the export of information to Log Analytics via the Diagnostic Settings pane for the respective database in the Azure portal. Diagnostic settings support streaming basic metrics, as well as varying types of logs to log analytics.

Contrary to Azure Monitor, Log Analytics supports the ingesting of information from on-premises servers as well via the Azure Log Analytics agent. You might be familiar with the System Center Operations Manager (SCOM) monitoring tool, the Microsoft Monitoring Agent (MMA). The Azure Monitor agent is the evolution and replacement of the MMA and enables you to attach to an Azure Monitor or send data to a Log Analytics workspace.

Once your Log Analytics workspace is receiving data, you can query the workspace via the Logs pane in the Log Analytics workspace resource using the Kusto Query Language (KQL).

The following sample query gathers all DTU consumption metrics for Azure SQL Databases sending their logs to the Log Analytics workspace. It displays the 80th percentile of DTU consumption per time grain—in this case every 60 minutes. The intent is to normalize spikes of DTU usage and help to visualize sustained increase in DTU percentage that may be indicative of inefficient queries or a degradation between deployments.

Click here to view code image

AzureMetrics
| where MetricName == 'dtu_consumption_percent'
| summarize percentile(Average, 80) by bin(TimeGenerated, 1h)
| render timechart

• For more on KQL, see https://learn.microsoft.com/azure/data-explorer/kusto/query/.

Figure 8-6 visualizes the results of this query, which will work with any Log Analytics workspaces that have SQL databases sending log data.

As mentioned, the preceding query extrapolates the 80th percentile of average DTU usage as a percent of quota, denoted by 'dtu_consumption_percent', in 1-hour increments. While useful, variances in usage patterns of the databases can lead to numerous peaks and valleys in the data rendered. This can make it hard to visually spot when the analyzed data is indicating a regression in performance—that is, a spike in DTU consumption.

As an alternative, the following query, visualized in Figure 8-7, applies a finite impulse response (series_fir()) to produce a 12-hour moving average of the analyzed data. This type of function is often used in signal processing, which a log stream resembles. This second example is a minor demonstration of the power and ease of drawing meaningful metrics out of the log data stream coming from Azure SQL resources, a more sophisticated look that should be more useful and readable at larger time scales.

Click here to view code image

AzureMetrics
| where MetricName == 'dtu_consumption_percent'
| make-series 80thPercentile=percentile(Average, 80)
 on TimeGenerated in range(ago(7d), now(), 60m)
| extend 80thPercentile=series_fir(80thPercentile, repeat(1, 12), true, true)
| mv-expand 80thPercentile, TimeGenerated
| project todouble(80thPercentile), todatetime(TimeGenerated)
| render timechart with (xcolumn=TimeGenerated)

• For more on series_fir(), see https://learn.microsoft.com/azure/data-explorer/kusto/query/series-firfunction.

Create Microsoft Log Analytics solutions

Perhaps the most important takeaway from Log Analytics is the ability to add or create solution packages. These can encapsulate queries, dashboards, and drill down reports of information.

Added via the Azure Marketplace, the Azure SQL Analytics solution and SQL Health Check solution attach to a Log Analytics workspace and can provide near-immediate feedback across your environment, scaling to the hundreds of thousands of databases if necessary.

Figure 8-8 is a sample live display from a production Azure SQL Analytics solution backed by a Log Analytics workspace. It shows at-a-glance information from production Azure SQL databases regarding database tuning recommendations, resource utilization, wait types and duration, as well as health check outcomes for metrics like timeouts and deadlocks.