SQL Server Scripts
open-menu closeme
Home
All SQLServer70 Scripts
Scripts & Commands
Links icon
SQL Server Documentation SQL Server Mgmnt Studio
About
Contact Us
Privacy Statement
rss
  • Inventory Open Cursors per Session with sys.dm_exec_cursors

    calendar Nov 3, 2026 / Nov 3, 2026 · 9 min read · sys.dm_exec_cursors sys.dm_exec_sessions sys.dm_exec_sql_text sys.dm_exec_requests sys.dm_exec_xml_handles sql server cursors dmv monitoring tsql tempdb  ·
    Share on: twitter facebook linkedin copy

    If code declares a GLOBAL cursor and never deallocates it, the cursor stays allocated until the connection closes. While it stays open it can hold tempdb space, row locks or both. The collector query below snapshots every cursor on the instance with its properties, state, I/O and idle time, and a short follow-up query …


    Read More
  • Track Azure SQL Database Load with sys.dm_db_resource_stats

    calendar Nov 1, 2026 / Nov 1, 2026 · 9 min read · sys.dm_db_resource_stats sys.resource_stats sys.dm_elastic_pool_resource_stats sys.server_resource_stats azure sql database dmv dtu vcore elastic pool performance monitoring  ·
    Share on: twitter facebook linkedin copy

    When an Azure SQL database slows down, the first thing to find out is whether it has hit the limits of its service tier. sys.dm_db_resource_stats answers that with one row for every 15 seconds, even when the database is idle, and keeps about an hour of them. The three queries below return the raw rows, a DTU-style peak …


    Read More
  • Rank Index Contention with sys.dm_db_index_operational_stats

    calendar Oct 22, 2026 / Oct 22, 2026 · 7 min read · sql server sys.dm_db_index_operational_stats sys.indexes sys.dm_os_wait_stats sys.dm_db_index_usage_stats lock contention row locks blocking indexes performance tuning dmv tsql  ·
    Share on: twitter facebook linkedin copy

    Once sys.dm_os_wait_stats points to locking, the next question is which index the waits belong to. The script below reads sys.dm_db_index_operational_stats for the current database and ranks each user-table index by its number of row-lock waits. Next to each count it returns the share of row-lock requests that had to …


    Read More
  • Measure Internal Latch Contention with sys.dm_os_latch_stats

    calendar Oct 21, 2026 / Oct 21, 2026 · 8 min read · sql server sys.dm_os_latch_stats sys.dm_os_wait_stats sys.dm_os_sys_info sys.dm_pdw_nodes_os_latch_stats latch contention latches wait statistics performance tuning dmv  ·
    Share on: twitter facebook linkedin copy

    When LATCH_* waits show up near the top of sys.dm_os_wait_stats, you know threads are waiting on non-buffer latches. You don't know which engine structure they're waiting for. sys.dm_os_latch_stats splits those waits by latch class. The script below ranks the non-buffer classes that make up the first 95% of latch wait …


    Read More
  • Age Open Transactions with sys.dm_tran_active_transactions

    calendar Aug 2, 2026 / Oct 16, 2026 · 10 min read · sql server sys.dm_tran_active_transactions sys.dm_tran_session_transactions sys.dm_exec_sessions long running transactions transaction log blocking monitoring dmv scripts  ·
    Share on: twitter facebook linkedin copy

    A transaction that never commits keeps every log record written after it pinned in place, no matter how much work piles up behind it. sys.dm_tran_active_transactions lists every transaction currently open on the instance, but on its own it only shows a transaction ID and a start time — pairing it with …


    Read More
  • Rank Top Memory Consumers with sys.dm_os_memory_clerks

    calendar Jul 20, 2026 / Oct 16, 2026 · 8 min read · sql server sys.dm_os_memory_clerks memory clerks memory pressure plan cache xtp query store dmv performance monitoring scripts  ·
    Share on: twitter facebook linkedin copy

    Buffer pool counters explain data cache pressure but go silent the moment memory trouble comes from somewhere else. sys.dm_os_memory_clerks lists every active memory clerk — buffer pool, plan cache, In-Memory OLTP, Query Store, query memory grants, Extended Events — and reports pages_kb per clerk. Grouping those rows …


    Read More
  • Break Down Buffer Pool Memory by Database

    calendar Jul 12, 2026 / Sep 22, 2026 · 8 min read · sql server sys.dm_os_buffer_descriptors buffer pool data cache memory database memory dmv performance monitoring scripts  ·
    Share on: twitter facebook linkedin copy

    sys.dm_os_buffer_descriptors is the DMV that maps SQL Server's data cache down to the individual page level — one row per 8 KB page currently held in the buffer pool, tagged with the database_id and file_id it belongs to. Rolling those rows up with a simple GROUP BY database_id turns millions of individual page entries …


    Read More
  • Measure Disk I/O Performance per Database File

    calendar Jul 3, 2026 / Sep 22, 2026 · 8 min read · sql server sys.dm_io_virtual_file_stats sys.master_files disk io io stall disk latency performance database files dmv monitoring scripts  ·
    Share on: twitter facebook linkedin copy

    I/O latency is where disk bottlenecks hide. SQL Server accumulates every millisecond of file-level read and write wait in sys.dm_io_virtual_file_stats — one row per database file, reset at each service restart. Joining that DMV to sys.master_files replaces raw file IDs with physical paths and file types, and dividing …


    Read More

SQLServer70.com

Your Source for SQL Server Scripts
Read More

Recent Posts

  • Log Forced Plans That Automatic Tuning Reverts with XEvents
  • Inventory Open Cursors per Session with sys.dm_exec_cursors
  • Track Azure SQL Database Load with sys.dm_db_resource_stats
  • Catch Plan Regressions Automatic Tuning Leaves Uncorrected
  • Audit Per-Database MAXDOP, CE and Sniffing Config Overrides
  • Rank Index Contention with sys.dm_db_index_operational_stats
  • Measure Internal Latch Contention with sys.dm_os_latch_stats
  • Catch Memory Grant Waits via sys.dm_exec_query_memory_grants

Categories

SCRIPTS 85 SQL SERVER PERFORMANCE 19 SQL SERVER ADMINISTRATION 15 SQL SERVER MAINTENANCE 14 SQL SERVER MONITORING 13 SQL SERVER STRUCTURE 11 SQL SERVER BACKUP RECOVERY 10 SQL SERVER SECURITY 3

Series

SQL SERVER PERFORMANCE 19 SQL SERVER ADMINISTRATION 15 SQL SERVER MAINTENANCE 14 SQL SERVER MONITORING 13 SQL SERVER STRUCTURE 11 SQL SERVER BACKUP RECOVERY 10 SQL SERVER SECURITY 3

Tags

SQL SERVER 82 DATABASE ADMINISTRATION 40 SQL SCRIPTS 40 SCRIPTS 26 PERFORMANCE 21 MAINTENANCE SCRIPTS 20 MONITORING 19 SYS.OBJECTS 19 SYS.TABLES 17 SYS.DATABASES 15 DATABASE MAINTENANCE 14 SYS.INDEXES 13 DBCC COMMANDS 11 PERFORMANCE TUNING 11
All Tags
ACTIVE SESSIONS2 AD HOC QUERIES1 ADMINISTRATION4 ADVANCED OPTIONS1 ALTER INDEX1 ALWAYS ON1 ANSI STANDARD1 AUTOMATIC PLAN CORRECTION2 AUTOMATIC TUNING2 AUTOMATION3 AVAILABILITY GROUPS1 AZURE SQL DATABASE1 BACKUP3 BACKUP AUDIT1 BACKUP COMPRESSION1 BACKUP MONITORING2 BACKUP RECOVERY8 BACKUP VALIDATION1 BACKUPSET1 BLOCKING4 BOTTLENECK ANALYSIS1 BUFFER CACHE HIT RATIO1 BUFFER MANAGER1 BUFFER POOL2 CAPACITY PLANNING2 CARDINALITY ESTIMATION2 CATALOG VIEWS7 CLUSTERED INDEXES1 CNTR_TYPE1 COLUMNS1 COMPATIBILITY VIEWS1 COMPILED PLAN STUB1 CONFIGURATION DRIFT1 CONSISTENCY CHECKS2 CONSTRAINT REPORTING1 CONTENTION1 COPY ONLY BACKUP1 CPU CONFIGURATION1 CPU HISTORY1 CPU TIME1 CPU UTILIZATION1 CROSS DATABASE1 CURSOR PROGRAMMING1 CURSORS1 DATA AUDIT1 DATA CACHE1 DATA COMPRESSION1 DATA DICTIONARY1 DATA TYPES1 DATABASE1 DATABASE ADMINISTRATION40 DATABASE CONFIGURATION1 DATABASE CONNECTIONS1 DATABASE CORRUPTION2 DATABASE FILES2 DATABASE INTEGRITY1 DATABASE MAIL1 DATABASE MAINTENANCE14 DATABASE MANAGEMENT2 DATABASE MEMORY1 DATABASE MONITORING2 DATABASE OBJECTS1 DATABASE RECOVERY1 DATABASE ROLES1 DATABASE SCHEMA1 DATABASE SCRIPTS1 DATABASE SECURITY1 DATABASE STRUCTURE9 DBA SCRIPTS10 DBA TOOLS1 DBCC1 DBCC CHECKDB4 DBCC CHECKIDENT2 DBCC COMMANDS11 DBCC DBINFO1 DBCC DBREINDEX2 DBCC FREEPROCCACHE1 DBCC LOGINFO1 DBCC SHRINKFILE2 DEPENDENCIES1 DEV RESTORE1 DIAGNOSTICS1 DIFFERENTIAL BACKUP1 DISK IO1 DISK LATENCY1 DISK SPACE2 DM DB MISSING INDEX DETAILS1 DM DB MISSING INDEX GROUP STATS1 DM DB SESSION SPACE USAGE1 DM EXEC REQUESTS2 DM EXEC SESSIONS3 DM EXEC SQL TEXT1 DM OS WAIT STATS1 DMV8 DOCUMENTATION1 DTU1 DUPLICATE INDEXES1 DYNAMIC SQL4 ELASTIC POOL1 ERROR LOG1 EXECUTION PLAN1 EXTENDED EVENTS2 FAILOVER READINESS1 FILEPROPERTY1 FOR SYSTEM_TIME1 FORCE LAST GOOD PLAN1 FOREIGN KEYS2 FRAGMENTATION2 FULL BACKUP2 GRANT STATEMENTS1 HEAD BLOCKER1 HEAP TABLES2 HISTORY TABLE1 IDENTITY COLUMNS2 IDENTITY RESEED1 IMPACT ANALYSIS1 INDEX FRAGMENTATION2 INDEX MAINTENANCE5 INDEX OPTIMIZATION1 INDEX REBUILDING1 INDEX SIZE1 INDEX TUNING2 INDEXES5 INDEXING1 INFORMATION_SCHEMA2 INFORMATION_SCHEMA.TABLE_CONSTRAINTS1 INFORMATION_SCHEMA.TABLES1 INTEGRITY CHECK2 IO STALL1 JOB MONITORING1 KILL SPID1 LATCH CONTENTION1 LATCHES1 LOCK CONTENTION1 LOG MANAGEMENT1 LOGIN MANAGEMENT1 LONG RUNNING TRANSACTIONS1 LOW MEMORY1 MAINTENANCE2 MAINTENANCE SCRIPTS20 MAX SERVER MEMORY1 MAXDOP2 MEMORY1 MEMORY CLERKS1 MEMORY CONFIGURATION1 MEMORY GRANTS1 MEMORY PRESSURE2 METADATA7 MISSING INDEXES1 MONITORING19 MOVE OPTION1 MSDB10 MSDB.DBO.BACKUPMEDIAFAMILY3 MSDB.DBO.BACKUPSET3 NONCLUSTERED INDEXES1 NORECOVERY1 NUMA2 OBJECT DEPENDENCIES1 OBJECT SEARCH1 OPEN TRANSACTIONS1 OPTIMIZATION2 OPTIMIZE FOR AD HOC WORKLOADS1 ORPHAN USERS1 PAGE COMPRESSION1 PAGE CORRUPTION1 PAGE LIFE EXPECTANCY1 PARAMETER SNIFFING2 PERFORMANCE21 PERFORMANCE MONITORING2 PERFORMANCE TUNING11 PERMISSIONS2 PLAN CACHE5 PLAN REGRESSION2 POINT IN TIME QUERY1 POINT IN TIME RESTORE1 PRIMARY KEYS3 PROCEDURE CACHE1 QUERY OPTIMIZATION2 QUERY OPTIMIZER2 QUERY PERFORMANCE1 QUERY STORE4 QUERY TUNING3 REBUILD REORGANIZE1 RECONFIGURE1 RECOVERY1 REFACTORING1 REFERENTIAL INTEGRITY1 REPORTING3 RESOURCE SEMAPHORE1 RESTORE2 RESTORE DATABASE1 RESTORE FILELISTONLY1 RESTORE VERIFYONLY1 RING BUFFER1 ROLE MEMBERSHIPS1 ROW COMPRESSION1 ROW LOCKS1 SCHEMA ANALYSIS2 SCHEMA AUDIT1 SCHEMA DOCUMENTATION1 SCHEMA MANAGEMENT1 SCRIPTS26 SECURITY4 SECURITY AUDIT2 SERVER MONITORING1 SESSION MANAGEMENT1 SIZE_IN_BYTES1 SP CONFIGURE1 SP_CHANGE_USERS_LOGIN1 SP_CONFIGURE3 SP_EXECUTESQL1 SP_PKEYS2 SP_READERRORLOG1 SP_SEND_DBMAIL1 SP_SPACEUSED1 SP_UPDATESTATS1 SPACE MANAGEMENT1 SPACE USAGE1 SQL1 SQL SCRIPTS40 SQL SERVER82 SQL SERVER 20171 SQL SERVER AGENT2 SQL SERVER MONITORING1 SQL SERVER PERFORMANCE1 SQLSERVER PROCESS UTILIZATION1 STATISTICS2 STORAGE2 STORAGE ERROR1 STORED PROCEDURES2 SUSPECT PAGES1 SYNCHRONIZATION HEALTH1 SYS.ALL_COLUMNS1 SYS.ALL_OBJECTS1 SYS.ALLOCATION_UNITS3 SYS.AVAILABILITY_GROUPS1 SYS.AVAILABILITY_REPLICAS1 SYS.COLUMNS9 SYS.COMPUTED_COLUMNS1 SYS.CONFIGURATIONS6 SYS.DATABASE_FILES3 SYS.DATABASE_PERMISSIONS2 SYS.DATABASE_PRINCIPALS3 SYS.DATABASE_ROLE_MEMBERS1 SYS.DATABASE_SCOPED_CONFIGURATIONS1 SYS.DATABASES15 SYS.DM_DB_FILE_SPACE_USAGE1 SYS.DM_DB_INDEX_OPERATIONAL_STATS1 SYS.DM_DB_INDEX_PHYSICAL_STATS9 SYS.DM_DB_INDEX_USAGE_STATS5 SYS.DM_DB_LOG_SPACE_USAGE1 SYS.DM_DB_MISSING_INDEX_DETAILS1 SYS.DM_DB_MISSING_INDEX_GROUP_STATS1 SYS.DM_DB_MISSING_INDEX_GROUPS1 SYS.DM_DB_PARTITION_STATS3 SYS.DM_DB_RESOURCE_STATS1 SYS.DM_DB_STATS_PROPERTIES1 SYS.DM_DB_TUNING_RECOMMENDATIONS2 SYS.DM_DB_XTP_TABLE_MEMORY_STATS1 SYS.DM_ELASTIC_POOL_RESOURCE_STATS1 SYS.DM_EXEC_CACHED_PLANS3 SYS.DM_EXEC_CONNECTIONS2 SYS.DM_EXEC_CURSORS1 SYS.DM_EXEC_INPUT_BUFFER1 SYS.DM_EXEC_PLAN_ATTRIBUTES1 SYS.DM_EXEC_PROCEDURE_STATS1 SYS.DM_EXEC_QUERY_MEMORY_GRANTS1 SYS.DM_EXEC_QUERY_PLAN2 SYS.DM_EXEC_QUERY_RESOURCE_SEMAPHORES1 SYS.DM_EXEC_QUERY_STATS3 SYS.DM_EXEC_REQUESTS6 SYS.DM_EXEC_SESSIONS5 SYS.DM_EXEC_SQL_TEXT9 SYS.DM_EXEC_TEXT_QUERY_PLAN1 SYS.DM_EXEC_XML_HANDLES1 SYS.DM_HADR_AVAILABILITY_GROUP_STATES1 SYS.DM_HADR_AVAILABILITY_REPLICA_STATES1 SYS.DM_HADR_DATABASE_REPLICA_CLUSTER_STATES1 SYS.DM_HADR_DATABASE_REPLICA_STATES1 SYS.DM_IO_VIRTUAL_FILE_STATS1 SYS.DM_OS_BUFFER_DESCRIPTORS1 SYS.DM_OS_LATCH_STATS1 SYS.DM_OS_MEMORY_CLERKS3 SYS.DM_OS_PERFORMANCE_COUNTERS2 SYS.DM_OS_PROCESS_MEMORY1 SYS.DM_OS_RING_BUFFERS1 SYS.DM_OS_SCHEDULERS1 SYS.DM_OS_SYS_INFO9 SYS.DM_OS_SYS_MEMORY1 SYS.DM_OS_VOLUME_STATS1 SYS.DM_OS_WAIT_STATS4 SYS.DM_OS_WAITING_TASKS1 SYS.DM_PDW_NODES_EXEC_PROCEDURE_STATS1 SYS.DM_PDW_NODES_OS_LATCH_STATS1 SYS.DM_RESOURCE_GOVERNOR_RESOURCE_POOLS1 SYS.DM_SERVER_SERVICES1 SYS.DM_SQL_REFERENCED_ENTITIES1 SYS.DM_SQL_REFERENCING_ENTITIES1 SYS.DM_TRAN_ACTIVE_TRANSACTIONS2 SYS.DM_TRAN_LOCKS1 SYS.DM_TRAN_SESSION_TRANSACTIONS1 SYS.FOREIGN_KEY_COLUMNS2 SYS.FOREIGN_KEYS3 SYS.IDENTITY_COLUMNS2 SYS.INDEX_COLUMNS7 SYS.INDEXES13 SYS.KEY_CONSTRAINTS3 SYS.LOGIN_TOKEN1 SYS.MASTER_FILES4 SYS.MESSAGES1 SYS.OBJECTS19 SYS.PARTITIONS9 SYS.PROCEDURES1 SYS.QUERY_STORE_PLAN1 SYS.QUERY_STORE_QUERY1 SYS.QUERY_STORE_QUERY_TEXT1 SYS.QUERY_STORE_RUNTIME_STATS1 SYS.QUERY_STORE_RUNTIME_STATS_INTERVAL1 SYS.RESOURCE_STATS1 SYS.SCHEMAS9 SYS.SERVER_EVENT_SESSIONS2 SYS.SERVER_PRINCIPALS3 SYS.SERVER_RESOURCE_STATS1 SYS.SP_ENUMERRORLOGS1 SYS.SQL_EXPRESSION_DEPENDENCIES1 SYS.SQL_MODULES2 SYS.STATS1 SYS.TABLES17 SYS.TYPES2 SYS.VIEWS1 SYSCOLUMNS1 SYSDATABASES1 SYSINDEXES3 SYSJOBHISTORY1 SYSJOBS1 SYSMAIL_ADD_ACCOUNT_SP1 SYSMAIL_ALLITEMS1 SYSMAIL_EVENT_LOG1 SYSOBJECTS10 SYSTEM IDLE PROCESS1 SYSTEM TABLES4 SYSTEM VERSIONING1 T-SQL2 TABLE ANALYSIS1 TABLE INVENTORY1 TABLE MAINTENANCE1 TABLE SIZE1 TABLE STATISTICS1 TEMPDB2 TEMPORAL TABLES1 TRANSACTION LOG4 TRANSACTION LOG BACKUP1 TSQL2 UPDATE STATISTICS2 USECOUNTS1 VCORE1 VIEWS1 VIRTUAL LOG FILES1 WAIT STATISTICS2 WAIT TYPES3 XP_CMDSHELL1 XP_READERRORLOG1 XTP1
[A~Z][0~9]
SQLServer70.com

Copyright  SQLSERVER70.COM. All Rights Reserved

to-top