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
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....
7
votes
1answer
155 views

Do values in `sys.dm_os_wait_stats` reset to 0 or stop accumulating when they hit the max value?

When looking at sys.dm_os_wait_stats, the following columns are defined as BIGINT waiting_tasks_count wait_time_ms max_wait_time_ms If any of those values go above 9,223,372,036,854,775,807, do ...
-1
votes
2answers
623 views

Is there any method of looking for Head Blocking history in SQL 2012?

Is there any method of looking for Head Blocking history via DMVs. I have been able to find the subsequent wait details via Extended Events but there doesn't seem to be much information about what ...
0
votes
1answer
138 views

is there any reason to explicitly grant the server permission CONNECT SQL?

I have been using this script from Kenneth Fisher to get the server permissions. -- Server Permissions SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, Grantor.name ...
0
votes
1answer
869 views

SQL Server Average Lock Wait time Historical data

Monitoring software has indicated average lock wait times rising to 500ms during the night. Is there any way of confirming which locks these were as we currently do not log any performance data to a ...
2
votes
2answers
352 views

Confusion about connection_id in SQL Server DMV

I am confused about the connection_id column of sys.dm_exec_connections DMV: SELECT connection_id FROM sys.dm_exec_connections; If I connect to an instance of SQL Server using SSMS, should it be one ...
5
votes
1answer
269 views

How to include “Actual Row” counts in Execution plans pulled from the Query Cache or Query Store?

Obviously seeing the actual execution plan is a big deal when tuning, and normally I enable the query plan output via SET STATISTICS XML ON and run whatever query needs some TLC, but how can I see ...
5
votes
1answer
466 views

Is there DMV for seeing view access count?

I saw it once during a presentation when someone showed a query to get a count of how many times views have queried in a SQL server. I don't remember if it was from DMV or a combination of some other ...
2
votes
1answer
1k views

sys.dm_exec_sql_text returns NULL in dbid, objectid, and number columns, but not text

This is something that's bugged me though has never caused any real issues as I can generally locate this information by other means, but can someone explain why NULL may be returned in the dbid, ...
0
votes
1answer
187 views

Is there a way to show index_advantage more friendly? [closed]

Is there a way to show index_advantage more friendly like 99.54%? SELECT TOP 25 user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage] FROM sys....
0
votes
1answer
1k views

How to see the errors on AlwaysOn using T-SQL?

when I run the following query if you run the query below on bocss it says it is not healthy select * from sys.dm_hadr_availability_replica_states it tells me my alwayson is not healthy. ...
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 ...
1
vote
1answer
2k views

SQL Server Full-Text Index crawl: Is it done or not?

I am on SQL Server 2012. I have one table with one full-text index. That index was dropped and recreated around 8am due to a change in the datatype of the indexed column from TEXT to VARCHAR(MAX). ...
1
vote
1answer
197 views

sys.dm_db_partition_stats - incorrect row count for InMemory tables

I have a problem with sys.dm_db_partition_stats DMV. When having InMemory tables (with hash indexes, columnstores, classical b-tree) and trying to get row count via this query there is 0 displayed for ...
0
votes
1answer
268 views

When is dm_exec_procedure_stats updated?

I just created a new proc and ran it, but it is not showing up in my query: SELECT schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time ...
1
vote
2answers
72 views

Proc caching on read only db's/replicas

I've been given the task of finding which stored procs are being called in our database. To that end I started querying sys.dm_exec_cached_plans. All is well and good, until I go over to our ...
1
vote
1answer
379 views

What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans?

As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached ...
0
votes
1answer
52 views

DMV CONNECTION_HOST_APPLICATION field is NULL

I am trying to monitor cubes and expressions of Analysis Services using DMV queries that are stored on Server's RAM. I found many useful information about connections on connection's table of SSMS's ...
2
votes
1answer
142 views

Is there a DMV that shows the aggregate logical reads on a SQL server?

I want to use this metric to compare 2 servers. I've looked at sys.dm_io_virtual_file_stats, but this is missing logical. Also looked at query_stats, but that only includes cached plans.
4
votes
1answer
51 views

Is there a way to find out what queries are run the most against a particular table?

We have been experiencing issues with slowness regarding our Master Data Services application. So we have looked to see which tables have the highest value of range scans, singleton lookups, page lock ...
0
votes
0answers
66 views

NULL value in query_plan column from the sys.dm_exec_query_plan DMV [duplicate]

I've written a query to return data from the sys.dm_exec_query_plan DMV, but for a specific stored procedure the query_plan column which should contain a link to the XML version of the plan is NULL. ...
4
votes
2answers
4k views

Query to find plan details for a specific stored procedure, searching by name

I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored ...
13
votes
4answers
12k 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....
2
votes
2answers
766 views

Error with sys.dm_db_index_physical_stats

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error. SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent FROM SYS.TABLES A INNER ...
2
votes
1answer
610 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 ...
18
votes
1answer
4k 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
467 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 ...
2
votes
1answer
50 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 ...
2
votes
1answer
3k 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
188 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 ...
2
votes
1answer
1k 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. ...
1
vote
2answers
589 views

sys.dm_sql_referencing_entities not returning results

No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities for a parent table, I get zero results. No error, just no results. USE [source]; GO SELECT * FROM sys....
0
votes
1answer
75 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
161 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 ...
13
votes
3answers
3k 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
553 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
617 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
893 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
688 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 ...
2
votes
1answer
1k 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. ...
10
votes
1answer
884 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
87 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 ...
6
votes
2answers
381 views

Doubts in a Glenn Berry Query ( CPU per Database )

Please, if this is not place to post questions like these, let me know and I will delete it. Inside the Glenn berry's Diagnostic queries, there is a query to show how much CPU a database is using. ...
2
votes
3answers
295 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
840 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 ...
5
votes
1answer
464 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 ...
3
votes
1answer
83 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. ...
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, ...
2
votes
1answer
248 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 ...
5
votes
1answer
335 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 ...