1
vote
1answer
18 views

Troubleshooting Resource Governor / Classification Function

I've been trying to configure / troubleshoot the resource governor on a sql server 2014 instance. I can't get any work into anything but the default resource pool. I've been following this ...
0
votes
0answers
24 views

Trying to find unused stored procedures, but dm_exec_procedure_stats seems to be resetting [duplicate]

I need to identify stored procedures that are no longer used by our application (~2,000 sprocs, duplicated in ~1,000 databases). To do this, I'm querying dm_exec_procedure_stats, which initially ...
1
vote
1answer
94 views

Understanding sys.dm_exec_query_stats execution_count vs creation_time

I've just downloaded the SQL Server 2012 'Performance Dashboard Reports' and am having some issues interpreting the data shown by one of the results and linking it back to the DMV ...
1
vote
0answers
32 views

Any instance where DMV can cause blocking? [duplicate]

So we had some slow performance with timeouts this morning. When looking for the cause, our blocking query (uses DMVs) showed up as the blocking query. I think this was just a fluke but the job we ...
0
votes
1answer
225 views

Finding login or user name of last person to use a DB

The boss has asked for a list of DB names along with size and name of last person to use that DB. I've found resources for when a DB was last accessed but for by whom. How would I solve this? ...
0
votes
0answers
79 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 ...
7
votes
2answers
260 views

SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

I received a call yesterday from a customer who was complaining about high CPU usage on their SQL Server. We're using SQL Server 2012 64 bit SE. Server is running Windows Server 2008 R2 Standard, 2.20 ...
3
votes
1answer
169 views

SQL Server DMV to show if database is used

What is the best indicator in finding out whether a database is utilized or accessed? I tried index statistics and based on I/O utilization but none seem to be a good indicator. Problem: I have a ...
4
votes
2answers
728 views

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

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
2
votes
0answers
77 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
1answer
300 views

Get Results from DMV Query into SQL Server Table

today I began to troubleshoot an issue that I was asked to resolve, and for the first time I found myself querying in the SSAS database. I obtained a very useful set of results (who was connecting to ...
2
votes
1answer
77 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" ...
10
votes
2answers
354 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 ...
3
votes
0answers
200 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
113 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
813 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
530 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
6k 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 ...
2
votes
1answer
408 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
99 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
603 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
1k 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
693 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 ...