The choice of an appropriate tool depends on the performance factors to be monitored and the type of monitoring. The type of monitoring can be
- Real time
- Delayed (by saving information in the file, for example)
Real-time monitoring means that performance issues are investigated as they are happening. If you want to display the actual values of one or a few performance factors, such as the number of users or number of attempted logins, use dynamic management views because of their simplicity. In fact, DMVs can only be used for real-time monitoring. Therefore, if you want to trace performance activities during a specific time period, you have to use a tool such as SQL Server Profiler or Extended Events, both of which are described in this section.
Probably the best all-around tool for monitoring is the already described tool called Performance Monitor because of its many options. First, you can choose the performance activities you want to track and then display them simultaneously. Second, Performance Monitor allows you to set thresholds on specific counters (performance factors) to generate alerts that notify operators. This way, you can react promptly to any performance bottlenecks. Third, you can report performance activities and investigate the resulting chart log files later.
The following sections describe SQL Server Profiler, the Database Engine Tuning Advisor, and Extended Events.
1. SQL Server Profiler
SQL Server Profiler is a graphical tool that lets system administrators monitor and record database and server activities, such as login, user, and application information. SQL Server Profiler can display information about several server activities in real time, or it can create filters to focus on particular events of a user, types of commands, or types of Transact-SQL statements. Among others, you can monitor the following events using SQL Server Profiler:
- Login connections, attempts, failures, and disconnections
- CPU use of a batch
- Deadlock problems
- All DML statements (SELECT, INSERT, UPDATE, and DELETE)
- The start and/or end of a stored procedure
NOTE Since SQL Server 2017, SQL Server Profiler has been identified by Microsoft as a deprecated feature. This means that this tool may be removed any time. Microsoft recommends using Extended Events for tracing. (Extended Events is described in detail later in this chapter.)
The most useful feature of SQL Server Profiler is the ability to capture activities in relation to queries. These activities can be used as input for the Database Engine Tuning Advisor, which allows you to select indices and indexed views for one or more queries. For this reason, the following section discusses the features of SQL Server Profiler together with the Database Engine Tuning Advisor.
2. Database Engine Tuning Advisor
The Database Engine Tuning Advisor is part of the overall system and allows you to automate the physical design of your databases. As mentioned earlier, the Database Engine Tuning Advisor is tightly connected to SQL Server Profiler, which can display information about several server activities in real time, or it can create filters to focus on particular events of a user, types of commands, or Transact-SQL statements.
The specific feature of SQL Server Profiler that is used by the Database Engine Tuning Advisor is its ability to watch and record batches executed by users and to provide performance information, such as CPU use of a batch and corresponding I/O statistics, as explained next.
2.1. Providing Information for the Database Engine Tuning Advisor
The Database Engine Tuning Advisor is usually used together with SQL Server Profiler to automate tuning processes. You use SQL Server Profiler to record into a trace file information about the workload being examined. (As an alternative to a workload file, you can use any file that contains a set of Transact-SQL statements. In this case, you do not need SQL Server Profiler.) The Database Engine Tuning Advisor can then read the file and recommend several physical objects, such as indices, indexed views, and partitioning schema, that should be created for the given workload.
Example 20.10 creates two new tables, orders and order_details. These tables will be used to demonstrate the recommendation of physical objects by the Database Engine Tuning Advisor.
NOTE If your sample database already contains the orders table, you have to drop it using the DROP TABLE statement.
Example 20.10
USE sample;
CREATE TABLE orders
(orderid INTEGER NOT NULL,
orderdate DATE,
shippeddate DATE,
freight money);
CREATE TABLE order_details
(productid INTEGER NOT NULL,
orderid INTEGER NOT NULL,
unitprice money,
quantity INTEGER);
To demonstrate the use of the Database Engine Tuning Advisor, many more rows are needed in both tables. Examples 20.11 and 20.12 insert 3000 rows in the orders table and 30,000 rows in the order_details table, respectively.
Example 20.11
— This batch inserts 3000 rows in the table orders
USE sample;
declare @i int, @order_id integer
declare @orderdate datetime
declare @shipped_date datetime
declare @freight money
set @i = 1
set @orderdate = getdate()
set @shipped_date = getdate()
set @freight = 100.00
while @i < 3001
begin
insert into orders (orderid, orderdate, shippeddate, freight)
values(@i, @orderdate, @shipped_date, @freight)
set @i = @i+1
end
Example 20.12
— This batch inserts 30000 rows in order_details and modifies some of them
USE sample;
declare @i int, @j int
set @i = 3000
set @j = 10
while @j > 0
begin
if @i > 0
begin
insert into order_details (productid, orderid, quantity)
values (@i, @j, 5)
set @i = @i – 1
end
else begin
set @j = @j – 1
set @i = 3000
end
end
go
update order_details set quantity = 3
where productid in (1511, 2678)
The query in Example 20.13 will be used as an input file for SQL Server Profiler. (Assume that no indices are created for the columns that appear in the SELECT statement.) Click the Windows Start icon or press the Windows key and type SQL Server Profiler. When the SQL Server Profiler tile appears, click it. On the File menu, choose New Trace. After connecting to the server, the Trace Properties dialog box appears. Type a name for the trace and select an output .trc file for the Profiler information (in the Save to File field). Click Run to start the capture and use SQL Server Management Studio to execute the query in Example 20.13.
Example 20.13
USE sample;
SELECT orders.orderid, orders.shippeddate
FROM orders
WHERE orders.orderid between 806 and 1600
and not exists (SELECT order_details.orderid
FROM order_details
WHERE order_details.orderid = orders.orderid);
Finally, stop SQL Server Profiler by choosing File | Stop Trace and selecting the corresponding trace.
2.2. Working with the Database Engine Tuning Advisor
The Database Engine Tuning Advisor analyzes a workload and recommends the physical design of one or more databases. The analysis will include recommendations to add, remove, or modify the physical database structures, such as indices, indexed views, and partitions. The Database Engine Tuning Advisor will recommend a set of physical database structures that will optimize the tasks included in the workload.
To start the Database Engine Tuning Advisor, shown in Figure 20-2, begin typing its name in the Windows Search bar on the Start menu, and click its name when it appears in the results. (The alternative way is to start SQL Server Profiler and choose Tools | Database Engine Tuning Advisor.)
In the Session Name field, type the name of the session for which the Database Engine Tuning Advisor will create tuning recommendations. In the Workload frame, choose either File or Table. If you choose File, enter the name of the trace file. If you choose Table, you must enter the name of the table that is created by SQL Server Profiler. (Using SQL Server Profiler, you can capture and save data about each workload to a file or to a SQL Server table.)
NOTE Running SQL Server Profiler can place a heavy burden on a busy instance of the Database Engine.
In the Select Databases and Tables to Tune frame, choose one or more databases and/or one or more tables that you want to tune. (The Database Engine Tuning Advisor can tune a workload that involves multiple databases. This means that the tool can recommend indices, indexed views, and partitioning schema on any of the databases in the workload.) For our example, select the sample database and its two tables, orders and order_details.
NOTE If you choose Plan Cache as the workload option (see Figure 20-2), the Database Engine Tuning Advisor selects the top 1000 events from the plan cache to use for analysis. For a detailed description of how to tune a database using the plan cache, see Microsoft Docs.
To choose options for tuning, click the Tuning Options tab (see Figure 20-3). Most of the options on this tab are divided into three groups:
- Physical Design Structures (PDS) to use in database Allows you to choose which physical structures (indices and/or indexed views) should be recommended by the Database Engine Tuning Advisor, after tuning the existing workload. (The Evaluate Utilization of Existing PDS Only option causes the Database Engine Tuning Advisor to analyze the existing physical structures and recommend which of them should be deleted.)
- Partitioning strategy to employ Allows you to choose whether or not partitioning recommendations should be made. If you opt for partitioning recommendations, you can also choose the type of partitioning, full or aligned. (Partitioning is discussed in detail in Chapter 26.)
- Physical Design Structures (PDS) to keep in database Enables you to decide which, if any, existing structures should remain intact in the database after the tuning process.
For large databases, tuning physical structures usually requires a significant amount of time and resources. Instead of starting an exhaustive search for possible indices, the Database Engine
Tuning Advisor offers (by default) the restrictive use of resources. This operation mode still gives very accurate results, although the number of resources tuned is significantly reduced.
During the specification of tuning options, you can define additional customization options by clicking Advanced Options, which opens the Advanced Tuning Options dialog box (see Figure 20-4). Checking the check box at the top of the dialog box enables you to define the maximum space for recommendations. Increase the maximum space to 20MB if you intend to start an exhaustive search. (For large databases, selection of physical structures usually requires a significant amount of resources. Instead of starting an exhaustive search, the Database Engine Tuning Advisor offers you the option to restrict the space used for tuning.)
Of all index tuning options, one of the most interesting is the second option in this dialog box, which enables you to determine the maximum number of columns per index. A single-column index or a composite index built on two columns can be used several times for a workload with many queries and requires less storage space than a composite index built on four or more columns. (This applies in the case where you use a workload file on your own instead of using SQL Server Profiler’s trace for the specific workload.) On the other hand, a composite index built on four or more columns may be used as a covering index to enable index-only access for some of the queries in the workload. (For more information on covering indices, see Chapter 10.)
After you select options in the Advanced Tuning Options dialog box, click OK to close it. You can then start the analysis of the workload. To start the tuning process, choose Actions | Start Analysis. After you start the tuning process for the trace file of the query in Example 20.13 (presented earlier), the Database Engine Tuning Advisor creates tuning recommendations, which you can view by clicking the Recommendations tab, as shown in Figure 20-5. As you can see, the Database Engine Tuning Advisor recommends the creation of two indices.
The Database Engine Tuning Advisor recommendations concerning physical structures can be viewed using a series of reports that provide information about very interesting options.
These reports enable you to see how the Database Engine Tuning Advisor evaluated the workload. To see these reports, click the Reports tab in the Database Engine Tuning Advisor dialog box after the tuning process is finished. You can see the following reports, among others:
- Index Usage Report (recommended) Displays information about the expected usage of the recommended indices and their estimated sizes
- Index Usage Report (current) Presents the same information about expected usage for the existing configuration
- Index Detail Report (recommended) Displays information about the names of all recommended indices and their types
- Index Detail Report (current) Presents the same information for the actual configuration, before the tuning process was started
- Table Access Report Displays information about the costs of all queries in the workload (using tables in the database)
- Workload Analysis Report Provides information about the relative frequencies of all data modification statements (costs are calculated relative to the most expensive statement with the current index configuration)
There are two ways in which you can apply recommendations: immediately or after saving to the file. If you choose Actions | Apply Recommendations, the recommendations will be applied immediately. Similarly, if you choose Actions | Save Recommendations, the recommendations will be saved to the file. (This alternative is useful if you generate the script with a test system and intend to use the tuning recommendation with a production system.) The third option, Actions | Evaluate Recommendations, is used to evaluate the recommendations produced by the Database Engine Tuning Advisor.
3. Extended Events
Extended Events is a tracing and troubleshooting framework that enables you to control information at a granular level. That way, DBAs and programmers can collect information to find any performance bottlenecks and other system properties in the Database Engine.
3.1. Extended Events: Architecture
The architecture of Extended Events is based upon objects. There are four groups of objects:
- Events
- Actions
- Targets
- Predicates
Events indicate points of interest in an execution path. You can think of events as important places in the programming code where the execution is traced.
Actions are functions, which are invoked by events. In other words, an action is triggered when an event fires. At that point in time, all the columns in the event are available.
Targets consume events, either synchronously on the thread that fires the event, or asynchronously on a system-provided thread. In other words, a target is a destination where events can be published and stored. There are two different target types: event_file and ring_ buffer. The former permanently stores events to a file, while the latter saves events to memory buffers, which store volatile data.
Predicates retrieve values from event sources for use in comparison operations. They compare specific data types and return a Boolean value.
Another important concept of Extended Events is a session. To use Extended Events, you have to create a session. A session is used to trace the particular event(s). Extended Events sessions are stored in system tables, which belong to the master database and are exposed through catalog views and dynamic management views. To activate a created session, you have to start it. After starting, the session traces the corresponding event(s) and publishes information to the defined target, until it is explicitly stopped.
The creation of a session can be done either with SQL Server Management Studio or programmatically. The following sections describe both ways.
3.2. Creating a Session Using SQL Server Management Studio
To create a session in SQL Server Management Studio, select Management | Extended Events in Object Explorer. Right-click the Sessions folder and select New Session. The New Session window appears. In the window, there are four different pages (see the left pane of Figure 20-6):
- General
- Events
- Data Storage
- Advanced
The General page allows you to specify the session name and (optionally) to define the schedule of that session. In the Events page, shown in Figure 20-6, you select the events you want to capture from the event library and specify the properties to be traced.
Extended Events targets event data and stores it persistently or volatile. The Data Storage page allows you to specify file(s) where the data is stored persistently. Using the Advanced page you can modify miscellaneous values, such as maximum memory size and maximum event size.
As an example of how to enable an extended event and start it, suppose that you are interested in tracing all long-running queries with an execution time longer than one second.
To start, select the General page and enter the name Special_queries in the Session Name field. Additionally, you can choose between two different schedule forms. One starts the event session at server startup and the other starts the event session immediately after the session is created.
Now, select Events in the left pane. On the Events page, you can select the events you want to capture from the event library. To select one or more related events, type in the Event Library search box a phrase that is common for all related events. In the case of long-running queries, you are concerned with SQL statements, so type, for instance, the phrase sql_stat (see Figure 20-6). The system finds three hits: sql statement completed, sql statement recompile, and sql statement starting. You are interested in the completed queries, so select sql statement completed and click the right-pointing arrow between the left pane and right pane to move the selected event to the Selected Events list.
The Events page is also used to configure the event and add a filter condition. To configure the event, click the Configure button in the top right of the Events page. The Event Configuration Options section appears to the right of the Selected Events section, as shown in Figure 20-7. In the Event Configuration Options section, you first specify actions. Suppose that you want to know the name of users who execute long-running queries. Therefore, choose the Global Fields (Actions) tab. The list of all possible actions appears. Check the username check box.
Next, you specify the corresponding predicate, which states that the event should fire when the particular query runs too long. Therefore, on the Events page, choose the Filter (Predicate) tab (see Figure 20-7). Click where indicated to add a filter clause. Click in the Field column and select duration. (To select the desired action, you have to scroll through the list of all actions.) Under Operator, select >=. Under Value, enter 1000. That way, you restrict the created session to capture only those queries that run longer than 1000 milliseconds (1 second).
Move to the next page of the New Session window, Data Storage, where you specify how event data should be stored. You want to store the gathered information in a file. Therefore, in the Targets section, under Type, select event_file. In the Properties section, specify in the File Name on Server field the location and the name of your event file. (As you already know, the event_file option allows you to persistently save content of the event to a file.) Optionally, you can change the maximum file size in the field with the same name.
Now, select Advanced in the left pane. On the corresponding page, you can specify the event retention mode, the maximum dispatch latency, the maximum memory size, and the maximum event size.
You have completed the modification of all four pages so now you can click the OK button to create the session. If on the General page you chose Start Event Session Immediately After Session Creation, the session starts. If you chose Start the Event Session at Server Startup, the session is inactive. To start the session and track the corresponding event, select Management | Extended Events | Sessions and right-click the name of your session in Object Explorer. Select Start Session. This starts the tracking process. To stop capturing the data, choose Stop Session.
NOTE You can change properties of an already created session by right-clicking its name and selecting Properties. After that, you can modify properties by clicking the particular page (General, Events, Data Storage, and Advanced).
To display the collected data, double-click package0.event_file under the session name.
A new query window will open with the list of tracked events and corresponding values. Select an event to see the details of the collected data in the result window (see Figure 20-8).
NOTE Another example in relation to Extended Events can be found in the “Memory Grant Feedback” section of Chapter 28.
3.3. Creating a Session Using Transact-SQL
You can use T-SQL statements to programmatically create a new event session. Example 20.14 creates a session called session!
Example 20.14
CREATE EVENT SESSION sessionl ON SERVER
ADD EVENT sqlserver.sql_batch_starting(ACTION(package0.event_
sequence,sqlserver.client_app_name/sqlserver.client_pid/sqlserver.
database_id,sqlserver.database_name,sqlserver.nt_username/sqlserver.query_
hash,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
ADD TARGET package0.event_file(SET filename=N’Session1.xel’,
max_file_ size=(5),max_rollover_files=(4))
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
NOTE The following discussion of the CREATE EVENT SESSION statement is superficial, because the easier way to create a session is to use SQL Server Management Studio. For the description of all options of this statement, see Microsoft Docs.
As its name suggests, the CREATE EVENT SESSION statement creates an event session. The statement contains one or more ADD EVENT clauses. Each ADD EVENT clause associates a particular event with the event session. The ADD EVENT clause can include three options: SET, ACTION, and WHERE. The SET option allows you to set attributes for the event. The ACTION option specifies what you want to capture, while the WHERE option specifies the predicate expression used to determine the condition for whether an event should be processed.
The ADD TARGET clause specifies the target to associate with the event session. The name of the target is in three parts, specified in the following form:
[event_module_guid].event_package_name.target_name
The first part of the name defines the global ID of the event module, the second part specifies the name of the event package, and the last part specifies the name of the target. The global ID is necessary only in the case that the target is not locally stored. Therefore, in Example 20.14 the global ID is omitted, while the event package is called package0 and the target name is event_file. (You can use the optional SET clause to set different target parameters.)
As you already know, the session by default is in an inactive state. Examples 20.15 and 20.16 show you how to start and stop a session using Transact-SQL, respectively.
Example 20.15
USE master;
ALTER EVENT SESSION Session! ON SERVER STATE=start
Example 20.16
USE master;
ALTER EVENT SESSION Sessionl ON SERVER STATE=stop
3.4. Editing Information Concerning Extended Events
You can use several catalog views and/or dynamic management views to obtain information concerning events. This section discusses three catalog views:
- server_event_sessions
- server_event_session_events
- server_event_session_actions
and two dynamic management views:
- dm_xe_packages
- dm_xe_objects
The sys.server_event_sessions catalog view lists all the event session definitions that exist in the Database Engine. The most important columns of this view are event_session_id, which specifies the unique ID of the event session, and name, which defines the unique name for identifying the event session.
The sys.server_event_session_events catalog view returns a row for each event in an event session. The most important columns of this view are event_session_id, event_id, and name. The first column specifies the unique ID of the event session, and the last two columns define the ID and the name of the event, respectively.
The sys.server_event_session_actions catalog view returns a row for each action on each event in an event session. The most important columns of this view are event_session_id, event_id, and name. The first two columns specify the unique ID of the event session and event, respectively, and the name column defines the name of the action.
Example 20.17 shows how you can display information concerning an event session, its events, and the corresponding actions.
Example 20.17
USE master;
SELECT sessions.name AS SessionName, eventl.package as PackageName,
event1.name AS EventName,action1.name AS ActionName
FROM sys.server_event_sessions sessions
INNER JOIN sys.server_event_session_events event1
ON sessions.event_session_id = event1.event_session_id
INNER JOIN sys.server_event_session_actions action1
ON sessions.event_session_id = action1.event_session_id
WHERE sessions.name = ‘session1’ ;
The result is
Example 20.17 uses three catalog views described earlier to display the information concerning the session called session1 (see Example 20.14). To display all events and actions contained in this session, you have to join the sys.server_event_sessions catalog view with two other views, sys.server_event_session_events and sys.server_event_session_actions, using the column sessions.event_session_id, which exists in all three views. (The sessions.event_ session_id column specifies the unique ID of the corresponding session.)
NOTE Example 20.17 can easily be exetended to display where particular events are stored. The corresponding catalog view is called sys.server_event_session_targets (see Exercise 20.3).
As mentioned at the beginning of this section, you also can use two dynamic management views to obtain information about events. The sys.dm_xe_packages DMV lists all the packages registered with Extended Events of your system. The most important columns of this view are name, which specifies the name of package, and guid, which is the global identifier that identifies the package.
The sys.dm_xe_objects DMV returns a row for each object type that is exposed by an event package. (The object type can be an event, action, or target.) The most important columns of this view are name, object_type, and package_guid.name specifies the name of the object, while object_type specifies its type. package_guid is the global ID for the package that exposes the corresponding action.
Example 20.18 displays the names of all objects of the event type that belong to the system package called filestream.
Example 20.18
USE master;
SELECT pkg.name as PackageName, obj.name as EventName
FROM sys.dm_xe_packages pkg
INNER JOIN sys.dm_xe_objects obj on pkg.guid = obj.package_guid
WHERE obj.object_type = ‘event’ AND pkg.name = ‘filestream’
ORDER by 2;
The result is
Example 20.18 uses sys.dm_xe_packages and sys.dm_xe_objects to display the requested objects. Both DMVs are joined using the guid column of the former view and the package_ guid of the latter view.
3.5. Advantages of Extended Events over SQL Server Profiler
The advantages of using extended events in relation to SQL Server Profiler are as follows:
- They are lightweight.
- They can trace and track more events.
- They provide better flexibility.
The most important advantage of Extended Events in relation to SQL Server Profiler is that the former does not have as much negative influence on the database server as the latter. SQL Server Profiler is a GUI that utilizes SQL Server Trace through the client side. This has impact on your system, and this impact can be severe. On the other hand, Extended Events is lightweight and uses a limited amount of resources, so it does not have any significant negative impact on the system performance.
Extended Events was introduced in SQL Server 2008. At that time, the system supported 253 events. Over time, the number of supported extended events significantly increased. For instance, SQL Server 2017 supports 564 different events, while in SQL Profiler there are altogether 235 events.
Extended events are more flexible than the events supported by SQL Server Profiler. For example, you can bind extended events to any target or you can specify any action with any event. Additionally, you can dynamically filter extended events using predicates. Finally, although extended events are fired synchronously in a host application, they can be processed asynchronously, too.
Besides SQL Server Profiler, a set of existing dynamic management views is another “tool” that provides similar functionality to extended events, because DMVs dynamically trace the state of the system as well as its performance. The main difference between them is that DMVs provide a snapshot of the system, while the trace made by extended events has a specific duration that is configurable.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.