Post reply

Database Health Report

  • September 25, 2008 at 12:45 pm

    #154758

    I would like to generate a database health report that includes a dashboard summary and then some spreadsheets of performance details.

    Can anyone give me any ideas on how you currently report your overall health up to management? (From a presentation or graphical representation standpoint).

  • September 25, 2008 at 1:18 pm

    #877299

    Hi room,

    I am interested too.

    Thanks,

    Edwin

  • September 25, 2008 at 1:35 pm

    #877311

    I'd really love to hear what other people think is a "healthy" database. Hmm, maybe that would be a good Friday poll 😉

    I think that health has to be measured according to what you need from your system. Is it uptime? Is it space capacity? Is it some SLA of rate of transactions/data/something else?

    To me I've never reported on the health of the db. It's my job to manage it and from my side I've worried about average and peak CPU (peak across minutes. Every db will hit 100% at time on the CPU), disk space, memory usage (cache hit ratio) and then users and transactions. That's often for me to be able to request upgrades or budget for new hardware. It also lets me know if the db is performing well when someone says "it's slow."

    For reporting to management, I might tackle different metrics, perhaps rate of work or total work done, maybe some dashboard that shows them how the DB is performing for the business. They don't care about disk space or other stuff, but they would care about work being done, data stored, maybe estimates of time to recover if the server exploded, etc.

    Has your boss given you any guidance here?

  • September 25, 2008 at 1:42 pm

    #877317

    Health of a database to me relates to looking for signs of potential problems to come.

    I think you need to include fragmentation, available disk space vs database growth, query response time. Things that would tell you if you need to do something to the database or just leave it as it is.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 25, 2008 at 2:02 pm

    #877327

    I'm focusing right now on backups, capacity, CPU, replication, event log exceptions, job history, and various trends.

    I guess I was more curious if anyone actually has to do this now. I have a tabbed spreadsheet with each area and a way to quickly drop in information to roll up to a dashboard view for mgmt.

  • September 25, 2008 at 2:05 pm

    #877330

    I know SQL 2005 has Performance Dashboard reports you can import

    SQL 2008 has them built-in as Standard Report (right-click on server -> reports -> standard report)

    Some monitoring tools (like RedGate SQL Response) report "bad health"

    also in SQL 2008 there is Policy Management, so you can get a quick glance of health of the policies in Object Explorer Details

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • September 25, 2008 at 2:25 pm

    #877345

    I think you need to focus on exactly what you're going for first ... all of this data and more is available in DMV's/WMI counters. It's all easily accessible ...

  • September 25, 2008 at 2:37 pm

    #877351

    Let me refocus you all on the question. I know how to get the information, I was looking for ideas on presenting it in a fashion that is meaningful.

    I know managers like to see Gauges et al.

    I'll be using various tools to harvest and trend the data from my servers, it's the report ideas that I'm looking for specifically.

    And to answer the question about what I'm looking for as far as counters, I'm looking for anything that could be improved with analysis. My management would like to see just about every aspect of our SQL boxes trended out so that we can marry that against changing business needs.

    Thanks for all of the great comments so far though.

  • September 25, 2008 at 2:42 pm

    #877356

    Just a couple I currently use that work pretty well ...

    I display everything in reporting services with graphs (managers love the graphs):

    - DDL Changes

    - Job Activity Monitor (History, Trending)

    - Backup Information (sizes, counts, durations, etc.)

    - Performance Trending (reading from many WMI collectors)

    - SQL Users Audit (capturing unauthorized access on prod servers)

    - Database Sizes (trending)

    - Database Descriptions

    - Server Sizes (disk drives, trending)

  • September 25, 2008 at 3:21 pm

    #877364

    If you want gauges and want to impress managers, try dumping data into tables and reporting it with Reporting services from SQL 2008. From what I've seen MS has added some nice visual indicator and KPI objects.

    If you don't have access to SQL 2008 then I'd do the best I can with Reporting Services (2005) or Excel.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 26, 2008 at 6:17 am

    #877592

    Hi

    If you are using SQL 2005 for reporting systems, you can use Dundas charts which provides you with gagues.

    🙂

  • September 26, 2008 at 6:20 am

    #877594

    Hi Adam

    This Sounds interesting, how are you in cooperating this with report, i am new to reports, got some knowledge, haven't worked on it much, can you please explain me how to do this on reports, this is something which is going on back of my mind.

    Thanks in Advance

    Cheers

    🙂

  • September 26, 2008 at 6:46 am

    #877619

    Dundas does have some great looking gauges. Unfortunately, it's out of my price range. SQL Health and History canned reports are decent but they lack anything really management friendly.

  • September 26, 2008 at 7:07 am

    #877642

    you can download a trail version, if the reports are been used internally then i suggest to use, since you can only see a water mark on the chart stating that its a trail version and its has got unlimited free trail.

    you can use this until u change to SQL 2008, or if your managers like the chart sometimes they might fund for that as well 😉

    🙂

  • September 26, 2008 at 8:17 am

    #877734

    SQL Dba 808.

    I have used Excel to report similar information to management using graphs with a twist. I found managers not so interested in what the situation was last week or last month, heck the bean counters supplied them with that data. Rather I found that using Excel I could plot the trend of the data, showing the probably future for example the upward trend of disc usage (including the DB, Transaction logs, and backups) allowing them to forecast when and if they would have to / or not have to spend $$ for additional discs. Ditto for the time required to process data retrieval, addition, deletion - forecasting the need for additional memory - again spending $$. The trend forecasting allowed the managers to be proactive and as a bonus when I went forward with a request for equipment, personnel, etc., it did not come as a surprise to them.

    If you have Excel 2007 it has an excellent series of tools to perform trend analysis and add trend lines to a chart.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply