|
The following Reports do not need any code on the Server, typically run these against master or msdb
· Database Backup and Transaction Log Backup Status
o This will colour according to how long since the last backup
o it assumes all databases in full recovery should have transaction log backups
o You may wish to alter the setting according to your log backup frequency
· Workers , Signal Waits and cpu counters
o This is essentially ums stats
o Use this to check on numbers of workers and and worker waits
· Top 20 indexes in cache ( this is database specific so run against the database you’re interested in )
o It’s sometimes handy to know which tables and indexes are actually in cache
· Procedure Cache Status
o This was very useful when I was tracking memory problems
· Active Running Processes on the Server
o This captures what is actually running when you open this report
o It also captures the entire text of the running commands
o It’s excellent for capturing that query from hell
o May produce a big result set
· Plans in Cache
o OK this is messy and produces a lot of output
o You may want to restrict output further
o Hopefully the report explains it self.
· Blocked Users
o This was for specific use to show who was blocking who when blocking occurred
o Useful if you want to deploy a SSRS report to show basic blocking chains for non DBA support staff or you want to restrict users running their own queries against a production server
· Buffer Cache Usage
o Check out how your data cache is being used
o With many servers now deploying lots of memory it can be interesting to see how it’s actually used
o You may be surprised too!
· User Sessions – similar to sp_who2
o This was developed as a SSRS report to keep support staff off the production systems
o ( stops endless sp_who2 commands )
o You’ll probably want to customise the colour scheme
· So right click and save the zip files to your PC, extract the rdl files below to a folder on your workstation and open them in SSMS – it’s that easy!
o BackupStatus
o blockedusers
o BufferCache
o CachePlans
o Procedure Cache Status
o RunningProcesses
o shortsysprocesses
o Top 20 Indexes in cache
o Workers
· You don’t need to load them into BIDs, just browse to them from Custom reports and they will run.
· Here are examples of each report, except Plans in Cache (CachePlans1.rdl ) which was too difficult to manipulate to produce a suitable example.
o Database Backup and Transaction Log Backup Status
o Workers , Signal Waits and cpu counters
o Top 20 indexes in cache
o Procedure Cache Status
o Active Running Processes on the Server
o Blocked Users
o Buffer Cache Usage
o User Sessions
You will need BIDS if you wish to modify the reports ,deploy them to SSRS or change the logo
So what is this all about then?
· To support or tune a database server you must have the ability to collect statistical performance data, perfmon counters for example.
· You must be able to establish a baseline to which performance can be compared, otherwise how do you know if your server is busier than normal or quieter than normal.
· You must have regular reports of performance and reports that assist in diagnosing problems of any sort.
· You should also build Application Dashboards that track your particular applications, I will include a fictitious example.
· These reports are intended for the DBA therefore they assume you have sysadmin/dbo rights to the databases.
· You should have a DBA admin database on each server, this will hold your custom code.
I have a number of other reports which I will publish, some of which link to other statistical gathering processes
( A quick word about the posts - I work mainly in word rather than html so most of my posts and web pages are output from word , sorry, I do use Expression Web for grumpyolddba.co.uk. but again many of the pages still come from word. )
|