Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
1 answer
117 views

Is Analysis Services DMV query executing "on server" or "on database"

I am doing some DMV queries on SQL Server Analysis Service, like SELECT * FROM $system.DISCOVER_SESSIONS. This query will return data for all databases on the server, so it's not really database-...
PajLe's user avatar
  • 133
0 votes
2 answers
47 views

database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

how can I find the list of databases I actually can connect to? I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is ...
Marcello Miorelli's user avatar
0 votes
1 answer
88 views

Do stolen pages (as in sys.dm_os_performance_counters) report anything that sys.dm_os_memory_clerks does not?

I have been under the misconception that SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Stolen Server Memory%'; shows how much memory the OS is stealing from SQL Server. The ...
J. Mini's user avatar
  • 433
0 votes
1 answer
65 views

what sql server is holding in the used memory?

I have a query that shows me how much memory sql server is using, the settings like max memory and min memory, cpu, etc. here is the query: --===========================================================...
Marcello Miorelli's user avatar
0 votes
1 answer
65 views

why there are differences between sql server max memory and the actual utilization?

I have just asked infrastructure to add memory to a sql server box, the total memory now is 16Gb I have set the maximum memory in sql server to 12GB. when I run the memory query below: SELECT [...
Marcello Miorelli's user avatar
1 vote
2 answers
104 views

how to change the database create_date after a restore?

I just restored a database, but then I noticed the create_date remains from previous to the restore. is there a way I can change the create_date to be as the new database (after the restore)? this is ...
Marcello Miorelli's user avatar
0 votes
1 answer
81 views

DMV sys.dm_exec_trigger_stats - duplicate entries

I am running following select: SELECT tstats.object_id, tstats.database_id, tstats.sql_handle, DB_NAME(tstats.database_id) AS [db_name], OBJECT_NAME(tstats.object_id) AS trigger_name, tstats....
Sranda's user avatar
  • 360
1 vote
2 answers
87 views

last backup - how many backup files and where they are?

I am restoring a database, this database is very big and it has 6 backup files (meaning the backup is split into 6 smaller files) I can see an indication of that using the restore labelonly ...
Marcello Miorelli's user avatar
0 votes
1 answer
43 views

How to find the records that are waiting to be replicated?

I had a look using the replication monitor and it is showing me commands that are awaiting to be replicated: My question is - how do I find out what these commands are? I Have tried this query by Ben ...
Marcello Miorelli's user avatar
0 votes
1 answer
43 views

what is stopping my transactional replication services?

I will be straightforward and put my question before I add any other information, as I want to keep it objective and to the point. the question is: How to find out why the replication services are ...
Marcello Miorelli's user avatar
-1 votes
1 answer
162 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, ...
Dusan K's user avatar
4 votes
1 answer
363 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. ...
mwigdahl's user avatar
  • 263
-1 votes
1 answer
44 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 ...
QFirstLast's user avatar
1 vote
1 answer
235 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 ...
Niels Broertjes's user avatar
0 votes
1 answer
91 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.
Balazs Kiss's user avatar
0 votes
1 answer
148 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(...
Mark Freeman's user avatar
  • 2,181
0 votes
1 answer
2k 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 ...
user7660047's user avatar
6 votes
2 answers
360 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. ...
John K. N.'s user avatar
  • 17.9k
1 vote
2 answers
102 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....
variable's user avatar
  • 3,636
1 vote
1 answer
171 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 ...
Razvan Socol's user avatar
3 votes
1 answer
197 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 ...
Zikato's user avatar
  • 4,787
2 votes
0 answers
159 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 ...
Stockburn's user avatar
  • 419
4 votes
1 answer
226 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 ...
olichap's user avatar
  • 41
-1 votes
1 answer
51 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 ...
variable's user avatar
  • 3,636
0 votes
1 answer
286 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 ...
Newbie-DBA's user avatar
0 votes
1 answer
774 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?
SqlNightOwl's user avatar
3 votes
1 answer
1k 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 ...
Newbie-DBA's user avatar
0 votes
1 answer
54 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 ...
user1930901's user avatar
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 ...
Mac-Chiato's user avatar
0 votes
2 answers
608 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 whereas it used to complete in less than 5 seconds earlier. Query is just to fetch the right partition for ...
user6849192's user avatar
0 votes
1 answer
196 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 ...
LCJ's user avatar
  • 900
0 votes
1 answer
206 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 --> ...
BeginnerDBA's user avatar
  • 2,170
1 vote
0 answers
125 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 ...
Andy Eggers's user avatar
8 votes
1 answer
421 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 ...
ruskind's user avatar
  • 253
1 vote
1 answer
465 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 ...
Keith Rivera's user avatar
4 votes
1 answer
663 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. ...
SE1986's user avatar
  • 1,970
2 votes
1 answer
385 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 ...
Ianwork's user avatar
  • 81
3 votes
1 answer
8k 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 [...
Marcello Miorelli's user avatar
0 votes
1 answer
670 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 ...
J.D.'s user avatar
  • 37.9k
0 votes
1 answer
3k 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 ...
J.D.'s user avatar
  • 37.9k
1 vote
0 answers
304 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,...
TheGameiswar's user avatar
  • 2,989
2 votes
1 answer
75 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 ...
K. Zakoski's user avatar
2 votes
0 answers
275 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 ...
DMDM's user avatar
  • 1,738
1 vote
1 answer
306 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 ...
J.D.'s user avatar
  • 37.9k
0 votes
1 answer
109 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?
J.D.'s user avatar
  • 37.9k
1 vote
1 answer
432 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 ...
Shekar Kola's user avatar
  • 2,427
13 votes
1 answer
3k 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 ...
J.D.'s user avatar
  • 37.9k
1 vote
1 answer
975 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 ...
J.D.'s user avatar
  • 37.9k
0 votes
1 answer
2k 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.)
J.D.'s user avatar
  • 37.9k
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 ...
Josh Greenlaw's user avatar

1
2 3 4 5