The Database Engine supports four additional performance tools:
- Query Store
- Automatic tuning (as an “add-on” to Query Store)
- Performance Data Collector
- Resource Governor
- Live Query Statistics
The following sections describe these tools.
1. Query Store
As described in Chapter 19, the USE PLAN query hint advises the Database Engine to use the particular execution plan for the query execution. Query Store is a Database Engine component that has a similar task: to simplify performance troubleshooting by enabling you to find performance differences caused by changes in query plans. The main difference between the USE PLAN hint and Query Store is that the latter does not require any changes in user applications. Query Store can be used for the following tasks:
- Store the history of all plans for your queries.
- Capture the performance of a query plan over time.
- Identify the top n queries (by execution time, memory usage, etc.).
- Identify queries with performance regression over time.
- Force a particular query plan for a query with performance regression.
Every time the Database Engine executes or compiles a query, the gathered information concerning each query is kept in memory and, after that, stored on disk. (You can specify the time interval for how long the data is kept in memory before being sent to disk.) Finally, the data is stored in the user database. This process describes roughly the functionality of the Query Store component. By default, Query Store is disabled. For this reason, I will first explain how you can enable it and modify its configuration parameters.
1.1. Enabling Query Store and Setting Configuration Options
As mentioned, Query Store must be enabled before you can use it. You can enable Query Store using Transact-SQL. Example 20.19 shows the use of Transact-SQL. (You have to change the name of the database if you do not use the AdventureWorks database as shown in the example.)
Example 20.19
USE master;
ALTER DATABASE AdventureWorks SET QUERY_STORE=ON;
Once you have enabled Query Store, you can configure its various options. Again, you can do so using SQL Server Management Studio or Transact-SQL. To configure options using Management Studio, right-click the database, choose Properties, and select Query Store in the left pane. The page in Figure 20-9 appears. (All configurable settings are in bold font and highlighted.) The following options, among others (which are self-explanatory), can be modified with SQL Server Management Studio:
- Operation Mode Can be READ WRITE, READ ONLY, or OFF.
- Data Flush Interval Determines the frequency at which data written to Query Store is persisted to disk.
- Max Size Configures the maximum size of Query Store. If the data in Query Store hits the limit for maximum size, Query Store automatically changes the state from read-write to read-only and stops collecting new data.
- Query Store Capture Mode Designates if Query Store captures all queries, captures only relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries.
- Size Based Cleanup Mode Controls whether the cleanup process will be automatically activated when the total amount of data gets close to the maximum size.
The ALTER DATABASE statement can also be used to configure options of Query Store, as shown in Example 20.20.
Example 20.20
USE master;
GO
ALTER DATABASE AdventureWorks
SET QUERY_STORE ( OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 3000, MAX_STORAGE_SIZE_MB
INTERVAL_LENGTH_MINUTES = 15, QUERY_CAPTURE_MODE = AUTO
MAX_PLANS_PER_QUERY = 1000);
The ALTER DATABASE statement and its QUERY_STORE clause are used to set configuration options of Query Store. The INTERVAL_LENGTH_MINUTES option determines the time interval at which run-time statistics data is aggregated into Query Store. The MAX_PLANS_PER_QUERY option represents the maximum number of plans maintained for each query. The default value for this option is 200.
The CLEANUP_POLICY option describes the data retention policy of Query Store, while STALE_QUERY_THRESHOLD_DAYS determines the number of days for which the information for a query is retained in Query Store. DATA_FLUSH_INTERVAL_SECONDS determines the frequency at which data written to Query Store is persisted to disk. (For optimization reasons, data collected by Query Store is asynchronously written to the disk.)
The QUERY_CAPTURE_MODE option designates the currently active query capture mode. The AUTO value of this option specifies that only relevant queries based on execution count and resource consumption are captured. (The default value is ALL.)
1.2. Analyze Query Plans with SQL Server Management Studio
Once Query Store is enabled and configured, you can start analyzing your query plans.
To analyze plans, right-click the Query Store container in the folder of your database. The following options appear:
- View Regressed Queries
- View Overall Resource Consumption
- View Top Resource Consuming Queries
- View Queries with Forced Plans
- View Queries with High Variation
- Query Wait Statistics
- View Tracked Queries
After selecting the View Regressed Queries option, the Regressed Queries window shows queries and corresponding execution plans in Query Store. In this window you can select queries based on various criteria. Among other things, you can view the source query, force and unforce a particular query plan, and refresh the display. (The next section describes in detail how you can correct regressed queries.)
Selecting the View Overall Resource Consumption option opens a window that displays CPU Time, Duration, Execution Count, and Logical Reads for the specified time period and aggregated by an interval. (The default value for the time period is a week, and the default value for the aggregation interval is one hour.) Each metric appears in its own grid.
The View Top Resource Consuming Queries window displays all queries sorted by their use of a particular resource (metric). The possible metrics are CPU Time, Duration, Execution Count, Logical Reads and Writes, and Memory Consumption.
The View Queries with Forced Plans window enables you to see all queries where the stored execution plan will be forced when the corresponding query is executed. (To force an execution plans using Query Store, you can either click the Force Plan button in one of the Query Store reports or use the sp_query_store_force_plan system stored procedure.)
The View Queries with High Variation window shows all queries with high variation in query text. (If you have an ad hoc workload that has this property, you will have more individual queries stored, and thus more plans and more run-time and wait statistics.)
The View Query Wait Statistics window shows a bar chart containing the top wait categories in Query Store. Use the drop-down menu at the top to select an aggregate criteria for the wait time: avg, max, min, std dev, and total (default).
The View Tracked Queries window allows you to track a particular query. Additionally, you can track a query using the Track the Selected Query button in the Top Resource Consuming Queries window or Regressed Queries window.
All of these windows have a similar structure and functionality. For this reason, I will explain only one of them, the Top Resource Consuming Queries window.
1.3. Top Resource Consuming Queries
As you already know, the window that opens when you choose the View Top Resources Consuming Queries option displays all queries in relation to the particular metric in descending order. The chart in the top-left pane of the window displays total statistics based on selected metrics for the vertical and horizontal axes. It also has two drop-down menus: Metric and Statistic (see Figure 20-10). The Metric drop-down menu contains, among others, the following metrics: CPU Time, Duration, and Memory Consumption. The Statistic drop-down menu enables you to select functions corresponding to the well-known aggregate functions (AVG, MAX, MIN, and COUNT).
Many metrics are available for the vertical axis of the left chart, so I will explain only three: execution count (exec count), number of execution plans (num plans), and the default option, which is dynamic. The default option depends on the values you choose in the Metric and Statistic drop-down menus. (Note, when the exec count metric is selected from the drop-down menu, only exec count and num plans are available for the vertical axis.)
There are three metrics available for the horizontal axis. The default value is query id, which contains values that uniquely identify each query. The second option, plan_id, contains values that uniquely identify execution plans. The third option is dynamic and, again, is based on the values you choose in the Metric and Statistic drop-down menus.
The chart in the top-right pane of the window, called Plan Summary, displays statistics of the selected/highlighted query (green bar on the left chart) based on the selected Metric and Statistic values (see Figure 20-10). The selected value of the Statistic drop-down menu becomes a vertical axis for the right chart. You can move the mouse cursor over one of the “bubbles” in the right part of the chart to see the detailed statistics for a specific query. Figure 20-10 has two bubbles in the right chart, which represent plans with plan_id 73 and 76. (The values 73 and 76 may be different when you create the same detailed statistics.) Also, the statistics details will be different depending on the metric selected. The size of these bubbles depends on total number of executions: the higher the number, the bigger the bubble. When you click a particular bubble, the bottom pane displays the corresponding execution plan.
Above the left pane, there are several additional buttons that are used to display queries and their plans in different forms. (If you have smaller screen resolution, these buttons will be hidden, as in Figure 20-10; click the drop-down arrow to the right of the Statistic field to expose them.) The following buttons belong to that group:
- Last refreshed time
- Track the selected query in a new Tracked Queries window
- View the query text of the selected query in the Query Editor window
- View top resource consuming queries in a grid format
- View top resource consuming queries in a grid format with additional details
- View top resource consuming queries in a chart form
When you move the mouse cursor over the Last Refreshed Time button, the last refreshed time will be shown. By clicking the second button, Track the Selected Query in a New Tracked Queries Window, a corresponding window will be opened, and you can select one of the queries represented by bubbles and track it. The third button, View the Query Text of the Selected Query in the Query Editor Window, shows the SELECT statement associated with the selected query. (The SELECT statement displays the state of the query from the corresponding catalog view.)
Each of the last three buttons orders all existing queries according to how they consume existing resources and displays them in descending order in a grid or chart in the left pane. The first two buttons display these queries in the grid format, while the last one displays them in the chart form. Figure 20-11 shows the left pane with the list of queries displayed in the grid format.
To force a plan, select a query and a corresponding plan, and then click Force Plan. (The Force Plan button is available below the Plan Summary pane.) You can only force plans that were saved and are still retained in the query plan cache. Next to the Force Plan button is the Unforce Plan button, which is used to unforce the corresponding plan.
1.4. Analyze Query Plans with Catalog Views
The Database Engine supports several catalog views in relation to Query Store. The following are the most important views:
- query_store_query
- query_store_query_text
- query_store_plan
- query_store_runtime_stats
The sys.query_store_query view contains the general information about the query. The most important columns are
- query_id The unique ID number of the query
- query_text_id The ID of the query text
- object_id The ID of the database object to which the query belongs
The sys.query_store_query_text view contains the text of the corresponding SQL statement and the SQL handle of the query. The most important columns are
- query_text_id The unique ID of the query text
- query_sql_text SQL text of the query
- statement_sql_handle The SQL handle of the particular query
The sys.query_store_plan view contains information about each execution plan associated with a query. Using this view, and joining it with other Query Store catalog views, you can track the history of all execution plans of a query. The most important columns are
- plan_id The unique ID of the execution plan
- query_id The ID of the query to which the plan belongs
The sys.query_store_runtime_stats view contains information about the run-time execution statistics information for the query. The most important columns are
- runtime_stats_id The unique ID of the run-time statistics
- plan_id The ID of the execution plan to which the statistics belong
With the help of the views described, you will see how some queries in relation to Query Store can be specified. For other queries that can be written using these and other views in relation to Query Store, please consult Microsoft Docs. Example 20.21 calculates the number of executions of a query. (The higher the number of query executions, the more important the design of such a query in relation to performance.)
Example 20.21
USE AdventureWorks;
SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
SUM(rs.count_executions) AS execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY execution_count DESC;
The four catalog views discussed previously are joined in Example 20.21 to calculate the number of executions. To display the calculation for each query, you must group the information using the values of the columns query_id and query_text_id as well as the text in the query_sql_text column. Finally, the count_executions column from the sys.query_store_ runtime_stats catalog view gives you the number of executions of each query.
Example 20.22 shows how the number of queries (in this case, ten) with the longest average execution time can be calculated within a time period.
Example 20.22
USE AdventureWorks;
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS curr_time,
rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
In Example 20.22, the four catalog views are again joined together. The TOP n clause in the SELECT list specifies that only top ten queries should be displayed. The DATEADD system function in the WHERE clause is used to specify the time period (in this case HOUR). The column last_execution_time of the sys.query_store_runtime_stats catalog view displays the time at which the query was last executed.
2. Automatic Tuning
As you already know, the query optimizer of the Database Engine is used to generate the query execution plan based on the existing statistics. The optimizer generates and evaluates multiple execution plans based on the calculation done for the least amount of resources. The most important factors negatively impacting selection of good execution plans are erroneous indexing and out-of-date statistics.
The previous section introduced Query Store, a feature that captures the compile-time and run-time statistics of statements being executed. With the data being collected, you can find out, among other things, whether the execution plans have regressed over time. With this information, you can identify a previous execution plan that performs better and manually force any future query executions to utilize the forced execution plan.
Starting with SQL Server 2017, the features of Query Store are taken one step further and the Database Engine can retrieve the information captured in Query Store and then make decisions automatically, forcing the most recent good execution plan. Therefore, the Database Engine now supports two kinds of correction:
- Manual plan tuning
- Automatic plan tuning
The following sections discuss both forms in detail so that you can see how automatic plan tuning is a big improvement over manual plan tuning.
2.1. Manual Plan Tuning
Without automatic plan tuning, administrators must periodically monitor the system and look for queries that have regressed. If any plan has regressed, the administrator should find some previous good plan and force it to be used instead of the current one using the sp_query_ store_force_plan system stored procedure. The best practice would be to force the most recent known good plan because older plans might be invalid due to statistic or index changes.
The administrator who forces the last known good plan should monitor performance of the query that is executed using the forced plan and verify that the forced plan works as expected. Depending on the results of monitoring and analysis, the plan should be forced or the administrator should find some other way to optimize the query.
The following series of examples use Microsoft’s new sample database called WideWorldlmporters to show how you can manually correct execution plan regression.
Example 20.23 loads the Order and OrderLine tables of the Sales schema from the WideWorldlmporters database into the sample database.
Example 20.23
— Copy Sales.OrderLines and Sales.Orders in the sample DB USE sample;
SELECT * INTO salesorderLines
FROM WideWorldImporters.Sales.OrderLines;
SELECT * INTO salesorders
FROM WideWorldImporters.Sales.Orders;
Next, Example 20.24 enables and configures Query Store.
Example 20.24
USE master;
GO
— Enable Query Store
ALTER DATABASE sample SET QUERY_STORE = ON;
GO
— Configure Query Store
ALTER DATABASE sample SET QUERY_STORE
(OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 600,
MAX_STORAGE_SIZE_MB = 500,
INTERVAL_LENGTH_MINUTES = 30);
The first ALTER DATABASE statement in Example 20.24 enables Query Store, while the second one sets several configuration options for this tool. (For the detailed description of the options in Example 20.24, see Example 20.20.)
In Example 20.25, the DBCC command clears the content of the procedure cache, while the ALTER DATABASE statement drops all persistently stored execution plans in Query Store. That way, all existing execution plans stored in the cache and in Query Store will be dropped.
Example 20.25
USE sample;
GO
— Clear procedure cache DBCC FREEPROCCACHE;
GO
— Clear Query Store
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL;
All previous Transact-SQL statements in this subsection have been used to set the stage for execution of queries that will show plan change regression. Example 20.26 starts the SQL workload by executing the sp_executesql system procedure 600 times.
Example 20.26
EXEC sp_executesql N’SELECT SUM(UnitPrice*Quantity)
FROM SalesOrderLines sl
JOIN salesorders so ON sl.OrderID=so.OrderID WHERE PackageTypeID = @typeID’, N’@typeID int’, @typeID = 7;
GO 6 00
The WHERE clause of the SELECT statement in Example 20.26 introduces a new variable called @typeID and specifies the data type of that variable (INT) as well as its value (7). This value then is assigned to the PackageTypeID column. Therefore, Example 20.26 uses the sp_executesql system stored procedure to execute 600 times the following statement:
SELECT SUM(UnitPrice*Quantity) FROM SalesOrderLines sl
JOIN salesorders so ON sl.OrderID=so.OrderID
WHERE PackageTypeID = 7;
NOTE The reason to embed the query inside the sp_executesql procedure, instead of executing it directly, is that the particular plan created on first execution will be reused by subsequent executions of the same query.
Examples 20.27 and 20.28 again clear the procedure cache and start the sp_executesql system procedure twice, with different values for the PackageTypeID column.
Example 20.27
USE sample;
DBCC FREEPROCCACHE;
EXEC sp_executesql N’SELECT SUM(UnitPrice*Quantity)
FROM SalesOrderLines sl JOIN salesorders so ON sl.OrderID=SO.OrderID
WHERE PackageTypeID = @typeID’, N’@typeID INT’, @typeID = 0;
Example 20.28
EXEC sp_executesql N’SELECT SUM(UnitPrice*Quantity)
FROM SalesOrderLines sl JOIN salesorders so ON sl.OrderID=so.OrderID
where PackageTypeID = @typeID’, N’@typeID INT’, @typeID = 7;
GO 2 0
Now, Example 20.29 uses the sys.dm_db_tuning_recommendations DMV to see whether there is plan regression during the execution of the queries. (This DMV was introduced in SQL Server 2017.)
Example 20.29
USE sample;
SELECT reason, score,
script=JSON_VALUE(details, ‘$.implementationDetails.script’)
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, ‘$.planForceDetails’)
WITH ([query_id] int ‘$.queryId’,
regressedPlanId int ‘$.regressedPlanId’,
recommendedPlanId int ‘$.recommendedPlanId’,
regressedPlanErrorCount int, recommendedPlanErrorCount int,
regressedPlanExecutionCount int,
regressedPlanCpuTimeAverage float,
recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float
) AS planForceDetails;
The result is
reason score
Average query CPU time changed from 101.11ms to 627.7ms 84
script
—————————————————————–
exec sp_query_store_force_plan @query_id = 1, @plan_id = 1
As you can see, the three columns of the sys.dm_db_tuning_recommendations view are reason, score, and script. The reason column explains why this recommendation is being provided. The score column shows the estimated value for this recommendation on a scale from 0 (lowest) to 100 (highest). The script column displays the Transact-SQL script that should be executed to force the recommended plan. (Often, the execution of the DMV will not have any result, meaning that the system could not make any recommendations for the execution of the particular query.)
In Example 20.29, the system found that the execution time of the last query is approximately six times slower than the one stored in Query Store (the reason column). Therefore, we have the typical case of execution plan regression. Additionally, the probability that this is true is high (score = 84 percent). Finally, the system identifies the statement that should be executed to force the right execution plan (the script column).
Copying and executing the statement from the script column allows you to force the execution of the right plan.
NOTE Manually forced plans should not be forced forever. The database administrator should periodically test the performance of the forced plan. If the plan is not optimal enough, she can unforce the plan using the sp_query_store_unforce_plan system procedure and let the Database Engine find the optimal plan.
2.2. Automatic Plan Tuning
Automatic plan tuning is a database feature that identifies potential query performance problems, identifies recommend solutions, and automatically fixes identified problems. More precisely, it enables the Database Engine to identify and fix performance issues caused by query execution plan choice regressions. You can enable automatic tuning per database and specify that the most recent good plan should be forced whenever some plan change regression is detected. The ALTER DATABASE statement in Example 20.30 enables automatic tuning.
NOTE To automate the manual process, you have to repeat the execution of Examples 20.23 through 20.28, with one modification. Example 20.30 must be executed after Example 20.25.
Example 20.30
USE master;
ALTER DATABASE sample
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON );
Once you enable automatic tuning, the Database Engine automatically forces any recommendation where the estimated CPU gain is higher than 10 seconds, or the number of errors in the new plan is higher than the number of errors in the recommended plan. Additionally, the system will verify that the forced plan is better than the current one.
To show that the system automatically forces the obtained information, Example 20.31 again uses the sys.dm_db_tuning_recommendations DMV, this time with different columns in the SELECT list.
Example 20.31
USE sample;
SELECT name, state
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, ‘$.planForceDetails’)
WITH ([query_id] int ‘$.queryId’,
regressedPlanId int ‘$.regressedPlanId’,
recommendedPlanId int ‘$.recommendedPlanId’,
regressedPlanErrorCount int,
recommendedPlanErrorCount int,
regressedPlanExecutionCount int,
regressedPlanCpuTimeAverage float,
recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float
) AS planForceDetails;
The result is
name state
————————————————————-
PR_1 {“currentValue”:”Success”,”reason”:”PlanForcedByUser”}
The SELECT list in Example 20.31 uses only the columns that are related to the automatic tuning option: name and state. (The reason, score, and script columns have the same output as in Example 20.29.) The name column specifies the unique name of the recommendation (PR_1 in Example 20.31). The state column describes the state of the recommendation.
The data type of this column is a JSON document. The document has two name-value pairs. The name of the first name-value pair, currentValue, describes the current state of the recommendation. The value “Success” means that the recommendation is successfully applied. The name of the second name-value pair, reason, is a string that describes why the recommendation is in the current state. (See Chapter 29 for the detailed description of JSON documents.)
2.3. Editing Information Concerning Automatic Tuning
There are two dynamic management views related to the automatic tuning option:
- dm_db_tuning_recommendations
- datab ase_automatic_tuning_options
As you already know, the sys.dm_db_tuning_recommendations DMV returns detailed information about tuning recommendations. The discussion of Examples 20.29 and 20.31 already explained the most important columns of the view. While most of the columns of this view are JSON documents, you need the OPENJSON functions to retrieve their values. The OPENJSON function presents JSON documents as relational data. That way, the JSON values can be “joined” with values in relational columns, as has been done in Examples 20.29 and 20.31. (The OPENJSON function is described in detail in Chapter 29.)
The sys.database_automatic_tuning_options view returns the automatic tuning options for the current database. The most important columns are name, actual_state, desired_state, and reason. The name column specifies the name of the automatic tuning option (see the output of Example 20.32). The actual_state column indicates the operation mode of the automatic tuning. The desired_state column indicates the desired operation mode for the automatic tuning option, explicitly set by user. The possible values for the actual_state and desired_state columns are 0 (OFF) and 1 (ON). The reason column indicates why actual and desired states are different. Example 20.32 shows the use of this view.
Example 20.32
USE sample;
SELECT name, actual_state, desired_state, reason
FROM sys.database_automatic_tuning_options;
If you execute this statement after the statement in Example 20.30, the result will be
name actual_state desired_state reason
—————————————————————-
FORCE_LAST_GOOD_PLAN 0 0 NULL
The result of Example 20.32 shows that the automatic tuning option of the system has forced the last good execution plan. Also, there is no difference between the operation mode of the system and the mode of the user. Therefore, the value of the reason column is NULL.
3. Performance Data Collector
Generally, it is very hard for DBAs to track down performance problems, primarily because DBAs usually are not there at the exact time a problem occurs, and thus they have to react to an existing problem by first tracking it down. Microsoft has included an entire infrastructure called Performance Data Collector to solve the problem.
Performance Data Collector is a component that is installed as a part of an instance of the Database Engine and can be configured to run either on a defined schedule or nonstop. The tool has three tasks:
- To collect different sets of data related to performance
- To store this data in the management data warehouse (MDW)
- To allow a user to view collected data using predefined reports
To use Performance Data Collector, you have to configure the MDW first. To do this using SQL Server Management Studio, expand the server, expand Management, right-click Data Collection, click Tasks, and select Configure Management Data Warehouse. The Configure Management Data Warehouse Wizard appears. The wizard has two tasks: to create the MDW and to set up data collection. After you complete these tasks, you can run Performance Data Collector and view the reports it generates.
3.1. Creating the MDW
After you click Next on the Welcome screen of the wizard, the Configure Data Warehouse Storage screen appears. Choose a server and database to host your management data warehouse and click Next. On the Map Login and Users screen, map existing logins and users to MDW roles. This activity has to be performed explicitly because no user is a member of an MDW role by default. Click Next when you are finished. On the Complete the Wizard screen, verify the configuration and click Finish.
3.2. Setting Up Data Collection
After setting up the MDW, you have to start data collection. Choose Tasks and then Configure Data Collection. Click Next. On the Setup Data Collection Sets screen (see Figure 20-12), specify the server name and the name of the data warehouse that you created in the prior section, and then specify where you want collected data to be cached locally before it is uploaded to the MDW. Click Next. On the Complete the Wizard screen, click Finish. The wizard finishes its work, giving you a summary of the executed tasks.
3.3. Viewing Reports
Once Performance Data Collector is configured and active, the system will start collecting performance information and uploading the data to the MDW. Also, three new reports (Server Activity History, Disk Usage Summary, and Query Statistics History) will be created for viewing data collected by Performance Data Collector. To open the reports, right-click Data Collection, click Reports, and choose one of these reports under Management Data Warehouse.
The first report, Server Activity History, displays performance statistics for system resources described in this chapter. The second report, Disk Usage Summary, displays the starting size and average daily growth of data and log files. The last report, Query Statistics History, displays query execution statistics.
4. Resource Governor
One of the biggest problems in relation to performance tuning is trying to manage resources with the competing workloads on a shared database server. You can solve this problem using either server virtualization or several instances. In both cases, it is not possible for an instance to ascertain whether the other instances (or virtual machines) are using memory and the CPU. Resource Governor manages such a situation by enabling one instance to reserve a portion of a system resource for a particular process.
Generally, Resource Governor enables DBAs to define resource limits and priorities for different workloads. That way, consistent performance for processes can be achieved.
Resource Governor has two main components:
- Workload groups
- Resource pools
When a process connects to the Database Engine, it is classified and then assigned to a workload group based on that classification. (The classification is done using either a built-in classifier or a user-defined function.) One or more workload groups are then assigned to specific resource pools (see Figure 20-13).
As you can see in Figure 20-13, there are two different workload groups:
- Internal group
- Default group
The internal group is used to execute certain system functions, while the default group is used when the process doesn’t have a defined classification. (You cannot modify the classification for the internal group. However, monitoring of the workload of the internal group is possible.)
NOTE The internal and default groups are predefined workload groups. In addition to them, the tool allows the specification of 18 additional (user-defined) workload groups.
A resource pool represents the allocation of system resources of the Database Engine. Each resource pool has two different parts, which specify the minimum and maximum resource reservation. While minimum allocations of all pool resources cannot overlap, the sum of them cannot exceed 100 percent of all system resources. On the other hand, the maximum value of a resource pool can be set between its minimal value and 100 percent.
Analogous to workload groups, there are two predefined resource pools: the internal pool and the default pool. The internal pool contains the system resources, which are used by the internal processes of the system. The default pool contains both the default workload group and user-defined groups.
4.1. Creation of Workload and Resource Groups
The following steps are necessary to create workload and resource groups:
- Create resource pools.
- Create workload groups and assign them to pools.
- For each workload group, define and register the corresponding classification function.
Resource Governor can be managed using SQL Server Management Studio or Transact-SQL. I will show you how you can manage it using Management Studio.
Before you create new resource pools, you have to check whether Resource Governor is enabled. To do this, start Management Studio, expand the server, expand Management, right-click Resource Governor, and click Enable. (Alternatively, you can use the ALTER RESOURCE GOVERNOR T-SQL statement.)
NOTE Resource pools and workload groups can be created in one dialog box, as described next.
To create a new resource pool, in Management Studio, expand the instance, expand Management, expand Resource Governor, right-click Resource Pools, and click New Resource Pool. The Resource Governor Properties dialog box appears (see Figure 20-14).
Generally, when you create a new resource pool, you have to specify its name and the minimum and maximum boundaries for CPU and memory. Therefore, in the Resource Pools table of the Resource Governor Properties dialog box, click the first column of the first empty row and type the name of the new pool. After that, specify the minimum and maximum values for CPU and memory.
You can specify a corresponding workload group in the same dialog box (see Figure 20-14). In the Workload Groups for Resource Pool table, double-click the empty cell in the Name column and type the name of the corresponding group. Optionally, you can specify several different properties for that workload group. Click OK to exit the dialog box.
NOTE To create a new resource pool using T-SQL, use the CREATE RESOURCE POOL statement. To create a new workload group, use the CREATE WORKLOAD GROUP statement.
After you specify the new pool and its corresponding workload group, you have to create a classification function. This function is a user-defined function that is used to create any association between a workload group and users. (An example of such a function can be found in the description of the ALTER RESOURCE GOVERNOR statement in Microsoft Docs.)
4.2. Monitoring Configuration of Resource Governor
The following two DMVs can be used to monitor workload groups and resource pools:
- dm_resource_governor_workload_groups
- dm_resource_governor_resource_pools
The sys.dm_resource_governor_workload_groups view displays the information concerning workload groups. The total_query_optimization_count column of this view displays the cumulative count of query optimizations in this workload group; if the value is too high, it may indicate memory pressure.
The sys.dm_resource_governor_resource_pools view displays the information concerning resource pools. The total_cpu_usage_ms and used_memory_kb columns specify the total usage of CPU and the used memory, respectively, and indicate how your resource pools consume these two system resources.
5. Live Query Statistics
Accurate real-time estimation of the overall progress of a Transact-SQL query execution is very important for database administrators. This information can help DBAs to decide whether a long-running, resource-intensive query should be terminated or allowed to run to completion. Live Query Statistics allows the real-time monitoring of various execution metrics, including CPU memory usage, execution time, and progress of query execution.
Since SQL Server 2016, estimation of progress for individual operators in a query execution plan has been supported. This feature allows DBAs to rapidly identify issues with operators that require significantly more time or resources than expected.
This feature can be enabled in several ways using SQL Server Management Studio. First, you can click the Include Live Query Statistics icon, located in the toolbar of Management Studio. Second, you can launch it from Object Explorer to monitor queries executed by others by right-clicking the Server node, selecting Activity Monitor, and clicking the Active Expensive Queries option; all currently executing queries are listed. Figure 20-15 shows how you can monitor several parameters in relation to a given query.
NOTE The use of Live Query Statistics is demonstrated in Chapter 28 (see Example 28.3).
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.