AEU13 - Optimizing Queries in SQL Server (Maria Barnes)

In this session for Access Europe, Maria Barnes (MVP) discussed how to optimize queries in SQL Server using the various tools available:
- How to decide which SQL queries need to be optimized?
- How to Use the Activity Monitor in SSMS
- An Introduction to the Performance Dashboard for SSMS 17.2 and above
- Query Simplification Basics
- Displaying the Estimated Execution Plan in SSMS
- A brief mention of the Query Store and what this is

TIMES:
0:00 Welcome and Intro by Colin Riddington
0:34 Maria Barnes
0:48 Why are we talking about SQL Server in an Access User Group!
1:40 Background & experience with SQL Server
3:41 Outline of 6 topics to be covered during this presentation
4:33 SQL Server Management Studio (SSMS)
6:17 Which SQL queries need to be optimized?
7:32 Possible causes of consistently slow queries - hardware constraints / suboptimal query structure
8:03 Causes of occasionally slow queries - data skew / blocking or hardware contention
11:24 SARGability - use indexes to speed up queries
15:23 How to Use the Activity Monitor in SSMS - Server based tool
18:06 Activity Monitor Overview Pane
19:00 Activity Monitor DEMO
19:57 Introduction to the Performance Dashboard
20:42 Performance Dashboard DEMO
22:00 Missing Indexes
23:21 Query Simplification Basics
27:44 Use bulk insert/update instead of looping recordsets
29:43 Estimated/Actual Execution Plans
31:51 Query Execution Plans DEMO - diagram/XML/ text formats available
34:42 Execution Plans - Important Terms/Objects
35:27 Execution Plans - Things to watch for - missing indexes / table scans / key lookups
37:30 Improve Execution Plan DEMO
41:09 The Query Store - collects plans for DML statements. Available with SQL Server 2016 or later
42:08 SQL Query Optimization - Turn on Query Store
43:23 Resources Links
43:45 Follow up & questions
45:42 SQL Sentry tool mentioned by Klaus/Jack
48:56 Next month - Duane Hookom - Creating Calendar Format Reports
51:38 Thanks for watching. See you next month

LINKS:
PDF of Maria's presentation: https://isladogs.co.uk/aeu-13/

SQL Server Monitor & Tune for Performance: https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitor-and-tune-for-performance?view=sql-server-ver16

Optimize query performance in Azure SQL: https://learn.microsoft.com/en-us/training/paths/optimize-query-performance-sql-server/

SQL Adventure Works Sample Database: https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

SQL Server execution plans: https://blog.quest.com/sql-server-execution-plan-what-is-it-and-how-does-it-help-with-performance-problems/

Isolate problem areas in poorly performing SQL queries: https://github.com/MicrosoftLearning/dp-300-database-administrator/blob/master/Instructions/Labs/10-isolate-problem-areas-poor-performing-queries.md

SQL Server Query Store Overview: https://www.sqlshack.com/sql-server-query-store-overview/

SQL Sentry Tool: https://www.sentryone.com/products/sentryone-platform/sql-sentry/sql-server-performance-monitoring

Видео AEU13 - Optimizing Queries in SQL Server (Maria Barnes) канала AccessUserGroups.org