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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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
32 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
59 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
37 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
38 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
53 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
17 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
89 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
47 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
85 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
48 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
116 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
108 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 ...
4
votes
1answer
193 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
35 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
51 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....
1
vote
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
249 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 ...
2
votes
2answers
367 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
113 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
268 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
57 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
313 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
157 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
117 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
457 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
432 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
62 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
442 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
959 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
120 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
58 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
197 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
38 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
112 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.
4
votes
1answer
48 views

Is there a way to find out what queries are run the most against a particular table?

We have been experiencing issues with slowness regarding our Master Data Services application. So we have looked to see which tables have the highest value of range scans, singleton lookups, page lock ...
0
votes
0answers
37 views

NULL value in query_plan column from the sys.dm_exec_query_plan DMV [duplicate]

I've written a query to return data from the sys.dm_exec_query_plan DMV, but for a specific stored procedure the query_plan column which should contain a link to the XML version of the plan is NULL. ...
4
votes
2answers
2k views

Query to find plan details for a specific stored procedure, searching by name

I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored ...
13
votes
4answers
6k views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
2
votes
2answers
359 views

Error with sys.dm_db_index_physical_stats

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error. SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent FROM SYS.TABLES A INNER ...
2
votes
1answer
477 views

Wrong Last Execution Time sys.dm_exec_procedure_stats

I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I ...
16
votes
1answer
2k views

Plan cache size and reserved memory

When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB. A query that joins sys.dm_exec_cached_plans and sys....
6
votes
2answers
336 views

Running query against DMVs for Query Stats and Execution Plans joined to sys.databases

I originally posted this as a different question in Twitter #sqlhelp, but wanted to post it here differently. I'm trying to create a job that tracks worst performing statements for various statistics ...
2
votes
1answer
48 views

SQL Server Index recomended but already there (DMVs)

I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc. Some examples are Index ON X ( Y ) and Table ...
1
vote
1answer
2k views

How do I tell which Edition and Version my SSAS Instance is running?

In SQL Server on the database engine, you can run a SQL query like this to get the server's version info like Edition and Version, and Update Level, etc. SELECT @@VERSION What is the MDX equivalent ...
5
votes
2answers
163 views

What sort of exec requests don't have a sql handle? (sys.dm_exec_requests)

I have crafted a query against SQL Server 2012 that looks like the following: SELECT r.session_id, r.sql_handle, t.text -- more details excluded for brevity FROM sys.dm_exec_requests r CROSS APPLY ...
1
vote
1answer
814 views

sys.dm_exec_procedure_stats reset [duplicate]

I want to get stats from the sys.dm_exec_procedure_stats view. When I check the records from months ago, I see that after a point of time its stats reset daily, but there have been no server resets. ...
1
vote
2answers
415 views

sys.dm_sql_referencing_entities not returning results

No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities for a parent table, I get zero results. No error, just no results. USE [source]; GO SELECT * FROM sys....
0
votes
1answer
65 views

Does dm_exec_procedure_stats.total_worker_time include signal waits?

Does sys.dm_exec_procedure_stats.total_worker_time include signal waits or only time spent by procedure on scheduler? The reason of question - when number of cores was increased on the server signal ...