Post reply

Server Health Check

  • July 23, 2011 at 8:10 am

    #240386

    Hi All,

    We are having a yearly mains supply check, therefore I would need to check for the availability and the health of the SQL Server. I would like to know what the nessessary steps to make sure everything is stable as possible please.

    Thank you in advance

  • July 23, 2011 at 12:46 pm

    #1357444

    tt-615680 (7/23/2011)

    Hi All,

    We are having a yearly mains supply check, therefore I would need to check for the availability and the health of the SQL Server. I would like to know what the nessessary steps to make sure everything is stable as possible please.

    Thank you in advance

    It's way to large a subject for a simple forum answer. I'd recommend you do a web-search for "SQL SERVER HEALTH CHECK".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 26, 2011 at 6:27 am

    #1358439

    Not a comprehensive list by any means; however, this is where I start for server / instance:

    Available Memory

    Buffer Cache Hit Ratio

    Page Life Expectancy

    Page Reads per Second

    Page Writes per Second

    File system latency measured as Free List Stalls

    File system latency measured as Lazy Writes Per Second

    Average Latch Wait Time in milliseconds

    CPU Utilization

    Number of User Connections

    Measure these over time to get trends then, there's query and data analysis.

    There are several very good sources available via Google that, when taken together, form the basis of an excellent periodic system check strategy. IMHO, snapshot statistics do not provide realistic metrics everything must be measured over time.

  • July 26, 2011 at 6:33 am

    #1358444

    pretty wide wave of the hands.. "how to i check my server's health"

    You'll probably want to define what you want to check, first, as Jeff said. We can help with details, but not so much on such a wide open question.

    GreyBeards got a great point...if you don't have a baseline for your server, you cannot say whether its doing good or bad for many of those perforamnce metrics.

    I'd also lean towards using Brent Ozars Blitz 60 minute takeover scripts[/url] to identify anything that was neglected and fallen throug the cracks.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 26, 2011 at 8:34 am

    #1358566

    On a lot of our servers, we like to keep performance traces running throughout the year. This allows us to look at a few reports to check on long running queries, consistent blocking, etc. Here is an article that describes the basics for setting up a performance trace and getting it loaded into a table:

    SQL Performance Trace

    Once you have the data getting loaded in a table on a daily basis, it is pretty easy to create a report off it.

    Jason

    Webmaster at SQL Optimizations School

Viewing 5 posts - 1 through 4 (of 4 total)

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