none
how to check health of the sql server and database RRS feed

Answers

  • Hi Johnny201186,

    This is quite a large topic. You have to monitor lots of performance counters, as well as other information like wait and file stats from inside SQL to really get a good server "health check".

    If by a health check, you mean checking the consistency of the database. Then you would need to run a DBCC CHECKDB against the database. This would definitely affect performance if the database is a highly transactional database due to kind of locks the consistency check needs to acquire while performing the operation and also the IO done on the database would increase.

    Normally when I perform a health check I usually do a number of things. You can get a good start by running the SQL Server Best Practice Analyzer : against your servers.

    In case you want to check the performance metrics for the database. Eg. The most expensive queries executed against the database w.r.t. to I/O or CPU or Duration. Then you could use Performance Dashboard.


    Regards, Amber zhang
    Monday, December 12, 2011 4:51 AM
    Moderator

All replies

  • Pls check this http://blog.sqlauthority.com/2007/11/17/sql-server-2005-best-practices-for-sql-server-health-check/

    http://uk.linkedin.com/in/ramjaddu
    Thursday, December 8, 2011 3:14 PM
  • Hi Johnny201186,

    This is quite a large topic. You have to monitor lots of performance counters, as well as other information like wait and file stats from inside SQL to really get a good server "health check".

    If by a health check, you mean checking the consistency of the database. Then you would need to run a DBCC CHECKDB against the database. This would definitely affect performance if the database is a highly transactional database due to kind of locks the consistency check needs to acquire while performing the operation and also the IO done on the database would increase.

    Normally when I perform a health check I usually do a number of things. You can get a good start by running the SQL Server Best Practice Analyzer : against your servers.

    In case you want to check the performance metrics for the database. Eg. The most expensive queries executed against the database w.r.t. to I/O or CPU or Duration. Then you could use Performance Dashboard.


    Regards, Amber zhang
    Monday, December 12, 2011 4:51 AM
    Moderator