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 |
|
m_saad
Starting Member
22 Posts |
 Posted - 2004-07-06 : 01:38:13
|
| This question might have been asked before, though tried to search the forum and could not find . So here it goesDevelopers at my company wish to monitor performance of their stored procedures and queries on a production server. They are thinking of running SQL profiler continuous for 2 days and analyze the results later on, especially read/write, locks and duration of stored procedures. I have made a custom template of events and columns we need to record and taken out its script and made a procedure on the server. I plan to run this procedure for 48 hours and store the results in a .trc file. Will you guys favor this approach or running profiler for that long is any risk? Are there any other alternatives?Ok I have learnt one thing I should run profiler from the client. Any other suggestions?Thanks in Advance |
|
|
MuadDBA
628 Posts |
Posted - 2004-07-06 : 15:09:25
|
| Instead of storing it in a .TRC file I would recommend storing it in a table on another database. It's a HECK of a lot easier to search if you're looking for long-running queries and such. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-06 : 15:58:27
|
| Yes store it in a table. Then for long running queries:SELECT TOP 100 TextData, DurationFROM TraceTableNameWHERE TextData IS NOT NULL AND Duration > 5000ORDER BY Duration DESCThe above gives you the 100 worst performing queries. Duration > 5000 means anything that took longer than 5 seconds.Tara |
|
|
|
m_saad
Starting Member
22 Posts |
Posted - 2004-07-07 : 00:34:52
|
Hey thanks a lot both of you  , usually I store data in the file and then export it in db and I have a set of quries that I run against these records.Though thanks againBut still my question remains, is it ok to run profiler for such a long duration on db? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-07-07 : 03:16:50
|
| Be careful about recording locks, they generate thousands of results in seconds.-------Moo. :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-07 : 12:04:24
|
| I certainly would not run SQL Profiler for so long. Why not just run it during peak hours?Tara |
|
|
|
|
|
|
Sql Profiler