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.
2
votes
1answer
50 views
Synchronize Queries Retrieving Sessions And Locks From DMVs
I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" ...
8
votes
2answers
201 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 ...
2
votes
0answers
99 views
Are the following statements concerning sys.dm_exec_requests true?
Parallel plans mean that cpu_time can be greater than total_elapsed time.
Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread.
...
0
votes
2answers
63 views
Is there a combination of columns in sys.dm_exec_sessions that is unique per the server?
In SQL Server, each session has its own spid. Spids are unique at any given notice, but spids, like process and thread identifiers in the OS are recycled.
However sys.dm_exec_sessions has other ...
6
votes
1answer
99 views
Ok to drop indexes on FK's if they have no stats in the DMVS
I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.
Are you 100% safe to drop these ...
2
votes
1answer
150 views
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, ...
5
votes
1answer
350 views
SQL Server 2008 R2 DMV Question
I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV.
SELECT TOP 10
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE ...
5
votes
2answers
163 views
What units are 'reads' and 'writes' in dm_exec_query_stats
MSDN describes the various logical / physical read and write columns in dm_exec_query_stats as:
Number of physical reads performed the last time the plan was executed.
Those columns are all 64 ...
6
votes
3answers
2k views
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 ...
1
vote
1answer
184 views
Resource Usage Statistics by Login
Is there a way to get resource usage statistics by login in SQL Server?
Ideally, I'd like to have a version of sys.dm_exec_sessions that tracks usage for all sessions, rather than just the ...
3
votes
1answer
79 views
Inconsistent information from different DMVs
I've a server with 12 GB of physical RAM. I just lowered its 'Max Server Memory (MB)' setting from 10 GB to 8 GB.
If I look at the processes' committed memory in Perfmon, I see:
select *
from ...
2
votes
1answer
181 views
In SQL Server 2008, in DMV sys.dm_db_index_phsicial_stats, what does compressed_page_count mean in comparision with page_count
In SQL Server 2008, DMV sys.dm_db_index_physical_stats, what does compressed_page_count mean in comparison with page_count. I have a table with the following info:
Object_id Index_type_desc ...
2
votes
1answer
289 views
Index physical stats DMV - Object ID/table not found
I have installed SQL Server 2012 RC0 on my laptop recently and I was trying a demo which illustrates how shrink database will cause fragmentation. I created a table, a clustered index on the identity ...
3
votes
2answers
859 views
sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups
These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing?
Here is how I proved it is the same data:
select
...
4
votes
2answers
162 views
How do I track the usage of XML indexes?
My XML indexes are not listed in sys.dm_db_index_usage_stats
Does this mean they aren't being used? Or does this particular DMV not track XML index usage? (Books Online doesn't appear to answer the ...
3
votes
2answers
531 views
DMV Queries as Perfmon Counters
Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters.
For example, take:
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;
Most ...
