September 29, 2010 at 2:49 am
I encountered SQL Server time-out issue, and I could not even login to the server using MSSMS. I checked the even log, it shows idle hit 95%. The issue resolved after I restart the SQL Server services. I am currently doing RCA (root cause analysis) for this issue.
I wish to find out which SQL queries run and causing the performance issue.
Can any one advice me on this?
Your help is highly appreciated. Thanks.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
September 29, 2010 at 3:38 am
If this was an AdHoc query it may be difficult to spot. If this is caused from "normal" routine usage then I suggest you run a SQL Profiler trace and try use it to identify statements / procedures with high CPU time values, then you'll have a good idea of which stored procedures / statements need looking at.
September 29, 2010 at 3:41 am
Is that possible if we could execute any t-sql to show a list of quries/transaction that caused high cpu usage?
Just an idea pop up from my mind may be we could get it from sql log. 😀
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
September 29, 2010 at 3:46 am
SELECT TOP 50
SUM(QS.TOTAL_WORKER_TIME) AS TOTAL_CPU_TIME,
SUM(QS.EXECUTION_COUNT) AS TOTAL_EXECUTION_COUNT,
COUNT(*) AS NUMBER_OF_STATEMENTS,
SQL_TEXT.TEXT,
QS.PLAN_HANDLE
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT
GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE ORDER
BY SUM(QS.TOTAL_WORKER_TIME) DESC
This query will show you top 50 CPU consuming statements. Its not my own work, I aquired from the web some time ago. It may help you diagnose / spot the offending statement
September 29, 2010 at 3:49 am
That query is dependent on the queries' plans still being in cache, which means no restarts of the SQL process.
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
September 29, 2010 at 3:54 am
Excellent point. I think he did restart the service.
I would still advise running a trace to try and identify timeout issues, may not necessarily be a soley CPU issue.
September 29, 2010 at 4:07 am
Please check the default trace file stored in errorlog folder.
To find your default trace
SELECT *
FROM fn_trace_getinfo(default);
GO
USe the following function to dump the trace details into a table
SELECT * FROM ::fn_trace_gettable('traceFileName', DEFAULT)
Probably can be of somehelp your root cause analysis..
Regards,
Raj
September 29, 2010 at 4:16 am
arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful informationlike CPU, starttime etc. .. Probably can help your root cause analysis
The default trace does not contain any information on queries run. It contains only the following events:
Most of the events in the Audit category
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings
There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.
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
September 29, 2010 at 4:32 am
my mistake. Thanks for correcting
Regards,
Raj
September 29, 2010 at 6:40 am
Just to reiterate, since you bounced the server, there's no real recourse for identifying an issue other than the error logs. That is, unless you put some type of monitoring in place ahead of time. At this point, you're probably going to have to write this one down as unknown.
"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
September 30, 2010 at 12:13 am
Hi all,
I really appreciated with all your comments, advice and help. It helps me a lot. I do not realize that it can be done. It really impressive. Now I know anything if one can think of, it can be done. The future is no limits.
Again, thank you all very much! 😀
Have a nice day.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
October 5, 2010 at 6:10 am
GilaMonster (9/29/2010)
arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful informationlike CPU, starttime etc. .. Probably can help your root cause analysis
The default trace does not contain any information on queries run. It contains only the following events:
Most of the events in the Audit category
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings
There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.
I was wondering if there is a script for sql 2000 that would do the same job please.
Thank you
October 5, 2010 at 6:40 am
tt-615680 (10/5/2010)
GilaMonster (9/29/2010)
arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful informationlike CPU, starttime etc. .. Probably can help your root cause analysis
The default trace does not contain any information on queries run. It contains only the following events:
Most of the events in the Audit category
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings
There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.
I was wondering if there is a script for sql 2000 that would do the same job please.
Thank you
Which, do the same as the default trace or capture RPC:Complete & SQL:BatchComplete events?
"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
October 5, 2010 at 6:51 am
Grant Fritchey (10/5/2010)
tt-615680 (10/5/2010)
GilaMonster (9/29/2010)
arr.nagaraj (9/29/2010)
Default trace contains the recently ran queries along with a few useful informationlike CPU, starttime etc. .. Probably can help your root cause analysis
The default trace does not contain any information on queries run. It contains only the following events:
Most of the events in the Audit category
Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings
There are no batch or statement completed events in the default trace. They're too frequent and too intense for what's meant to be a light-weight, low volume trace.
I was wondering if there is a script for sql 2000 that would do the same job please.
Thank you
Which, do the same as the default trace or capture RPC:Complete & SQL:BatchComplete events?
Yes, I know there are many queries or sysyem sp's that cannot be used in sql 2000.
Thank you
October 5, 2010 at 7:06 am
tt-615680 (10/5/2010)
Grant Fritchey (10/5/2010)
Which, do the same as the default trace or capture RPC:Complete & SQL:BatchComplete events?Yes, I know there are many queries or sysyem sp's that cannot be used in sql 2000.
Thank you
Sorry, I'm still confused what you're looking for. Do you want A) a similar listing of events inside of the 2000 trace events that would replicate the default trace for SQL Server 2000, or B) A method of capturing query trace events within SQL Server 2000?
"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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply