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
4
votes
1answer
169 views

SQL 2019 CU13 breaks Cluster_nodename

With total disregard to my paranoia about this unlucky number we installed CU 13 for SQL 2019 in one of our environments (in defense of the number 13 I have the same issue with 14). The progression ...
-1
votes
1answer
35 views

Which dmv can help identify the cost of index maintenance for exisitng and missing indexes?

I want to identity: Indexes that are costly to maintain. Missing indexes along with their maintenance cost. By maintenance I mean - the index maintenance during insert/update/delete. Which dmv can ...
0
votes
1answer
47 views

How can i get AG dashboard values via SQL Query across all supported versions of SQL server

I am trying to ease the monitoring of availability groups across multiple SQL servers. Came across few dmvs which got me the data. But cannot figure out some important columns which i am seeing in ...
0
votes
1answer
47 views

Is there a way to determine the command timeout for an executing spid

Is there a DMV or other mechanism available that returns the command timeout for an executing session_id?
3
votes
1answer
86 views

Database Unallocated Space - Correct way to measure

I am confused over the web regrading few scripts on calculating the database size and free space within same. Most of the scripts over the web uses sys.databases_files dmv to calculate free space like ...
0
votes
1answer
37 views

Join duplicating the data - SQL Server

I am working with SQL Server Always On and I am querying the data as follows: SELECT name as AGname, replica_server_name, CASE WHEN (primary_replica = replica_server_name) THEN 1 ELSE '0' END AS ...
0
votes
0answers
37 views

Compile Memory - Relationship between XE query_optimizer_memory_gateway and DMV sys.dm_exec_query_optimizer_memory_gateways

I’m studying and testing what happens during the compilation process of a query and how the Compile Memory is treated. You can have a look here (https://techcommunity.microsoft.com/t5/sql-server/multi-...
1
vote
0answers
39 views

select from Dynamic Management View causes blocked processes [duplicate]

we have troubles regarding querying from a dynamic view in SQL Server. We basically check for an existing object lock on a table (CHANGELOG) from sys.dm_tran_locks with the @@spid in a stored ...
0
votes
1answer
268 views

Simple query on sys.dm_db_partition_stats is running for 30 minutes

Simple query on sys.dm_db_partition_stats on user database is running for close to 30 mins where as it used to complete in less than 5 seconds earlier. Query is just to fetch the right partition for ...
0
votes
1answer
24 views

Find Unique Index that does not have Unique Constraint

I need to find list of unique indices that does not have unique constraint on that column. How to achieve this? I know, following query will give me unique constraints. SELECT * FROM ...
0
votes
1answer
34 views

How do get correct instance name on Multi instance cluster

Just a quick question and somehow unable to figure it out. We have a Active Active cluster or should i say Multi instance cluster as below. node A and B are part of same window cluster Node A --> ...
1
vote
0answers
58 views

My question is in a select statement how do you return the RID along with the table data?

I need your help with a concept I want to show developers, that concept is to help them understand how SQL Server retrieves data from a table. In the event that a developer does not use a clustered ...
8
votes
1answer
279 views

Improve the poor performance of the sys.dm_db_stats_properties DMV

We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL ...
1
vote
1answer
125 views

Results in sys.dm_db_index_usage_stats

I have this query below that I use to clean up indexes that are not being used anymore to reduce runtime and overhead during index maintenance and during inserts. On some tables that I've ran this ...
4
votes
1answer
278 views

Cursoring over sys.databases skips databases

I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem: I have the following code to perform database backups. ...
2
votes
1answer
157 views

sys.dm_db_file_space_usage not returning correct values in SQL 2019

Scripts I use for database space metrics on our SQL Server do not return the correct values for allocated_extent_page_count and unallocated_extent_page_count from sys.dm_db_file_space_usage when run ...
3
votes
1answer
1k views

where can I find the DNS name associated to the listener of an Availability Group?

I have an availability group that has a listener. The listener is called SQLPROD as you can see on the picture below. It uses port 1433 When I run the following script: ALTER AVAILABILITY GROUP [...
0
votes
1answer
57 views

Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache?

I ran a home-brew trace for the length of 1 day that captured a few things that were running. The trace includes the specific query text, the parent entity text of that query, the Plan Handle, and the ...
0
votes
1answer
248 views

How can I check index fragmentation in the quickest way possible? [duplicate]

Checking index fragmentation in my database seems unruly slow. Regardless if I use the DMV sys.dm_db_index_physical_stats (for a specific database, table, or even index) or if I use the SSMS Index ...
1
vote
0answers
190 views

Can not run Sys.dm_exec_requests during backup restore

We do database creation through an application,i am seeing below error when running below DMV(connection context is master) query ran : SELECT session_id as SPID,wait_type,wait_resource,...
2
votes
1answer
54 views

Are Clustered Index Usage Stats skewed since they are referenced in Non-Clustered Keys?

When using tools like SP_BlitzIndex and looking at usage stats for indexes. Would clustered index usage be skewed in any way since they are secret keys in all non-clustered indexes and would be read ...
2
votes
0answers
163 views

sys.database_files DMV, max_size column

I was just modifying my script that I use to get size of database file and wanted to use max_size to display if a max size was set for a database file. As per Microsoft Docs, the column description ...
1
vote
1answer
158 views

What could be the cause for my high disk IO latency (from sys.dm_io_virtual_file_stats DMV)?

These are the results from querying the sys.dm_io_virtual_file_stats DMV. (I add some calculated fields for clarity). I'm finding certain basic operations in Database 7 are bottlenecking core ...
0
votes
1answer
40 views

Is there a way to determine what caused the last growth operation of my database (MDF file)? [duplicate]

If I don't already have any logging in place, is there any way to use the DMVs or anything else on SQL Server to determine which query / event caused my database's last grow operation?
1
vote
1answer
283 views

What does "sample_ms" and "num_of_reads" means in "sys.dm_io_virtual_file_stats". what is the interval time of stats

As per MS-Docs it returns the Number of milliseconds since the computer was started, but i'm getting different result when i compare with actual server start time in sys.dm_os_sys_info. Here are the ...
12
votes
1answer
1k views

How to get the parameter values of a stored procedure that's in the middle of execution?

Is there a way to get the values of the parameters that were passed in to a stored procedure while the stored procedure is in the middle of execution? (I'm trying to debug an actively running stored ...
1
vote
1answer
346 views

Does querying the sys entities and DMVs lock the underlying tables and what happens when SQL Server tries to write to them?

If I do a vanilla SELECT * FROM sys.tables or SELECT * FROM sys.dm_exec_query_stats are those tables getting locked since I'm reading data from them? If so, what happens when SQL Server ties to write ...
0
votes
1answer
355 views

Is there a way to figure out an executing query's plan handle by the query text?

Is there a way to determine the plan handle for the query plan of an executing query by that query's text? Perhaps in any of the DMVs? (I'm trying to find the query stats on an ad-hoc query.)
9
votes
1answer
674 views

CPU Usage Incorrect in sys.dm_os_ring_buffers

I'm running a cloud VPS with a SQL Server instance. Because it's for personal use, I'm using express edition (I can't use developer edition because I technically have production apps running on it and ...
1
vote
2answers
306 views

How to query SQL Server Memory Manager performance counters in DMV?

Is there any system catalog views or DMVs that can be used to query (in SSMS for example) below performance counters from SQL Server:Memory Manager ? Free Memory (KB) Target Server Memory Total ...
0
votes
0answers
112 views

How to find the MAXSIZE for user databases from a connection to master in Azure SQL Database?

While it is simple enough to SELECT DATABASEPROPERTYEX(@DatabaseName, 'MaxSizeInBytes') when connected to a user database, I have been unable to find how to get this information for all of the ...
-2
votes
1answer
62 views

Need detailed info regarding sys.dm_os_buffer_descriptors

I tried to search a lot on internet about the dmv,but didn't get much. I'm unable to get,Meaning of some of the columns of this dmv. Which are: pg_level allocation_unit_id row_count numa_node Please ...
10
votes
1answer
241 views

What is a "hashed transaction" in SQL Server Replication terminology?

sys.dm_repl_tranhash contains information about the hashing of transactions, but I have difficulties understanding the terminology. What is a "hashed transaction"?
2
votes
0answers
98 views

the mystery of the table showing up in the subscription, but not in the publication

TL;DR: This question refer to sql server 2016, transactional replication I've got a table that shows up in the subscription but not in the publication. Here are the details: In one of my servers ...
0
votes
1answer
473 views

SSMS 2014 does not launch Graphical Execution Plan from clicking hyper-linked query plan - ShowPlanXML

I am working with an older version of Microsoft SQL Server Management Studio 2014. I run the following query: SELECT TOP 10 Q.* FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan( qs....
3
votes
1answer
2k 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 ...
11
votes
2answers
148 views

What are the practical impacts of the sys.dm_exec_query_stats warning in the documentation?

The documentation for sys.dm_exec_query_stats states the following: An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the ...
1
vote
1answer
52 views

Datetime columns in sys.dm_db_mirroring_connections are an hour behind

I have database mirroring set up on my SQL Server instance. If I run SELECT * FROM sys.dm_db_mirroring_connections I get information about the mirroring connections, however, all the time columns in ...
2
votes
1answer
116 views

SQL Server Audit File Metrics Information

I'm currently working in an environment that requires we enable SQL Server Audit, enterprise-wide. We generate .sqlaudit files as we have retention and access guidelines, and I'm finding that we need ...
4
votes
1answer
57 views

How to know which DMVs relate to each other?

Is there any way to know which DMVs/DMFs are related to each other? Some of them are well known or obvious, like sys.dm_exec_connections JOIN sys.dm_exec_sessions ON session_id = session_id, or the ...
1
vote
2answers
70 views

Inline function Plan Properties in SQL Server 2008

Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is ...
0
votes
0answers
154 views

sys.dm_db_index_usage_stats showing incorrect figures for last_user_update

As the title suggests, I seem to be getting unexpected readings from the last_user_update column in the index_usage_stats DMV. It seems to update the datetime value everytime I run a script inserting ...
6
votes
2answers
666 views

How to find if a column is referenced in a computed column?

I'm trying to mass retype columns. That means first dropping and recreating any constraints they are part of. I found columns referenced by these constraints Foreign Keys, Primary Keys, Indexes, ...
1
vote
2answers
350 views

sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way?

I have this table in one of my databases: IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL DROP TABLE [dbo].[repl_Gender_Type] GO CREATE TABLE [dbo].[repl_Gender_Type] ( [Gender_TypeID] CHAR(...
0
votes
1answer
766 views

Frequently Querying the DMV - sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_exec_requests

Could frequently (e.g. 5 seconds) querying the below DMVs from a custom tool to get the connection info impact performance on SQL server? I understand this would give a snapshot at that point. SQL ...
2
votes
1answer
182 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 ...
-2
votes
1answer
208 views

Centralized Logs (error, deadlocks, slow queries) For SQL Servers

I want to establish a centralized log for many SQL Servers, similar to the products Graylog or elk stack. What is the best and performant way to send these kind of logs to a log collector? I ...
-1
votes
1answer
78 views

Some sys.dm_io_virtual_file_stats columns interpretation needed

I need someone to help me understand two columns of dm_io_virtual_file_stats. Looking to my results of that procedure running and filtered against one database, I can see that the size_on_disk_bytes ...
0
votes
2answers
247 views

How to find out the service accounts for each of my instances in the current server?

I have the following query that gives me one line for each sql server instance installed in the current server: --list the instances in the current server --marcello miorelli --16-sep-2016 ...
1
vote
1answer
220 views

Print Column Names for Synonym for Table in Another Database

I am utilizing one database A. How do I print out the columns names for synonym for table in another database B? create table DatabaseB.dbo.Customer ( CustomerId int primary key, ...