Sql Server Stress Analysis

--26.02.2013 

--Just before starting to stress analysis we should delete all statistical records from database. After stress you can use second query to find Recent Expensive Queries.



--Dont run in production environment!
--After running query 1 you should wait 5-10 minute. CPU utilization can be hit 100 percent.

--Query 1:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

--Query 2:

--After using Stress tool you can select expensive queries by using different ORDER BY clouses.

SELECT  top 15
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) Sorgu,
qs.execution_count CalismaAdedi,
--qs.total_logical_reads ,
qs.last_logical_reads,
--qs.total_logical_writes,
qs.last_logical_writes,
--qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 [ToplamCalismaZamaniSN],
qs.last_elapsed_time/1000000 [SonCalismaZamaniSN],
qs.last_execution_time [Enson NezamanCalisti],
qp.query_plan



FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
where qt.TEXT not like'%sys%'
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time



----End