Tagged Questions

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
199 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
98 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
61 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 ...
2
votes
1answer
138 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
2answers
160 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
286 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 ...
2
votes
2answers
854 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 ...
3
votes
2answers
529 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 ...