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.

learn more… | top users | synonyms

11
votes
1answer
233 views

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 ...
2
votes
1answer
59 views

Is there a Windows Performance Counter which contains the “Log Send Rate” for Availability Groups?

I wish to be able to get the "Log Send Rate kb/s", as per the SSMS AG dashboard (which is also available via log_send_rate column on the DMV sys.dm_hadr_database_replica_states) I'd like to get it as ...
1
vote
1answer
158 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 ...
0
votes
1answer
36 views

Does dm_exec_procedure_stats.total_worker_time include signal waits?

Does dm_exec_procedure_stats.total_worker_time include signal waits or only time spent by procedure on scheduler? The reason of question - when number of cores was increased on the server signal waits ...
0
votes
1answer
41 views

sys.dm_exec_requests and cpu_time

If a query is executing on a multi-core server, and the query uses parallel threads, will sys.dm_exec_requests.cpu_timeshow the cpu time consumed by all threads executing the query? I assume this is ...
0
votes
1answer
86 views

Optimizing a DMV query

I have found a query that is a major performance issue in my environment. I looked at the actual execution plan for this query and found out the main problem is the remote scan (98%). Its remote ...
4
votes
0answers
307 views

Interpreting sys.dm_os_sys_memory

I try to figure out a way(using only TSQL) to determine if and how much the PAGEFILE (I will capitalize this term if I refer to the windows page file from now on) is used (like pagefileusage% ...
3
votes
0answers
326 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. ...
2
votes
0answers
214 views

Collect information about currently running queries only when client timeout occurs

We would like to be able to collect information about all currently running queries when (and only when) a client timeout occurs in SQL Server 2008. I know I can collect information about the client ...
2
votes
0answers
198 views

plan_generation_num keeps incrementing, why?

I have a query that is executed using sp_executesql code is as follows DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3); SET @ppCityID = CAST(@cityID AS NVARCHAR(3)); exec sp_executesql ...
1
vote
0answers
37 views

DMV Index proposals still reported as missing

We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues. After running the DMV query: ...
1
vote
0answers
68 views

Non clustered indexes

I have a questions on Index Our Application is throwing the following SQL eroor Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...
0
votes
0answers
27 views

How can I find last access time for users or specific users to all databases in SQL Server

I have a SQL Server with more than 100 Databases and I should manage the server and it's space usage, users can restore and use backups they have on the server, I have a software for this server,if ...
0
votes
0answers
248 views

When is transaction removed from sys.dm_tran_active_transactions?

We are having some issues with clients getting Transaction Timeout when connecting to our clustered SQL Server 2008 R2. To investigate the problems I have been digging into ...