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.
208
questions
-1
votes
1
answer
81
views
DMV sys.dm_exec_procedure_stats sometimes returns weird (incorrect?) values in successive object snapshots
I'm working on a small project to monitor activity and execution metrics of user stored procedures. Periodically (every 10 minutes or so), I'm taking snapshots of the sys.dm_exec_procedure_stats DMV, ...
- 3
4
votes
1
answer
252
views
Selecting from sys.dm_tran_locks takes 100+ ms
Our application uses SQL Server application locks and we have cases where we need to see whether a given lock exists or not. To do this, we retrieve and filter information from sys.dm_tran_locks.
...
- 263
-1
votes
1
answer
19
views
dm_exec_query_stats returning all NULLs for Worker and Elapsed time
I am executing the following statement on a customer's SQL Server. The version is Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) .
declare @Milliseconds int
SELECT top 200 *
--sum([Total Worker ...
- 373
1
vote
1
answer
100
views
SET SINGLE_USER stuck on wait type PRINT_ROLLBACK_PROGRESS
I want to drop a database on one of our systems (SQL Server 2016 SP2 CU17), but the process to set the database in single user mode is stuck on the wait type PRINT_ROLLBACK_PROGRESS. I use the ...
- 517
0
votes
1
answer
71
views
Who was using a spid 2 hours earlier in SQL Server?
I have a specific spid from 2 hours ago, and would like to know who was using it.
Is this possible? Is this stored in the cache, some DMV, Ringbuffer? SQL Server 2019.
Thank you in advance.
- 13
0
votes
1
answer
125
views
Why is sqlserver_start_time not equal to sqlserver_start_time_ms_ticks - ms_ticks in sys.dm_os_sys_info?
Why do these two queries produce different results (but within about a minute of each other)?
SELECT StartTime = sqlserver_start_time
FROM sys.dm_os_sys_info;
SELECT StartTime = DATEADD(...
- 2,149
0
votes
1
answer
526
views
How to get definition of partition function programmatically?
I have tables partitioned by date with various frequency space (multi-years, years, quarters, months etc.)
sys.partition_functions gives name and function_id.
Is there a way to query the actual ...
- 11
6
votes
2
answers
263
views
Querying the sys.dm_db_log_info() function while reducing to the max
I'm currently querying the sys.dm_db_log_info() DMV to retrieve the VLFs from a database to determine when I can shrink, reorganise and reduce the amount of fragmented (10 MB VLFs) in the TLOG file.
...
- 17.1k
1
vote
2
answers
92
views
What is the difference between latency calculation using sys.dm_os_buffer_descriptors.read_microsec vs sys.dm_io_virtual_file_stats?
I'm trying to calculate the disk io latency (time required to fetch the page from disk into memory) for a SQL database.
Following are 2 approaches I am exploring:
Approach 1 - Using sys....
- 3,360
1
vote
1
answer
126
views
New DMVs related to Availability Groups in SQL Server 2019 CU9
In SQL Server 2019 since Cumulative Update 9, there are two new DMVs related to Availability Groups:
sys.dm_hadr_cached_replica_states
sys.dm_hadr_cached_database_replica_states
I found out about ...
- 338
3
votes
1
answer
126
views
Finding the names of objects in the Resource database [duplicate]
I'm looking at the sys.dm_exec_function_stats view to find scalar function stats. That view also includes rows from a database with an id 32767, which I've found is the Resource database.
Some of ...
- 3,891
2
votes
0
answers
145
views
Sys.dm_os_performance_counters DMV row counts
We have noticed an odd issue on two servers in a 3 node SQL Server 2017 cluster running Always On Availability Groups.
When checking the DMV sys.dm_os_performance_counters the row counts are totally ...
- 427
4
votes
1
answer
219
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 ...
- 41
-1
votes
1
answer
47
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 ...
- 3,360
0
votes
1
answer
180
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 ...
- 762
0
votes
1
answer
550
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?
- 45
3
votes
1
answer
904
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 ...
- 762
0
votes
1
answer
49
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 ...
- 349
1
vote
0
answers
40
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 ...
- 11
0
votes
1
answer
541
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
1
answer
145
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 ...
- 899
0
votes
1
answer
146
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 --> ...
- 2,169
1
vote
0
answers
98
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 ...
- 11
8
votes
1
answer
378
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 ...
- 243
1
vote
1
answer
358
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 ...
- 617
4
votes
1
answer
559
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.
...
- 1,898
2
votes
1
answer
340
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 ...
- 81
3
votes
1
answer
6k
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 [...
- 15.8k
0
votes
1
answer
388
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 ...
- 34.2k
0
votes
1
answer
2k
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 ...
- 34.2k
1
vote
0
answers
277
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,949
2
votes
1
answer
74
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 ...
- 31
2
votes
0
answers
246
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,738
1
vote
1
answer
237
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 ...
- 34.2k
0
votes
1
answer
103
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?
- 34.2k
1
vote
1
answer
401
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 ...
- 2,397
12
votes
1
answer
2k
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 ...
- 34.2k
1
vote
1
answer
799
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 ...
- 34.2k
0
votes
1
answer
1k
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.)
- 34.2k
9
votes
1
answer
1k
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
2
answers
492
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 ...
- 5,302
0
votes
0
answers
172
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,149
-2
votes
1
answer
91
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 ...
- 348
10
votes
1
answer
265
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,120
2
votes
0
answers
226
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 ...
- 15.8k
0
votes
1
answer
733
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....
- 1
3
votes
1
answer
4k
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 ...
- 665
11
votes
2
answers
207
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 ...
- 31.8k
1
vote
1
answer
59
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 ...
- 1,898
2
votes
1
answer
269
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 ...
- 9,282