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
2 answers
28 views

database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

how can I find the list of databases I actually can connect to? I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is ...
0 votes
1 answer
82 views

Do stolen pages (as in sys.dm_os_performance_counters) report anything that sys.dm_os_memory_clerks does not?

I have been under the misconception that SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Stolen Server Memory%'; shows how much memory the OS is stealing from SQL Server. The ...
0 votes
2 answers
595 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 whereas it used to complete in less than 5 seconds earlier. Query is just to fetch the right partition for ...
0 votes
1 answer
65 views

what sql server is holding in the used memory?

I have a query that shows me how much memory sql server is using, the settings like max memory and min memory, cpu, etc. here is the query: --===========================================================...
15 votes
4 answers
27k 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....
0 votes
1 answer
61 views

why there are differences between sql server max memory and the actual utilization?

I have just asked infrastructure to add memory to a sql server box, the total memory now is 16Gb I have set the maximum memory in sql server to 12GB. when I run the memory query below: SELECT [...
1 vote
2 answers
95 views

how to change the database create_date after a restore?

I just restored a database, but then I noticed the create_date remains from previous to the restore. is there a way I can change the create_date to be as the new database (after the restore)? this is ...
0 votes
1 answer
81 views

DMV sys.dm_exec_trigger_stats - duplicate entries

I am running following select: SELECT tstats.object_id, tstats.database_id, tstats.sql_handle, DB_NAME(tstats.database_id) AS [db_name], OBJECT_NAME(tstats.object_id) AS trigger_name, tstats....
2 votes
3 answers
1k views

Database Last Usage

Is there a way to find out when a database was last accessed on SQL Server 2000? By this I mean, had a table read, a stored procedure called etc... I have a list of databases and I am trying to find ...
3 votes
2 answers
4k views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
1 vote
2 answers
73 views

last backup - how many backup files and where they are?

I am restoring a database, this database is very big and it has 6 backup files (meaning the backup is split into 6 smaller files) I can see an indication of that using the restore labelonly ...
0 votes
0 answers
34 views

How to find the records that are waiting to be replicated?

I had a look using the replication monitor and it is showing me commands that are awaiting to be replicated: My question is - how do I find out what these commands are? I Have tried this query by Ben ...
0 votes
1 answer
37 views

what is stopping my transactional replication services?

I will be straightforward and put my question before I add any other information, as I want to keep it objective and to the point. the question is: How to find out why the replication services are ...
4 votes
3 answers
3k 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, ...
9 votes
1 answer
2k views

Zero Rows return on querying DMV sys.dm_os_performance_counters

I have a user on SQL Server 2014 Standard Edition (RTM) with SYSADMIN role with Server View State Permissions but when I execute DMV sys.dm_os_performance_counters it returns no record. Any idea ...
4 votes
3 answers
4k 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....
-1 votes
1 answer
133 views

DMV sys.dm_exec_procedure_stats sometimes returns weird (incorrect?) values ​in successive object snapshots

I'm working on a small project to monitor activity and execution metrics of user stored procedures. Periodically (every 10 minutes or so), I'm taking snapshots of the sys.dm_exec_procedure_stats DMV, ...
4 votes
1 answer
343 views

Selecting from sys.dm_tran_locks takes 100+ ms

Our application uses SQL Server application locks and we have cases where we need to see whether a given lock exists or not. To do this, we retrieve and filter information from sys.dm_tran_locks. ...
-1 votes
1 answer
39 views

dm_exec_query_stats returning all NULLs for Worker and Elapsed time

I am executing the following statement on a customer's SQL Server. The version is Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) . declare @Milliseconds int SELECT top 200 * --sum([Total Worker ...
2 votes
1 answer
441 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
2 answers
2k views

SQL Server 2005 Easy way to add all users to a database role

I have three application databases: A, B and C. Users of A and B are all users in C; the users from A are in the public role with only SELECT permission, while the users from B are in a role with more ...
1 vote
1 answer
178 views

SET SINGLE_USER stuck on wait type PRINT_ROLLBACK_PROGRESS

I want to drop a database on one of our systems (SQL Server 2016 SP2 CU17), but the process to set the database in single user mode is stuck on the wait type PRINT_ROLLBACK_PROGRESS. I use the ...
12 votes
2 answers
1k views

Setting READ UNCOMMITTED when reading DMVs

I've seen several people call SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before reading system DMVs. Is there ever any reason to do this, assuming you aren't mixing calls to DMVs and tables in ...
0 votes
1 answer
90 views

Who was using a spid 2 hours earlier in SQL Server?

I have a specific spid from 2 hours ago, and would like to know who was using it. Is this possible? Is this stored in the cache, some DMV, Ringbuffer? SQL Server 2019. Thank you in advance.
2 votes
2 answers
7k 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 ...
4 votes
2 answers
3k views

Pulling Top queries returns NULL in query plan and sql text

I'm using the following code to pull our top 20 queries (ordered by CPU): SELECT TOP 20 qs.sql_handle ,qs.execution_count ,qs.total_worker_time AS [Total CPU] ,qs.total_worker_time / ...
2 votes
2 answers
4k 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
1 answer
137 views

Why is sqlserver_start_time not equal to sqlserver_start_time_ms_ticks - ms_ticks in sys.dm_os_sys_info?

Why do these two queries produce different results (but within about a minute of each other)? SELECT StartTime = sqlserver_start_time FROM sys.dm_os_sys_info; SELECT StartTime = DATEADD(...
0 votes
1 answer
1k views

How to get definition of partition function programmatically?

I have tables partitioned by date with various frequency space (multi-years, years, quarters, months etc.) sys.partition_functions gives name and function_id. Is there a way to query the actual ...
6 votes
2 answers
328 views

Querying the sys.dm_db_log_info() function while reducing to the max

I'm currently querying the sys.dm_db_log_info() DMV to retrieve the VLFs from a database to determine when I can shrink, reorganise and reduce the amount of fragmented (10 MB VLFs) in the TLOG file. ...
4 votes
1 answer
226 views

SQL 2019 CU13 breaks Cluster_nodename

With total disregard to my paranoia about this unlucky number we installed CU 13 for SQL 2019 in one of our environments (in defense of the number 13 I have the same issue with 14). The progression ...
1 vote
2 answers
99 views

What is the difference between latency calculation using sys.dm_os_buffer_descriptors.read_microsec vs sys.dm_io_virtual_file_stats?

I'm trying to calculate the disk io latency (time required to fetch the page from disk into memory) for a SQL database. Following are 2 approaches I am exploring: Approach 1 - Using sys....
1 vote
1 answer
154 views

New DMVs related to Availability Groups in SQL Server 2019 CU9

In SQL Server 2019 since Cumulative Update 9, there are two new DMVs related to Availability Groups: sys.dm_hadr_cached_replica_states sys.dm_hadr_cached_database_replica_states I found out about ...
3 votes
1 answer
177 views

Finding the names of objects in the Resource database [duplicate]

I'm looking at the sys.dm_exec_function_stats view to find scalar function stats. That view also includes rows from a database with an id 32767, which I've found is the Resource database. Some of ...
2 votes
0 answers
155 views

Sys.dm_os_performance_counters DMV row counts

We have noticed an odd issue on two servers in a 3 node SQL Server 2017 cluster running Always On Availability Groups. When checking the DMV sys.dm_os_performance_counters the row counts are totally ...
2 votes
2 answers
760 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 ...
0 votes
1 answer
949 views

When is dm_exec_procedure_stats updated? [duplicate]

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 ...
-1 votes
1 answer
51 views

Which dmv can help identify the cost of index maintenance for exisitng and missing indexes?

I want to identity: Indexes that are costly to maintain. Missing indexes along with their maintenance cost. By maintenance I mean - the index maintenance during insert/update/delete. Which dmv can ...
0 votes
1 answer
243 views

How can i get AG dashboard values via SQL Query across all supported versions of SQL server

I am trying to ease the monitoring of availability groups across multiple SQL servers. Came across few dmvs which got me the data. But cannot figure out some important columns which i am seeing in ...
5 votes
3 answers
717 views

Resource Pool Stats Counter Reporting Zero

I utilize the resource governor on my 2016 SQL Server. In prior 2016 SQL Server versions, this query: select * from master.sys.dm_os_performance_counters opc where opc.object_name = 'SQLServer:...
0 votes
1 answer
692 views

Is there a way to determine the command timeout for an executing spid

Is there a DMV or other mechanism available that returns the command timeout for an executing session_id?
4 votes
1 answer
645 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. ...
3 votes
1 answer
1k views

Database Unallocated Space - Correct way to measure

I am confused over the web regrading few scripts on calculating the database size and free space within same. Most of the scripts over the web uses sys.databases_files dmv to calculate free space like ...
0 votes
1 answer
54 views

Join duplicating the data - SQL Server

I am working with SQL Server Always On and I am querying the data as follows: SELECT name as AGname, replica_server_name, CASE WHEN (primary_replica = replica_server_name) THEN 1 ELSE '0' END AS ...
2 votes
1 answer
367 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 ...
6 votes
2 answers
878 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....
10 votes
3 answers
10k views

sys.dm_db_index_physical_stats is extremly slow

I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. ...
1 vote
0 answers
40 views

select from Dynamic Management View causes blocked processes [duplicate]

we have troubles regarding querying from a dynamic view in SQL Server. We basically check for an existing object lock on a table (CHANGELOG) from sys.dm_tran_locks with the @@spid in a stored ...
1 vote
2 answers
123 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 ...
2 votes
2 answers
3k 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
2 3 4 5