Database Administrators Stack Exchange
Database Administrators 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.

Database Management Views & Functions began being added to SQL Server starting with the 2005 version. They are meant to provie a consistent way of retrieving system, database, performance, etc... information out of SQL Server. Several of the DMVs are direct replacements for DBCC commands so that you can join the resulting information returned with another table or query.

Resource: For more information on Database Management Views, Functions, Objects see: http://msdn.microsoft.com/en-us/library/ms188754.aspx

227 questions
27
votes
3 answers

Check progress of alter index reorganize / rebuild

How can I check the progress / status when I submit an alter index reorganize / rebuild ?
nojetlag
  • 2,927
  • 9
  • 34
  • 42
19
votes
2 answers

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.dm_os_memory_objects, looking at the plan in question, says that…
GordonLiddy
  • 445
  • 5
  • 10
15
votes
2 answers

stats_column_id and index_column_id do not update with physical order of clustered index is changed

Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the column in the sys.stats_columns DMV. (Tested in…
swasheck
  • 10,755
  • 5
  • 48
  • 89
15
votes
4 answers

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.dm_exec_sql_text(req.sql_handle) AS sqltext However, this…
user420667
  • 261
  • 1
  • 2
  • 7
14
votes
2 answers

space usage on sys.allocation_units and sp_spaceused

It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't. I am working on a monitoring tool, want to know disk size of each…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
14
votes
5 answers

Improve performance of sys.dm_db_index_physical_stats

During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on sys.dm_db_index_physical_stats. Is there any way to speed up the…
Rob Bird
  • 243
  • 1
  • 2
  • 6
13
votes
1 answer

How to get the parameter values of a stored procedure that's in the middle of execution?

Is there a way to get the values of the parameters that were passed in to a stored procedure while the stored procedure is in the middle of execution? (I'm trying to debug an actively running stored procedure.) I know I can see the queries of the…
J.D.
  • 40,776
  • 12
  • 62
  • 141
13
votes
3 answers

Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate?

I am running SQL Server 2012 and am trying to put some queries together for monitoring using the DMVs. However, when looking at the total_elapsed_time field in the sys.dm_exec_requests DMV, the numbers look way off. Here's an example: SELECT …
JoeNahmias
  • 464
  • 6
  • 18
12
votes
2 answers

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 the same transaction? What prompted me to ask the…
James Lupolt
  • 4,278
  • 5
  • 31
  • 46
12
votes
1 answer

what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats?

what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them? when I fire below query SELECT TOP 20 qs.last_worker_time, qs.last_worker_time/1000000…
Pritesh
12
votes
1 answer

What does the "reads" column in sys.dm_exec_sessions actually indicate?

This may seem like a very basic question, and indeed it should be. However, as a fan of the scientific method, I like to create a hypothesis, then test it to see if I'm correct. In this case, I'm trying to better understand the output of…
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
11
votes
2 answers

Finding the query causing the missing index DMVs

How can I find the query causing the missing index DMVs suggesting to create a particular index? Thank you.
Stackoverflowuser
  • 1,550
  • 3
  • 27
  • 42
11
votes
4 answers

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data? I need to nullify the data in all encrypted columns in a development server (according to our business rules). I know most of the columns because…
efesar
  • 1,072
  • 1
  • 6
  • 12
11
votes
2 answers

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 server. More accurate results may be determined…
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
10
votes
3 answers

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. When I do a select from…
nojetlag
  • 2,927
  • 9
  • 34
  • 42
1
2 3
…
15 16