Dr DMV for SQL Server 2019
Dynamic Management Views and functions allow you to easily see exactly what is happening inside your SQL Server instances and databases with a high level of detail. You can discover your top wait types, most CPU intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples.
This video presents, demonstrates and explains a complete set of diagnostic DMV queries that you can easily use to detect and diagnose configuration and performance issues in your SQL Server instances and databases.
This video goes into exhaustive detail on how to interpret the results of each of the diagnostic queries, including relevant background information on how to properly configure your hardware, storage subsystem, operating system, SQL Server instance and databases in order to avoid performance and scalability issues.
The information in this video is also relevant to older versions of SQL Server, and I have separate versions of these queries for every major version of SQL Server from SQL Server 2005 through SQL Server 2019.
You can download the queries used in this video here:
https://glennsqlperformance.com/resou...
⏱️TIMESTAMPS⏱️
00:00 Intro
01:45 SQL and OS Version instance info
03:04 Core Counts from SQL Error Log
03:52 Server Properties
04:43 Instance Configuration Values
05:54 Enabled Trace Flags
06:49 SQL Process Memory LPIM check
07:20 SQL Services Info
08:18 Last Backup By DB
09:06 SQL Server Agent Jobs
09:48 SQL Server Agent Alerts
10:52 Host Info Edition
11:26 SQL NUMA Info
12:22 OS memory amounts and state
13:13 Cluster Node Properties
13:29 AlwaysOn AG Cluster
13:40 AlwaysOn AG Status
13:51 Hardware Info
14:52 System Manufacturer
15:24 BIOS Date
16:03 Processor Name and Description
16:40 Location and SQL Memory Dump Info
17:32 Suspect Pages Corruption
18:09 Most Recent Error Log Entries
18:40 Number of TempDB Data Files
19:11 All DB Filenames and Paths
20:04 Drives from OS
20:29 Volume Info for all LUNS
21:02 Drive Level Latency Info
21:42 IO Latency for each DB file
22:31 IO Warnings taking longer than 15s
23:29 RG Resource Pools - Enterprise
24:48 Database Properties
25:56 Missing Indexes All DBs
30:10 VLF Counts
31:26 CPU Usage by DB
32:13 IO Usage by DB
33:02 Memory Usage by DB
33:35 TempDB RCSI Version Store Space Usage by DB
33:57 Top Waits
35:35 Connection Counts by IP
36:05 Average Task Counts
37:17 Detect Blocking
37:45 CPU Utilization History
38:38 Top Worker CPU Time Queries
39:06 PLE by NUMA Node
39:54 Memory Grants Pending when Low PLE
40:24 Memory Clerk Usage
41:40 Ad hoc Queries bloating plan cache
42:28 Memory Top Logical Reads Queries
43:07 Top Avg Elapsed Time Queries - Longest
43:36 UDF Statistics by DB
45:19 DB Flat File Sizes and Space
45:56 Log Space Usage
46:45 Last VLF Status
47:11 Database-scoped Configurations
48:45 IO Statistics by File (OLTP/OLAP-DW)
49:35 Most Query Execution Counts
50:23 Top Cached SPs Execution Count for DEVs
52:05 Top Cached SPs Average Elapsed Time
52:38 Top Cached SPs Total CPU Worker Time
53:59 Top Cached SPs Total Memory Logical Reads
54:34 Top Cached SPs Total Disk IO Physical Reads
54:55 Top Cached SPs Total Memory/Disk IO Logical Writes
55:39 Cached SPs Missing Index Execution Count
56:32 Top Cached SPs IO Usage Statements
57:10 Bad NonClustered Indexes
58:47 Missing Indexes
1:00:26 SP Missing Index Warnings
1:02:32 Table Indexes Memory Usage
1:04:59 Table Sizes RowCounts for compression
1:05:25 Table Properties
1:05:59 Last Statistics Update on All Indexes
1:06:28 Most Frequently Modified Indexes and Statistics
1:07:09 Index Fragmentation
1:07:52 Overall Index Usage - Reads
1:08:46 Overall Index Usage - Writes careful with compression
1:09:09 Lock Waits
1:10:01 UDF Statistics
1:10:39 Inlineable UDFs
1:10:55 Input Buffer for non-system sessions
1:11:30 Resumable Index Rebuild
1:12:36 Automatic Tuning Options
1:13:43 Recent Full/Log Backups Info
1:15:59 Outro
1:16:18 Chorizo!
🔴 Please subscribe for more content like this!
SOCIAL
===============================
📝 - BLOG: https://glennsqlperformance.com/
🐦 - TWITTER: / glennalanberry
👥 - FACEBOOK: / glenn.berry.92
LinkedIn: / glberry
Host, Video: Glenn Berry
Watch video Dr DMV for SQL Server 2019 online, duration hours minute second in high quality that is uploaded to the channel Glenn Berry 01 May 2021. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 5,583 times and liked it 128 visitors.
