February 4, 2014 at 10:57 am
I find out a few top cpu consuming queries, what should I do with them? Thanks.
February 4, 2014 at 10:59 am
Depends, Start by investigating what they do and determine whether that CPU usage is a problem or not.
Chapters 1 and 3 of https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ should get you started.
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
February 5, 2014 at 6:03 am
If you know you have a query that is causing performance problems, I'd start by looking at the code to see any of the common issues and then I'd look at the execution plan to understand how the optimizer is choosing to resolve the plan. You can download my book on execution plans in the link below, it's free.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2014 at 12:35 pm
Grant Fritchey (2/5/2014)
If you know you have a query that is causing performance problems, I'd start by looking at the code to see any of the common issues and then I'd look at the execution plan to understand how the optimizer is choosing to resolve the plan. You can download my book on execution plans in the link below, it's free.
Relevant to this post:
http://www.sqldownunder.com/Podcasts/Shows/54
Grant, I've listened to that episode a few times, great content!
February 7, 2014 at 4:16 am
Thanks. That was a fun discussion. Greg is a great guy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2014 at 3:52 am
you can also try this:
select top 10 er.session_id, db.name as DBName,
blocking_session_id,
substring(st.text,(er.statement_start_offset/2) + 1,
((case statement_end_offset when -1 then datalength(st.text)
else er.statement_end_offset end - er.statement_start_offset)/2) + 1) as statement_text ,
cpu_time,
total_elapsed_time,
reads,writes,logical_reads,
objectid
session_id,
status
,command,
er.database_id,
wait_type,wait_time,
last_wait_type
,text
from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text(er.sql_handle) st
join sys.databases db on er.database_id = db.database_id
order by total_elapsed_time desc
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2014 at 8:28 am
kapil_kk (2/10/2014)
you can also try this:
What for?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply