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.
218
questions
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) ...