Benutzer:MovGP0/SQL/Query Performance Metrics

Programmierung

T-SQL / SQL Server

T-SQL Fundamantals Select • Programming • Pitfalls • Query from Multiple Tables • Groups and Summaries • Advanced Select • Aggregation and Windowing • Insert, Update, Delete • Strings • Date and Time • Numbers • Transactions, Locking, Blocking, Deadlock • Tables • Views • Large Tables and Databases • Indexes • Stored Procedures • User-Defined Functions and Types • Triggers • Error Handling • Query Performance and Tuning • Hints • Index Tuning and Statistics • XML • Files, Filegroups, Integrity • Backup • Recovery • Principals and Users • Securables, Permissions, Auditing • Objects and Dependencies SQL Server Performance Tuning Memory Performance Analysis • Disk Performance Analysis • CPU Performance Analysis • Baseline Creation • Query Performance Metrics • Query Performance Analysis • Index Architecture and Behavior • Index Analysis • Database Engine Tuning Advisor • Key Lookups • Statistics, Data Distributuon, and Cardinality • Index Fragmentation • Execution Plan Generation • Execution Plan Cache Behavior • Parameter Sniffing • Query Recompilation • Query Design Analysis • Reduce Query Resource Use • Blocking and Blocked Processes • Causes and Solutions for Deadlocks • Row-By-Row Processing • Memory-Optimized OLTP Tables and Procedures • Database Performance Testing • Database Workload Optimization • SQL Server Optimization Checklist Service Broker Message • Contract • Queue • Service • .NET Integration • Route • Security   Datatypes • Unit Testing • Dependency Injection • Partitioning •

Query Performance Metrics

Extended Events

Predefined Templates

Predefined Templates

Query Batch Sampling	queries and procedure calls for 20% of all active sessions
Query Batch Tracking	all queries and procedures on the server
Query Detail Sampling	set of events from queries and procedure calls for 20% of all active sessions
Query Detail Tracking	set of events from all queries and procedures on the server
Query Wait Statistic	wait statistics of queries and procedure calls for 20% of all active sessions

What to capture

• CPU utilization
• Memory usage
• I/O activity
• Execution time
• Query frequency
• Errors and warnings

Events

Important Events

Category	Event	Description
Execution	rpc_completed	remote procedure call completion event (OLDBC)
Execution	sp_statement_completed	remote procedure call statement completion event
Execution	sql_batch_completed	batch of T-SQL statements completed
Execution	sql_statement_completed	single T-SQL statement completed
Session	loginlogout	user connects and disconnects
Session	existing_connection	current number of user connections
Errors	attention	termination of request (caused by query cancellation, connection loss, timeout, etc.)
Errors	error_reported
Errors	execution_warning	warnings during execution of a query or stored procedure
Errors	hash_warning	error in an hashing operation
Warnings	missing_column_statistics	statistics required by the query optimizer is missing
Warnings	missing_join_predicate	query is executed without joining predicate between them
Warnings	sort_warnings	sort operation that does not fit into memory
lock	lock_deadlock	process becomes deadlock victim
lock	lock_deadlock_chain	chain of queries creating the deadlock
lock	lock_timeout	lock has exceeded the timeout parameter
execution	sql_statement_recompile	execution plan had to be recompiled because it did not exist
execution	rpc_starting	starting of a stored procedure
execution	query_post_compilation_showplan	execution plan after compilation
execution	query_post_execution_showplan	execution plan and query statistics (capturing is resource intensive)
transaction	sql_transaction	informations about transactions (including time of start, stop, and rollback)

Global Fields

Important Fields

• client_app_name
• database_id
• plan_handle
• query hash
• query_plan_hash
• session_id
• transaction_id

Creating an Session

SQL Server Manager

• Extended Events
• right click on Sessions
• New Session/New Session Wizard

T-SQL

Create Session

CREATE EVENT SESSION

Activate session

ALTER EVENT

Get active sessions

SELECT dxs.name, dxs.create_date
FROM sys.dm_xe_sessions AS dxs

Stop Session

ALTER EVENT SESSION [Query Performance Metric]
ON SERVER
STATE = STOP;

Recomendations

• Set max file size properly (default is 1GB; increase as needed; typically 50 GB)
• Avoid debug events (halting SQL Server on error for debugger)
• avoid use of No_Event_Loss (performance drain)

More Metrics

SELECT TOP(100) *
FROM sys.dm_exec_query_stats AS res
OUTER APPLY sys.dm_exec_sql_text(res.sql_handle) -- resolve handle
OUTER APPLY sys.dm_exec_query_plan(res.plan_handle) -- resolve query plan