Top expensive queries

Question

Top expensive queries

WhiteLotus

SSChampion

Points: 12122
More actions
April 22, 2015 at 6:56 pm

#317631

Hi All,
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
Thank you
Cheers

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

WhiteLotus (4/22/2015)
Hi All,
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
Thank you
Cheers

Query the sys.dm_exec_query_stats

😎

WhiteLotus SSChampion Points: 12122 More actions · Answer 2

Thanks for the reply

I came across this query from Google :

SELECT TOP 20

qs.sql_handle,

qs.execution_count,

qs.total_worker_time AS Total_CPU,

total_CPU_inSeconds = --Converted from microseconds

qs.total_worker_time/1000000,

average_CPU_inSeconds = --Converted from microseconds

(qs.total_worker_time/1000000) / qs.execution_count,

qs.total_elapsed_time,

total_elapsed_time_inSeconds = --Converted from microseconds

qs.total_elapsed_time/1000000,

st.text,

qp.query_plan

FROM

sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC

What do you think ?:)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Slightly improved version

😎

USE master;

GO

SELECT TOP 20

ISNULL(DB_NAME(st.dbid),N'N/A') AS DATABASE_NAME

,ROUND(qs.max_elapsed_time * 0.000001,2) AS MAX_ELAPSED_SEC

,qs.total_physical_reads / qs.execution_count AS AVG_PHYS_READ

,qs.total_logical_reads / qs.execution_count AS AVG_LOGIC_READ

,qs.execution_count AS EXEC_COUNT

,qs.creation_time AS CREATE_TIME

,qs.last_execution_time AS LAST_EXEC_TIME

,qs.total_worker_time AS Total_CPU

,ROUND(qs.total_worker_time * 0.000001,2) AS total_CPU_inSeconds

,ROUND((qs.total_worker_time * 0.000001)

/ qs.execution_count,2) AS average_CPU_inSeconds

,qs.total_elapsed_time

,ROUND(qs.total_elapsed_time * 0.000001,2) AS total_elapsed_time_inSeconds

,st.text AS QUERY_TEXT

,qp.query_plan AS EXECUTION_PLAN

FROM sys.dm_exec_query_stats qs

OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) st

OUTER APPLY sys.dm_exec_query_plan (qs.plan_handle) qp

ORDER BY qs.total_worker_time / qs.execution_count DESC;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

GilaMonster (4/23/2015)
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

Thanks Gail

😎

WhiteLotus SSChampion Points: 12122 More actions · Answer 6

WhiteLotus

SSChampion

Points: 12122

April 23, 2015 at 5:45 pm

#1793154

Thanks Eri

WhiteLotus SSChampion Points: 12122 More actions · Answer 7

WhiteLotus

SSChampion

Points: 12122

April 23, 2015 at 5:46 pm

#1793155

Thanks Gail

WhiteLotus SSChampion Points: 12122 More actions · Answer 8

Hi Gail ,

That's a very good article !

I just wonder what about if I need to monitor for 3 days ( from Wed until Friday ) ?

In that article giving the example for monitoring SQL in 30 mins

Cheers..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

WhiteLotus (4/23/2015)
I just wonder what about if I need to monitor for 3 days ( from Wed until Friday ) ?

Why do you need to?

In that article giving the example for monitoring SQL in 30 mins

Yup, because most systems have a pattern of activity. The stuff being run on Tuesday at 10AM is much the same as the stuff being run on Thursday at 3PM. You're trying to get a representative view of what runs on the server. Unless what the users do on Wednesday radically differs from what the users do on Friday, you don't need to trace for 3 days straight.

You can if you want to, but beware of the file sizes. I have a client who's system generates 10GB of trace data an hour at peak times. If I was to trace his server for 3 days straight, I'd probably be looking at around 400-500GB of trace data. That's hard to move, takes forever to analyse and requires a lot of disk space and won't give me a much better understanding of what his workload looks like than tracing for 1 hour twice in one day.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WhiteLotus SSChampion Points: 12122 More actions · Answer 10

Thats make sense to me Gail ... I will discuss with my team about it ..cheers mate 🙂

Sporidium SSCommitted Points: 1550 More actions · Answer 11

...and then there's always SSMS' built-in reports (right-click on the database instance in Object Explorer).

Presumably they do something very similar to Eirikur's query under the hood, but with prettier output.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

Gary Harding (4/28/2015)
...and then there's always SSMS' built-in reports (right-click on the database instance in Object Explorer).
Presumably they do something very similar to Eirikur's query under the hood, but with prettier output.

Heh... and now we know the answer to one of my favorite interview questions when people claim to know anything about performance tuning. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125562 More actions · Answer 13

Also, so it's more readable, you can convert miliseconds to hh:mmss format using something like the following.

...

,convert(varchar,dateadd(ms,last_elapsed_time,getdate())-getdate(),108)last_elapsed_time

...

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho