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
0
votes
1answer
86 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
0answers
9 views

No Data for RING_BUFFER_SCHEDULER_MONITOR

I am attempting to add a query for monitor recent CPU usage for our SQL servers into an SSRS dashboard. I have encountered numerous examples online recommending querying the sys.dm_os_ring_buffers DMV,...
0
votes
1answer
17 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
27 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
57 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 ...
0
votes
0answers
19 views

Query store : same sql text with different query_text_id?

I'm investigating some issues with parameterization for our app, which uses Sql Azure. I'm using the query store and analyzing the following 2 queries, which are similar. See following image: There ...
8
votes
1answer
176 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 ...
0
votes
1answer
42 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 ...
2
votes
1answer
107 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
62 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
148 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
37 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
0answers
25 views

Is there a way to access the LRU information in SQL Server Buffer Pool? [duplicate]

In SQL Server (2017) you can find all pages that are loaded into memory, and the page life expectancy etc. But is there a way to see the last time a page in the pool was actually read? The process ...
0
votes
1answer
79 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
116 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
51 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
82 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
67 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
28 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
173 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
683 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
88 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
76 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
487 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
158 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
74 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
51 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
226 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
43 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
250 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
vote
1answer
1k 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 ...
10
votes
2answers
137 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
42 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
55 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
56 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
56 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
116 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
456 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
246 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
533 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
96 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
161 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
66 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
143 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
145 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, ...
1
vote
1answer
38 views

Print Column Names for Table in Another Database

I am in one database. How do I print out the columns names for table in another database? create table dbo.Customer ( CustomerId int primary key, CustomerName varchar(100), ZipCode ...
2
votes
2answers
1k views

Generate SQL Table DDL from a View

How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql (1) can remove primary key/constraint, (2) ...
1
vote
0answers
990 views

service broker - poison message - how to handle? how to enable poison messase handling?

I am getting constantly this error message on service broker: POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411 select top 1000 * from ...
0
votes
0answers
84 views

is there a way to find out what route and endpoint my service broker session is using?

I have been trying in many way, from sys.conversation_endpoints to find out which route and which endpoint my service broker session is using. SELECT conversation_handle, is_initiator, s.name as '...
2
votes
0answers
123 views

is there any advantage when troubleshooting service broker, to create the queue with retention=ON?

from CREATE QUEUE (Transact-SQL): RETENTION Specifies the retention setting for the queue. If RETENTION = ON, all messages sent or received on conversations that use this queue are retained in ...