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

11
votes
2answers
721 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 ...
3
votes
1answer
599 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 ...
2
votes
2answers
234 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 ...
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
180 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 ...
9
votes
3answers
14k 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 ...
8
votes
1answer
151 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 ...
6
votes
1answer
259 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 ...
6
votes
2answers
2k 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 ...
5
votes
2answers
131 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 ...
4
votes
2answers
897 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 ...
2
votes
1answer
109 views

sys.dm_exec_procedure_stats missing history?

My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...