We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [dmv]

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.

Filter by
Sorted by
Tagged with
12
votes
2answers
5k 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 ...
2
votes
2answers
5k 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 ...
12
votes
2answers
871 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 ...
4
votes
1answer
916 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
1answer
56 views

How to find out the oldest full backup still existing on disk?

one of the things I like to find out is what is the oldest full backup file still living in my servers. it is the custom to delete or move those files to a different place after a number of days. How ...
3
votes
1answer
1k 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 ...
2
votes
2answers
305 views

Generate SQL Table DDL from a View

How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql (1) can remove primary key/constraint, (2) ...
4
votes
2answers
2k 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
3answers
2k views

Batch Requests/sec reported by DMV millions of times larger than Activity Monitor

I have verified through SQL Server Profiler that my request per-second are around 30 request/s as is corroborated by SSMS Activity Monitor but sys.dm_os_performance_counters is reporting hundreds of ...
3
votes
1answer
3k views

sys.dm_db_index_physical_stats is extremly slow

I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. ...
10
votes
1answer
887 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 ...
8
votes
1answer
414 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 ...
6
votes
1answer
181 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 ...
3
votes
2answers
1k views

Why aren't some spid values showing up in sys.dm_exec_requests?

I am executing the following dmv query against SQL Server 2014 to find all activity on a server and any blocking activities: SELECT Distinct ... FROM sys.dm_exec_requests er OUTER APPLY sys....
0
votes
2answers
319 views

How to script all the service broker elements for each database it is on, in the current server?

I have service broker running on different servers. Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there. For the next time I need to ...
8
votes
2answers
7k 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 bit ...
8
votes
2answers
2k 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 ...
7
votes
1answer
1k 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
816 views

Is it possible to see which SPID uses which scheduler (worker thread)?

We recently had a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads. Got the message: The thread pool for AlwaysOn Availability Groups was unable ...
5
votes
1answer
3k 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, ...
5
votes
2answers
254 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 ...
2
votes
2answers
2k views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
2
votes
0answers
61 views

is there any advantage when troubleshooting service broker, to create the queue with retention=ON?

from CREATE QUEUE (Transact-SQL): RETENTION Specifies the retention setting for the queue. If RETENTION = ON, all messages sent or received on conversations that use this queue are retained in ...
1
vote
0answers
468 views

service broker - poison message - how to handle? how to enable poison messase handling?

I am getting constantly this error message on service broker: POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411 select top 1000 * from ...
1
vote
1answer
136 views

SQL Server sys.dm_tran_active_transactions long running transaction worktable

today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no ...
1
vote
4answers
220 views

replace all references to a drive inside the jobs to another drive

I have a server where all my sql server backups are currently being saved to the G: drive. Now there is no enough disk space on drive G: to keep backups for the last 5 days. I have asked to add ...