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

6
votes
1answer
119 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 ...
0
votes
0answers
22 views

How can I find last access time for users or specific users to all databases in SQL Server

I have a SQL Server with more than 100 Databases and I should manage the server and it's space usage, users can restore and use backups they have on the server, I have a software for this server,if ...
2
votes
3answers
64 views

Discover available HD storage from inside SQL Server

I have a SQL Server 2012 instance hosted in a VPS. I can't login on VPS's Windows directly. I know that there are multiple instances installed on that VPS. I have a query that shows me each DB's file ...
1
vote
1answer
42 views

How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]?

I have a function which is generating dynamic T-SQL statement. The generated code is executed then in a stored procedure. I want to log some statistics about how long the execution of the generated ...
0
votes
1answer
27 views

sys.dm_exec_requests and cpu_time

If a query is executing on a multi-core server, and the query uses parallel threads, will sys.dm_exec_requests.cpu_timeshow the cpu time consumed by all threads executing the query? I assume this is ...
2
votes
1answer
47 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
53 views

Is there a Windows Performance Counter which contains the “Log Send Rate” for Availability Groups?

I wish to be able to get the "Log Send Rate kb/s", as per the SSMS AG dashboard (which is also available via log_send_rate column on the DMV sys.dm_hadr_database_replica_states) I'd like to get it as ...
4
votes
1answer
110 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 ...
4
votes
1answer
71 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 ...
6
votes
1answer
149 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 ...
4
votes
1answer
136 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 ...
6
votes
2answers
217 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 ...
1
vote
0answers
27 views

DMV Index proposals still reported as missing

We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues. After running the DMV query: ...
3
votes
1answer
155 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 ...
5
votes
1answer
84 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 ...
2
votes
1answer
148 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
1answer
138 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 ...
0
votes
1answer
34 views

Referring system views and functions in production functions

I am using the following system objects in a stored procedure in order to build dynamic T-SQL statements: [sys].[objects] [sys].[parameters] [sys].[dm_exec_describe_first_result_set] [sys].[columns] ...
1
vote
1answer
135 views

How to find out the account(s) under which the sql server is running?

I have just created a proxy and I need to grant access to the proxy to the account that runs the sql server agent. It will probably be a domain account. How can I find it using T-SQL?
4
votes
2answers
114 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
1answer
118 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 ...
1
vote
1answer
117 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 ...
0
votes
1answer
97 views

Saving SQL Server DMV's for later use [closed]

Is it possible to save DMV's in SQL server database backup, which can be restored on different server, I mean with master db details( views and all)? If not, what's the best way to maintain 2 ...
7
votes
1answer
154 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 ...
4
votes
0answers
244 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% ...
4
votes
1answer
84 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 ...
11
votes
1answer
186 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 ...
0
votes
1answer
82 views

Optimizing a DMV query

I have found a query that is a major performance issue in my environment. I looked at the actual execution plan for this query and found out the main problem is the remote scan (98%). Its remote ...
7
votes
2answers
1k 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.
1
vote
1answer
141 views

Clear missing index DMVs

How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details? I don't have an option to restart the server or taking it ...
3
votes
1answer
109 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 ...
3
votes
2answers
836 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 ...
1
vote
0answers
68 views

Non clustered indexes

I have a questions on Index Our Application is throwing the following SQL eroor Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...
2
votes
1answer
689 views

What means Command column in sp_who2 and text column in sys.dm_exec_sql_text?

I have a script that shows commands being fired in SQL Server 2008 database as follows: --shows who is active and executed commands declare @SPID int=null, @Status varchar(max) = null, @LOGIN ...
0
votes
1answer
304 views

[sys].[dm_db_index_usage_stats] returns more entries in some databases

I am using the following query to get some indexes usage statistics: SELECT DB.[name] ,OBJECT_NAME(IDX.[object_id]) AS [Table] ,IDX.[name] AS [Index] ,US.[user_seeks] ...
7
votes
2answers
8k views

Check progress of alter index reorganize / rebuild

How can I check the progress / status when I submit an alter index reorganize / rebuild ?
2
votes
1answer
162 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. ...
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 / ...
1
vote
1answer
122 views

Troubleshooting Resource Governor / Classification Function [closed]

I've been trying to configure / troubleshoot the resource governor on a sql server 2014 instance. I can't get any work into anything but the default resource pool. I've been following this ...
3
votes
2answers
1k 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 ...
0
votes
0answers
24 views

Trying to find unused stored procedures, but dm_exec_procedure_stats seems to be resetting [duplicate]

I need to identify stored procedures that are no longer used by our application (~2,000 sprocs, duplicated in ~1,000 databases). To do this, I'm querying dm_exec_procedure_stats, which initially ...
0
votes
2answers
69 views

Knowing your workload type using dmv's? [closed]

Is there a DMV through which I can tell that my workload type is 'ad hoc'?
4
votes
1answer
3k 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 ...
1
vote
0answers
32 views

Any instance where DMV can cause blocking? [duplicate]

So we had some slow performance with timeouts this morning. When looking for the cause, our blocking query (uses DMVs) showed up as the blocking query. I think this was just a fluke but the job we ...
0
votes
1answer
3k views

Finding login or user name of last person to use a DB

The boss has asked for a list of DB names along with size and name of last person to use that DB. I've found resources for when a DB was last accessed but for by whom. How would I solve this? ...
0
votes
0answers
241 views

When is transaction removed from sys.dm_tran_active_transactions?

We are having some issues with clients getting Transaction Timeout when connecting to our clustered SQL Server 2008 R2. To investigate the problems I have been digging into ...
7
votes
2answers
689 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 ...
3
votes
1answer
545 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 ...
5
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 ...
2
votes
0answers
198 views

Collect information about currently running queries only when client timeout occurs

We would like to be able to collect information about all currently running queries when (and only when) a client timeout occurs in SQL Server 2008. I know I can collect information about the client ...