Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
debug
Starting Member
29 Posts |
 Posted - 2003-08-07 : 08:23:41
|
| Hi all,,does anyone has or knows how to find out the TOP 5 resourse consuming statements ?Its very crucial for my project...as I have to schedule the script to give me data in a table or text file for 15 days ...Please helpregards,DU |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-08-07 : 08:49:57
|
| you can use profile to capture 'SQLProfilerTSQL_Duration' and save it to the table. Also you can provide filter for the duration in the advanced tab. Then you can retrive top 5 statements from the table from any simple program.Hope this helps. Might some one have better ideas.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
 |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-08-07 : 08:52:49
|
| you can use profile to capture 'SQLProfilerTSQL_Duration' and save it to the table. Also you can provide filter for the duration in the advanced tab. Then you can retrive top 5 statements from the table from any simple program.Hope this helps. Might some one have better ideas.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-08-07 : 08:56:11
|
| you can use profile to capture 'SQLProfilerTSQL_Duration' and save it to the table. Also you can provide filter for the duration in the advanced tab. Then you can retrive top 5 statements from the table from any simple program.Hope this helps. Might some one have better ideas.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-07 : 13:25:35
|
| Yes, you will need to use SQL Profiler to get this data. Have the trace be saved as a table. Then once you have sufficient amount of data, stop the trace, then open up Query Analyzer and connect to the server and database where you saved the trace to.Then, run this:SELECT TOP 5 TextData, DurationFROM NameOfTraceTableWHERE TextData IS NOT NULLORDER BY Duration DESCI typically add another condition to the WHERE:SELECT TOP 5 TextData, DurationFROM NameOfTraceTableWHERE TextData IS NOT NULL AND Duration > 5000 --which means took longer than 5 secondsORDER BY Duration DESCTara |
|
|
|
|
|
|
TOP 5 SQL statements