question

vijay 2 avatar image
vijay 2 asked

How to monitor Performance?

Hi everyone,

I am working on SQL Server 2005 & 2008 Databases. I want to know the performance of my system and how to trace the things in sql? How to make use of the profiler? And also the methods to find out performance issues and solutions .

Please expalian in detail.

Thanks In Advance. Veejay.

performance-tuning
10 |1200

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

Grant Fritchey avatar image
Grant Fritchey answered

That is a very broad set of information that you're asking for. No one is going to give it all to you in sufficient detail in a post on a forum like this. Gail Shaw has an excellent set of articles over on Simple-Talk that cover a great deal of what you're looking for. I'd start there. It covers the basics extremely well.

If you still need more information after that, you might look up my book on Amazon.

2 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.

Blackhawk-17 avatar image Blackhawk-17 commented ·
@Grant - don't you have that Amazon link hotkeyed yet ;)
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Tempting, but no. I don't mind talking it up, but linking to it feels to much like blatant adverts.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered

As Grant has said - it's a big question.

Start by understanding what can cause performance issues in the first place.

  • Disk I/O
  • RAM (speed & amount)
  • Network
  • Number of users
  • Locking
  • CPUs (number & speed)
  • Complexity of queries
  • Quality of queries
  • Amount of data returned by queries
  • File design/placement
  • Read/write ratio of activity
  • and many more...

This list gives you a start on areas of investigation. It doesn't even begin to touch the O/S level (hardware drivers etc.) so you can see how big a question you are asking.

Search through Books OnLine for the above and look up DMVs. There's a lot of information to get you started. After that you can come back with targeted questions and the group will be able to help you. Just remember that all these things work in concert and what starts out looking like it's caused by "A" may only be a symptom, be sure to take a peek at "B", "C" as well :)

1 comment
10 |1200

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

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Bad queries, gotta mention bad queries. Excellent list otherwise.
2 Likes 2 ·
DaniSQL avatar image
DaniSQL answered

I agree with both suggestions above. I will also recommend you to go through different articles in sites like sql-server-performance.com. some of them are basic and some advanced, but you can quickly get urself upto speed. You can also read different articles and watch videos in sqlservercentral.com. You may want to buy books as well.....

10 |1200

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

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.