Monitor, configure, and optimize database resources (20–25%) Flashcards

Question 1

Q

Prepare an operational performance baseline

Answer

A

Prepare an Operational Performance Baseline

Overview:
Preparing an operational performance baseline is a critical process for monitoring and optimizing SQL Server and Azure SQL Database performance. It involves capturing a set of performance metrics that represent the normal operating conditions of your database environment. This baseline serves as a reference point for identifying performance deviations and potential issues.

Key Concepts:

Performance Metrics:
- CPU Usage: Percentage of CPU capacity being utilized.
- Memory Usage: Amount of memory being consumed by SQL Server.
- Disk I/O: Rates of read and write operations to disk.
- Wait Statistics: Types and durations of waits experienced by queries.
- Query Performance: Execution times and resource consumption of key queries.
Data Collection Tools:
- SQL Server Management Studio (SSMS): Provides tools like Activity Monitor and Query Store.
- Dynamic Management Views (DMVs): SQL queries to extract performance data.
- Performance Monitor (PerfMon): Windows tool for tracking various system and SQL Server performance counters.
- Azure Monitor: Azure service for collecting and analyzing performance data from Azure SQL Database.

Steps to Prepare an Operational Performance Baseline

Identify Key Metrics:

CPU Usage:

sql
SELECT record_id, [SQL Process Utilization] AS [SQL Server Process CPU], 
       [System Idle Process] AS [Idle CPU], 
       [100 - System Idle Process - [SQL Process Utilization]] AS [Other Process CPU]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
AND record_id = (SELECT MAX(record_id) FROM sys.dm_os_ring_buffers 
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR');

Memory Usage:

sql
SELECT 
    (physical_memory_in_use_kb/1024) AS used_memory_in_MB, 
    (locked_page_allocations_kb/1024) AS locked_pages_in_MB,
    (total_virtual_address_space_kb/1024) AS total_VAS_in_MB, 
    process_physical_memory_low, 
    process_virtual_memory_low 
FROM sys.dm_os_process_memory;

Disk I/O:

sql
SELECT 
    database_id, 
    file_id, 
    io_stall_read_ms, 
    num_of_reads, 
    io_stall_write_ms, 
    num_of_writes 
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

Wait Statistics:

sql
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats;

Query Performance:

sql
SELECT TOP 10
    total_worker_time/execution_count AS AvgCPUTime,
    total_elapsed_time/execution_count AS AvgDuration,
    execution_count,
    text 
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY AvgDuration DESC;

Collect Data Over Time:
- Schedule regular data collection intervals (e.g., every 15 minutes, hourly) to capture a representative set of metrics.
- Use SQL Agent Jobs or automated scripts to gather and store this data.
Analyze and Establish Baseline:
- Aggregate the collected data to calculate average values and identify normal performance patterns.
- Document the baseline metrics, including peak usage times and average performance values.
Use Baseline for Comparison:
- Compare current performance metrics against the baseline to identify deviations.
- Investigate and troubleshoot significant deviations to maintain optimal performance.

Example Scenario

Scenario: Preparing a performance baseline for an Azure SQL Database to ensure optimal performance during peak business hours.

Steps:

Identify Key Metrics:
- CPU usage, memory usage, disk I/O, wait statistics, and query performance.
Collect Data Over Time:
- Use Azure Monitor to collect and store performance data at 15-minute intervals for a month.
Analyze and Establish Baseline:
- Aggregate the data to determine average CPU usage, memory usage, disk I/O rates, common wait types, and average query execution times.
Use Baseline for Comparison:
- Regularly compare current metrics to the baseline to detect performance issues and optimize resource usage.

Best Practices:

Regular Data Collection:
- Continuously collect performance data to keep the baseline up to date.
Comprehensive Metrics:
- Include a wide range of performance metrics to ensure a holistic view of the database performance.
Historical Data Analysis:
- Use historical data to understand trends and seasonal variations in performance.
Automated Monitoring:
- Implement automated monitoring and alerting based on the baseline to quickly identify and address performance issues.

Resources:

Microsoft Learn: Monitor Performance for SQL Server
Microsoft Docs: Dynamic Management Views and Functions (Transact-SQL)
Microsoft Learn: Azure Monitor

By following these steps and best practices, you can effectively prepare an operational performance baseline for your SQL Server and Azure SQL Database, ensuring optimal performance and quick identification of issues.

Question 2

Q

Determine sources for performance metrics

Answer

A

Determine Sources for Performance Metrics

Overview:
To effectively monitor and optimize SQL Server and Azure SQL Database performance, it is crucial to identify the right sources for performance metrics. These sources provide the necessary data to analyze and understand the performance characteristics and potential bottlenecks within the database environment.

Key Sources for Performance Metrics:

Dynamic Management Views (DMVs):
- DMVs provide detailed information about the health, performance, and activity of SQL Server instances. They are a primary source for real-time monitoring and diagnostics.
Examples of DMVs:
- sys.dm_os_wait_stats: Provides information about wait statistics, which can help identify performance bottlenecks.
```
sql
  SELECT wait_type, wait_time_ms, waiting_tasks_count
  FROM sys.dm_os_wait_stats;
 
```
- sys.dm_exec_requests: Offers details about currently executing requests, including query text and resource usage.
```
sql
  SELECT session_id, status, cpu_time, total_elapsed_time
  FROM sys.dm_exec_requests;
 
```
- sys.dm_io_virtual_file_stats: Reports I/O statistics for data and log files.
```
sql
  SELECT database_id, file_id, io_stall_read_ms, num_of_reads
  FROM sys.dm_io_virtual_file_stats(NULL, NULL);
 
```
Performance Monitor (PerfMon):
- A Windows tool that collects and analyzes system performance metrics. PerfMon can track various SQL Server counters, including CPU usage, memory usage, and disk I/O.
- Key Counters:
  - Processor Time: Measures CPU usage.
  - Available MBytes: Indicates available memory.
  - Disk Read/Write Bytes/sec: Tracks disk I/O performance.
SQL Server Management Studio (SSMS):
- SSMS provides built-in tools such as Activity Monitor and Query Store to visualize and analyze performance data.
- Activity Monitor: Displays real-time performance data, including CPU, I/O, and expensive queries.
- Query Store: Captures query performance metrics over time, helping identify regressed queries and performance trends.

Extended Events:

Extended Events is a lightweight performance monitoring system that captures detailed event data for SQL Server.

Example:

Create an Extended Event Session:

sql
CREATE EVENT SESSION [MySession] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
ADD TARGET package0.event_file (SET filename = N'C:\temp\MySession.xel');
ALTER EVENT SESSION [MySession] ON SERVER STATE = START;

Azure Monitor:
- Azure Monitor provides a comprehensive solution for collecting, analyzing, and acting on telemetry data from Azure SQL Database and other Azure services.
- Key Features:
  - Metrics: Track performance metrics such as DTU usage, CPU percentage, and storage usage.
  - Logs: Collect and analyze diagnostic logs to gain insights into database performance.
SQL Server Profiler:
- SQL Server Profiler is a graphical tool for tracing and monitoring events in SQL Server. It captures detailed information about SQL queries and their execution times.
- Example:
  - Create a Trace:
    - Open SQL Server Profiler, create a new trace, and select the events and columns to capture.

Example Scenario

Scenario: Determining sources for performance metrics to monitor an on-premises SQL Server instance.

Steps:

Use DMVs to Collect Performance Data:
- Query sys.dm_os_wait_stats to identify common waits and potential bottlenecks.
```
sql
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats;
```
Monitor System Performance with PerfMon:
- Track key performance counters such as Processor Time, Available MBytes, and Disk Read/Write Bytes/sec.
Leverage SSMS Tools:
- Use Activity Monitor to visualize real-time performance data.
- Enable and analyze Query Store to track query performance over time.

Configure Extended Events:

Set up an Extended Event session to capture SQL statement completions and their execution times.

sql
CREATE EVENT SESSION [MySession] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
ADD TARGET package0.event_file (SET filename = N'C:\temp\MySession.xel');
ALTER EVENT SESSION [MySession] ON SERVER STATE = START;

Use SQL Server Profiler for Detailed Analysis:
- Create a trace to capture detailed query execution data and identify slow-running queries.

Best Practices:

Combine Multiple Sources:
- Use a combination of DMVs, PerfMon, SSMS tools, Extended Events, and SQL Server Profiler for a comprehensive view of performance.
Regular Monitoring:
- Continuously monitor performance metrics to detect and resolve issues proactively.
Automate Data Collection:
- Automate the collection of performance data using scripts and scheduled tasks to ensure consistent and up-to-date monitoring.
Analyze Trends:
- Regularly analyze performance data to identify trends and predict potential issues before they impact the system.

Resources:

By leveraging these sources and best practices, you can effectively monitor and optimize the performance of your SQL Server and Azure SQL Database environments.

Question 3

Q

Interpret performance metrics

Answer

A

Interpret Performance Metrics

Overview:
Interpreting performance metrics involves analyzing data collected from various sources to understand the performance characteristics and potential issues within SQL Server or Azure SQL Database environments. This process helps in identifying bottlenecks, optimizing resource usage, and ensuring the database operates efficiently.

Key Metrics and Their Interpretation:

CPU Usage:

Metric: Percentage of CPU utilization by SQL Server.

Interpretation:

High CPU Usage: Indicates that the server might be under heavy load due to complex queries, poor indexing, or insufficient hardware resources.
Low CPU Usage: Generally a good sign, but if accompanied by poor performance, it might indicate other bottlenecks like I/O issues or excessive waits.

Example:

sql
SELECT record_id, [SQL Process Utilization] AS [SQL Server Process CPU], 
       [System Idle Process] AS [Idle CPU], 
       [100 - System Idle Process - [SQL Process Utilization]] AS [Other Process CPU]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
AND record_id = (SELECT MAX(record_id) FROM sys.dm_os_ring_buffers 
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR');

Memory Usage:
- Metric: Amount of memory being used by SQL Server.
- Interpretation:
  - High Memory Usage: Indicates efficient use of available memory. However, if memory pressure exists, it might lead to increased paging and slow performance.
  - Low Memory Usage: Might suggest underutilization of resources or insufficient data caching.
  - Example:
```
sql
SELECT 
    (physical_memory_in_use_kb/1024) AS used_memory_in_MB, 
    (locked_page_allocations_kb/1024) AS locked_pages_in_MB,
    (total_virtual_address_space_kb/1024) AS total_VAS_in_MB, 
    process_physical_memory_low, 
    process_virtual_memory_low 
FROM sys.dm_os_process_memory;
```
Disk I/O:
- Metric: Rates of read and write operations to disk.
- Interpretation:
  - High I/O Wait Times: Indicates disk subsystem might be a bottleneck, often due to slow storage devices or excessive I/O operations.
  - Balanced I/O: Suggests that the disk subsystem is handling the load efficiently.
  - Example:
```
sql
SELECT database_id, file_id, io_stall_read_ms, num_of_reads, io_stall_write_ms, num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
```
Wait Statistics:
- Metric: Types and durations of waits experienced by queries.
- Interpretation:
  - High Wait Times: Indicates contention for resources like CPU, memory, or disk I/O. Specific wait types can help identify the bottleneck.
  - Common Wait Types:
    - CXPACKET: Parallelism issues.
    - PAGEIOLATCH_*: Disk I/O bottlenecks.
    - LCK_*: Lock contention.
  - Example:
```
sql
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats;
```
Query Performance:
- Metric: Execution times and resource consumption of queries.
- Interpretation:
  - Long Running Queries: Identify and optimize slow queries, potentially by indexing, query rewriting, or resource allocation.
  - Frequent Queries: Ensure that frequently executed queries are efficient and have necessary indexes.
  - Example:
```
sql
SELECT TOP 10
    total_worker_time/execution_count AS AvgCPUTime,
    total_elapsed_time/execution_count AS AvgDuration,
    execution_count,
    text 
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY AvgDuration DESC;
```

Example Scenario

Scenario: Interpreting performance metrics for an Azure SQL Database experiencing slow response times.

Steps:

Analyze CPU Usage:

Query:

sql
SELECT record_id, [SQL Process Utilization] AS [SQL Server Process CPU], 
       [System Idle Process] AS [Idle CPU], 
       [100 - System Idle Process - [SQL Process Utilization]] AS [Other Process CPU]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
AND record_id = (SELECT MAX(record_id) FROM sys.dm_os_ring_buffers 
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR');

Interpretation: High CPU utilization might indicate heavy query processing or lack of indexing.

Check Memory Usage:

Query:

sql
SELECT 
    (physical_memory_in_use_kb/1024) AS used_memory_in_MB, 
    (locked_page_allocations_kb/1024) AS locked_pages_in_MB,
    (total_virtual_address_space_kb/1024) AS total_VAS_in_MB, 
    process_physical_memory_low, 
    process_virtual_memory_low 
FROM sys.dm_os_process_memory;

Interpretation: High memory usage is normal, but insufficient memory might lead to paging issues.

Evaluate Disk I/O:

Query:

sql
SELECT database_id, file_id, io_stall_read_ms, num_of_reads, io_stall_write_ms, num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

Interpretation: High I/O wait times suggest the need for faster storage or query optimization to reduce I/O load.

Examine Wait Statistics:
- Query:
```
sql
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats;
```
- Interpretation: High wait times for specific wait types can pinpoint resource contention areas.

Review Query Performance:

Query:

sql
SELECT TOP 10
    total_worker_time/execution_count AS AvgCPUTime,
    total_elapsed_time/execution_count AS AvgDuration,
    execution_count,
    text 
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY AvgDuration DESC;

Interpretation: Identifying and optimizing long-running queries can significantly improve overall performance.

Best Practices:

Regular Monitoring:
- Continuously monitor performance metrics to detect and address issues proactively.
Holistic Analysis:
- Analyze multiple metrics together to get a comprehensive view of the system’s performance.
Historical Comparisons:
- Compare current performance data with historical baselines to identify trends and deviations.
Automate Alerts:
- Set up automated alerts for significant deviations from normal performance metrics.

Resources:

By following these steps and best practices, you can effectively interpret performance metrics for SQL Server and Azure SQL Database, ensuring optimal performance and quick identification of potential issues.

Question 4

Q

Configure and monitor activity and performance

Answer

A

Configure and Monitor Activity and Performance

Overview:
Configuring and monitoring activity and performance in SQL Server and Azure SQL Database involves setting up tools and mechanisms to continuously track database operations, identify bottlenecks, and ensure optimal performance. This process includes using built-in features like Dynamic Management Views (DMVs), Performance Monitor, SQL Server Profiler, Extended Events, and Azure Monitor.

Key Steps to Configure and Monitor Activity and Performance

Configure Performance Monitoring Tools:Using Dynamic Management Views (DMVs):
- Query DMVs for Performance Data:

sql
  -- Example: Querying for CPU usage
  SELECT record_id, [SQL Process Utilization] AS [SQL Server Process CPU], 
         [System Idle Process] AS [Idle CPU], 
         [100 - System Idle Process - [SQL Process Utilization]] AS [Other Process CPU]
  FROM sys.dm_os_ring_buffers
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
  AND record_id = (SELECT MAX(record_id) FROM sys.dm_os_ring_buffers 
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR');

Using Performance Monitor (PerfMon):
- Add SQL Server Counters:
- Open PerfMon.
- Add counters for SQL Server, such as Processor Time, Memory Usage, Disk I/O, and SQL Statistics.Using Extended Events:
- Create an Extended Event Session:

sql
  CREATE EVENT SESSION [MySession] ON SERVER
  ADD EVENT sqlserver.sql_statement_completed
  ADD TARGET package0.event_file (SET filename = N'C:\temp\MySession.xel');
  ALTER EVENT SESSION [MySession] ON SERVER STATE = START;

Set Up Azure Monitor for Azure SQL Database:Enable Diagnostic Settings:
- Navigate to the Azure portal.
- Go to your Azure SQL Database instance.
- Select “Diagnostic settings” and add a diagnostic setting to send logs and metrics to Azure Monitor, Log Analytics, or an Event Hub.Monitor Key Metrics:
- Configure alerts for important metrics like DTU usage, CPU percentage, and storage usage.
Use SQL Server Management Studio (SSMS) Tools:Activity Monitor:
- Open Activity Monitor in SSMS to view real-time performance data, including CPU usage, I/O statistics, and active sessions.Query Store:
- Enable and configure Query Store to capture and analyze query performance over time.
```
sql
  ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;
 
```
Monitor and Analyze Performance Data:Review Collected Data:
- Regularly review performance data collected from DMVs, PerfMon, Extended Events, and Azure Monitor.Identify Bottlenecks:
- Use the data to identify performance bottlenecks such as high CPU usage, memory pressure, disk I/O issues, or inefficient queries.Optimize Performance:
- Implement optimizations based on the findings, such as indexing, query tuning, resource allocation, and hardware upgrades.

Example Scenario

Scenario: Configuring and monitoring performance for an on-premises SQL Server instance to ensure optimal performance.

Steps:

Configure Performance Monitoring:

Using DMVs:

sql
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats;

Using PerfMon:
- Add counters for Processor Time, Memory Usage, and Disk I/O.

Using Extended Events:

sql
CREATE EVENT SESSION [PerformanceSession] ON SERVER
ADD EVENT sqlserver.rpc_completed
ADD TARGET package0.event_file (SET filename = N'C:\temp\PerformanceSession.xel');
ALTER EVENT SESSION [PerformanceSession] ON SERVER STATE = START;

Monitor Activity Using SSMS:
- Open Activity Monitor to view real-time data.
- Enable and configure Query Store to capture query performance.
Analyze and Optimize:
- Regularly review collected data.
- Identify performance issues such as high wait times or inefficient queries.
- Implement optimizations like indexing and query tuning.

Best Practices:

Regular Monitoring:
- Continuously monitor performance metrics to detect and resolve issues proactively.
Comprehensive Data Collection:
- Use multiple sources (DMVs, PerfMon, Extended Events, Azure Monitor) for a comprehensive view of performance.
Automate Alerts:
- Set up automated alerts for significant deviations from normal performance metrics.
Historical Analysis:
- Use historical performance data to identify trends and make informed decisions about capacity planning and optimization.

Resources:

Microsoft Learn: Monitoring Performance for SQL Server
Microsoft Docs: Dynamic Management Views and Functions
Microsoft Learn: Azure Monitor Overview
Microsoft Docs: Using Extended Events

By following these steps and best practices, you can effectively configure and monitor activity and performance for SQL Server and Azure SQL Database, ensuring optimal performance and quick identification of potential issues.

Question 5

Q

Monitor by using SQL Insights

Answer

A

Monitor by Using SQL Insights

Overview:
SQL Insights is an advanced performance monitoring and diagnostic solution in Azure SQL Database that helps you gain deeper insights into database performance. It provides a comprehensive view of SQL Server and Azure SQL Database instances, capturing detailed metrics and telemetry data to aid in troubleshooting and optimizing database performance.

Key Concepts:

Metrics Collection:
- SQL Insights collects various performance metrics, including CPU usage, memory usage, disk I/O, and wait statistics.
Telemetry Data:
- It provides detailed telemetry data, including query performance, execution plans, and resource utilization.
Integration with Azure Monitor:
- SQL Insights integrates seamlessly with Azure Monitor, allowing you to leverage its powerful analytics and alerting capabilities.

Steps to Monitor Using SQL Insights

Enable SQL Insights:Using Azure Portal:
- Navigate to your Azure SQL Database or SQL Server instance in the Azure portal.
- Select “Monitoring” > “SQL Insights”.
- Follow the prompts to enable SQL Insights and configure the necessary settings.
Configure Data Collection:
- Specify the metrics and telemetry data to be collected.
- Configure the retention period and storage options for the collected data.
Set Up Alerts and Notifications:
- Use Azure Monitor to set up alerts based on the metrics collected by SQL Insights.
- Configure notifications to receive alerts via email, SMS, or other communication channels.
Analyze Performance Data:
- Use Azure Monitor’s dashboards and workbooks to visualize and analyze the collected data.
- Identify performance bottlenecks, resource contention, and query inefficiencies.
Optimize Performance:
- Based on the insights gained, implement optimizations such as query tuning, indexing, and resource scaling.

Example Scenario

Scenario: Using SQL Insights to monitor an Azure SQL Database for performance issues.

Steps:

Enable SQL Insights:
- Navigate to your Azure SQL Database instance in the Azure portal.
- Select “Monitoring” > “SQL Insights” and enable it.
Configure Data Collection:
- Choose the metrics to be collected, such as CPU usage, memory usage, and query performance.
- Set the retention period for the collected data.
Set Up Alerts:
- Use Azure Monitor to create alerts for high CPU usage and long-running queries.
- Configure email notifications for these alerts.
Analyze Performance Data:
- Use Azure Monitor dashboards to visualize CPU usage trends and identify periods of high activity.
- Analyze query performance data to pinpoint inefficient queries.
Optimize Performance:
- Implement query tuning and indexing based on the insights gained from SQL Insights.
- Scale the database resources if necessary to handle peak loads.

Best Practices:

Regular Monitoring:
- Continuously monitor performance metrics to detect and resolve issues proactively.
Comprehensive Data Collection:
- Collect a wide range of metrics to gain a holistic view of database performance.
Timely Alerts:
- Set up timely alerts for critical performance metrics to ensure quick response to issues.
Historical Analysis:
- Use historical performance data to identify trends and make informed decisions about capacity planning and optimization.

Resources:

Microsoft Learn: Azure Monitor SQL Insights
Microsoft Docs: SQL Insights Overview
Microsoft Learn: Monitor Performance with Azure Monitor

By following these steps and best practices, you can effectively monitor your SQL Server and Azure SQL Database instances using SQL Insights, ensuring optimal performance and quick identification of potential issues.

Question 6

Q

Monitor by using Extended Events

Answer

A

Monitor by Using Extended Events

Overview:
Extended Events (XEvents) is a lightweight performance monitoring system built into SQL Server that allows you to collect and analyze detailed information about server and database activity. It is highly configurable, making it suitable for both troubleshooting specific issues and general performance monitoring.

Key Concepts:

Event Sessions:
- Event Session: A collection of events, actions, and targets that define what data to collect and where to store it.
- Event: A specific action or occurrence in SQL Server, such as a completed SQL statement or a lock acquired.
- Target: The destination for the collected event data, such as a file or the ring buffer.
Actions:
- Additional information that can be collected when an event occurs, such as SQL text, query plans, or system process IDs.

Steps to Monitor Using Extended Events

Create an Extended Event Session:Using T-SQL:

sql
-- Create an Extended Event session to capture completed SQL statements
CREATE EVENT SESSION [MySession] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.database_id)
)
ADD TARGET package0.event_file (SET filename = N'C:\temp\MySession.xel');
-- Start the session
ALTER EVENT SESSION [MySession] ON SERVER STATE = START;

Using SQL Server Management Studio (SSMS):
- Navigate to “Management” > “Extended Events” > “New Session Wizard”.
- Follow the wizard to create and configure the session, including selecting events and targets.

Monitor the Event Session:
- Use SSMS or T-SQL to view the status and data of the event session.
- Example:
```sql
– View the status of the session
SELECT * FROM sys.dm_xe_sessions WHERE name = ‘MySession’;– Query the event data
SELECT
event_data.value(‘(event/@name)[1]’, ‘varchar(50)’) AS event_name,
event_data.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS sql_text
FROM sys.fn_xe_file_target_read_file(‘C:\temp\MySession*.xel’, NULL, NULL, NULL)
CROSS APPLY event_data.nodes(‘//event’) AS event_data(event_data);
```
Analyze the Data:
- Use the collected data to identify performance bottlenecks, resource contention, and query inefficiencies.
- Example Analysis:
  - Look for long-running queries, frequent waits, and high resource consumption.

Stop and Drop the Event Session:Using T-SQL:

sql
-- Stop the session
ALTER EVENT SESSION [MySession] ON SERVER STATE = STOP;
-- Drop the session
DROP EVENT SESSION [MySession] ON SERVER;

Example Scenario

Scenario: Monitoring and analyzing performance of SQL Server to identify long-running queries and high CPU usage.

Steps:

Create an Extended Event Session:

sql
CREATE EVENT SESSION [PerfMonitorSession] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.database_id)
)
ADD TARGET package0.event_file (SET filename = N'C:\temp\PerfMonitorSession.xel');
ALTER EVENT SESSION [PerfMonitorSession] ON SERVER STATE = START;

Monitor the Event Session:
```sql
– View session status
SELECT * FROM sys.dm_xe_sessions WHERE name = ‘PerfMonitorSession’;– Query event data
SELECT
event_data.value(‘(event/@name)[1]’, ‘varchar(50)’) AS event_name,
event_data.value(‘(event/data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS sql_text
FROM sys.fn_xe_file_target_read_file(‘C:\temp\PerfMonitorSession*.xel’, NULL, NULL, NULL)
CROSS APPLY event_data.nodes(‘//event’) AS event_data(event_data);
```
Analyze the Data:
- Identify long-running queries and analyze their execution plans.
- Look for patterns indicating high CPU usage or frequent waits.

Stop and Drop the Event Session:

sql
ALTER EVENT SESSION [PerfMonitorSession] ON SERVER STATE = STOP;
DROP EVENT SESSION [PerfMonitorSession] ON SERVER;

Best Practices:

Define Clear Objectives:
- Before creating an event session, define the specific performance issues or metrics you want to monitor.
Use Filters:
- Apply filters to the events to reduce overhead and focus on the most relevant data.
Regularly Review and Adjust:
- Regularly review the collected data and adjust the event session configuration as needed to capture the most useful information.
Secure Event Data:
- Ensure that the collected event data is stored securely and access is restricted to authorized users only.

Resources:

Microsoft Learn: Extended Events Overview
Microsoft Docs: CREATE EVENT SESSION (Transact-SQL)
Microsoft Learn: Extended Events Targets

By following these steps and best practices, you can effectively use Extended Events to monitor and analyze SQL Server and Azure SQL Database performance, identifying and addressing potential issues proactively.

Question 7

Q

Configure Query Store

Answer

A

Configure Query Store

Overview:
Query Store is a feature in SQL Server and Azure SQL Database that captures a history of queries, plans, and runtime statistics, providing insights into query performance over time. It helps in identifying and troubleshooting performance issues, analyzing query regressions, and comparing query performance before and after changes.

Key Concepts:

Operation Modes:
- Read Write: Query Store collects and stores query performance data.
- Read Only: Query Store stops collecting new data but allows access to existing data.
- Off: Query Store is disabled and no data is collected or stored.
Data Collection:
- Execution Plans: Captures the execution plans for queries.
- Runtime Statistics: Collects performance metrics such as execution time, logical reads, and CPU time.
Policies:
- Data Flush Interval: Specifies how often data is flushed to disk.
- Statistics Collection Interval: Determines the frequency of collecting runtime statistics.
- Stale Query Threshold: Defines the period after which query data is considered stale and eligible for cleanup.

Steps to Configure Query Store

Enable Query Store:Using T-SQL:
```
sql
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON;
```
Using SQL Server Management Studio (SSMS):
- Navigate to the database in Object Explorer.
- Right-click on the database, select “Properties”.
- Go to the “Query Store” page and set “Operation Mode (Requested)” to “Read Write”.
Configure Query Store Settings:Using T-SQL:
```
sql
ALTER DATABASE YourDatabase
SET QUERY_STORE ( 
    OPERATION_MODE = READ_WRITE, 
    DATA_FLUSH_INTERVAL_SECONDS = 900, 
    INTERVAL_LENGTH_MINUTES = 60, 
    MAX_STORAGE_SIZE_MB = 1024, 
    QUERY_CAPTURE_MODE = ALL 
);
```
Using SSMS:
- In the “Query Store” page under database properties, configure settings such as:
- Operation Mode: Read Write
- Data Flush Interval (Minutes): e.g., 15 minutes
- Statistics Collection Interval: e.g., 60 minutes
- Max Size (MB): e.g., 1024 MB
- Query Capture Mode: All or Auto (to capture based on performance characteristics)

Monitor Query Store:Using SSMS:
- Go to “Database” > “Query Store” > “Reports”.
- Use built-in reports such as “Regressed Queries”, “Top Resource Consuming Queries”, and “Overall Resource Consumption”.Using T-SQL:

sql
-- View top resource-consuming queries
SELECT TOP 10
    qs.query_id, 
    qs.total_execution_count, 
    qs.total_logical_reads, 
    qs.total_worker_time, 
    qs.total_elapsed_time, 
    qt.query_sql_text
FROM sys.query_store_query_stats AS qs
JOIN sys.query_store_query_text AS qt
    ON qs.query_text_id = qt.query_text_id
ORDER BY qs.total_logical_reads DESC;

Analyze and Optimize Queries:
- Use Query Store data to identify long-running and resource-intensive queries.
- Compare query performance before and after changes.
- Implement indexing, query rewriting, or plan forcing to optimize performance.

Example Scenario

Scenario: Configuring Query Store for a production database to monitor and optimize query performance.

Steps:

Enable Query Store:

sql
ALTER DATABASE ProductionDB
SET QUERY_STORE = ON;

Configure Query Store Settings:

sql
ALTER DATABASE ProductionDB
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE, 
    DATA_FLUSH_INTERVAL_SECONDS = 900, 
    INTERVAL_LENGTH_MINUTES = 60, 
    MAX_STORAGE_SIZE_MB = 2048, 
    QUERY_CAPTURE_MODE = AUTO 
);

Monitor Query Store Using SSMS:
- Navigate to “Database” > “Query Store” > “Reports”.
- Review the “Top Resource Consuming Queries” report to identify problematic queries.

Analyze Query Performance:

sql
SELECT TOP 10
    qs.query_id, 
    qs.total_execution_count, 
    qs.total_logical_reads, 
    qs.total_worker_time, 
    qs.total_elapsed_time, 
    qt.query_sql_text
FROM sys.query_store_query_stats AS qs
JOIN sys.query_store_query_text AS qt
    ON qs.query_text_id = qt.query_text_id
ORDER BY qs.total_logical_reads DESC;

Optimize Identified Queries:
- Investigate execution plans and optimize queries through indexing and query rewriting.
- Use plan forcing if necessary to ensure optimal execution plans.

Best Practices:

Regular Monitoring:
- Continuously monitor Query Store data to proactively identify and address performance issues.
Data Retention Management:
- Configure data retention settings to balance between historical data availability and storage consumption.
Selective Query Capture:
- Use the “Auto” query capture mode to focus on capturing relevant queries based on performance characteristics.
Integrate with Performance Tuning Tools:
- Combine Query Store insights with other performance tuning tools like Database Engine Tuning Advisor and Execution Plan analysis.

Resources:

Microsoft Learn: Query Store in SQL Server
Microsoft Docs: Monitoring Performance by Using the Query Store
Microsoft Docs: ALTER DATABASE SET Options (Transact-SQL)

By following these steps and best practices, you can effectively configure and utilize Query Store to monitor, analyze, and optimize query performance in SQL Server and Azure SQL Database environments.

Question 8

Q

Monitor by using Query Store

Answer

A

Monitor by Using Query Store

Overview:
Query Store is a feature in SQL Server and Azure SQL Database that collects and stores query execution statistics, execution plans, and runtime metrics. It helps in monitoring and troubleshooting query performance by providing insights into query behavior over time.

Key Concepts:

Data Collection:
- Query Store captures query text, execution plans, runtime statistics (e.g., duration, CPU time, logical reads), and wait statistics.
Query Store Views:
- sys.query_store_query: Contains information about each query, including query text and execution stats.
- sys.query_store_plan: Stores execution plans for queries.
- sys.query_store_runtime_stats: Contains runtime statistics for each query plan.
- sys.query_store_wait_stats: Captures wait statistics for each query plan.
Operation Modes:
- Read Write: Actively collects and stores data.
- Read Only: Stops collecting new data but allows access to existing data.
- Off: Disables Query Store.

Steps to Monitor Using Query Store

Enable Query Store:Using T-SQL:

sql
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON;

Configure Query Store Settings:Using T-SQL:

sql
ALTER DATABASE YourDatabase
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE, 
    DATA_FLUSH_INTERVAL_SECONDS = 900, 
    INTERVAL_LENGTH_MINUTES = 60, 
    MAX_STORAGE_SIZE_MB = 1024, 
    QUERY_CAPTURE_MODE = ALL 
);

Monitor Query Store Data:Using SSMS:
- Go to “Database” > “Query Store” > “Reports”.
- Use built-in reports such as “Top Resource Consuming Queries”, “Regressed Queries”, and “Overall Resource Consumption”.Using T-SQL:
- View Top Resource Consuming Queries:

sql
  SELECT TOP 10
      qs.query_id, 
      qt.query_sql_text,
      SUM(rs.avg_duration) AS total_duration,
      SUM(rs.count_executions) AS total_executions
  FROM sys.query_store_query_stats AS qs
  JOIN sys.query_store_query_text AS qt
      ON qs.query_text_id = qt.query_text_id
  JOIN sys.query_store_runtime_stats AS rs
      ON qs.plan_id = rs.plan_id
  GROUP BY qs.query_id, qt.query_sql_text
  ORDER BY total_duration DESC;

Analyze Query Performance:

Identify Long-Running Queries:

sql
SELECT 
    qt.query_sql_text, 
    MAX(rs.avg_duration) AS max_duration
FROM sys.query_store_query_stats AS qs
JOIN sys.query_store_query_text AS qt
    ON qs.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats AS rs
    ON qs.plan_id = rs.plan_id
GROUP BY qt.query_sql_text
ORDER BY max_duration DESC;

Compare Query Performance Before and After Changes:

sql
SELECT 
    qt.query_sql_text, 
    MIN(rs.first_execution_time) AS first_execution,
    MAX(rs.last_execution_time) AS last_execution,
    AVG(rs.avg_duration) AS avg_duration
FROM sys.query_store_query_stats AS qs
JOIN sys.query_store_query_text AS qt
    ON qs.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats AS rs
    ON qs.plan_id = rs.plan_id
GROUP BY qt.query_sql_text
ORDER BY avg_duration DESC;

Optimize Queries:
- Based on the insights gained, implement query optimizations such as indexing, query rewriting, and plan forcing.

Example Scenario

Scenario: Monitoring and optimizing query performance for an Azure SQL Database.

Steps:

Enable Query Store:

sql
ALTER DATABASE SalesDB
SET QUERY_STORE = ON;

Configure Query Store Settings:

sql
ALTER DATABASE SalesDB
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE, 
    DATA_FLUSH_INTERVAL_SECONDS = 900, 
    INTERVAL_LENGTH_MINUTES = 60, 
    MAX_STORAGE_SIZE_MB = 2048, 
    QUERY_CAPTURE_MODE = AUTO 
);

Monitor Query Store Using SSMS:
- Navigate to “Database” > “Query Store” > “Reports”.
- Review the “Top Resource Consuming Queries” report to identify high-impact queries.

Analyze Query Performance:

sql
SELECT TOP 10
    qs.query_id, 
    qt.query_sql_text,
    SUM(rs.avg_duration) AS total_duration,
    SUM(rs.count_executions) AS total_executions
FROM sys.query_store_query_stats AS qs
JOIN sys.query_store_query_text AS qt
    ON qs.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats AS rs
    ON qs.plan_id = rs.plan_id
GROUP BY qs.query_id, qt.query_sql_text
ORDER BY total_duration DESC;

Optimize Identified Queries:
- Investigate execution plans and optimize queries through indexing and query rewriting.

Best Practices:

Regular Monitoring:
- Continuously monitor Query Store data to proactively identify and address performance issues.
Data Retention Management:
- Configure data retention settings to balance between historical data availability and storage consumption.
Use Built-In Reports:
- Leverage SSMS built-in Query Store reports for quick insights into query performance.
Selective Query Capture:
- Use the “Auto” query capture mode to focus on capturing relevant queries based on performance characteristics.

Resources:

Microsoft Learn: Monitor Performance by Using the Query Store
Microsoft Docs: Query Store in SQL Server
Microsoft Docs: ALTER DATABASE SET Options (Transact-SQL)

By following these steps and best practices, you can effectively monitor and optimize query performance using Query Store in SQL Server and Azure SQL Database.

Question 9

Q

Identify sessions that cause blocking

Answer

A

Identify Sessions That Cause Blocking

Overview:
In SQL Server and Azure SQL Database, blocking occurs when one session holds a lock on a resource that another session requires, causing the second session to wait. Identifying sessions that cause blocking is crucial for troubleshooting and resolving performance issues.

Key Concepts:

Blocking Sessions:
- A blocking session holds locks on resources needed by other sessions, causing them to wait.
Blocked Sessions:
- A blocked session is waiting for a resource that is locked by another session.

Steps to Identify Blocking Sessions

Use Dynamic Management Views (DMVs):Identify Blocking and Blocked Sessions:

sql
SELECT 
    blocking_session_id AS BlockingSessionID, 
    session_id AS BlockedSessionID, 
    wait_type, 
    wait_time, 
    wait_resource, 
    last_wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Join with sys.dm_exec_sessions to Get Additional Information:

sql
SELECT 
    blocking_session_id AS BlockingSessionID, 
    s1.session_id AS BlockedSessionID, 
    s1.wait_type, 
    s1.wait_time, 
    s1.wait_resource, 
    s1.last_wait_type, 
    s2.login_name AS BlockingLoginName, 
    s2.host_name AS BlockingHostName, 
    s2.program_name AS BlockingProgramName,
    s2.text AS BlockingQueryText
FROM sys.dm_exec_requests s1
JOIN sys.dm_exec_sessions s2
    ON s1.blocking_session_id = s2.session_id
CROSS APPLY sys.dm_exec_sql_text(s2.most_recent_sql_handle) AS s2;

Use sys.dm_exec_requests and sys.dm_exec_sessions:

sql
SELECT 
    r.blocking_session_id AS BlockingSessionID, 
    r.session_id AS BlockedSessionID, 
    r.wait_type, 
    r.wait_time, 
    r.wait_resource, 
    s.login_name AS BlockingLoginName, 
    s.host_name AS BlockingHostName, 
    s.program_name AS BlockingProgramName
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
    ON r.blocking_session_id = s.session_id
WHERE r.blocking_session_id <> 0;

Use SQL Server Management Studio (SSMS) Activity Monitor:
- Open Activity Monitor in SSMS.
- Expand the “Processes” pane to view the list of active sessions.
- Look for sessions with a “Blocked By” value, indicating they are blocked by another session.
- The “Head Blocker” column identifies sessions causing the blocking.

Use Extended Events:Create an Extended Event Session:

sql
CREATE EVENT SESSION [BlockingMonitor] ON SERVER
ADD EVENT sqlserver.lock_acquired
ADD EVENT sqlserver.lock_released
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file (SET filename = 'C:\temp\BlockingMonitor.xel');
ALTER EVENT SESSION [BlockingMonitor] ON SERVER STATE = START;

Analyze Collected Data:

Use SSMS or T-SQL to read the Extended Events file and analyze blocking events.

Example:

sql
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/data[@name="session_id"]/value)[1]', 'int') AS session_id,
    event_data.value('(event/data[@name="blocked_process"]/value')[1]', 'xml') AS blocked_process
FROM sys.fn_xe_file_target_read_file('C:\temp\BlockingMonitor*.xel', NULL, NULL, NULL)
CROSS APPLY event_data.nodes('//event') AS event_data(event_data);

Example Scenario

Scenario: Identifying sessions causing blocking in a production SQL Server instance to troubleshoot performance issues.

Steps:

Use DMVs to Identify Blocking Sessions:

sql
SELECT 
    blocking_session_id AS BlockingSessionID, 
    session_id AS BlockedSessionID, 
    wait_type, 
    wait_time, 
    wait_resource, 
    last_wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Join with sys.dm_exec_sessions to Get Additional Information:

sql
SELECT 
    blocking_session_id AS BlockingSessionID, 
    s1.session_id AS BlockedSessionID, 
    s1.wait_type, 
    s1.wait_time, 
    s1.wait_resource, 
    s1.last_wait_type, 
    s2.login_name AS BlockingLoginName, 
    s2.host_name AS BlockingHostName, 
    s2.program_name AS BlockingProgramName
FROM sys.dm_exec_requests s1
JOIN sys.dm_exec_sessions s2
    ON s1.blocking_session_id = s2.session_id
WHERE s1.blocking_session_id <> 0;

Use SSMS Activity Monitor:
- Open Activity Monitor.
- Expand the “Processes” pane to view blocked sessions and their blockers.

Create and Use an Extended Event Session:

sql
CREATE EVENT SESSION [BlockingMonitor] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file (SET filename = 'C:\temp\BlockingMonitor.xel');
ALTER EVENT SESSION [BlockingMonitor] ON SERVER STATE = START;

Analyze the Collected Data:

sql
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/data[@name="session_id"]/value)[1]', 'int') AS session_id,
    event_data.value('(event/data[@name="blocked_process"]/value')[1]', 'xml') AS blocked_process
FROM sys.fn_xe_file_target_read_file('C:\temp\BlockingMonitor*.xel', NULL, NULL, NULL)
CROSS APPLY event_data.nodes('//event') AS event_data(event_data);

Best Practices:

Regular Monitoring:
- Continuously monitor for blocking sessions to detect and resolve issues promptly.
Automate Alerts:
- Set up alerts to notify you when blocking sessions exceed a threshold.
Analyze Blocking Patterns:
- Identify recurring blocking patterns and optimize queries or indexes to reduce blocking.
Use Extended Events for Detailed Analysis:
- Leverage Extended Events for detailed and lightweight monitoring of blocking and other performance issues.

Resources:

Microsoft Learn: Monitor and Troubleshoot Blocking
Microsoft Docs: Dynamic Management Views and Functions (Transact-SQL)
Microsoft Docs: Using Extended Events

By following these steps and best practices, you can effectively identify and resolve sessions causing blocking in SQL Server and Azure SQL Database, ensuring smoother and more efficient database operations.

Question 10

Q

Identify performance issues using dynamic management views (DMVs)

Answer

A

Identify Performance Issues Using Dynamic Management Views (DMVs)

Overview:
Dynamic Management Views (DMVs) in SQL Server provide real-time insights into server and database performance. They help identify performance issues such as resource bottlenecks, inefficient queries, and contention points. By querying DMVs, database administrators can monitor, diagnose, and optimize SQL Server performance.

Key DMVs for Identifying Performance Issues

sys.dm_exec_requests:

Provides information about requests currently executing on SQL Server.

Example Query:

sql
SELECT 
    session_id, 
    status, 
    command, 
    cpu_time, 
    total_elapsed_time, 
    blocking_session_id
FROM sys.dm_exec_requests
WHERE status = 'running';

sys.dm_exec_sessions:

Provides information about user connections to the database.

Example Query:

sql
SELECT 
    session_id, 
    login_name, 
    host_name, 
    program_name, 
    status, 
    cpu_time, 
    memory_usage
FROM sys.dm_exec_sessions;

sys.dm_os_wait_stats:

Provides information about wait statistics, helping to identify bottlenecks.

Example Query:

sql
SELECT 
    wait_type, 
    wait_time_ms, 
    waiting_tasks_count, 
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

sys.dm_exec_query_stats:

Provides aggregate performance statistics for cached query plans.

Example Query:

sql
SELECT 
    TOP 10 
    total_worker_time / execution_count AS avg_cpu_time, 
    total_elapsed_time / execution_count AS avg_duration, 
    execution_count, 
    text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY avg_duration DESC;

sys.dm_exec_sql_text:

Retrieves the text of SQL batches or modules given a SQL handle.

Example Query:

sql
SELECT 
    text, 
    creation_time, 
    execution_count, 
    total_worker_time, 
    total_elapsed_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
WHERE qs.total_worker_time > 0;

sys.dm_io_virtual_file_stats:

Provides I/O statistics for data and log files.

Example Query:

sql
SELECT 
    database_id, 
    file_id, 
    num_of_reads, 
    num_of_writes, 
    io_stall_read_ms, 
    io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

sys.dm_db_index_usage_stats:

Provides information about index usage, which can help identify unused or heavily used indexes.

Example Query:

sql
SELECT 
    database_id, 
    object_id, 
    index_id, 
    user_seeks, 
    user_scans, 
    user_lookups, 
    user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('YourDatabaseName');

Example Scenario

Scenario: Identifying performance issues in an SQL Server instance experiencing slow query performance.

Steps:

Identify Blocking Sessions:

sql
SELECT 
    blocking_session_id AS BlockingSessionID, 
    session_id AS BlockedSessionID, 
    wait_type, 
    wait_time, 
    wait_resource, 
    last_wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Check Wait Statistics:

sql
SELECT 
    wait_type, 
    wait_time_ms, 
    waiting_tasks_count, 
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Analyze Query Performance:

sql
SELECT 
    TOP 10 
    total_worker_time / execution_count AS avg_cpu_time, 
    total_elapsed_time / execution_count AS avg_duration, 
    execution_count, 
    text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY avg_duration DESC;

Review I/O Statistics:

sql
SELECT 
    database_id, 
    file_id, 
    num_of_reads, 
    num_of_writes, 
    io_stall_read_ms, 
    io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

Inspect Index Usage:

sql
SELECT 
    database_id, 
    object_id, 
    index_id, 
    user_seeks, 
    user_scans, 
    user_lookups, 
    user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('YourDatabaseName');

Best Practices:

Regular Monitoring:
- Continuously monitor performance metrics using DMVs to detect and resolve issues proactively.
Correlate Data:
- Combine data from multiple DMVs to get a comprehensive view of performance issues.
Historical Analysis:
- Store and analyze historical DMV data to identify performance trends and predict future issues.
Automate Alerts:
- Set up automated alerts for critical performance metrics to ensure timely responses to issues.

Resources:

Microsoft Learn: Dynamic Management Views and Functions (Transact-SQL)
Microsoft Learn: SQL Server Performance Monitoring and Tuning
Microsoft Docs: sys.dm_exec_requests (Transact-SQL)
Microsoft Docs: sys.dm_os_wait_stats (Transact-SQL)

By following these steps and best practices, you can effectively use DMVs to identify and resolve performance issues in SQL Server and Azure SQL Database, ensuring optimal database performance.

Question 11

Q

Identify and implement index changes for queries

Answer

A

Identify and Implement Index Changes for Queries

Overview:
Proper indexing is crucial for optimizing query performance in SQL Server and Azure SQL Database. Indexes speed up data retrieval by providing efficient access paths to the data. Identifying and implementing the right index changes can significantly reduce query execution times and improve overall database performance.

Key Concepts:

Types of Indexes:
- Clustered Index: Sorts and stores the data rows in the table based on the index key. Each table can have only one clustered index.
- Non-Clustered Index: Contains pointers to the data rows in the table and can be created on one or more columns of the table.
- Covering Index: A non-clustered index that includes all the columns needed to satisfy a query without having to reference the table data.
Index Fragmentation:
- Fragmentation can degrade performance. Use sys.dm_db_index_physical_stats to check fragmentation levels and reorganize or rebuild indexes as needed.

Steps to Identify and Implement Index Changes

Identify Missing Indexes:Using Dynamic Management Views (DMVs):

sql
SELECT
    migs.user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS improvement_measure,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.last_user_seek
FROM
    sys.dm_db_missing_index_group_stats AS migs
    INNER JOIN sys.dm_db_missing_index_groups AS mig
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid
        ON mig.index_handle = mid.index_handle
WHERE
    migs.user_seeks > 0
ORDER BY
    improvement_measure DESC;

Analyze Index Usage:Using sys.dm_db_index_usage_stats:

sql
SELECT
    db_name(database_id) AS database_name,
    object_name(object_id, database_id) AS table_name,
    index_id,
    user_seeks,
    user_scans,
    user_lookups,
    user_updates
FROM
    sys.dm_db_index_usage_stats
WHERE
    database_id = DB_ID('YourDatabaseName')
ORDER BY
    user_seeks DESC;

Evaluate Index Fragmentation:Using sys.dm_db_index_physical_stats:

sql
SELECT
    object_name(object_id) AS table_name,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(N'YourDatabaseName'), NULL, NULL, NULL, 'LIMITED')
WHERE
    avg_fragmentation_in_percent > 10
ORDER BY
    avg_fragmentation_in_percent DESC;

Implement Index Changes:Creating Indexes:

sql
CREATE NONCLUSTERED INDEX idx_YourIndexName
ON YourTableName (Column1, Column2)
INCLUDE (Column3, Column4);

Rebuilding Indexes:

sql
ALTER INDEX idx_YourIndexName
ON YourTableName
REBUILD;

Reorganizing Indexes:

sql
ALTER INDEX idx_YourIndexName
ON YourTableName
REORGANIZE;

Example Scenario

Scenario: Optimizing query performance for a sales database by identifying and implementing index changes.

Steps:

Identify Missing Indexes:

sql
SELECT
    migs.user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS improvement_measure,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.last_user_seek
FROM
    sys.dm_db_missing_index_group_stats AS migs
    INNER JOIN sys.dm_db_missing_index_groups AS mig
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid
        ON mig.index_handle = mid.index_handle
WHERE
    migs.user_seeks > 0
ORDER BY
    improvement_measure DESC;

Analyze Index Usage:

sql
SELECT
    db_name(database_id) AS database_name,
    object_name(object_id, database_id) AS table_name,
    index_id,
    user_seeks,
    user_scans,
    user_lookups,
    user_updates
FROM
    sys.dm_db_index_usage_stats
WHERE
    database_id = DB_ID('SalesDB')
ORDER BY
    user_seeks DESC;

Evaluate Index Fragmentation:

sql
SELECT
    object_name(object_id) AS table_name,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(N'SalesDB'), NULL, NULL, NULL, 'LIMITED')
WHERE
    avg_fragmentation_in_percent > 10
ORDER BY
    avg_fragmentation_in_percent DESC;

Implement Index Changes:

Create Index:

sql
CREATE NONCLUSTERED INDEX idx_CustomerSales
ON Sales (CustomerID, SaleDate)
INCLUDE (SaleAmount);

Rebuild Index:

sql
ALTER INDEX idx_CustomerSales
ON Sales
REBUILD;

Reorganize Index:

sql
ALTER INDEX idx_CustomerSales
ON Sales
REORGANIZE;

Best Practices:

Regular Monitoring:
- Continuously monitor index usage and performance to identify opportunities for improvement.
Avoid Over-Indexing:
- Balance the number of indexes to avoid excessive maintenance overhead and reduced performance for write operations.
Index Maintenance:
- Regularly rebuild or reorganize indexes to minimize fragmentation and maintain optimal performance.
Covering Indexes:
- Use covering indexes to reduce the need for key lookups and improve query performance.

Resources:

Microsoft Learn: Index Design Guidelines
Microsoft Docs: sys.dm_db_missing_index_details (Transact-SQL)
Microsoft Docs: sys.dm_db_index_usage_stats (Transact-SQL)
Microsoft Learn: Reorganize and Rebuild Indexes

By following these steps and best practices, you can effectively identify and implement index changes to optimize query performance in SQL Server and Azure SQL Database.

Question 12

Q

Recommend query construct modifications based on resource usage

Answer

A

Recommend Query Construct Modifications Based on Resource Usage

Overview:
Optimizing query constructs based on resource usage can significantly improve the performance of SQL Server and Azure SQL Database by reducing CPU, memory, and I/O consumption. This involves analyzing query execution plans, identifying inefficient patterns, and recommending modifications.

Key Concepts:

Execution Plans:
- Actual Execution Plan: Shows the detailed steps SQL Server takes to execute a query, including the cost and resources used.
- Estimated Execution Plan: Provides an estimated view of the execution plan without executing the query.
Common Query Issues:
- Missing Indexes: Queries that scan entire tables instead of using indexes.
- Inefficient Joins: Poorly structured joins that lead to excessive I/O and CPU usage.
- Suboptimal Filtering: Lack of proper filtering or use of non-sargable (non-indexable) predicates.
- Overuse of SELECT *: Retrieving more data than necessary by selecting all columns.
- Implicit Conversions: Data type mismatches that force SQL Server to convert data types at runtime.

Steps to Recommend Query Construct Modifications

Analyze Execution Plans:Using SSMS:
- View the actual execution plan by running the query with the “Include Actual Execution Plan” option.
- Look for expensive operations like table scans, hash joins, and high-cost nodes.Using T-SQL:
```
sql
SET STATISTICS PROFILE ON;
-- Your query here
SET STATISTICS PROFILE OFF;
```

Identify and Resolve Missing Indexes:

Check for table scans or index scans that could benefit from indexing.
Use DMVs to identify missing indexes.

Example:

sql
SELECT 
    migs.user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS improvement_measure,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.last_user_seek
FROM
    sys.dm_db_missing_index_group_stats AS migs
    INNER JOIN sys.dm_db_missing_index_groups AS mig
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid
        ON mig.index_handle = mid.index_handle
WHERE
    migs.user_seeks > 0
ORDER BY
    improvement_measure DESC;

Optimize Joins:
- Ensure joins are using indexed columns and consider using INNER JOIN instead of OUTER JOIN where possible.
- Example:
```
sql
SELECT a.*, b.*
FROM Orders a
INNER JOIN Customers b ON a.CustomerID = b.CustomerID;
```

Improve Filtering:

Use indexed columns in the WHERE clause and avoid functions or calculations on these columns.

Example:

sql
-- Avoid
SELECT * FROM Sales WHERE YEAR(SaleDate) = 2023;
-- Better
SELECT * FROM Sales WHERE SaleDate >= '2023-01-01' AND SaleDate < '2024-01-01';

Avoid SELECT *:

Select only the columns needed to reduce I/O and improve performance.

Example:

sql
-- Avoid
SELECT * FROM Orders;
-- Better
SELECT OrderID, OrderDate, CustomerID FROM Orders;

Resolve Implicit Conversions:

Ensure data types in predicates match the column data types to avoid implicit conversions.

Example:

sql
-- Avoid
SELECT * FROM Orders WHERE OrderDate = '2023-01-01';
-- Better
SELECT * FROM Orders WHERE OrderDate = CONVERT(DATE, '2023-01-01');

Example Scenario

Scenario: Optimizing a query that retrieves customer orders to improve performance.

Steps:

Analyze Execution Plan:
- Review the actual execution plan and identify a table scan on the Orders table.

Identify Missing Indexes:

Use DMVs to find missing indexes on the CustomerID column in the Orders table.

sql
SELECT 
  migs.user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS improvement_measure,
  mid.statement AS table_name,
  mid.equality_columns,
  mid.inequality_columns,
  mid.included_columns,
  migs.last_user_seek
FROM
  sys.dm_db_missing_index_group_stats AS migs
  INNER JOIN sys.dm_db_missing_index_groups AS mig
      ON migs.group_handle = mig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details AS mid
      ON mig.index_handle = mid.index_handle
WHERE
  migs.user_seeks > 0
ORDER BY
  improvement_measure DESC;

Create the Missing Index:

sql
CREATE NONCLUSTERED INDEX idx_CustomerID ON Orders (CustomerID);

Optimize the Query:
- Avoid using SELECT * and include only necessary columns.
- Ensure the WHERE clause uses indexed columns without functions.
```
sql
SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE CustomerID = 12345;
```

Best Practices:

Regular Query Reviews:
- Periodically review and optimize queries based on execution plans and performance metrics.
Use Covering Indexes:
- Create indexes that include all columns needed by queries to avoid key lookups.
Educate Developers:
- Train developers to write efficient SQL queries and understand the impact of query constructs on performance.
Monitor and Adjust:
- Continuously monitor query performance and make adjustments as data and usage patterns change.

Resources:

Microsoft Learn: Execution Plans
Microsoft Docs: sys.dm_db_missing_index_details (Transact-SQL)
Microsoft Docs: Index Design Guide

By following these steps and best practices, you can effectively recommend and implement query construct modifications to optimize resource usage and improve performance in SQL Server and Azure SQL Database.

Question 13

Q

Assess the use of query hints for query performance

Answer

A

Assess the Use of Query Hints for Query Performance

Overview:
Query hints in SQL Server are directives that influence the behavior of the query optimizer. They can be used to override default query execution plans and improve performance. However, they should be used cautiously as they can also lead to suboptimal performance if not properly managed.

Key Concepts:

Query Hints:
- FORCESEEK: Forces the query optimizer to use an index seek instead of an index scan.
- FORCESCAN: Forces the query optimizer to use an index scan.
- OPTIMIZE FOR: Provides a hint to the optimizer about the value to use for a parameter during query optimization.
- MAXDOP: Limits the number of processors to use for the query.
- RECOMPILE: Recompiles the query every time it runs, ensuring the most current statistics are used.
- LOOP JOIN, MERGE JOIN, HASH JOIN: Specifies the type of join algorithm to use.

Steps to Assess the Use of Query Hints

Analyze Current Performance:
- Use DMVs and execution plans to understand the current performance characteristics of the query.
- Example Query to Analyze Execution Plan:
```
sql
SET STATISTICS PROFILE ON;
-- Your query here
SET STATISTICS PROFILE OFF;
```
Identify Performance Bottlenecks:
- Look for high-cost operations, excessive I/O, and CPU usage in the execution plan.
- Identify if the query is suffering from issues like parameter sniffing or suboptimal join algorithms.
Apply and Test Query Hints:
- Apply appropriate query hints to address identified performance issues.
- Example: Using FORCESEEK to Improve Index Usage:
```
sql
SELECT * 
FROM Orders WITH (FORCESEEK)
WHERE OrderDate = '2023-01-01';
```
Compare Performance:
- Compare the execution plans and performance metrics before and after applying the hints.
- Use tools like SQL Server Profiler, Extended Events, and DMVs to measure the impact.
Iterate and Refine:
- Test different query hints and combinations to find the optimal configuration.
- Continuously monitor and adjust based on changing data patterns and workload characteristics.

Example Scenario

Scenario: Improving the performance of a query that retrieves customer orders using query hints.

Steps:

Analyze Current Performance:
- Review the execution plan and identify that the query is performing an index scan instead of an index seek.
```
sql
SET STATISTICS PROFILE ON;
SELECT * 
FROM Orders 
WHERE CustomerID = 12345;
SET STATISTICS PROFILE OFF;
```
Identify Performance Bottlenecks:
- Notice that the index scan is causing high I/O and increasing query execution time.

Apply Query Hint:

Use the FORCESEEK hint to force an index seek.

sql
SELECT * 
FROM Orders WITH (FORCESEEK)
WHERE CustomerID = 12345;

Compare Performance:
- Compare execution times and I/O statistics before and after applying the FORCESEEK hint.
- Measure the reduction in I/O operations and execution time.

Iterate and Refine:

Test other hints like OPTIMIZE FOR and MAXDOP if necessary.

sql
SELECT * 
FROM Orders
WHERE CustomerID = 12345
OPTION (OPTIMIZE FOR (CustomerID = 12345));

Best Practices:

Use Sparingly:
- Apply query hints only when necessary and after thorough testing, as they can lead to maintenance challenges.
Understand the Data:
- Have a good understanding of the data distribution and workload to apply the most appropriate hints.
Monitor and Adjust:
- Continuously monitor the performance and adjust hints as the data and workload change.
Test in Non-Production:
- Always test query hints in a non-production environment before deploying to production.

Resources:

Microsoft Learn: Query Hints (Transact-SQL)
Microsoft Docs: Execution Plan Caching and Reuse
SQLShack: SQL Server Query Hints - An Overview

By following these steps and best practices, you can effectively assess and implement query hints to optimize query performance in SQL Server and Azure SQL Database.

Question 14

Q

Review execution plans

Answer

A

Review Execution Plans

Overview:
Execution plans in SQL Server and Azure SQL Database provide a detailed roadmap of how queries are executed. Reviewing execution plans helps identify inefficiencies and potential bottlenecks, enabling database administrators to optimize query performance.

Key Concepts:

Types of Execution Plans:
- Estimated Execution Plan: Provides a prediction of how SQL Server will execute a query without running it.
- Actual Execution Plan: Shows the actual steps taken by SQL Server to execute a query, including runtime metrics.
Key Components of Execution Plans:
- Operators: Represent individual steps in the execution plan (e.g., Index Seek, Index Scan, Hash Join).
- Cost: The estimated expense of each operation relative to the total cost of the query.
- Cardinality: The number of rows processed by each operator.
- Warnings: Indicate potential issues such as missing statistics or implicit conversions.

Steps to Review Execution Plans

Generate an Execution Plan:Using SQL Server Management Studio (SSMS):
- For Estimated Execution Plan: Click on “Display Estimated Execution Plan” or press Ctrl + L.
- For Actual Execution Plan: Click on “Include Actual Execution Plan” or press Ctrl + M before executing the query.Using T-SQL:
```sql
– For Estimated Execution Plan
SET SHOWPLAN_XML ON;
GO
– Your query here
SET SHOWPLAN_XML OFF;
GO– For Actual Execution Plan
SET STATISTICS XML ON;
GO
– Your query here
SET STATISTICS XML OFF;
GO
```
Analyze Key Operators:
- Index Seek: Indicates efficient use of an index to find specific rows.
- Index Scan: Indicates scanning the entire index, which can be less efficient.
- Table Scan: Indicates scanning the entire table, often a performance issue.
- Nested Loops, Hash Match, Merge Join: Types of join operations with varying performance characteristics.
Example:
```
sql
SELECT *
FROM Orders
WHERE OrderDate = '2023-01-01';
```
Review Cost Distribution:
- Examine the relative cost of each operator to identify expensive operations.
- Focus on operators with the highest cost percentage as potential optimization targets.
Check for Warnings and Issues:
- Look for warnings such as missing statistics, implicit conversions, and spills to tempdb.
- Address these issues to improve query performance.
Interpret Cardinality Estimates:
- Compare estimated and actual row counts for each operator.
- Significant discrepancies can indicate issues with statistics or the query optimizer’s understanding of data distribution.

Use Query Store for Historical Analysis:

Query Store captures and retains historical execution plans, allowing for comparison over time.

Example:

sql
SELECT 
    q.query_id, 
    q.query_text_id, 
    p.plan_id, 
    p.creation_time, 
    p.last_execution_time, 
    p.execution_count, 
    t.text AS query_text
FROM 
    sys.query_store_query AS q
    JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
    JOIN sys.query_store_query_text AS t ON q.query_text_id = t.query_text_id
WHERE 
    t.text LIKE '%YourQueryPattern%'
ORDER BY 
    p.last_execution_time DESC;

Example Scenario

Scenario: Reviewing the execution plan of a query retrieving customer orders to optimize performance.

Steps:

Generate the Actual Execution Plan:

sql
SET STATISTICS XML ON;
SELECT * 
FROM Orders 
WHERE CustomerID = 12345;
SET STATISTICS XML OFF;

Analyze Key Operators:
- Identify that the plan includes an Index Scan on the Orders table.
- Review the costs and note that the Index Scan is the most expensive operation.
Check for Warnings:
- Notice a warning about missing statistics on the CustomerID column.
Interpret Cardinality Estimates:
- Compare the estimated rows to actual rows processed and identify significant discrepancies.
Optimize the Query:
- Create an index on the CustomerID column to improve performance.
```
sql
CREATE NONCLUSTERED INDEX idx_CustomerID ON Orders (CustomerID);
```
Re-evaluate the Execution Plan:
- Generate a new execution plan after the index creation to ensure the query is now using an Index Seek.

Best Practices:

Regularly Review Execution Plans:
- Periodically review execution plans for critical queries to identify and address performance issues.
Use Statistics and Indexes:
- Ensure up-to-date statistics and appropriate indexes to aid the query optimizer.
Monitor Query Performance:
- Use Query Store and DMVs to monitor and analyze query performance over time.
Educate Developers:
- Train developers on reading and interpreting execution plans to write more efficient queries.

Resources:

Microsoft Learn: Execution Plans
Microsoft Docs: Estimated and Actual Execution Plans
Microsoft Docs: sys.query_store_query (Transact-SQL)
SQLShack: SQL Server Execution Plans

By following these steps and best practices, you can effectively review and optimize execution plans to improve query performance in SQL Server and Azure SQL Database.

Question 15

Q

Monitor by using Intelligent Insights

Answer

A

Monitor by Using Intelligent Insights

Overview:
Intelligent Insights in Azure SQL Database provides automatic performance monitoring and tuning by leveraging machine learning algorithms to analyze performance data and identify potential issues. It offers actionable recommendations to help optimize database performance.

Key Concepts:

Automatic Performance Analysis:
- Intelligent Insights automatically analyzes performance data to detect potential issues and their root causes.
Actionable Recommendations:
- Provides specific recommendations to address detected issues, such as query optimization, indexing suggestions, and configuration changes.
Integration with Azure Monitor:
- Intelligent Insights integrates with Azure Monitor to provide detailed diagnostics and performance data.

Steps to Monitor by Using Intelligent Insights

Enable Intelligent Insights:Using Azure Portal:
- Navigate to your Azure SQL Database.
- Select “Intelligent Performance” under the “Monitoring” section.
- Enable Intelligent Insights by toggling the feature on.
Access Performance Recommendations:
- View the Intelligent Insights dashboard in the Azure portal.
- Review the performance recommendations and detected issues.
Review Detected Issues and Recommendations:Example:
- Issue: High CPU usage detected during peak hours.
- Recommendation: Optimize the identified query by creating an index on the CustomerID column in the Orders table.
```
sql
  CREATE NONCLUSTERED INDEX idx_CustomerID ON Orders (CustomerID);
 
```
Implement Recommendations:
- Apply the recommended changes to optimize database performance.
- Monitor the impact of these changes using the Intelligent Insights dashboard.
Monitor Ongoing Performance:
- Continuously monitor the performance metrics and recommendations provided by Intelligent Insights.
- Use Azure Monitor to set up alerts based on specific performance thresholds.

Example Scenario

Scenario: Using Intelligent Insights to monitor and optimize the performance of an Azure SQL Database experiencing slow query response times.

Steps:

Enable Intelligent Insights:
- Go to the Azure portal and navigate to your Azure SQL Database.
- Enable Intelligent Insights under the “Intelligent Performance” section.
Access Performance Recommendations:
- Open the Intelligent Insights dashboard.
- Review detected issues, such as high CPU usage and long-running queries.
Review and Implement Recommendations:
- Detected Issue: A query performing a full table scan on the Orders table.
- Recommendation: Create a non-clustered index on the OrderDate column.
```
sql
CREATE NONCLUSTERED INDEX idx_OrderDate ON Orders (OrderDate);
```
Monitor Impact:
- Use the Intelligent Insights dashboard to monitor the impact of the implemented changes.
- Verify improvements in query response times and overall database performance.

Best Practices:

Regularly Review Recommendations:
- Regularly review and implement recommendations from Intelligent Insights to maintain optimal performance.
Automate Monitoring:
- Set up automated alerts in Azure Monitor to stay informed about performance issues detected by Intelligent Insights.
Test Recommendations:
- Test recommendations in a non-production environment before applying them to production to ensure they do not negatively impact other queries or workloads.
Use in Conjunction with Other Tools:
- Combine Intelligent Insights with other performance monitoring tools, such as Query Store and DMVs, for comprehensive performance analysis.

Resources:

By following these steps and best practices, you can effectively use Intelligent Insights to monitor and optimize the performance of your Azure SQL Database, ensuring efficient and reliable database operations.

Question 16

Q

Implement index maintenance tasks

Answer

A

Implement Index Maintenance Tasks

Overview:
Index maintenance is crucial for ensuring optimal database performance in SQL Server and Azure SQL Database. Regular maintenance tasks include reorganizing and rebuilding indexes to reduce fragmentation, updating statistics, and monitoring index usage to keep the database running efficiently.

Key Concepts:

Index Fragmentation:
- Logical Fragmentation: When the logical order of pages does not match the physical order, causing inefficient I/O operations.
- Physical Fragmentation: Occurs when the data pages are not contiguous on disk, leading to increased I/O operations.
Reorganize vs. Rebuild:
- Reorganize Index: A lightweight operation that defragments the leaf level of an index without taking it offline.
- Rebuild Index: A more intensive operation that recreates the index, which can be done online or offline.
Update Statistics:
- Statistics provide the query optimizer with information about data distribution, helping it create efficient execution plans. Regularly updating statistics ensures the optimizer has accurate data.

Steps to Implement Index Maintenance Tasks

Check Index Fragmentation:Using T-SQL:

sql
SELECT 
    object_name(object_id) AS TableName,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC;

Reorganize Indexes:Using T-SQL:

sql
ALTER INDEX ALL ON YourTableName
REORGANIZE;

Rebuild Indexes:Using T-SQL:

sql
ALTER INDEX ALL ON YourTableName
REBUILD;

Online Rebuild:

sql
ALTER INDEX ALL ON YourTableName
REBUILD WITH (ONLINE = ON);

Update Statistics:Using T-SQL:

sql
UPDATE STATISTICS YourTableName;

Update All Statistics:

sql
EXEC sp_updatestats;

Automate Index Maintenance:Using SQL Server Maintenance Plan Wizard:
- Open SSMS, navigate to Management > Maintenance Plans.
- Create a new maintenance plan with tasks to reorganize, rebuild indexes, and update statistics.Using Ola Hallengren’s Maintenance Solution:
- Download and install the solution from Ola Hallengren’s website.
- Schedule the IndexOptimize stored procedure to automate index and statistics maintenance.
```
sql
EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = 'INDEX_REORGANIZE',
    @FragmentationMedium = 'INDEX_REBUILD,INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD',
    @UpdateStatistics = 'ALL';
```

Example Scenario

Scenario: Implementing index maintenance tasks for a sales database to ensure optimal performance.

Steps:

Check Index Fragmentation:

sql
SELECT 
    object_name(object_id) AS TableName,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC;

Reorganize Indexes with Moderate Fragmentation (10-30%):
```
sql
ALTER INDEX ALL ON Sales
REORGANIZE;
```
Rebuild Heavily Fragmented Indexes (>30%):
```
sql
ALTER INDEX ALL ON Sales
REBUILD;
```
Update Statistics:
```
sql
UPDATE STATISTICS Sales;
```

Automate Index Maintenance Using Ola Hallengren’s Solution:

Install the maintenance solution.

Schedule the IndexOptimize procedure.

sql
EXECUTE dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = 'INDEX_REORGANIZE',
  @FragmentationMedium = 'INDEX_REBUILD,INDEX_REORGANIZE',
  @FragmentationHigh = 'INDEX_REBUILD',
  @UpdateStatistics = 'ALL';

Best Practices:

Regular Maintenance:
- Schedule regular index maintenance tasks to minimize fragmentation and ensure up-to-date statistics.
Monitor Fragmentation:
- Continuously monitor index fragmentation levels and adjust maintenance schedules as needed.
Automate Maintenance:
- Use automated tools and scripts to ensure consistent and efficient index maintenance.
Balance Performance:
- Balance the performance impact of maintenance tasks with database usage patterns to avoid excessive downtime.

Resources:

By following these steps and best practices, you can effectively implement index maintenance tasks to ensure optimal performance of your SQL Server and Azure SQL Database environments.

Question 17

Q

Implement statistics maintenance tasks

Answer

A

Implement Statistics Maintenance Tasks

Overview:
Statistics in SQL Server and Azure SQL Database are crucial for the query optimizer to create efficient execution plans. They provide information about the distribution of data in a table or index. Regular maintenance of statistics ensures the optimizer has the most accurate and up-to-date information, leading to better query performance.

Key Concepts:

Types of Statistics:
- Column Statistics: Created on individual columns, usually by the query optimizer.
- Index Statistics: Automatically created and maintained with indexes.
- Multi-Column Statistics: Created manually to cover multiple columns, improving multi-column query performance.
Updating Statistics:
- Regularly updating statistics is essential for maintaining query performance.
- Automatic Updates: SQL Server can automatically update statistics when significant changes occur.
- Manual Updates: Manual updates can be scheduled to ensure timely and comprehensive updates.

Steps to Implement Statistics Maintenance Tasks

Check Current Statistics:Using T-SQL:

sql
-- View statistics for a specific table
DBCC SHOW_STATISTICS ('YourTableName', 'YourIndexName');

Update Statistics:Using T-SQL:

sql
-- Update statistics for a specific table
UPDATE STATISTICS YourTableName;

Update All Statistics:

sql
EXEC sp_updatestats;

Automate Statistics Maintenance:Using SQL Server Maintenance Plan Wizard:
- Open SSMS, navigate to Management > Maintenance Plans.
- Create a new maintenance plan with a task to update statistics.Using Ola Hallengren’s Maintenance Solution:
- Download and install the solution from Ola Hallengren’s website.
- Schedule the IndexOptimize stored procedure to include statistics maintenance.
```
sql
EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @UpdateStatistics = 'ALL';
```

Monitor Statistics Updates:

Use DMVs to monitor the status and last update time of statistics.

Example:

sql
SELECT 
    object_name(s.object_id) AS TableName,
    c.name AS ColumnName,
    s.stats_id,
    s.name AS StatsName,
    s.auto_created,
    s.user_created,
    s.no_recompute,
    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM 
    sys.stats AS s
    JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
    JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE 
    s.object_id = object_id('YourTableName');

Example Scenario

Scenario: Implementing statistics maintenance tasks for a sales database to ensure optimal query performance.

Steps:

Check Current Statistics:

sql
DBCC SHOW_STATISTICS ('Sales', 'IX_Sales_OrderDate');

Update Statistics:

sql
UPDATE STATISTICS Sales;

Update All Statistics:

sql
EXEC sp_updatestats;

Automate Statistics Maintenance Using Ola Hallengren’s Solution:
- Install the maintenance solution.
- Schedule the IndexOptimize procedure.
```
sql
EXECUTE dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @UpdateStatistics = 'ALL';
```

Monitor Statistics Updates:

sql
SELECT 
    object_name(s.object_id) AS TableName,
    c.name AS ColumnName,
    s.stats_id,
    s.name AS StatsName,
    s.auto_created,
    s.user_created,
    s.no_recompute,
    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM 
    sys.stats AS s
    JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
    JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE 
    s.object_id = object_id('Sales');

Best Practices:

Regular Maintenance:
- Schedule regular updates of statistics to ensure the query optimizer has accurate data.
Monitor Automatic Updates:
- Verify that automatic updates are functioning as expected and consider manual updates if necessary.
Automate Maintenance:
- Use tools and scripts to automate statistics maintenance tasks for consistency and efficiency.
Balance Performance:
- Schedule maintenance tasks during low-usage periods to minimize the impact on database performance.

Resources:

By following these steps and best practices, you can effectively implement statistics maintenance tasks to ensure optimal performance of your SQL Server and Azure SQL Database environments.

Question 18

Q

Implement database integrity checks

Answer

A

Implement Database Integrity Checks

Overview:
Database integrity checks are essential for maintaining the health and reliability of SQL Server and Azure SQL Database. These checks ensure that the database structure, indexes, and data are consistent and free from corruption. Regularly performing integrity checks helps in early detection and resolution of potential issues, preventing data loss and ensuring optimal performance.

Key Concepts:

DBCC CHECKDB:
- DBCC CHECKDB is the primary command used to perform comprehensive integrity checks on a database. It checks the logical and physical integrity of all the objects in the database.
- Other Related Commands:
  - DBCC CHECKALLOC: Checks the consistency of disk space allocation structures.
  - DBCC CHECKTABLE: Checks the integrity of a specific table or indexed view.
  - DBCC CHECKCATALOG: Checks the consistency of the catalog metadata.
Automated Integrity Checks:
- Integrity checks can be automated using SQL Server Agent jobs, maintenance plans, or third-party tools to ensure they are performed regularly.

Steps to Implement Database Integrity Checks

Perform Manual Integrity Checks:Using T-SQL:
```sql
– Comprehensive integrity check for the entire database
DBCC CHECKDB (‘YourDatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS;– Check the consistency of disk space allocation structures
DBCC CHECKALLOC (‘YourDatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS;– Check the integrity of a specific table
DBCC CHECKTABLE (‘YourDatabaseName.YourTableName’) WITH NO_INFOMSGS, ALL_ERRORMSGS;– Check the consistency of the catalog metadata
DBCC CHECKCATALOG (‘YourDatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS;
```
Automate Integrity Checks Using Maintenance Plan:Using SQL Server Management Studio (SSMS):
- Open SSMS and connect to your SQL Server instance.
- Navigate to Management > Maintenance Plans.
- Create a new maintenance plan.
- Add a “Check Database Integrity Task” to the plan.
- Configure the task to check the desired databases and schedule it to run at regular intervals (e.g., weekly).
Automate Integrity Checks Using SQL Server Agent:Using T-SQL for a SQL Server Agent Job:
```sql
USE msdb;
GOEXEC dbo.sp_add_job
@job_name = N’Database Integrity Check’,
@enabled = 1,
@description = N’Weekly database integrity check for all user databases’;EXEC dbo.sp_add_jobstep
@job_name = N’Database Integrity Check’,
@step_name = N’CheckDB’,
@subsystem = N’TSQL’,
@command = N’DBCC CHECKDB (N’‘YourDatabaseName’’) WITH NO_INFOMSGS, ALL_ERRORMSGS;’,
@database_name = N’master’;EXEC dbo.sp_add_schedule
@job_name = N’Database Integrity Check’,
@name = N’Weekly Schedule’,
@freq_type = 4, – Weekly
@freq_interval = 1, – Every week
@active_start_time = 010000; – Start at 1:00 AMEXEC dbo.sp_attach_schedule
@job_name = N’Database Integrity Check’,
@schedule_name = N’Weekly Schedule’;EXEC dbo.sp_start_job
@job_name = N’Database Integrity Check’;
```
Monitor and Review Integrity Check Results:
- Regularly monitor the results of the integrity checks.
- Review any reported errors and take appropriate actions to resolve them.

Example Scenario

Scenario: Implementing automated database integrity checks for a sales database to ensure data consistency and reliability.

Steps:

Perform Manual Integrity Check:

sql
DBCC CHECKDB ('SalesDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Automate Integrity Checks Using Maintenance Plan:
- Open SSMS and create a new maintenance plan.
- Add a “Check Database Integrity Task” for the SalesDB database.
- Schedule the task to run every Sunday at 2:00 AM.
Automate Integrity Checks Using SQL Server Agent:
- Create a SQL Server Agent job to run DBCC CHECKDB on SalesDB.
- Schedule the job to run weekly.
Monitor and Review Results:
- Regularly check the job history and logs for any reported integrity issues.
- Address any errors promptly to maintain database health.

Best Practices:

Schedule During Low Usage:
- Schedule integrity checks during low usage periods to minimize the impact on performance.
Automate and Monitor:
- Automate integrity checks to ensure they are performed regularly and monitor the results for any issues.
Respond Promptly to Issues:
- Investigate and resolve any reported errors promptly to prevent data corruption and maintain database reliability.
Combine with Other Maintenance Tasks:
- Integrate integrity checks with other maintenance tasks like backups and index maintenance for comprehensive database health management.

Resources:

By following these steps and best practices, you can effectively implement and manage database integrity checks to ensure the consistency and reliability of your SQL Server and Azure SQL Database environments.

Question 19

Q

Configure database automatic tuning

Answer

A

Configure Database Automatic Tuning

Overview:
Automatic tuning in SQL Server and Azure SQL Database helps maintain optimal performance by automatically applying tuning recommendations based on query performance insights. This feature leverages machine learning to continuously monitor and improve query performance by addressing common performance issues such as missing indexes, poorly performing queries, and suboptimal query plans.

Key Concepts:

Automatic Plan Correction:
- Automatically forces the last known good plan if a query’s performance degrades due to a plan change.
Index Recommendations:
- Identifies and creates missing indexes and drops unused indexes.
Force Last Good Plan:
- Reverts to the last known good execution plan if a regression is detected.

Steps to Configure Database Automatic Tuning

Enable Automatic Tuning in Azure SQL Database:Using Azure Portal:
- Navigate to your Azure SQL Database.
- Select “Automatic tuning” under the “Settings” section.
- Enable the desired options: “Create Indexes”, “Drop Indexes”, “Force Last Good Plan”.Using Azure CLI:

bash
az sql db update \
    --resource-group YourResourceGroup \
    --server YourServerName \
    --name YourDatabaseName \
    --set automaticTuning.settings="{'desiredState':'Auto', 'desiredOptions':{'createIndex':'Auto', 'dropIndex':'Auto', 'forceLastGoodPlan':'Auto'}}"

Using T-SQL:

sql
ALTER DATABASE current
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = ON);

Enable Automatic Tuning in SQL Server:Using SQL Server Management Studio (SSMS):
- Connect to your SQL Server instance.
- Right-click the database, select “Properties”, and navigate to the “Automatic Tuning” page.
- Enable the desired tuning options.Using T-SQL:
```
sql
ALTER DATABASE YourDatabaseName
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
```
Monitor and Review Automatic Tuning Recommendations:Using Azure Portal:
- Go to your Azure SQL Database and select “Automatic tuning” > “Recommendations”.
- Review and apply or reject the recommendations based on your assessment.Using T-SQL:
```
sql
SELECT *
FROM sys.dm_db_tuning_recommendations;
```
Review and Respond to Tuning Actions:
- Monitor the impact of automatic tuning actions and manually review any significant changes.
- Ensure that the automatic actions align with your overall performance goals and do not negatively impact other queries.

Example Scenario

Scenario: Configuring automatic tuning for an Azure SQL Database to optimize query performance.

Steps:

Enable Automatic Tuning:
- Using Azure Portal:
  - Navigate to the Azure SQL Database.
  - Enable “Create Indexes”, “Drop Indexes”, and “Force Last Good Plan”.
Monitor Automatic Tuning Recommendations:
- Go to “Automatic tuning” > “Recommendations” to view active recommendations.
- Apply recommendations automatically or review and manually apply them as needed.
Review Tuning Actions:
- Use the following query to review tuning actions:
```
sql
SELECT *
FROM sys.dm_db_tuning_recommendations;
```

Best Practices:

Monitor Regularly:
- Regularly monitor automatic tuning recommendations and actions to ensure they are beneficial and do not introduce new issues.
Combine with Manual Tuning:
- Use automatic tuning in conjunction with manual performance tuning for comprehensive database optimization.
Test Changes:
- In a non-production environment, test the impact of automatic tuning recommendations before applying them to production.
Stay Updated:
- Keep abreast of updates and enhancements to the automatic tuning feature to leverage the latest improvements.

Resources:

By following these steps and best practices, you can effectively configure and manage automatic tuning to optimize the performance of your SQL Server and Azure SQL Database environments.

Question 20

Q

Configure server settings for performance

Answer

A

Configure Server Settings for Performance

Overview:
Optimizing SQL Server settings for performance involves configuring server-level options that enhance query execution, resource management, and overall database performance. Properly tuning these settings can lead to significant improvements in response times and resource utilization.

Key Server Settings to Configure for Performance

Max Degree of Parallelism (MAXDOP):
- Controls the number of processors used for parallel query execution.
- Recommended Configuration:
  - Start with a value of 4 for OLTP workloads and adjust based on testing.
  - For OLAP workloads, a higher value may be suitable.
- Using T-SQL:
```
sql
EXEC sys.sp_configure 'max degree of parallelism', 4;
RECONFIGURE;
```
Cost Threshold for Parallelism:
- Determines the threshold at which SQL Server creates parallel execution plans.
- Recommended Configuration:
  - Default is 5; increase to a value between 20 and 50 based on workload testing.
- Using T-SQL:
```
sql
EXEC sys.sp_configure 'cost threshold for parallelism', 25;
RECONFIGURE;
```
Optimize for Ad Hoc Workloads:
- Reduces memory consumption by storing a small compiled plan stub for single-use ad hoc queries.
- Recommended Configuration:
  - Enable this setting to improve memory management.
- Using T-SQL:
```
sql
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
```
Max Server Memory:
- Sets the maximum amount of memory SQL Server can use.
- Recommended Configuration:
  - Set to 75-80% of total available memory, leaving enough for the OS and other processes.
- Using T-SQL:
```
sql
EXEC sys.sp_configure 'max server memory (MB)', 32768;
RECONFIGURE;
```
Min Server Memory:
- Sets the minimum amount of memory SQL Server should use.
- Recommended Configuration:
  - Set to ensure a baseline level of memory for SQL Server to prevent memory pressure.
- Using T-SQL:
```
sql
EXEC sys.sp_configure 'min server memory (MB)', 8192;
RECONFIGURE;
```
TempDB Configuration:
- Number of Data Files:
  - Configure one TempDB data file per CPU core, up to 8 files. Adjust as needed based on contention.
- Initial Size and Autogrowth:
  - Set initial size to prevent frequent autogrowth and configure autogrowth to a fixed size.
- Example T-SQL:
```
sql
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 4096MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', FILEGROWTH = 512MB);
```
File Placement:
- Distribute database files across multiple physical drives to reduce I/O contention.
- Place TempDB on a separate, fast storage to enhance performance.

Example Scenario

Scenario: Configuring SQL Server settings for a production database server to enhance performance.

Steps:

Set Max Degree of Parallelism:

sql
EXEC sys.sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

Adjust Cost Threshold for Parallelism:

sql
EXEC sys.sp_configure 'cost threshold for parallelism', 25;
RECONFIGURE;

Enable Optimize for Ad Hoc Workloads:

sql
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Configure Max and Min Server Memory:
```sql
EXEC sys.sp_configure ‘max server memory (MB)’, 32768;
RECONFIGURE;EXEC sys.sp_configure ‘min server memory (MB)’, 8192;
RECONFIGURE;
```

Optimize TempDB:

Set initial size and autogrowth for TempDB.

sql
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 4096MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', FILEGROWTH = 512MB);

Best Practices:

Regular Monitoring:
- Continuously monitor performance metrics to identify and address any issues promptly.
Performance Testing:
- Test configuration changes in a staging environment before applying them to production.
Documentation:
- Document all configuration changes for future reference and troubleshooting.
Stay Informed:
- Keep up with SQL Server updates and best practices to leverage new features and improvements.

Resources:

Microsoft Learn: Server Configuration Options (SQL Server)
Microsoft Docs: Optimize tempdb Performance
Microsoft Learn: Max Degree of Parallelism Configuration
SQLShack: Best practices for configuring SQL Server memory settings
Redgate: SQL Server configuration best practices

By following these steps and best practices, you can effectively configure SQL Server settings to optimize performance and ensure efficient resource utilization.

Question 21

Q

Configure Resource Governor for performance

Answer

A

Configure Resource Governor for Performance

Overview:
Resource Governor in SQL Server provides a mechanism to manage SQL Server workload and system resource consumption. It allows you to classify and limit the amount of CPU, memory, and I/O resources that incoming requests can use, thus ensuring predictable performance and preventing resource-intensive workloads from overwhelming the server.

Key Concepts:

Resource Pools:
- Define the maximum and minimum amount of system resources (CPU, memory, and I/O) that a group of workloads can use.
Workload Groups:
- Group similar workloads together and apply the resource limits defined in the resource pools.
Classification Function:
- A user-defined function that assigns incoming requests to workload groups based on their characteristics (e.g., user, application, database).

Steps to Configure Resource Governor

Create Resource Pools:Using T-SQL:
```sql
– Create a resource pool for reporting workloads
CREATE RESOURCE POOL ReportingPool
WITH (
MIN_CPU_PERCENT = 10,
MAX_CPU_PERCENT = 50,
MIN_MEMORY_PERCENT = 10,
MAX_MEMORY_PERCENT = 50
);
GO– Create a resource pool for OLTP workloads
CREATE RESOURCE POOL OLTPPool
WITH (
MIN_CPU_PERCENT = 20,
MAX_CPU_PERCENT = 70,
MIN_MEMORY_PERCENT = 20,
MAX_MEMORY_PERCENT = 70
);
GO
```
Create Workload Groups:Using T-SQL:
```sql
– Create a workload group for reporting workloads
CREATE WORKLOAD GROUP ReportingGroup
USING ReportingPool;
GO– Create a workload group for OLTP workloads
CREATE WORKLOAD GROUP OLTPGroup
USING OLTPPool;
GO
```

Create a Classification Function:Using T-SQL:
```sql
– Create a function to classify requests
CREATE FUNCTION dbo.ResourceGovernorClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group SYSNAME;

-- Classify requests based on application name
IF (APP_NAME() LIKE '%Report%')
    SET @workload_group = 'ReportingGroup';
ELSE IF (APP_NAME() LIKE '%OLTP%')
    SET @workload_group = 'OLTPGroup';
ELSE
    SET @workload_group = 'default';

RETURN @workload_group; END; GO ```

Apply the Classification Function:Using T-SQL:
```sql
– Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);
GO– Enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
```
Monitor and Adjust:
- Use Dynamic Management Views (DMVs) to monitor Resource Governor and adjust resource limits as needed.
- Example:
```
sql
-- View resource pool statistics
SELECT * FROM sys.dm_resource_governor_resource_pools;
```

Example Scenario

Scenario: Configuring Resource Governor to manage reporting and OLTP workloads on a SQL Server instance.

Steps:

Create Resource Pools:
```sql
CREATE RESOURCE POOL ReportingPool
WITH (
MIN_CPU_PERCENT = 10,
MAX_CPU_PERCENT = 50,
MIN_MEMORY_PERCENT = 10,
MAX_MEMORY_PERCENT = 50
);
GOCREATE RESOURCE POOL OLTPPool
WITH (
MIN_CPU_PERCENT = 20,
MAX_CPU_PERCENT = 70,
MIN_MEMORY_PERCENT = 20,
MAX_MEMORY_PERCENT = 70
);
GO
```
Create Workload Groups:
```sql
CREATE WORKLOAD GROUP ReportingGroup
USING ReportingPool;
GOCREATE WORKLOAD GROUP OLTPGroup
USING OLTPPool;
GO
```
Create and Apply Classification Function:
```sql
CREATE FUNCTION dbo.ResourceGovernorClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group SYSNAME;
```
IF (APP_NAME() LIKE '%Report%')
    SET @workload_group = 'ReportingGroup';
ELSE IF (APP_NAME() LIKE '%OLTP%')
    SET @workload_group = 'OLTPGroup';
ELSE
    SET @workload_group = 'default';

RETURN @workload_group; END; GO
```
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);
GOALTER RESOURCE GOVERNOR RECONFIGURE;
GO
```

Best Practices:

Regular Monitoring:
- Regularly monitor resource usage and adjust resource pool settings based on workload patterns.
Testing:
- Test Resource Governor settings in a non-production environment to determine the optimal configuration before applying to production.
Balanced Resource Allocation:
- Ensure that resource allocations do not starve critical workloads of necessary resources.
Document Changes:
- Document all Resource Governor configurations and changes for future reference and troubleshooting.

Resources:

By following these steps and best practices, you can effectively configure Resource Governor to manage and optimize the performance of SQL Server workloads, ensuring efficient resource utilization and predictable performance.

Question 22

Q

Implement database-scoped configuration

Answer

A

Implement Database-Scoped Configuration

Overview:
Database-scoped configuration in SQL Server allows for setting certain configuration options at the database level, rather than the server level. This feature provides more granular control over database behavior and performance, enabling better tuning for specific workloads.

Key Concepts:

Database Scoped Configurations:
- Configurations that can be set at the database level to customize the behavior and performance of individual databases.
Common Configurations:
- LEGACY_CARDINALITY_ESTIMATION: Controls whether the database uses the legacy cardinality estimator.
- QUERY_OPTIMIZER_HOTFIXES: Enables query optimizer hotfixes that are delivered in Cumulative Updates and Service Packs.
- MAXDOP (Maximum Degree of Parallelism): Limits the number of processors used for parallel plan execution.
- PARAMETER_SNIFFING: Controls the use of parameter sniffing during query compilation.
- ACCELERATED_PLAN_FORCING: Improves plan forcing performance by reducing compilation time.

Steps to Implement Database-Scoped Configuration

Enable Database-Scoped Configurations:Using T-SQL:
```sql
– Enable legacy cardinality estimation
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO– Enable query optimizer hotfixes
ALTER DATABASE SCOPED CONFIGURATION
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO– Set MAXDOP to 4
ALTER DATABASE SCOPED CONFIGURATION
SET MAXDOP = 4;
GO– Disable parameter sniffing
ALTER DATABASE SCOPED CONFIGURATION
SET PARAMETER_SNIFFING = OFF;
GO– Enable accelerated plan forcing
ALTER DATABASE SCOPED CONFIGURATION
SET ACCELERATED_PLAN_FORCING = ON;
GO
```

View Current Database-Scoped Configurations:Using T-SQL:

sql
-- View current database-scoped configurations
SELECT * 
FROM sys.database_scoped_configurations;

Monitor and Adjust Configurations:
- Regularly monitor the performance impact of database-scoped configurations.
- Adjust settings based on workload characteristics and performance metrics.

Example Scenario

Scenario: Configuring database-scoped settings for a reporting database to optimize query performance.

Steps:

Enable Legacy Cardinality Estimation:

sql
ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

Enable Query Optimizer Hotfixes:

sql
ALTER DATABASE SCOPED CONFIGURATION 
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

Set MAXDOP:

sql
ALTER DATABASE SCOPED CONFIGURATION 
SET MAXDOP = 4;
GO

Disable Parameter Sniffing:

sql
ALTER DATABASE SCOPED CONFIGURATION 
SET PARAMETER_SNIFFING = OFF;
GO

Enable Accelerated Plan Forcing:

sql
ALTER DATABASE SCOPED CONFIGURATION 
SET ACCELERATED_PLAN_FORCING = ON;
GO

Best Practices:

Granular Control:
- Use database-scoped configurations to fine-tune the behavior of individual databases based on specific workload requirements.
Monitor Performance:
- Continuously monitor the performance impact of database-scoped configurations using tools like Query Store, Dynamic Management Views (DMVs), and performance counters.
Adjust Based on Workload:
- Be prepared to adjust configurations as workloads change or as new performance issues are identified.
Document Changes:
- Document all configuration changes for future reference and troubleshooting.

Resources:

By following these steps and best practices, you can effectively implement and manage database-scoped configurations to optimize the performance and behavior of individual databases in SQL Server and Azure SQL Database environments.

Question 23

Q

Configure compute and storage resources for scaling

Answer

A

Configure Compute and Storage Resources for Scaling

Overview:
Scaling compute and storage resources in SQL Server and Azure SQL Database allows databases to handle increased workloads efficiently. Proper configuration ensures that performance remains optimal as demand changes. This involves adjusting the number of compute resources, configuring storage options, and using elastic pools or autoscaling features.

Key Concepts:

Compute Scaling:
- Vertical Scaling: Increasing the size of the instance (more CPUs, more memory).
- Horizontal Scaling: Adding more instances or nodes to distribute the load.
Storage Scaling:
- Vertical Scaling: Increasing the size of storage.
- Horizontal Scaling: Adding more storage disks or distributing data across multiple storage systems.
Elastic Pools (Azure SQL Database):
- Allows multiple databases to share resources within a pool, optimizing resource utilization and cost.
Autoscaling (Azure SQL Database):
- Automatically adjusts compute resources based on workload demand.

Steps to Configure Compute and Storage Resources for Scaling

Vertical Scaling (Compute):SQL Server:
- Add more CPUs or memory to the server hardware.
- Adjust SQL Server configuration to utilize additional resources.

sql
-- Example: Increase max server memory
EXEC sys.sp_configure 'max server memory (MB)', 65536;
RECONFIGURE;

Azure SQL Database:
- Change the service tier and compute size.

bash
az sql db update --resource-group YourResourceGroup --server YourServerName --name YourDatabaseName --service-objective S3

Horizontal Scaling (Compute):SQL Server:
- Implement a scale-out architecture using Always On Availability Groups.
- Use SQL Server replication or sharding.Azure SQL Database:
- Use elastic pools to manage and scale multiple databases.
```
bash
az sql elastic-pool create --resource-group YourResourceGroup --server YourServerName --name YourElasticPool --dtu 50 --database-dtu-max 20
```
Vertical Scaling (Storage):SQL Server:
- Increase the size of storage disks.
- Configure SQL Server to use the additional storage.Azure SQL Database:
- Increase the database size limit.
```
bash
az sql db update --resource-group YourResourceGroup --server YourServerName --name YourDatabaseName --max-size 250GB
```
Horizontal Scaling (Storage):SQL Server:
- Implement partitioning to distribute data across multiple disks.
- Use SAN or NAS to add more storage devices.Azure SQL Database:
- Use sharding or partitioning to distribute data across multiple databases.
- Configure geo-replication for high availability and disaster recovery.

Autoscaling (Azure SQL Database):

Configure autoscaling rules to adjust resources based on performance metrics.

bash
az monitor autoscale create --resource-group YourResourceGroup --name YourAutoscaleSetting --target-resource /subscriptions/{subscription-id}/resourceGroups/{resource-group}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name} --min-count 1 --max-count 5 --count 2

Example Scenario

Scenario: Scaling an Azure SQL Database to handle increased workload demands.

Steps:

Vertical Scaling:

Increase the compute size of the database to the next service tier.

bash
az sql db update --resource-group YourResourceGroup --server YourServerName --name YourDatabaseName --service-objective P2

Horizontal Scaling:

Implement an elastic pool to manage resource utilization across multiple databases.

bash
az sql elastic-pool create --resource-group YourResourceGroup --server YourServerName --name YourElasticPool --dtu 100 --database-dtu-max 20

Vertical Scaling (Storage):

Increase the maximum size of the database.

bash
az sql db update --resource-group YourResourceGroup --server YourServerName --name YourDatabaseName --max-size 500GB

Autoscaling:

Configure autoscaling to handle peak workloads automatically.

bash
az monitor autoscale create --resource-group YourResourceGroup --name YourAutoscaleSetting --target-resource /subscriptions/{subscription-id}/resourceGroups/{resource-group}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name} --min-count 1 --max-count 5 --count 3

Best Practices:

Monitor Resource Usage:
- Continuously monitor resource usage and performance metrics to identify scaling needs.
Test Scaling Configurations:
- Test scaling configurations in a non-production environment to ensure they meet performance requirements.
Balance Cost and Performance:
- Optimize the balance between cost and performance by selecting appropriate scaling options.
Automate Scaling:
- Use automation tools to dynamically adjust resources based on real-time demand.

Resources:

By following these steps and best practices, you can effectively configure compute and storage resources to scale your SQL Server and Azure SQL Database environments, ensuring optimal performance and resource utilization.

Question 24

Q

Configure intelligent query processing (IQP)

Answer

A

Configure Intelligent Query Processing (IQP)

Overview:
Intelligent Query Processing (IQP) in SQL Server and Azure SQL Database encompasses a set of features designed to improve the performance of queries without requiring changes to the application code. IQP enhances the SQL Server engine’s ability to process and optimize queries, especially those involving complex and large datasets.

Key Features of IQP:

Batch Mode on Rowstore:
- Enables batch mode execution on rowstore tables, improving performance for analytical queries.
Batch Mode Memory Grant Feedback:
- Adjusts memory grants for queries in subsequent executions based on feedback from previous executions to prevent over- or under-allocation of memory.
Table Variable Deferred Compilation:
- Defers the compilation of queries involving table variables until actual runtime to provide more accurate cardinality estimates.
Scalar UDF Inlining:
- Converts scalar user-defined functions into relational expressions to improve performance by eliminating the function call overhead.
Approximate Count Distinct:
- Provides an approximate count distinct aggregation, significantly improving performance for large datasets.

Steps to Enable and Configure IQP

Ensure Compatibility Level:
- Set the database compatibility level to 150 or higher to enable IQP features.
```
sql
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 150;
```

Enable Specific IQP Features:Batch Mode on Rowstore:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET BATCH_MODE_ON_ROWSTORE = ON;

Batch Mode Memory Grant Feedback:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Table Variable Deferred Compilation:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET TABLE_VARIABLE_DEFERRED_COMPILATION = ON;

Scalar UDF Inlining:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET SCALAR_UDF_INLINING = ON;

Approximate Count Distinct:

sql
-- No specific configuration needed, use the APPROX_COUNT_DISTINCT function in queries
SELECT APPROX_COUNT_DISTINCT(column_name)
FROM YourTableName;

Monitor and Evaluate:

Use Dynamic Management Views (DMVs) and Query Store to monitor the performance impact of IQP features.

Example:

sql
-- Monitor query performance improvements
SELECT 
    query_id, 
    plan_id, 
    runtime_stats_interval_id, 
    total_duration
FROM sys.query_store_runtime_stats;

Example Scenario

Scenario: Enabling and configuring IQP features for a sales database to optimize query performance.

Steps:

Set Compatibility Level:

sql
ALTER DATABASE SalesDB
SET COMPATIBILITY_LEVEL = 150;

Enable Batch Mode on Rowstore:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET BATCH_MODE_ON_ROWSTORE = ON;

Enable Batch Mode Memory Grant Feedback:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Enable Table Variable Deferred Compilation:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET TABLE_VARIABLE_DEFERRED_COMPILATION = ON;

Enable Scalar UDF Inlining:

sql
ALTER DATABASE SCOPED CONFIGURATION
SET SCALAR_UDF_INLINING = ON;

Use Approximate Count Distinct:

sql
SELECT APPROX_COUNT_DISTINCT(CustomerID)
FROM Sales;

Best Practices:

Monitor Performance:
- Continuously monitor query performance using DMVs and Query Store to evaluate the impact of IQP features.
Test Changes:
- Test the impact of enabling IQP features in a non-production environment before applying them to production.
Iterative Enablement:
- Enable and evaluate IQP features incrementally to identify which features provide the most benefit for your workload.
Documentation:
- Document all changes to database configurations for future reference and troubleshooting.

Resources:

By following these steps and best practices, you can effectively configure and utilize Intelligent Query Processing features to enhance the performance of your SQL Server and Azure SQL Database environments.

Brainscape's Knowledge GenomeTM

Monitor, configure, and optimize database resources (20–25%) Flashcards

Brainscape's Knowledge Genome^TM