question

Cyborg avatar image
Cyborg asked

SQL Server Performance Report

How to write a SQL Server Performance Report?
performanceadministration
10 |1200

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

DaniSQL avatar image
DaniSQL answered
All Great recommendations above. Have you seen Brent Ozar's videos on [Writing a SQL Server Performance Report][1] ? It might be what you are looking for. There is even a sample report you can download at the bottom of the page. [1]: http://sqlserverpedia.com/wiki/Writing_a_SQL_Server_Performance_Report
3 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.

ah! Thanks a lot DaniSQL this was the one that i am really looking for a lot of stuffs there. I contacted Brent Ozar regarding this topic, and his response was that, he is going to build a training course in the future!
0 Likes 0 ·
Congrats Dani for crossing 2000!
0 Likes 0 ·
Thanks @Cyborg!! Now I can edit your answer :-)
0 Likes 0 ·
ramesh 1 avatar image
ramesh 1 answered
hi performance report are of three type cpu bottle neck, memory bottle neck, I/O and user bottle neck,add these bottle neck . collect disk space utilization, check nightly jobs executed fine or not. click on start -> run -> perfmon-> add counters like memory bottle neck available mBYtes sql buffer manager cache hit ratio\ page life expectancy cpu bottle neck 1.total processor time 2.query queue lenght 3.transaction /sec h/d 1. cpu usage " should be less than 25 % or cpu is in preassur 2. networl i/o query should be 0 or else somthing wrong
10 |1200

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

Håkan Winther avatar image
Håkan Winther answered
Good question, but it is a huge area to cover! As ramesh answered, CPU, Memory & IO is common problems, but it is not the only ones. You may also experience problems with Locking, parallelism, indexes (too few or too many), statistics (out of date), bad queries, cursors, implicit conversions, etc. You need to find all this kinds of issues and include them in your report. You can find some of the information in the performance counters ( sys.dm_os_performance_counters ), the profiler, dynamic management views, execution plans, and of course the code. Our company have a complete "toolbox" to find all of these kind of issues.
10 |1200

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

ThomasRushton avatar image
ThomasRushton answered
"It depends". Sorry. Seriously, though - what is your target audience for this report? That'll help outline the sort of things you can & can't do in it - management-types are interested in pretty pictures that quickly show a trend of something. Techies may be more interested in snapshot information. My steps would be: 1. Define your report's audience 2. Decide what you want the report to show 3. Work out how to gather the data required for the report 4. Then start building. And now to prepare for a meeting where I'm, erm, presenting a report to management... ;-)
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
Everyone has excellent answers on this (and you'll all be receiving nice big fat +1's). But the real issue is not writing a report. The real issue is gathering the performance metrics into a location from which you can query in order to write a report. That's step 1. Step 2 is writing the report and you should follow everyone's answers. As far as gathering the metrics goes, assuming you don't have a 3rd party monitoring tool, you'll need to gather performance counters using Performance Monitor (perfmon). I'd strongly suggest collecting the counters into a file and the moving the data from the file into a SQL Server database, two step process there. I'd also suggest gathering individual query performance metrics using event tracing in a server-side trace. Again, output to file and then import the data from the file into a database. With the information in your database, you can begin building your report.
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.

You are absolutely right, to be able to create a report, you have to gather the performance metrics and what you should to is to create a performance baseline to compare the metrix over time to see if the performance decreases (that is a part of our "toolbox").
0 Likes 0 ·
"Everyone has excellent answers on this and you'll all be receiving nice big fat +1's...". OK, so istead of writing +1 to everyone, I will do **+1** this time (to make it nice and big) :)
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.