Stack Overflow
Stack Overflow

Questions tagged [dynamic-management-views]

13 questions
7
votes
2 answers

SQL Server DMV - sys.dm_db_missing_index_group_stats - What do these columns mean?

I am tinkering with creating a query to find missing indexes. I've taken a base query created by the Red-Gate folks in their SQL Server DMV Starter Pack eBook and am modifying it a bit. There are a couple columns in…
Randy Minder
  • 47,200
  • 49
  • 204
  • 358
4
votes
1 answer

DMV in SQL 2008 to replace dm_os_exec_requests

I've been going through an old SQL 2005 performance tuning document and it talks about the dynamic management view sys.dm_os_exec_requests. It looks like that view is no longer available in SQL 2008. Is there a direct replacement? Is there a…
Tom H
  • 46,766
  • 14
  • 87
  • 128
2
votes
1 answer

SQL Server - sys.dm_exec_requests Issue

In SSMS 2008 I created a new query window and issued the following statement (notice I don't commit the transaction): Begin Tran Update Master.Part Set LastModifiedUser = 'me' I then open another new query window and entered the following…
Randy Minder
  • 47,200
  • 49
  • 204
  • 358
2
votes
1 answer

Should recursive common table expressions over dmvs be built on cached data?

I have written a little CTE to get the total blocking time of a head blocker process, and I am unsure if I should first copy all of the processes that I want the CTE to run over into a temp table and then perform the query over this - i.e. I want to…
Kram
  • 4,099
  • 4
  • 39
  • 60
1
vote
1 answer

Flaw in dm_exec_procedure_stats?

I am either off in my thinking or I don't understand the documentation. Just for clarity, the documentation on this management view uses the phrase "since it was last compiled" but does not clarify if that is the same as cached time. My ultimate…
Paul Wichtendahl
  • 117
  • 7
1
vote
0 answers

SQL server DMV (Dynamic Management View) not showing last user update info on table with 2 indexes

I am using the following SQL server DMV to find out when a table was last updated. USE DB_NAME GO SELECT [database_name] = DB_NAME(DB_ID()) , [table_name] = tbl.[name] , ius.last_user_update , ius.user_updates , ius.last_user_seek ,…
gwoo
  • 31
  • 7
1
vote
1 answer

SQL Server - report query progress with sys.dm_exec_requests

Is there a way to report a percentage progress of an own query using a sys.dm_exec_requests field: percent_complete? msdn link It works quite well for a BACKUP/RESTORE/SHRINK operations, for example: SELECT percent_complete FROM…
malpka
  • 83
  • 1
  • 5
1
vote
2 answers

How to get index name from sys.dm_tran_locks

I'm working on a multi-tenanted solution. As such we have a lot of databases on our SQL Server. I'm looking at a locking issue and need to be able to see which locks are being waited on. I have queried the sys.dm_tran_locks dynamic view, but would…
Martin Brown
  • 24,692
  • 14
  • 77
  • 122
1
vote
1 answer

can you use dynamic management views to find out how many times a query is called in a time period

I've been learning more about the dynamic management views in sql server because we've been having some performance problems at work. Perhaps this is the wrong way to look at things but the tools that are available seem to be ... they just don't…
Kevin Donde
  • 912
  • 3
  • 15
  • 32
0
votes
1 answer

sys.dm_os_ring_buffers vs wmi query for cpu

Is anyone using WMI to alert when cpu is over a threshold? Here is the query I'm using : SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA 'Win32_Processor' AND TargetInstance.LoadPercentage > 95 I am comparing the…
user210084
  • 1
  • 1
0
votes
2 answers

Retrieving the Name of Running Stored Procedures Across Multiple Databases

I'm trying to write a query that reports the current database activity. The query links together various DMV's like sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests, etc. The query also pulls the actual queries being run via the…
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
0
votes
1 answer

get connection with connection_id

When I execute select * from sys.dm_exec_connections there will be a resulting column connection_id. Now, I am searching for the connectionstring which corresponds to this connection_id. Can someone help me, where I can find this information?
BennoDual
  • 5,865
  • 15
  • 67
  • 153
0
votes
1 answer

Join multiple Dynamic Management View tables

I'm trying to get a list of all the sessions with the login name, status and the SQL query if they are currently running a query. The below query works fine but only shows people who are currently running a query. How can I display them all even if…
bcooper
  • 27
  • 5