Some time when you try to win some arguments with your seniors you need some valid points and numbers to prove that you know exactly what you say.
The same happened with me several time, that what I was saying was correct but it was flying over head of my manager, so I thought when I will come next time to discussion with him, I will bring some proofs to shut his mouth.
So one day when I was fighting for to push some optimized work to production, he asked me do you have a list of the queries that is performing slow on the production DB?
I thought for a second "NO", but I say yes I have, although it was not difficult to cheat on, I can again take a snapshot of the activity monitor on production server and show this is the list, but I start looking to find it from with in SQL Server (I was more then 100% sure that SQL server must remember the slow performance queries).
So here is the final query I came with to find the slow queries....
SELECT TOP 20
SUBSTRING(EST.text, (EQS.statement_start_offset/2)+1,
((CASE EQS.statement_end_offset
WHEN -1 THEN DATALENGTH(EST.text)
ELSE EQS.statement_end_offset
END - EQS.statement_start_offset)/2)+1) As Query,
EQS.execution_count,
EQS.total_logical_reads,
EQS.last_logical_reads,
EQS.min_logical_reads,
EQS.max_logical_reads,
EQS.total_elapsed_time,
EQS.last_elapsed_time,
EQS.min_elapsed_time,
EQS.max_elapsed_time,
EQS.last_execution_time,
EQP.query_plan,
DBS.name
FROM sys.dm_exec_query_stats EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) EST
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) EQP
Left Outer Join sys.databases DBS ON EST.dbid = DBS.database_id
WHERE EST.encrypted=0 And DBS.name NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY EQS.total_logical_reads DESC
Nothing is new !!!
CROSS APPLY => is used as Inner Join with Tabled value functions
The same happened with me several time, that what I was saying was correct but it was flying over head of my manager, so I thought when I will come next time to discussion with him, I will bring some proofs to shut his mouth.
So one day when I was fighting for to push some optimized work to production, he asked me do you have a list of the queries that is performing slow on the production DB?
I thought for a second "NO", but I say yes I have, although it was not difficult to cheat on, I can again take a snapshot of the activity monitor on production server and show this is the list, but I start looking to find it from with in SQL Server (I was more then 100% sure that SQL server must remember the slow performance queries).
So here is the final query I came with to find the slow queries....
SELECT TOP 20
SUBSTRING(EST.text, (EQS.statement_start_offset/2)+1,
((CASE EQS.statement_end_offset
WHEN -1 THEN DATALENGTH(EST.text)
ELSE EQS.statement_end_offset
END - EQS.statement_start_offset)/2)+1) As Query,
EQS.execution_count,
EQS.total_logical_reads,
EQS.last_logical_reads,
EQS.min_logical_reads,
EQS.max_logical_reads,
EQS.total_elapsed_time,
EQS.last_elapsed_time,
EQS.min_elapsed_time,
EQS.max_elapsed_time,
EQS.last_execution_time,
EQP.query_plan,
DBS.name
FROM sys.dm_exec_query_stats EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) EST
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) EQP
Left Outer Join sys.databases DBS ON EST.dbid = DBS.database_id
WHERE EST.encrypted=0 And DBS.name NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY EQS.total_logical_reads DESC
Nothing is new !!!
CROSS APPLY => is used as Inner Join with Tabled value functions
