Figure 4-38 shows the relationship between several components managed by Resource Governor. A resource pool defines the physical resources of the server and behaves much like a virtual server. SQL Server creates an internal pool and a default pool during installation, and you can add user-defined resource pools. You associate one or more workload groups, a set of requests having common characteristics, to a resource pool. As SQL Server receives a request from a session, the classification process assigns it to the workload group having matching characteristics. You can fine-tune the results of this process by creating classifier user-defined functions.

You must enable Resource Governor to start using it. You can do this in SQL Server Management Studio by expanding the Management node in Object Explorer, right-clicking Resource Governor, and selecting Enable. As an alternative, you can execute the following T-SQL statement:

Click here to view code image

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

After you enable Resource Governor, SQL Server has the following types of resource pools:

Internal SQL Server uses the internal resource pool for resources required to run the database engine. You cannot change the resource configuration for the internal resource pool. SQL Server creates one when you enable the Resource Governor.

Default In SQL Server 2016, there is one resource pool for standard database operations and a separate resource pool for external processes such as R script execution. These two resource pools are created when you enable the Resource Governor.

External An external resource pool is a new type for SQL Server 2016 that was added to support R Services. Because the execution of R scripts can be resource-intensive, the ability to manage resource consumption by using the Resource Governor is necessary to protect normal database operations. In addition, you can add an external resource pool to allocate resources for other external processes. The configuration for an external resource pool differs from the other resource pool types and includes only the following settings: Maximum CPU%, Maximum Memory %, and Maximum Processes.

User-defined resource pool You can add a resource pool to allocate resources for database operations related to a specific workload.

You can use the Resource Governor node in Object Explorer to open a dialog box and add or configure resource pools as needed, although this interface does not include all settings available to configure by using T-SQL. When you create a resource pool by using T-SQL, as shown in Listing 4-33, you specify any or all arguments for CPU, the scheduler, memory, and I/O operations per second (IOPS).

Listing 4-33 Create user-defined resource pools

Click here to view code image

---

CREATE RESOURCE POOL poolExamBookDaytime
WITH (
    MIN_CPU_PERCENT = 50,
    MAX_CPU_PERCENT = 80,
    CAP_CPU_PERCENT = 90,
    AFFINITY SCHEDULER = (0 TO 3),
    MIN_MEMORY_PERCENT = 50,
    MAX_MEMORY_PERCENT = 100,
    MIN_IOPS_PER_VOLUME = 20,
    MAX_IOPS_PER_VOLUME = 100
);
GO
CREATE RESOURCE POOL poolExamBookNighttime
WITH (
    MIN_CPU_PERCENT =       0,
    MAX_CPU_PERCENT = 50,
    CAP_CPU_PERCENT = 50,
    AFFINITY SCHEDULER = (0 TO 3),
    MIN_MEMORY_PERCENT = 5,
    MAX_MEMORY_PERCENT = 15,
    MIN_IOPS_PER_VOLUME = 45,
    MAX_IOPS_PER_VOLUME = 100
);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

---

When you configure a workload group, as shown in Listing 4-34, you can specify the relative importance of a workload group as compared to other workload groups in the same resource pool only. You can also specify the maximum amount of memory or CPU time that a request in the workload group can acquire from the resource pool, the maximum degree of parallelism (DOP) for parallel requests, or the maximum number of concurrent requests.

Listing 4-34 Create workload groups

Click here to view code image

---

CREATE WORKLOAD GROUP apps
WITH (
    IMPORTANCE = HIGH,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 35,
    REQUEST_MAX_CPU_TIME_SEC = 0, --0 = unlimited
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60, --seconds
    MAX_DOP = 0, -- uses global setting
    GROUP_MAX_REQUESTS = 1000 --0 = unlimited
)
USING "poolExamBookNighttime";
GO
CREATE WORKLOAD GROUP reports
WITH (
    IMPORTANCE = LOW,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    REQUEST_MAX_CPU_TIME_SEC = 0, --0 = unlimited
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60, --seconds
    MAX_DOP = 0, -- uses global setting
    GROUP_MAX_REQUESTS = 100 --0 = unlimited
)
USING "poolExamBookNighttime";
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

---

Let’s say that you want to establish a classification function to assign a request to a workload group based on the time of day. Furthermore, you want to use a lookup table for the start and end times applicable to a workload group. Let’s start by creating and adding a row to the lookup table, as shown in Listing 4-35. Note that you must create this table in the master database because Resource Governor uses schema bindings for classifier functions.

Listing 4-35 Create lookup table

Click here to view code image

---

USE master
GO
CREATE TABLE tblClassificationTime  (
    TimeOfDay SYSNAME NOT NULL,
    TimeStart TIME NOT NULL,
    TimeEnd   TIME NOT NULL
) ;
GO
INSERT INTO tblClassificationTime
VALUES('apps', '8:00 AM', '6:00 PM');
GO
INSERT INTO tblClassificationTime
VALUES('reports', '6:00 PM', '8:00 AM');
GO

---

Next, you create the classifier function that uses the lookup table to instruct the Resource Governor which workload group to use when classifying an incoming request. An example of such a classifier function is shown in Listing 4-36.

Listing 4-36 Create and register classifier function

Click here to view code image

---

USE master;
GO
CREATE FUNCTION fnTimeOfDayClassifier()
RETURNS sysname
WITH SCHEMABINDING  AS
BEGIN
    DECLARE @TimeOfDay sysname
    DECLARE @loginTime time
    SET @loginTime = CONVERT(time,GETDATE())
    SELECT
        TOP 1 @TimeOfDay = TimeOfDay
    FROM dbo.tblClassificationTime
    WHERE TimeStart <= @loginTime and TimeEnd >= @loginTime
    IF(@TimeOfDay IS NOT NULL)
        BEGIN
            RETURN @TimeOfDay
        END
    RETURN N'default'
END;
GO
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeOfDayClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

---

Listing 4-37 Monitor resource consumption

Click here to view code image

---

--Current runtime data
SELECT * FROM sys.dm_resource_governor_resource_pools;
GO

SELECT * FROM sys.dm_resource_governor_workload_groups;
GO

--Determine the workload group for each session
SELECT
    s.group_id,
    CAST(g.name as nvarchar(20)) AS WkGrp,
    s.session_id,
    s.login_time,
    CAST(s.host_name as nvarchar(20)) AS Host,
    CAST(s.program_name AS nvarchar(20))  AS Program
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
    ON g.group_id = s.group_id
ORDER BY g.name ;
GO

SELECT
    r.group_id,
    g.name,
    r.status,
    r.session_id,
    r.request_id,
    r.start_time,
    r.command,
    r.sql_handle,
    t.text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_resource_governor_workload_groups g
    ON g.group_id = r.group_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    ORDER BY g.name
GO

-- Determine the classifier running the request
SELECT
    s.group_id,
    g.name,
    s.session_id,
    s.login_time,
    s.host_name,
    s.program_name
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
    ON g.group_id = s.group_id  AND
        s.status = 'preconnect'
ORDER BY g.name;
GO

SELECT
    r.group_id,
    g.name,
    r.status,
    r.session_id,
    r.request_id,
    r.start_time,
    r.command,
    r.sql_handle,
    t.text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_resource_governor_workload_groups g
    ON g.group_id = r.group_id
        AND r.status = 'preconnect'
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name;
GO

---

Although sharding is not a new concept, it requires the use of custom code to create and manage sharded applications and adds complexity to your solution architecture. Elastic Scale provides an elastic database client library and a Split-Merge service that help simplify the management of your applications. That way you can adapt the capacity of SQL Database to support varying workloads and ensure consistent performance without manual intervention.

Shard map management You first register each database as a shard, and then define a shard map manager that directs connection requests to the correct shard by using a sharding key or a key range. A sharding key is data such as a customer ID number that the database engine uses to keep related transactions in one database.

Data-dependent routing Rather than define a connection in your application, you can use this feature to automatically assign a connection to the correct shard.

Multishard querying The database engine uses this feature to process queries in parallel across separate shards and then combine the results into a single result set.

Shard elasticity This feature monitors resource consumption for the current workload and dynamically allocates more resource as necessary and shrinks the database to its normal state when those resources are no longer required.

SQL Server 2016 is available in the following editions:

Express This edition is a free version of SQL Server with limited features that you can use for small applications and Web sites. The maximum database size supported by this edition is 10 GB. It uses up to 1 GB memory and to the lesser of 1 physical processor or 4 cores. There are three types of SQL Server 2016 Express from which to choose:

LocalDB You use LocalDB for a simple application with a local embedded database that runs in single-user mode.

Express You use Express when your application requires a small database only and does not require any other components packaged with SQL Server in the Standard edition. You can install this edition on a server and then enable remote connections to support multiple users.

Express with Advanced Services This edition includes the database engine as well as Full Text Search and Reporting Services.

Web This edition is scalable up to 64 GB of memory and the lesser of 4 physical processors or 16 cores with a maximum database size of 524 PB. It includes the database engine, but without support for availability groups and other high-availability features. It also does not include many of the advanced security and replication features available in Standard or Enterprise edition, nor does it include the business intelligence components such as Analysis Services and Reporting Services, among others. Web edition is intended for use only by Web hosters and third-party software service providers.

Standard This edition scales to 128 GB of memory and the lesser of 4 physical processors or 24 cores. The maximum database size with Standard edition is 524 PB. This edition includes core database and business intelligence functionality and includes basic high-availability and disaster recovery features, new security features such as row-level security and dynamic data masking, and access to non-relational data sources by using JSON and PolyBase.

Enterprise This edition includes all features available in the SQL Server platform and provides the highest scalability, greatest number of security features, and the most advanced business intelligence and analytics features. Like Standard edition, Enterprise edition supports a database size up to 524 PB, but its only limits on memory and processor sizes are the maximums set by your operating system.

To support higher availability, this edition supports up to 8 secondary replicas, with up to two synchronous secondary replicas, in an availability group, online page and file restore, online indexing, fast recovery, mirrored backups, and the ability to hot add memory and CPU.

For greater performance, Enterprise edition supports in-memory OLTP, table and index partitioning, data compression, Resource Governor, parallelism for partitioned tables, multiple file stream containers, and delayed durability, among other features.

Enterprise edition includes many security features not found in Standard edition. In particular, Always Encrypted protects data at rest and in motion. Additional security features exclusive to Enterprise edition include more finely-grained auditing, transparent data encryption, and extensible key management.

Features supporting data warehouse operations found only in Enterprise edition include change data capture, star join query optimizations, and parallel query processing on partitioned indexes and tables.

Developer This edition is for developers that create, test, and demonstrate applications using any of the data platform components available in Enterprise edition. However, the Developer edition cannot be used in a production environment.

Evaluation This edition is a free trial version of SQL Server 2016. You can use this for up to 180 days that you can use to explore all of the features available in Enterprise Edition before making a purchasing decision.

When you select the type of SQL Database to implement, you choose a service tier along with a performance level. The service tier sets the maximum database size while the performance level determines the amount of CPU, memory, and IO thresholds which collectively are measured as a DTU. When you create a new SQL Database, you can choose from the following service tiers:

Basic This service tier has a maximum database size of 2 GB and performance level of 5 DTUs. You use this option when you need a small database for an application or website with relatively few concurrent requests. The benchmark transaction rate is 16,600 transactions per hour.

Standard This service tier has a maximum database size of 250 GB and performance levels ranging from 10 to 100 DTUs. You can use this option when a database needs to support multiple applications and multiple concurrent requests for workgroup and web applications. With 50 DTUs, the benchmark transaction rate is 2,570 transactions per minute.

Premium This service tier has a maximum database size of 1 TB and performance levels ranging from 125 to 4,000 DTUs. You use this option for enterprise-level database requirements. With 1,000 DTUs, the benchmark transaction rate is 735 transactions per second.

File placement Data and log files should be placed on separate physical disks for better performance. Remember from the explanation of transactions that we described in Chapter 3, “Managing database concurrency,” that SQL Server writes each transaction to the log before updating the data file. By separating the two file types, the read/write head for the disk with the log file can work more efficiently without frequent interruptions by the writes to the data file. Furthermore, consider using a disk with high write performance for the log file. This recommendation is less applicable when the bulk of SQL Server activity is read operations.

By default, the data and log files for a new database are placed on the same drive, and normally in the same directory as the system databases: Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA. You can move the data and log files to a new location by using the ALTER DATABASE command as shown in Listing 4-38, replacing <databasename> and <drive:filepath> with the appropriate names for your database and folder structures.

LISTING 4-38 Relocate data and log files

Click here to view code image

---

ALTER DATABASE <databasename>
SET OFFLINE;
GO
ALTER DATABASE <databasename>
MODIFY FILE (NAME = <databasename>_Data, FILENAME = "<drive:filepath>
Data<databasename>_Data.mdf");
GO
ALTER DATABASE <databasename>
MODIFY FILE (NAME = <databasename>_Log, FILENAME = "drive:filepath>
Log<databasename>_Log.mdf");
ALTER DATABASE <databasename>
SET ONLINE;

---

Another benefit of separating data and log files on separate drives is mitigating a potential failure. If the drive containing the data files fails, you can still access the log file from the other disk and recover data up to the point of failure.

File groups and secondary data files By default, each database has a primary filegroup that contains the primary data file containing system tables and database files created without a filegroup specification. SQL Server uses the primary filegroup as the default for new indexes and tables that you create without placing them in a specific filegroup, but you can create a new filegroup and designate it as the default. There can only be one default filegroup, but you can create as many additional file groups as you need as containers for one or more data and log files. Files within a single filegroup can be spread across multiple disks to increase the parallelism of data access. Furthermore, you can separate tables or indexes that are heavily accessed from lesser used tables or indexes by assigning them to different filegroups, which in turn are each placed on separate disks. Listing 4-39 shows how to create a database with multiple filegroups and how to add a filegroup to an existing database to isolate an index on its own disk:

LISTING 4-39 Create and alter database with multiple file groups

Click here to view code image

---

--Create a database on 4 drives
CREATE DATABASE DB1 ON
PRIMARY
    (Name = <databasename>, FILENAME = '<drive1:filepath><databasename>.mdf'),
    FILEGROUP FGHeavyAccess1
    (Name = <databasename>_1, FILENAME = '<drive3:filepath><databasename>_1.ndf')
LOG ON
    (Name = <databasename>_1_Log, FILENAME = '<drive3:filepath><databasename>_1_
log.ldf'),
    (Name = <databasename>_1, FILENAME = '<drive4:filepath><databasename>_1_
log_2.ldf');
-- Add filegroup for index
ALTER DATABASE <databasename>
    ADD FILEGROUP FGIndex;
--  Add data file to the new filegroup
ALTER DATABASE <databasename>
ADD FILE (
    NAME = <databasename>,
    FILENAME = '<drive1:filepath><databasename>.ndf',
    SIZE=1024MB,
    MAXSIZE=10GB,
    FILEGROWTH=10%)
TO FILEGROUP FGIndex;
-- Add index to filegroup
CREATE NONCLUSTERED INDEX ix_Example
    ON Examples.BusyTable(TableColumn)
   ON FGIndex;

---

Partitioning You can use partitioning to place a table across multiple filegroups. Each partition should be in its own filegroup to improve performance. To map a value in a partition function to a specific filegroup, you use a partition scheme. Let’s say you have four filegroups—FGYear1, FGYear2, FGYear3, and FGYear4—and a partition function PFYearRange that defines four partitions for a table. You can create a partition schema to apply the partition function to these filegroups as shown in Listing 4-40.

LISTING 4-40 Create partition scheme to map partition function to filegroups

Click here to view code image

---

CREATE PARTITION SCHEME PSYear
    AS PARTITION PFYearRange
    TO (FGYear1, FGYear2, FGYear3, FGYear4);

---

SIMPLE recovery model By using the SIMPLE recovery model, which is the default, SQL Server reclaims log space automatically so that the space required for the database is kept as low as possible.

Autogrowth You should keep the default setting which allows tempdb files to automatically grow as needed.

File placement The tempdb data and log files should be placed on different disks than your production database data and log files. Do not place the tempdb data files on the C drive to prevent the server from failing to start after running out of hard drive space. In addition, be sure to place the tempdb log file on its own disk. Regardless, put tempdb files on fast drives.

Files per core In general, the number of data files for tempdb should be a 1:1 ratio of data files to CPU cores. In fact, in SQL Server 2016, the setup wizard now assigns the correct number based on the number of logical processors that it detects on your server, up to a maximum of 8, as shown in Figure 4-41.

File size When you configure the database engine at setup, the default file size recommended by the setup wizard of 8 MB for an initial size with an autogrowth setting of 64MB is conservative and too small for most implementations. Instead, consider starting with an initial size of 4,096 MB with an autogrowth setting of 512 MB to reduce contention and minimize the impact of uncontrolled tempdb growth on performance. If you dedicate a drive to tempdb, you can set up the log files evenly on the drive to avoid performance issues caused by SQL Server pausing user activity as it grows the log files.

min server memory Use this option to prevent SQL Server from releasing memory to the operating system when the server memory drops to this threshold.

max server memory Use this option to ensure that other applications running on the same computer as SQL Server have adequate memory. When an application requests memory only as needed, you do not need to configure this option. It applies only when an application uses the memory available when it starts and does not later request more memory when necessary. You should configure this option to prevent SQL Server from taking the memory that the application might need.

max worker threads Use this configuration to define the number of threads available to user operations. If you keep the default value of 0, SQL Server configures the number of worker threads each time the service restarts.

index create memory Use this option to set the maximum amount of memory that SQL Server initially allocates for index creation. SQL Server will allocate more memory later if necessary, but only if it is available. Typically, you do not need to configure this option, but if SQL Server is experiencing performance delays related to indexing, you can increase the value of this option.

min memory per query Use this option to improve performance of memory-intensive queries by establishing the minimum amount of memory allocated for query execution. SQL Server can use more memory than the configured minimum if it is available.

To configure memory, right-click the server instance in Object Explorer and select Properties. Click the Memory page, and then in the Server Memory Options, type the appropriate values for any property except max worker threads. You can also use T-SQL to adjust a property value like this:

Click here to view code image

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'min memory per query', 512 ;
GO
RECONFIGURE;
GO

Wait statistics allow you to analyze the time a worker thread spends in various states before it completes a request by providing the following key pieces of information:

Wait type The cause of the wait. For example, the disk IO is slow, resources are locked, CPU is under pressure, an index is missing, or many other reasons. There are hundreds of wait types that SQL Server tracks.

Service time The amount of time that a thread runs on the CPU.

Wait time The amount of time that a thread is not running because it is in the waiter list.

Signal wait time The amount of time that a thread is in the runnable queue ready to run, but waiting for CPU time.

Total wait time The sum of wait time and signal wait time.

You can access wait statistics through the following DMVS:

sys.dm_os_wait_stats View information about completed waits at the instance level.

sys.dm_exec_session_wait_stats View information about waits at the session level.

sys.dm_os_waiting_tasks View information about requests in the waiter list.

By reviewing this DMV for specific characteristics, you can uncover some of the following potential issues on your server:

CPU pressure Compare the signal wait time to the total wait time to determine the relative percentage of time that a thread has to wait for its turn to run on the CPU. When this value is relatively high, it can be an indicator that the CPU is overwhelmed by queries that require tuning or your server needs more CPU. You can confirm whether the issue is related to CPU by checking the runnable_tasks_count column in the sys.dm_os_schedulers DMV to see if there is a high number of tasks in the runnable queue. You might also see a higher occurrence of the SOS_SCHEDULER_YIELD wait type if the CPU is under pressure. In addition, you can monitor CPU-related performance counters as described in Skill 4.4.

IO issues If tasks are waiting for the IO subsystem, you will see waits that contain IO in the name. In particular, monitor the trend in average wait time which is calculated by dividing wait_time_ms by waiting_tasks_count. If it starts trending upward, investigate IO using performance counters.

Two wait types that will appear frequently in this DMV when IO issues exist are ASYNC_IO_COMPLETION and IO_COMPLETION. Check physical disk performance counters to confirm this diagnosis, which we describe in the next section, “Troubleshoot and analyze storage, IO, and cache issues.” Consider adding indexes to reduce IO contention.

You might also see PAGEIOLATCH waits when a thread is waiting for latches to release after writing a data page in memory to disk or WRITELOG waits when the log management system is waiting to flush to disk. These wait types can indicate either an IO subsystem problem or a memory problem. To narrow down the possibilities, you need to check IO statistics by using sys.dm_io_virtual_file_stats and by reviewing IO-related performance counters.

Memory pressure The PAGEIOLATCH wait might also indicate memory pressure instead of an IO subsystem problem. It appears when SQL Server does not have enough free memory available for the buffer pool. Check the Page Life Expectancy performance counter to see if it is dropping as compared to a baseline value to confirm whether memory is the reason for this wait type. If you see an increase in CXPACKET waits in conjunction with PAGEIOLATCH waits, the culprit could be a query plan using large table or index scans.

Another indicator of a memory pressure issue is the RESOURCE_SEMAPHORE wait. It occurs when a query requests more memory than is currently available. You can check the sys.dm_exec_query_memory_grants DMV and combine it with sys.dm_exec_sql_text and sys.dm_exec_sql_plan DMVs to find the memory-intensive queries and review their query plans.

Your first indication that you might have an IO problem might come from an analysis of waits as we described in the previous section. Your next step is to use the sys.dm_io_virtual_file_stats DMV in combination with sys.master_files to analyze cumulative metrics related to each database including its data and log files. These DMVs help you find the busiest files and provides IO stall information that tells you how long users had to wait for IO operations to finish.

Another way to find these issues is to use the sys.dm_os_performance_counters DMV. The availability of the DMV means you can easily get SQL Server-related performance counter information without first setting up Windows Performance Monitor.

The following performance counters, accessible by executing the statements shown in Listing 4-41, provide insight into the amount of IO that SQL Server is directly contributing to the server:

SQLServer:Buffer Manager: Page lookups/sec Average requests per second at which SQL Server finds a page in the buffer pool. This value should be lower than SQLServer:SQL Statistics: Batch Requests/sec multiplied by 100.

SQLServer:Buffer Manager: Page reads/sec Average rate at which SQL Server reads from disk. This value should be lower than the hardware specifications for the IO subsystem’s read operations.

SQLServer:Buffer Manager: Page writes/sec Average rate at which SQL Server writes to disk. This value should be lower than the hardware specifications for the IO subsystem’s write operations.

Listing 4-41 Review SQL Server:Buffer Manager performance counters

Click here to view code image

---

SELECT
    object_name,
    counter_name,
    instance_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager' AND
    counter_name IN
        ('Page lookups/sec', 'Page reads/sec', 'Page writes/sec')

---

If these performance counters are too high, consider one or more of the following solutions:

Tune database performance by adding new indexes, improving existing indexes, or normalizing tables, or partitioning tables.

Replace the IO subsystem hardware with faster components.

The following DMVs are useful for understanding memory usage on your server:

sys.dm_os_memory_cache_counters View the current state of the cache.

sys.dm_os_sys_memory View resource usage information for the server, including total physical and available memory and high or low memory state.

sys.dm_os_memory_clerks View usage information by memory clerk processes that manage memory for SQL Server.

You should also use the following performance counters to monitor whether SQL Server has adequate memory:

SQLServer:Buffer Manager: Free List Stalls/Sec Number of requests per second that SQL Server waits for a free page in the buffer cache. If this value is greater than zero on a frequent basis, the server is experiencing memory pressure.

SQLServer:Buffer Manager: Lazy Writes/Sec Number of times per second that SQL Server flushes pages to disk. If this number is rising over time, and Free List Stalls/Sec is also greater than zero, you likely need more memory on the server.

SQLServer:Memory Manager: Memory Grants Outstanding Number of processes that have acquired a memory grant successfully. A low value might signify memory pressure.

SQLServer:Memory Manager: Memory Grants Pending Number of processes that are waiting for a memory grant. If this value is greater than zero, consider turning queries or adding memory to the server.

T-SQL statements You can use the Showplan SET options in SQL Server Management Studio to capture query plans, just as you can for SQL Server. You can also use the Display Estimated Execution Plan and Include Actual Execution Plan buttons in the toolbar to generate the respective graphical query plan.

Extended Events You can use Extended Events to capture query plans, much like you can for SQL Server. There are some slight differences, however. Instead of using the ON SERVER clause in the CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION commands, you must use ON DATABASE instead. If you want to save a query plan to a file, you must write the file to an Azure Storage container. In addition, there are also several DMVS for Extended Events that are unique to SQL Database.

Query Store Query Store is enabled by default for V12 databases. You can access the Query Store views in SQL Server Management Studio in the same way that you do for SQL Server. Also, you can use the same Query Store DMVs.

Dynamic management objects (DMOs) DMVs and DMFs provide insight into the historical and current state of SQL Server. You can query DMOs on an ad hoc basis, or you can create export the data into tables for long-term storage and trend analysis.

Performance Monitor Operating system and SQL Server performance counters provide useful information to corroborate metrics obtained from other sources and to help narrow down the range of possible causes when investigating specific problems.

SQL Trace You can set up server-side or SQL Server Profiler tracing for a variety of events as a method of investigating problematic workloads and poorly performing queries.

Extended Events You can create Extended Events sessions as a more lightweight approach to server-side tracing.

sys.dm_exec_* Connections, sessions, requests, and query execution

sys.dm_os_* Information for the operating system on which SQL Server runs

sys.dm_tran_* Details about transactions

sys.dm_io_* IO processes

sys.dm_db_* Database-scoped information

Most DMOs provide information about the current state, such as currently blocked sessions in sys.dm_os_waiting_tasks, or information accumulated since SQL Server last restarted, such as sys.dm_os_wait_stats. SQL Server retains the information accessible through DMOs in memory only and does not persist it to disk. Therefore, the information is reset when SQL Server restarts.

Typically, you use performance counters to confirm suspicions of a problem that you uncover by using wait statistics rather than as a starting point. Like DMOs,

For real-time analysis, open Performance Monitor, and click the Add button in the toolbar. In the Add Counters dialog box, select the server to monitor in the Select Counters From Computer drop-down list (or use the Browse button to locate a server on your network), and then scroll through the Available Counters list to locate the set of counters to monitor, such as PhysicalDisk. When you monitor PhysicalDisk counters, you can select a specific disk to monitor or all disks. Select a specific counter, such as % Disk Time, and click the Add button. Continue adding counters as needed. When finished, click OK. You can then view real-time metrics for the selected counters. If you are monitoring multiple counters at the same time, right-click a counter and select Properties to change Color, Width, Style values to more easily distinguish between counters, as shown in Figure 4-42. You might also need to reset the scale for a counter.

To identify issues in disk IO activity, start by reviewing the following performance counters (replacing PhysicalDisk with the LogicalDisk if you have multiple logical partitions on the same disk):

PhysicalDisk: % Disk Time Percentage of time the disk is active with reads and writes. If this percentage is greater than 90 percent, review the Physical Disk: Current Disk Queue Length counter.

PhysicalDisk: Avg. Disk sec/Read Average read latency in seconds. This value should be less than 0.20.

PhysicalDisk: Avg. Disk sec/Transfer Average latency of IO requests to the disk in seconds. This value should be less than .020.

PhysicalDisk: Avg. Disk sec/Write Average read latency of IO requests to the disk in seconds. This value should be less than .020.

PhysicalDisk: Current Disk Queue Length Number of IO requests waiting for access to the disk. This value should be no more than two times the number of spindles for the disk. Most disks have a single spindle, but a redundant array of independent disks (RAID) typically have more than one spindle, even though the RAID device appears as a single physical disk in System Monitor.

Memory: Page Faults/sec This performance counter increases when processes on the server are consuming too much memory and SQL Server must page to disk. Paging itself is not necessarily a cause of an I/O bottleneck, but can slow the performance of the IO subsystem.

Another important resource to monitor is CPU usage, which you can watch by using the following performance counters:

Processor: % Privileged Time Percentage of time that the operating system spends processes SQL Server I/O requests. If this value is high at the same time that the disk-related performance counters described in the “Troubleshoot and analyze storage, IO, and cache issues” section are also high, your server likely needs a faster disk.

Processor: % Processor Time Percentage of time that each processor spends executing a thread that is not idle. If this value is consistently between 80 and 90 percent, you should upgrade the CPU or add more processors.

Processor: % User Time Percentage of time that the operating system executes user processes, including IO requests from SQL Server. If this value approaches 100%, it might indicate that the CPU is under pressure.

System: Processor Queue Length Number of threads waiting for processor time. An increase in this counter means the CPU is not keeping up with demand and a faster processor is necessary.

To monitor memory, use the following performance counters:

Memory: Available Bytes Amount of memory available for processes on the server. If this value is too low, check to see if another application on the server is failing to release memory or if memory on the server is adequate for your requirements.

Memory: Pages/sec Frequency with which pages are retrieved from or written to disk due to hard page faults. When this value is consistently high, the server might be paging excessively. If it is, the Memory: Page Faults/sec performance counter will also be high.

Process: Working Set Amount of memory used by a process. If this number is consistently lower than the minimum server memory option, SQL Server is configured to use too much memory.

Besides using SQL Trace to get query plans, consider using it as a diagnostic tool when you need to monitor SQL Server’s behavior during query processing. For example, you might use SQL Trace in the following situations:

Lock escalation You can find correlations between lock escalation events and queries running when lock escalation occurs by creating a trace that includes the Lock:Escalation, SP:Started, T-SQL:StmtStarted, SP:Completed, and T-SQL:StmtCompleted.

Deadlocks As we described in Chapter 3, you can capture a deadlock graph by creating a trace that includes the Deadlock Graph, Lock:Deadlock, and Lock:Deadlock Chain events.

Slow queries You can identify and capture information about slow queries in a trace containing the RPC:Completed, SP:StmtCompleted, SQL:BatchStarting, SQL:BatchCompleted, and Showplan XML events.

As the replacement for SQL Trace, which is slated for deprecation in a future version of SQL Server, Extended Events not only allow you to perform the same tasks with greater flexibility and better performance, but also allow you to monitor more events. In SQL Server 2016, you can monitor 180 events by using SQL Trace, whereas you can monitor 1209 events by using Extended Events. Later in this chapter, we provide specific examples of how you might use Extended Events, we compare the performance impact between Extended Events and SQL Trace, and explain its architecture of this event-handling infrastructure.

SQL Server includes a data collector to help you gather data and store it in the management data warehouse. This is a database that you use as centralized storage for many types of data, not just performance data. When you set up the management data warehouse, you specify which DMOs to query, which performance counters from the operating system and SQL Server to collect, and which SQL Trace events to capture. You can also use this infrastructure to capture other types of data that you might want to store centrally.

To set up management data warehouse in SQL Server Management Studio, expand the Management node in Object Explorer, right-click Data Collection, point to Tasks, and select Configure Management Data Warehouse. In the Configure Management Data Warehouse Wizard, click Next, and click New to open the New Database dialog box. Type a name for the database, click OK, and then click Next. On the Map Logins And Users page of the wizard, select the login for your SQL Server service account, select mdw_admin, and then click OK. Next click Finish, and then click Close.

To set up data collection, right-click Data Collection again, point to Tasks, and select Configure Data Collection. In the Configure Data Collection Wizard, click Next, and then select the server and database hosting the management data warehouse. Select the System Data Collection Sets checkbox, click Next, click Finish, and then click Close.

To view information collected in the management data warehouse, right-click Data Collection once more, point to Reports, point to Management Data Warehouse, and select one of the following reports:

Server Activity History This report, a portion of which is shown in Figure 4-43, displays data collected from DMVs and performance counters, such as waits, locks, latches, among other SQL Server statistics, and CPU, memory, disk, and network usage. By default, this information is gathered every 60 seconds, uploaded into the Management Data Warehouse every 15 minutes, and retained for 14 days. The report is interactive. When you click on a chart, a new report page displays. For example, when you click the SQL Server Waits report, you can view a table and chart containing wait statistics for the selected period of time.

Disk Usage Summary This report displays a table of databases listing the starting and current size of the database and log files, the average growth measured as megabytes per day, and a sparkline chart to show the growth trend over time. You can click on a database name to view disk usage details for the database and log files as pie charts or click on the sparkline to view the disk space growth trends and collection details for each size metric, as shown in Figure 4-44.

Query Statistics History This report displays a column chart the top 10 queries within a specified interval by CPU usage, duration, total IO, physical reads, or logical writes and a table that includes the following query executions statistics: Executions/min, CPU ms/sec, Total Duration (sec), Physical Reads/sec, and Logical Writes/sec. The data is cached on the local file system and then uploaded to the Management Data Warehouse every 15 minutes.

You can click on one of these queries to view its details in a subreport, such as the query text, and more detailed execution statistics such as Average Duration (ms) per Execution or Average Executions Per Min. The subreport also displays a column chart of query plans that you can rank by CPU, duration, physical reads, or logical writes. Statistics by query plan are also available. Click on a query plan to view the query plan details much like the details for a query. Other links on this page allow you to view sampled waits or the graphical query plan.

Blocked by firewall

CPU percentage

DTU limit

DTU percentage

DTU used

Data IO percentage

Database size percentage

Deadlocks

Failed connections

In-memory OLTP storage percent

Log IO percentage

Sessions percentage

Successful connections

Total database size

Workers percentage

After you add the metrics to monitor, you can view the results in the Monitoring chart in addition to details in the Metric window. When you select metrics, you can select compatible metrics only. For example, you can select metrics that count connections, as shown in Figure 4-46, or you can select metrics that measure the percentage of resources consumed, but you cannot mix those two types of metrics in the same chart.

You can also use the Azure portal to configure an email alert when a performance metric exceeds or drops below a specified threshold. Click the Monitoring chart to open the Metric blade. Then click Add Alert and follow the instructions in the Add Alert Rule blade. For example, you can add an alert to send an email when the number of connections blocked by the firewall exceeds 1, as shown in Figure 4-47. Not shown in this figure is the name of the resource, the name of the alert rule, and whether to email the alert to owners, contributors, and readers and optionally additional administrators.

sys.database_connection_stats Count successful and failed connections. The count of failed connections is the sum of login failures, terminated connections, and throttled connections.

sys.dm_db_resource_stats Get the resource consumption percentages for CPU, data IO, and log IO. It returns one row for every 15 seconds, even when there is no activity in the database. For less granular data, you can use sys.resource_stats in the logical master database for your server.

sys.dm_exec_query_stats In combination with sys.dm_exec_sql_text, find queries that use a lot of resources, such as CPU time or IO.

sys.dm_tran_locks Discover blocked queries.

sys.event_log Find issues such as deadlocking and throttling over the last 30 days. You must have permission to read the master database on the Azure server. As an example, you can search for specific types of events, such as deadlocks or throttle events, and when they occurred, as shown in Listing 4-42.

LISTING 4-42 Review SQL Server:Buffer Manager performance counters

Click here to view code image

---

SELECT
    Event_Category,
    Event_Type,
    Event_Subtype_Desc,
    Event_Count,
    Description,
    Start_Time
FROM sys.event_log
WHERE Event_Type = 'deadlock' OR
    Event_Type like 'throttling%'

---

System health By default, an Extended Events session dedicated to system health information starts automatically when SQL Server starts. System health information includes session_id and sql_text for sessions with a severity greater than or equal to 20 or experiencing a memory-related error, non-yielding scheduler problems, deadlocks, long latch and lock waits, connectivity and security errors, and more. For complete details, see https://msdn.microsoft.com/en-us/library/ff877955.aspx.

Query performance diagnostics Find historical deadlocks or queries that did not end, troubleshoot waits for a particular session or query, capture queries that match a specific pattern, and get query plans, to name a few. You can count the number of occurrences of a specific event to determine if a problem is recurring frequently.

Resource utilization monitoring and troubleshooting You can create a session to capture information about server resources, such as CPU, IO or memory utilization. You can filter events for specific utilization thresholds to fine-tune the diagnostic process. Furthermore, you can correlate SQL Server events with Windows Event Tracing for Windows (ETW) logs that capture details about operating system activities.

Security audits Capture login failures by filtering events with Severity 14 and Error 18456 with client application name to find malicious login attempts.

etw_classic_sync_target Unlike the other targets that receive data asynchronously, this is an ETW target that receives data synchronously. You use it to monitor system activity.

event_counter This target counts the number of times that a specific event occurred.

event_file This target writes the event session output to a file on disk in binary format. You use the sys.fn_xe_file_target_read_file function to read the contents of the file.

histogram Like the event_counter target, the histogram target counts the occurrences of an event, but can count occurrences for multiple items separately and for both event fields or actions.

pair_matching This target helps you find start events that do not have a corresponding end event. For example, you can discover when a lock_acquired event occurred without a matching lock_released event within a reasonable time.

ring_buffer This target holds data in memory using an first-in, first-out method in which the oldest data is removed when the memory allocation is reached.

The least intrusive option is Extended Events, which was developed as a lightweight replacement for SQL Trace. Nonetheless, because it does incur overhead, you should take care to create events that collect the minimum amount of data necessary for troubleshooting. In particular, be aware that the query_post_execution_showplan event is expensive and should be avoided on a production server. If you must use it to troubleshoot a specific issue, take care to restrict its use to a limited time only.

Events in Extended Events correspond to events in SQL Trace, but many more are supported in Extended Events to give you better diagnostic capabilities. An example of an event is sp_statement_starting.

package0 Contains Extended Events system objects

sqlserver Contains objects related to SQL Server

sqlos Contains SQLOS objects

Click here to view code image

ADD TARGET package0.event_file(SET filename=N'C:ExamBook762Ch4query.xel',
    max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer

Click here to view code image

ADD EVENT sqlserver.sql_statement_completed(
    ACTION (sqlserver.session_id,
        sqlserver.sql_text))

LISTING 4-43 Create event session

Click here to view code image

---

CREATE EVENT SESSION [stored_proc]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
    ACTION (sqlserver.session_id,
        sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:ExamBook762Ch4query.xel',
    max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer;

---

SQL Server updates statistics automatically by default, but you can disable the automatic update and instead rely on a maintenance plan to update statistics when you need greater control over the timing of the update. When you create a maintenance plan, SQL Server creates a SQL Server Agent job that you can schedule to run at a convenient time.

There are several DMOs available to help you manage indexes. Use sys.dm_db_index_usage_stats to review current index usage or, in combination with sys.indexes and sys.objects, to find indexes that are never used. Use sys.dm_db_index_physical_stats to find fragmented indexes. To find missing indexes, use sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats.

The existence of overlapping indexes adds unnecessary overhead to SQL Server. You should periodically review index columns to ensure an index was not inadvertently added that overlaps with a similar index.

Use the query_pre_execution_showplan or query_post_execution_showplan Extended Events as a lightweight method to capture the estimated or actual query plans, respectively. As an alternative, you can use SQL Trace system stored procedures for server-side tracing or use SQL Server Profiler to capture the Showplan XML or Showplan XML For Query Compile events for an estimated query plan or the Showplan XML Statistics Profile event for an actual query plan.

When reviewing a query plan, you should review whether the following operators exist in the plan: Table Scan, Clustered Index Scan, Key Lookup (Clustered) or RID Lookup (Clustered) in conjunction with Index Seek (NonClustered), Sort, or Hash Match. Although the presence of these operators is not bad in all cases, you might consider options for tuning your query such as adding indexes to tables or adding filters to the query if operations are performed on large tables and your goal is to improve query performance.

Query Store is a new feature in SQL Server 2016 and SQL Database that you can use to capture query plans and associated runtime execution statistics over time. Several built-in views are available to help you find queries that consume a lot of resources or have regressed from a previous point in time. You can also force a query plan when necessary.

Usually an estimated query plan and an actual query plan are the same plan except that an actual query plan includes calculations and information that is available only after the query executes. You can create an estimated query plan to assess the impact of potential changes to a query or to table structures without executing the query. The actual query plan can vary from the estimated query plan if statistics are out-of-date or if the data structures changes after the estimated query plan is generated.

Query Performance Insight is an Azure SQL Database features that graphically shows you which queries are consuming the most CPU, memory, IO, and DTU resources over time.

The Resource Governor (available only in Enterprise, Developer, or Evaluation editions) allows you to define how SQL Server allocates CPU, memory, and IOPS resources on the server. You specify this allocation by creating one or more resource pools and then create workload groups that you assign to a resource pool. A workload group can have further resource consumption limits imposed and can be configured with relative importance within a resource pool to prioritize access to server resources when workload groups run concurrently. SQL Server assigns a session to a workload group by using criteria that you define in a classifier function. It if cannot match the session to a workload group, Resource Governor assigns the session to the default workload group assigned to the default resource pool.

By using Elastic Scale for Azure SQL Database, you can manage your data in separate shards for which resource requirements can dynamically grow or shrink as needed to match the current workload. To use this feature, you must use the elastic database client library in your application to perform operations across all shards or in individual shards. You can also use the Split-Merge service to separate data in one shard into multiple shards or to combine data from multiple shards into a single shard.

When choosing one of the following SQL Server 2016 editions, you must consider the scalability and features each supports: Express, Web, Standard, Enterprise, Developer, and Evaluation. For SQL Database, you choose the Basic, Standard, or Premium service level based on the maximum size of your database and the range of performance levels required. Performance levels are measured as DTUs which represent CPU, memory, and IO thresholds.

One aspect of query performance is the efficiency of your storage and IO subsystems which you can optimize by managing file placement for system, data, and log files. Use filegroups as separate containers that can be spread across separate disks and optionally use secondary data files and partitioning to improve data access.

The setup wizard for SQL Server 2016 makes it easier than in previous versions to optimize the configuration of tempdb by allowing you to define its file placement, the number of files to create, the initial file size, and autogrowth settings.

As long as SQL Server is the only application running on a server, it can manage memory dynamically without intervention. When you must run other applications on the server, you can optimize SQL Server’s memory configuration by specifying minimum and maximum memory thresholds, maximum worker threads, maximum memory for index creation, and minimum memory for query execution.

You use the sys.dm_os_wait_stats, sys.dm_exec_session_wait_stats, or sys.dm_os_waiting_tasks DMVs to gather information about the amount of time that threads must wait on resources, determine whether the server is experiencing CPU, memory, or IO pressure, or find out which resources are causing excessive waits.

You can troubleshoot IO issues by analyzing IO subsystem latencies captured in the sys.dm_io_virtual_file_stats and sys.master_files DMVs or by reviewing performance counters for SQL Server’s buffer manager in the sys.dm_os_performance_counters DMV.

You can troubleshoot cache issues by using the following DMVs: sys.dm_os_memory_cache_counters, sys.dm_os_sys_memory, or sys.dm_os_memory_clerks. You can also use performance counters for SQL Server’s buffer manager and memory manager in the sys.dm_os_performance_counters DMV.

SQL Database query plans are accessible by using Showplan SET options, Extended Events, or Query Store, but not by using SQL Trace.

You have a variety of tools that you can use to monitor operating system and SQL Server performance metrics: DMOs, Performance Monitor, SQL Trace, and Extended Events. In many cases, you are likely to use a combination of tools to diagnose SQL Server behavior.

Management Data Warehouse is a SQL Server feature that allows you to capture performance-related information over time in contrast to other available monitoring tools which primarily provide point-in-time or cumulative information. Management Data Warehouse provides the following reports for your review: Server Activity History, Disk Usage Summary, and Query Statistics History.

To monitor SQL Database performance, you can monitor specific metrics in the Azure portal. In addition, you can use the following DMVs with SQL Database for monitoring: sys.database_connection_stats, sys.dm_db_resource_stats, sys.dm_exec_query_stats, sys.dm_tran_locks, and sys.event_log. Extended Events are also available for SQL Database, although the set of supported events is smaller than the set that supports SQL Server.

You should be familiar with the following best practice use cases for extend events: system health, query performance diagnostics, resource utilization monitoring and troubleshooting, and security audits.

You should also understand how Extended Events uses the following targets to store information about an event: etw_classic_sync_target, event_counter, event_file, histogram, pair_matching, and ring_buffer.

Although Extended Events and SQL Trace can often be used interchangeably, Extended Events has a much lower impact on performance. You can measure the difference in impact by observing CPU processor time and batch requests per second.

The Extended Events architecture is comprised of several components. A package is the top-level container for the other objects. As data is collected about an event, it is sent to a target. In addition, you can configure an action to occur in response to an event. A session is a set of events, actions, and targets that you configure and enable or disable as a group.

You recently started as a new database administrator at Consolidated Messenger. Because you are unfamiliar with this SQL Server environment as a new employee, you decide to perform some analysis to determine if there are any significant problems to fix.

1. Which query or command do you use to find the most recent update for statistics on tables or indexed views?

A. DBCC SHOW_STATISTICS(‘ConsolidatedMessengerDB’, All Indexes);

B. SELECT name, STATS_DATE(object_id, stats_id) FROM sys.stats WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type = ‘U’);

C. SELECT name, auto_created (object_id, stats_id) FROM sys.stats WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type = ‘U’);

D. SELECT name, auto_created (object_id, stats_id) FROM sys.stats WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type = ‘U’);

2. When you query the sys.dm_db_index_physical_stats DMV, you see the output shown below. What problem do you see and what step should you take to resolve it?

Click here to view code image

idx_id ObjectName                              index_type_desc             pg_ct
AvgPageSpacePct     frag_ct  AvgFragPct
-------- -------------------------------------- -------------------------------
------- -------------------------- ---------- ---------------------------
1          PK_SalesOrders_OrderD        CLUSTERED INDEX           2037
54.9851742031134 237        34.02189781021898
2          IX_SalesOrders_CustomerID NONCLUSTERED INDEX   685    98.4313442055844
2             0

3. You enabled Query Store on the main corporate database several weeks ago. Which DMV do you use to locate the top 5 queries with the longest duration?

A. sys.query_store_plan

B. sys.query_store_query

C. sys.query_store_query_text

D. sys.query_store_runtime_stats

E. sys.query_store_runtime_stats_interval

4. When you use Query Store to examine the query plans for a frequently executed query, you notice that for one plan uses an Index Scan operator and a second plan uses and Index Seek operator. If the normal query pattern is to retrieve a small number of rows, which is the more optimal query plan and how can you require SQL Server to use it?

5. You have been monitoring wait statistics in the sys.dm_os_wait_stats DMV for several weeks and notice that the ratio of signal_wait_time_ms to wait_time_ms has been increased from 10% to 30%. What type of problem is this likely to indicate?

A. CPU pressure

B. Memory pressure

C. Network bandwidth issues

D. IO subsystem failures

6. As another method to confirm your diagnosis for the scenario in the previous question, which performance counters should you check?

A. Physical disk counters: % Disk Time, Avg. Disk sec/Read, Avg. Disk sec/Transfer, Avg. Disk sec/Write, Current Disk Queue Length.

B. Processor counters: % Privileged Time, % Processor Time, % User Time, Processor Queue Length.

C. Memory counters: Available bytes, Pages/sec, Working set.

D. SQL Server counters: Page lookups/sec, Page reads/sec, Page writes/sec, Free List Stalls/Sec, Lazy Writes/Sec, Memory Grants Outstanding, Memory Grants Pending.

7. Which tool monitoring tools can you use to get information about SQL Server memory usage without writing any code?

A. DMVs or SQL Profiler

B. Server-side SQL Trace or Extended Events

C. Performance Monitor or Management Data Warehouse

D. Client-side SQL Trace or Resource Governor

1. The answer is B. The sys.stats catalog view contains both the stats_id and object_id columns necessary to use the STATS_DATE system function that returns the most recent update date for an object’s statistics. The DBCC SHOW_STATISTICS command requires you to include a specific index name as the second argument, therefore A is incorrect due to the syntax. Similarly, C and D are examples of incorrect syntax because T-SQL does not include an auto_created function.

2. The clustered index has a page count greater than 500 and fragmentation is 34%. In this case, you should rebuild the index.

3. The answer is D. You can order by the avg_duration column in descending order in the sys.query_store_runtime_stats DMV to find the queries that run the longest. The sys.query_store_plan in A is incorrect because this DMV includes only information about estimated query plans. Sys.query_store_query is incorrect in B because this DMV collects aggregated information for a query’s compilation, but does not collect execution statistics. On its own the sys.query_store_query_text DMV in C is not a correct answer because it includes the query text without duration information, although you can join it to sys.query_store_runtime_stats to get more complete information about long-running queries. Sys.query_store_runtime_stats_interval is stores information about the intervals of time during which statistics are captured, but does not report duration information.

4. When multiple query plans exist for a selected query, Query Store allows you to select a query plan and then force that plan for all subsequent executions of the same query. In this example, the more optimal query plan is the one that includes the Index Seek because it incurs a lower cost by selecting specific rows from the index rather than scanning the entire index to find the rows to select.

5. The answer is A. The signal wait time is the amount of time that a thread is able to run a task, but is waiting its turn on the CPU. To confirm this diagnosis, check for an increasing number of SOS_SCHEDULER_YIELD wait types in the sys.dm_os_wait_stats DMV and check the sys.dm_os_schedulers DMV for a high value in the runnable_tasks_count column. Answer B is incorrect because memory pressure is indicated by PAGEIOLATCH waits in combination with a Page Life Expectancy performance counter value dropping over time. Answer C is incorrect because you generally use network-related wait types and performance counters to confirm your diagnosis of network bandwidth issues. Answer D is incorrect because a failure in the IO subsystem will become evident when there are many waits containing IO in the name and average wait time begins to increase.

6. The answer is B. You can monitor CPU usage by using the Processor performance counters. In particular, if % Processor Time is between 80 and 90 percent consistently, % User Time nears 100% consistently, and the Processor Queue Length value is increasing over time, the CPU is experiencing pressure. You should consider upgrading the CPU or adding more processors. The counters listed for Answers A, C, and D are useful performance counters. However, they do not provide any confirmation of whether the observations in Question 5 are related to CPU pressure.

7. The answer is C. Both Performance Monitor and Management Data Warehouse provide graphical interfaces that you can use to monitor SQL Server’s memory usage. In Performance Monitor, you can set up the data collection of performance counters for the SQL Server Memory Manager, such as Memory Grants Outstanding or Memory Grants Pending, among others. In Management Data Warehouse, you can use the Server Activity History report to drill into Memory Usage details for the server. To access information from DMVs, you must write a T-SQL query; therefore, A is incorrect. Similarly, B is incorrect because a server-side SQL Trace requires you to write code. Answer D is incorrect because Resource Governor is not a performance monitoring tool.