question

srivivek avatar image
srivivek asked

Performance during job execution

Hi, I need to check the performance while Jobs are running on SQL 2000. can i use the Perfmon and collect the data or do i need to use SQL profiler. Can anyone please suggest what are counters and the values to be taken in to consideration. What if i use profiler? please give your suggestions.... Thanks....
performance
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
You can use both. Brad McGehee has a good article, [Correlating SQL Server Profiler with Performance Monitor][1] that covers how to set up both Profiler and PerfMon as well the counters to include for each. He then goes on to show how to correlate the two sets of counters and begin your analysis. If you are more visual, Brent Ozar has a [Perfmon and Profiler for SQL Server][2] presentation on the same thing [1]: http://www.simple-talk.com/sql/database-administration/correlating-sql-server-profiler-with-performance-monitor/ [2]: http://www.brentozar.com/sql-server-training-videos/perfmon-and-profiler-for-sql-server/
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
I've got a process detailed in my blog that makes it a bit easier to get the details that Brent Ozar recommends. http://www.simple-talk.com/community/blogs/jonathanallen/archive/2010/12/20/96284.aspx
4 Likes 4 ·
KenJ avatar image KenJ commented ·
Great tip @Fatherjack
0 Likes 0 ·
srivivek avatar image srivivek commented ·
Thank you guys, Can we use profiler for this? In case of the batch jobs(execution time around 25-30 hrs) do we need to follow the same procedure.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@srivivek - TypePerf collects perfmon counters. I let mine run for 5 days at a time with 15s intervals, just make sure you have disk(or database) space to keep it all.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
You can use profiler by itself, perfmon by itself, or both of them together. If you want to use profiler by itself, the cited article and presentation still cover the appropriate counters for that. They just take the extra step of showing you how to correlate results from the two tools.
0 Likes 0 ·
Show more comments
denisrichi avatar image
denisrichi answered
There are many articles you can find on the internet about permanence monitoring Particularly for counter of SQL Profiler you can try SQL Storage Manager for the same purpose you can take a look from here as well [ http://www.sqlserverperformancemonitoring.com/][1] [1]: http://www.sqlserverperformancemonitoring.com/
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Three fourths of all your answers involve a link to this tool. Are there any other monitoring tools you can list to make this answer more complete?
3 Likes 3 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Or good reasons for using that tool.
3 Likes 3 ·
DirkHondong avatar image DirkHondong commented ·
Funny thing with that tool. The homepage says: Manage and increase SQL Server Performance. And one of the Software key features is: "Shrink database. Allow to shrink file and file groupd for better disk space" Now that is weird.... *sorry for trolling or to be off topic*
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
See, I didn't want to sully my browser history by looking at that page, but now I feel compelled to see what else they suggest... No. I shall be good. I shall resist.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.