- When was the last time a particluar query executed?
- How many times the query has been executed?
The following script shows the top 5 stored procedures that have many recompilations.
select top 5 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where [plan_generation_num] >1
order by [plan_generation_num desc]
As you see, sys.dm_exec_query_stats view keeps the categorized history information. If you use the SQL Server profiler, you must categorize yourself.
SQL Server profiler
If you want to find the server process and the identities of transactions that are using excessive CPU resource.
How to use the sys.dm_os_wait_stats?
--Initialize it first
dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs
---- Total waits are wait_time_ms
Select signal_wait_time_ms=sum(signal_wait_time_ms)
,'%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)
,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats
Running the script, I got the following:
signal_wait_time_ms:11122
%signal (cpu) waits : 0.22
resource_wait_time_ms :4950448
%resource waits:99.78
From data, you could conclude that the CPU is not an issue. The resource wait takes long time. It is because I issue one statement: SELECT * FROM Person.Address. It is resource-intensive. If you run many queries that include the keyword JOIN, GROUP, and ORDER, there are many query plan recompilations, you will see the high number of signal_wait_time_ms. If the percentage of signal_waits is less than 25%, CPU performance is acceptible.
Why should you run DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)?
All data collected in sys.dm_os_wait_stats is cumulative since the last time the statistics were reset or the server was started. But if you restart the SQL Server Instance, it is reset automatically. That's say, you have make some changes to your system both hardware and configuration. If you don't want that old data affect your judgement and don't want to restart SQL Server instance, you run the DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR).
There are more than 200 wait types. For example, lock waits (LCK_M_BU,LCK_M_IS, etc) indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.
sys.dm_tran_locks
In the view, each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. This view has no history data.
USE tempdb;
GO
CREATE TABLE Engine
(c1 int, c2 int);
GO
CREATE INDEX Engine_ci on Engine(c1);
GO
INSERT INTO Engine VALUES (1,1);
INSERT INTO Engine VALUES (2,2);
INSERT INTO Engine VALUES (3,3);
INSERT INTO Engine VALUES (4,4);
INSERT INTO Engine VALUES (5,5);
INSERT INTO Engine VALUES (6,6);
GO
You need to open 3 query windows. Each one creates a session.
Execute the script in Query Windows 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
SELECT c1
FROM Engine
WITH(holdlock, rowlock);
Execute the script in Query Window 2:
BEGIN TRAN
UPDATE Engine SET c1 = 10
ROLLBACK --it is not run immediately. It runs after the session 1 runs ROLLBACK.
Execute the script in Query Window 3:
SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('tempdb')
From the screenshot, you see the Request_status with WAIT. Its request_session_id is 56 and is a blocked session. The 54 is the blocking session.
To find out the user name of the user with the blocking connection:
execute sp_who 56
or
Use the Activity Monitor in Microsoft SQL Management Studio and locate the process 54.
SELECT resource_type, resource_database_id,
resource_associated_entity_id, request_mode,
request_session_id, blocking_session_id
FROM sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2
WHERE t1.lock_owner_address = t2.resource_address
Execute the statement in Query Window 1:
ROLLBACK;
Rerun the queries in Query Window 3--no information shows.
You can always run the DMVs and DMFs in a production server without causing excessive overhead. However, SQL Server profiler will generate a lot overhead on a production environment.
