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
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.
"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... ;-)
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.