DMV in SQL Server is a catch-all term for Database Management Views [& Functions]. Technically they are all now DMOs (Database Management Objects) however the term DMV is still widely understood and accepted by most.

learn more… | top users | synonyms

14
votes
1answer
505 views

Plan cache size and reserved memory

When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB. A query that joins sys.dm_exec_cached_plans and sys....
11
votes
2answers
736 views

Setting READ UNCOMMITTED when reading DMVs

I've seen several people call SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before reading system DMVs. Is there ever any reason to do this, assuming you aren't mixing calls to DMVs and tables in ...
11
votes
1answer
266 views

stats_column_id and index_column_id do not update with physical order of clustered index is changed

Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the ...
10
votes
3answers
362 views

Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate?

I am running SQL Server 2012 and am trying to put some queries together for monitoring using the DMVs. However, when looking at the total_elapsed_time field in the sys.dm_exec_requests DMV, the ...
10
votes
2answers
12k views

Check progress of alter index reorganize / rebuild

How can I check the progress / status when I submit an alter index reorganize / rebuild ?
9
votes
3answers
15k views

Improve performance of sys.dm_db_index_physical_stats

During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on sys....
8
votes
1answer
180 views

What does the “reads” column in sys.dm_exec_sessions actually indicate?

This may seem like a very basic question, and indeed it should be. However, as a fan of the scientific method, I like to create a hypothesis, then test it to see if I'm correct. In this case, I'm ...
7
votes
2answers
3k views

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data? I need to nullify the data in all encrypted columns in a development server (according to our ...
7
votes
1answer
1k views

SQL Server 2008 R2 DMV Question

I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV. SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs....
7
votes
2answers
2k views

Finding the query causing the missing index DMVs

How can I find the query causing the missing index DMVs suggesting to create a particular index? Thank you.
7
votes
1answer
169 views

Help understanding discrepency between sys.dm_exec_cached_plans and dm_os_memory_clerks

We've got a strange discrepancy on one of our SQL Server 2014 instances between the amount of memory being used for cached plans as per sys.dm_exec_cached_plans and the amount of memory used as per ...
7
votes
2answers
811 views

SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

I received a call yesterday from a customer who was complaining about high CPU usage on their SQL Server. We're using SQL Server 2012 64 bit SE. Server is running Windows Server 2008 R2 Standard, 2.20 ...
6
votes
2answers
237 views

Index Fragmentation - Am I interpreting the results correctly?

I'm not a DBA, but I'm responsible for a database that currently has hundreds of tables and ~5TB of data. I recently ran the following query, in hopes of determining index fragmentation: Declare @...
6
votes
2answers
185 views

Running query against DMVs for Query Stats and Execution Plans joined to sys.databases

I originally posted this as a different question in Twitter #sqlhelp, but wanted to post it here differently. I'm trying to create a job that tracks worst performing statements for various statistics ...
6
votes
2answers
3k views

What units are 'reads' and 'writes' in dm_exec_query_stats

MSDN describes the various logical / physical read and write columns in dm_exec_query_stats as: Number of physical reads performed the last time the plan was executed. Those columns are all 64 ...
6
votes
1answer
3k views

what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats?

what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them? when I fire below query SELECT TOP 20 qs.last_worker_time, qs....
6
votes
1answer
374 views

sys.dm_exec_requests - start_time

I have the following query that tells me the status of queries in a database I'm responsible for (although I am not a DBA). SELECT T3.FullStatement as FullSQLStatement ,T3....
6
votes
1answer
170 views

Ok to drop indexes on FK's if they have no stats in the DMVS

I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes. Are you 100% safe to drop these ...
5
votes
3answers
712 views

Why is there a difference between these transaction DMVs in SQL Server 2008 R2?

When I execute the two queries below, SELECT session_id, transaction_id FROM sys.dm_tran_session_transactions; AND SELECT session_id, request_id, at.transaction_id FROM sys....
5
votes
1answer
130 views

sys.dm_exec_procedure_stats DMV contents contradict BOL

I am doing some analysis of long running stored procedures and have come across something of an anomaly, according to books online sys.dm_exec_procedure_stats should only display one row per cached ...
5
votes
2answers
97 views

What sort of exec requests don't have a sql handle? (sys.dm_exec_requests)

I have crafted a query against SQL Server 2012 that looks like the following: SELECT r.session_id, r.sql_handle, t.text -- more details excluded for brevity FROM sys.dm_exec_requests r CROSS APPLY ...
5
votes
1answer
4k views

Understanding sys.dm_exec_query_stats execution_count vs creation_time

I've just downloaded the SQL Server 2012 'Performance Dashboard Reports' and am having some issues interpreting the data shown by one of the results and linking it back to the DMV sys....
5
votes
2answers
146 views

reporting space consumption to files in filegroup - how to reverse-engineer the algorithm spreading data across files

I would like to know how to calculate the distribution of data consumed per file in a filegroup, back to the index (HEAP, CLUSTERED, NONCLUSTERED) storing it. My intention is to define which I/O goes ...
5
votes
1answer
184 views

Zero Rows return on quering DMV sys.dm_os_performance_counters

I have a user on SQL Server 2014 Standard Edition (RTM) with SYSADMIN role with Server View State Permissions but when I execute DMV sys.dm_os_performance_counters it return no record. Any idea what ...
4
votes
3answers
291 views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
4
votes
2answers
1k views

Pulling Top queries returns NULL in query plan and sql text

I'm using the following code to pull our top 20 queries (ordered by CPU): SELECT TOP 20 qs.sql_handle ,qs.execution_count ,qs.total_worker_time AS [Total CPU] ,qs.total_worker_time / ...
4
votes
1answer
69 views

Incremental statistics: find out which partitions have been resampled

TL;DR: Is it possible to find out which partition(s) have been resampled and which have not when incremental statistics are used? Platform is SQL Server 2014 Enterprise edition. The long version with ...
4
votes
2answers
176 views

SQL Server : If a query is cancelled, where to get data on it?

Suppose am tracking a query using sys.dm_exec_query_stats, if the query ends fine, then we can find the relevant statistics in sys.dm_exec_query_stats. But if a query is cancelled, the statistics of ...
4
votes
2answers
2k views

sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups

These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing? Here is how I proved it is the same data: select fil....
4
votes
1answer
91 views

PAGEIOLATCH wait type

I'm trying to get a better understanding of how serious this wait type is. I'm using sp_WhoIsActive stored procedure. Our DBAs don't seem to know much about this wait type. But I often see it when I ...
4
votes
1answer
164 views

Why are these two HADR DMVs reporting different states?

SQL Server 2012 (11.0.5058.0) Enterprise Edition We have 8 Availability Groups in a 2(HA)+1(DR) cluster and our monitoring DMVs are reporting results that confuse me. 6 Availability Groups are ...
4
votes
2answers
481 views

How do I track the usage of XML indexes?

My XML indexes are not listed in sys.dm_db_index_usage_stats Does this mean they aren't being used? Or does this particular DMV not track XML index usage? (Books Online doesn't appear to answer the ...
4
votes
2answers
914 views

DMV Queries as Perfmon Counters

Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters. For example, take: SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats; Most ...
4
votes
1answer
94 views

What is the performance impact of queries against DM objects using DMVs in SQL Server 2008?

Recently, I've been running queries against our production system on the DMVs for querying the plan cache. In particular, here's the from clause of the query: FROM sys.dm_exec_cached_plans AS cp ...
4
votes
1answer
169 views

How to find out how much i/o resources a task, session, request or transaction is currently using?

How to find out how much i/o resources a task, session, request or transaction is currently using? by task I mean the DMV called DMV sys.dm_os_tasks. by session I mean the DMV sys.dm_exec_sessions ...
4
votes
0answers
359 views

Interpreting sys.dm_os_sys_memory

I try to figure out a way(using only TSQL) to determine if and how much the PAGEFILE (I will capitalize this term if I refer to the windows page file from now on) is used (like pagefileusage% counter)....
3
votes
2answers
1k views

space usage on sys.allocation_units and sp_spaceused

It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't. I am working on a ...
3
votes
1answer
82 views

Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs

In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to ...
3
votes
1answer
224 views

how to get the information about sleeping processes that are causing blocking?

I have a procedure that allows me to see what is currently running in my sql server. This procedure does not use sys.sysprocess because it has been deprecated. However, I am struggling to find ...
3
votes
2answers
2k views

How to figure out Disk I/O by table

Is it possible to figure out which tables contribute the most to read / writes operations if all of them in same file? I been looking at the following DMV SELECT * FROM sys.dm_io_virtual_file_stats ...
3
votes
1answer
625 views

SQL Server DMV to show if database is used

What is the best indicator in finding out whether a database is utilized or accessed? I tried index statistics and based on I/O utilization but none seem to be a good indicator. Problem: I have a ...
3
votes
2answers
178 views

Inconsistent information from different DMVs

I've a server with 12 GB of physical RAM. I just lowered its 'Max Server Memory (MB)' setting from 10 GB to 8 GB. If I look at the processes' committed memory in Perfmon, I see: select * from sys....
3
votes
1answer
122 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
3
votes
1answer
346 views

SQL Server: dm_exec_requests.wait_time vs. dm_exec_sessions.last_request_end_time

We have a third-party Microsoft Access application that uses SQL Server 2012 as its data store. It is a well known issue that Access often does not tell SQL Server that it has received the results of ...
3
votes
0answers
331 views

Are the following statements concerning sys.dm_exec_requests true?

Parallel plans mean that cpu_time can be greater than total_elapsed time. Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread. ...
2
votes
1answer
97 views

Synchronize Queries Retrieving Sessions And Locks From DMVs

I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" (sys....
2
votes
1answer
52 views

Missing indexes dmv count varies every day

I have a question to the missig index dmv. BOL says: Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. ...
2
votes
1answer
88 views

how to get the missing indexes that are “filtered indexes”

I have a procedure that shows me the missing indexes. It has been very good to me, but it does not show me the missing FILTERED INDEXES. I would like my procedure to be able to catch and suggest ...
2
votes
2answers
317 views

how to find the T-SQL of a sleeping session that is blocking some other process?

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ...
2
votes
1answer
56 views

Monitor number of records returned in a long-running SELECT statement during SSAS processing

On a SQL Server 2014, I have an SSAS tabular model which is querying a relational clustered columnstore table (with 800 mio. records), when the model is being processed. The query is just a simple ...