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.

22 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
461 views

Are the following statements concerning sys.dm_exec_requests true?

Parallel plans mean that cpu_time can be greater than total_elapsed time. Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread. ...
2
votes
1answer
68 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 ...
2
votes
0answers
87 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 ...
2
votes
0answers
44 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 ...
2
votes
0answers
127 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 ...
2
votes
0answers
46 views

Query to get Object_ID and Dimension_Name from DMV

I am looking for a DMV query which contain Dimension_Name and Object_ID together. I have System.mdschema_dimensions to get the Dimension Unique name and System.discover_object_activity to get the ...
2
votes
0answers
114 views

Why is sys.dm_exec_connection.connect_time later than sys.dm_exec_sessions.login_time for user processes for some cases?

I executed the query on my production server. select s.session_id, s.login_time, s.is_user_process, c.session_id, c.connect_time, case when c.Connect_Time ...
2
votes
0answers
97 views

DMV Index proposals still reported as missing

We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues. After running the DMV query: ...
2
votes
0answers
434 views

plan_generation_num keeps incrementing, why?

I have a query that is executed using sp_executesql code is as follows DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3); SET @ppCityID = CAST(@cityID AS NVARCHAR(3)); exec sp_executesql N'...
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 ...
1
vote
0answers
122 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,...
1
vote
0answers
1k 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 ...
1
vote
0answers
152 views

Cost / Capacity Planning: Quantify Data Ingress / Egress

To evaluate the migration of an on-prem SQL Server to a cloud-hosted VM, I want two fundamental pieces of information: The list of clients that access the on-prem DB Server. I'd like to know: client ...
1
vote
0answers
90 views

Nonclustered indexes

I have a question on index Our Application is throwing the following SQL error Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...
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
0answers
21 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 ...
0
votes
0answers
77 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 ...
0
votes
0answers
117 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 ...
0
votes
0answers
90 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 '...
0
votes
1answer
369 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 ...
0
votes
0answers
89 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 ...
0
votes
0answers
422 views

When is transaction removed from sys.dm_tran_active_transactions?

We are having some issues with clients getting Transaction Timeout when connecting to our clustered SQL Server 2008 R2. To investigate the problems I have been digging into sys....