Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
2answers
38 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
38 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
35 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
102 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
50 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
23 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
19 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
57 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
1answer
33 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
18 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
33 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
31 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
54 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
25 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
44 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
67 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
55 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
53 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
56 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 ...
1
vote
1answer
97 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 ...
2
votes
1answer
159 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
56 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
157 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
56 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
143 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
249 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 ...
5
votes
1answer
240 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
52 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 ...
0
votes
0answers
79 views

dm_exec_procedure_stats returning incorrect execution statistics on SQL Server 2008 R2

On Microsoft SQL Server 2008 R2 SP3, the DMV sys.dm_exec_procedure_stats is reporting incorrect statistics for me. It is reporting statistics of a regular INSERT statement against a stored procedure....
2
votes
1answer
2k 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 ...
0
votes
0answers
27 views

Phantom results from a DM or Missing results from Profiler Trace?

I was using a table that logs the results of querying dm_exec_procedure_stats every minute to identify activity of stored procedures on one of my servers. Basically it compares the database, object ...
6
votes
1answer
416 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 ...
3
votes
2answers
598 views

Why aren't some spid values showing up in sys.dm_exec_requests?

I am executing the following dmv query against SQL Server 2014 to find all activity on a server and any blocking activities: SELECT Distinct ... FROM sys.dm_exec_requests er OUTER APPLY sys....
7
votes
1answer
116 views

Do values in `sys.dm_os_wait_stats` reset to 0 or stop accumulating when they hit the max value?

When looking at sys.dm_os_wait_stats, the following columns are defined as BIGINT waiting_tasks_count wait_time_ms max_wait_time_ms If any of those values go above 9,223,372,036,854,775,807, do ...
-1
votes
2answers
353 views

Is there any method of looking for Head Blocking history in SQL 2012?

Is there any method of looking for Head Blocking history via DMVs. I have been able to find the subsequent wait details via Extended Events but there doesn't seem to be much information about what ...
0
votes
1answer
74 views

is there any reason to explicitly grant the server permission CONNECT SQL?

I have been using this script from Kenneth Fisher to get the server permissions. -- Server Permissions SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, Grantor.name ...
0
votes
1answer
500 views

SQL Server Average Lock Wait time Historical data

Monitoring software has indicated average lock wait times rising to 500ms during the night. Is there any way of confirming which locks these were as we currently do not log any performance data to a ...
2
votes
2answers
205 views

Confusion about connection_id in SQL Server DMV

I am confused about the connection_id column of sys.dm_exec_connections DMV: SELECT connection_id FROM sys.dm_exec_connections; If I connect to an instance of SQL Server using SSMS, should it be one ...
6
votes
1answer
152 views

How to include “Actual Row” counts in Execution plans pulled from the Query Cache or Query Store?

Obviously seeing the actual execution plan is a big deal when tuning, and normally I enable the query plan output via SET STATISTICS XML ON and run whatever query needs some TLC, but how can I see ...
5
votes
1answer
458 views

Is there DMV for seeing view access count?

I saw it once during a presentation when someone showed a query to get a count of how many times views have queried in a SQL server. I don't remember if it was from DMV or a combination of some other ...
2
votes
1answer
617 views

sys.dm_exec_sql_text returns NULL in dbid, objectid, and number columns, but not text

This is something that's bugged me though has never caused any real issues as I can generally locate this information by other means, but can someone explain why NULL may be returned in the dbid, ...
0
votes
1answer
85 views

Is there a way to show index_advantage more friendly? [closed]

Is there a way to show index_advantage more friendly like 99.54%? SELECT TOP 25 user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage] FROM sys....
0
votes
1answer
645 views

How to see the errors on AlwaysOn using T-SQL?

when I run the following query if you run the query below on bocss it says it is not healthy select * from sys.dm_hadr_availability_replica_states it tells me my alwayson is not healthy. ...
5
votes
3answers
1k views

Batch Requests/sec reported by DMV millions of times larger than Activity Monitor

I have verified through SQL Server Profiler that my request per-second are around 30 request/s as is corroborated by SSMS Activity Monitor but sys.dm_os_performance_counters is reporting hundreds of ...
1
vote
1answer
1k views

SQL Server Full-Text Index crawl: Is it done or not?

I am on SQL Server 2012. I have one table with one full-text index. That index was dropped and recreated around 8am due to a change in the datatype of the indexed column from TEXT to VARCHAR(MAX). ...
1
vote
1answer
141 views

sys.dm_db_partition_stats - incorrect row count for InMemory tables

I have a problem with sys.dm_db_partition_stats DMV. When having InMemory tables (with hash indexes, columnstores, classical b-tree) and trying to get row count via this query there is 0 displayed for ...
1
vote
2answers
62 views

Proc caching on read only db's/replicas

I've been given the task of finding which stored procs are being called in our database. To that end I started querying sys.dm_exec_cached_plans. All is well and good, until I go over to our ...
1
vote
1answer
259 views

What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans?

As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached ...
0
votes
1answer
42 views

DMV CONNECTION_HOST_APPLICATION field is NULL

I am trying to monitor cubes and expressions of Analysis Services using DMV queries that are stored on Server's RAM. I found many useful information about connections on connection's table of SSMS's ...
2
votes
1answer
124 views

Is there a DMV that shows the aggregate logical reads on a SQL server?

I want to use this metric to compare 2 servers. I've looked at sys.dm_io_virtual_file_stats, but this is missing logical. Also looked at query_stats, but that only includes cached plans.