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

4
votes
3answers
245 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....
1
vote
1answer
37 views

Wrong Last Execution Time sys.dm_exec_procedure_stats

I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I ...
14
votes
1answer
499 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....
6
votes
2answers
184 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 ...
0
votes
0answers
30 views

SQL Server Index recomended but already there (DMVs)

I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc. Some examples are Index ON X ( Y ) and Table ...
1
vote
1answer
28 views

How do I tell which Edition and Version my SSAS Instance is running?

In SQL Server on the database engine, you can run a SQL query like this to get the server's version info like Edition and Version, and Update Level, etc. SELECT @@VERSION What is the MDX equivalent ...
5
votes
2answers
90 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 ...
0
votes
1answer
46 views

sys.dm_exec_procedure_stats reset [duplicate]

I want to get stats from the sys.dm_exec_procedure_stats view. When I check the records from months ago, I see that after a point of time its stats reset daily, but there have been no server resets. ...
0
votes
1answer
45 views

Does dm_exec_procedure_stats.total_worker_time include signal waits?

Does sys.dm_exec_procedure_stats.total_worker_time include signal waits or only time spent by procedure on scheduler? The reason of question - when number of cores was increased on the server signal ...
3
votes
1answer
81 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 ...
10
votes
3answers
349 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 ...
2
votes
1answer
47 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 ...
0
votes
2answers
53 views

Can I see Table I/O per month?

I'm using SQL Server 2008, and will migrate DynamoDB some tables. I want to get I/O statistics of read bytes, and write bytes from table. I don't know how to do this. What command should I use?
2
votes
2answers
127 views

Why is context_info sometimes blank in sys.dm_exec_sessions?

I am trying to get information from the context_info column using Cast(context_info as varchar(128)) from sys.dm_exec_sessions as given in the article below: Finding User Sessions from SPID in ...
4
votes
1answer
68 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 ...
0
votes
1answer
35 views

What is the request_id in sys.dm_exec_requests and how does it change?

According to MSDN, the request_id in sys.dm_exec_requests is the "ID of the request. Unique in the context of the session. Is not nullable." It always appears to be 0, from casual observation....
8
votes
1answer
168 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
32 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
89 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
74 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
50 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
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
170 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, ...
2
votes
1answer
71 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
129 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
88 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
339 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....
4
votes
1answer
162 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
233 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
38 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: ...
2
votes
1answer
81 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 ...
3
votes
1answer
316 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
157 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 ...
3
votes
1answer
217 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
285 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
37 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
244 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
166 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
157 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
207 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
138 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
166 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
346 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)....
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 ...
11
votes
1answer
260 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
92 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
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.
1
vote
2answers
287 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 the option to restart the server or taking it ...
5
votes
2answers
144 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
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 ...