We’re rewarding the question askers & reputations are being recalculated! Read more.

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
9
votes
1answer
374 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
30 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
59 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
34 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
202 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
37 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
64 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
130 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
110 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
29 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
31 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
53 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
49 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
66 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
264 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
107 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
243 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
56 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
129 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
54 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
61 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
81 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
37 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
298 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
459 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
62 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
61 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 ...
0
votes
2answers
317 views

How to script all the service broker elements for each database it is on, in the current server?

I have service broker running on different servers. Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there. For the next time I need to ...
2
votes
2answers
778 views

availability groups - checking for primary server - cannot call a procedure with parameters

In my servers that are involved in alwayson availability groups if there are jobs running, inside the jobs I always test whether the current server is the primary in the availability group. So the ...
0
votes
1answer
21 views

Monitor volume of data returned to the client application

I've identified ASYNC_NETWORK_IO as our top wait by some distance averaging between 70-77% of wait time for our client application SQL Server. This has been put forward towards the development team ...
0
votes
1answer
39 views

how to optimise this group by on sys.dm_exec_query_stats?

I would like to know the last 1008 queries run in my systems and with this intent I have put this query together: -- get the last 1008 queries run SELECT top 1008 execquery.last_execution_time AS [...
1
vote
1answer
165 views

The current security context cannot be reverted

Msg 15199, Level 16, State 1, Line 29 T he current security context cannot be reverted. Please switch to the original database where Execute As was called and try it again. execute as login=...
2
votes
1answer
82 views

What connection do internal sessions belong to in MS SQL Server?

By querying select * from sys.dm_exec_connections I do not see corresponding connections for internal sessions, about the existance of which I know from sys.dm_exec_sessions DMV. My questions is ...
2
votes
0answers
43 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 ...
0
votes
2answers
233 views

Find out recovery model changes

I'd like to work around instances where a database were changed to simple and then reverted back to full or bulk logged recovery models. This is for my backup maintenance plan, as obviously any ...
1
vote
4answers
219 views

replace all references to a drive inside the jobs to another drive

I have a server where all my sql server backups are currently being saved to the G: drive. Now there is no enough disk space on drive G: to keep backups for the last 5 days. I have asked to add ...
1
vote
3answers
615 views

How to get the definition of a database trigger?

when I run the following script in one of my databases: SELECT * FROM SYS.triggers when I try to find out about sys.triggers Contains a row for each object that is a trigger, with a type of TR ...
3
votes
1answer
94 views

Group together similar queries in sys.dm_exec_sql_text

I'm analyzing a 2008 Instance that supports a 3rd party app. The app will generate the SQL Code then send it to the Database as an ad-hoc query. I'm using this query (based on a Glenn Berry script): ...
-1
votes
1answer
58 views

Analysis of queries or stored procedures which ran on the database for last 15 days [duplicate]

We had a site slowdown between 11.57 am and 12:02 pm on 6th Aug 2018, To trace back the queries which were ran our production database (SQL Server Standard Edition 2012) and which might have caused a ...
2
votes
1answer
856 views

List primary replicas and non-HA databases - excluding forwarder replicas

When asking which databases are writable on any given server, I've been using the below snippet to list PRIMARY replica databases and databases that do not participate in an AG. Now that I've ...
3
votes
1answer
363 views

How to find Stored Procedures With Recompile?

In my databases I have some Stored procedure with recompile The way I find them is: SELECT OBJECT_NAME(ID)AS SP_NAME,* FROM SYSCOMMENTS WHERE TEXT LIKE '%WITH RECOMPILE%' when I look at: select * ...
1
vote
0answers
104 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 ...
4
votes
3answers
461 views

Poor Performance Running dm_exec_query_stats

I have a job that executes every 5 minutes to gather info from DMVs that has not changed in over 2 years, normally running under a minute. All of a sudden starting last week, with no changes deployed,...
3
votes
2answers
125 views

how to find the database context of the last sql statement?

I would like to find out from the code below, what was the name of the database where the last command was run. In this case I am looking for my_other_database. his there any way of finding this? Is ...
1
vote
1answer
280 views

SQL Memory Usage Cumulative for each Database

The following query shows how to find Current view of Buffer Memory used by each database. How do I find Average/Cumulative Memory buffer usage of each database since the last SQL server restart, not ...
-2
votes
1answer
739 views

SQL CPU Current% By Database

In Question How to get CPU usage by database for particular instance? Solomon states: The query using sys.sysprocesses is only looking at right now. And the query using sys.dm_exec_query_stats is ...
6
votes
1answer
348 views

Should I use SQL Server DMVs with NOLOCK

I am trying to monitor live performance data and usage, which cannot be tracked with PerformanceMonitor. What are the consequences of reading DMVs in a production live OLTP database? : Eg: sys....
2
votes
0answers
95 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
1answer
5k views

Grant View Server State - is it possible for a none SA user to have in Azure SQL?

I'm trying to set up some scheduled jobs which will collect some server stats, using system DMV's in SQL Server Azure. If a none SA users uses 'sys.dm_os_schedulers', they get an error (below), query ...
6
votes
1answer
814 views

Is it possible to see which SPID uses which scheduler (worker thread)?

We recently had a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads. Got the message: The thread pool for AlwaysOn Availability Groups was unable ...