Server Health Check

Question

Server Health Check

tt-615680

SSCertifiable

Points: 7835
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

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)

GreyBeard Mr or Mrs. 500 Points: 535 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

jasonwi1202 SSC Veteran Points: 291 More actions · Answer 4

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