Tuesday, March 27, 2012

Database Healthchecks

Hi,
Does anyone have any good suggestions for things to look at/run etc. in a
database healthcheck?
It's purely the database I'm interested in, not the server.
Many thanks.Hi Chappers
There are a number of things you can do.
Look at the SQL Error logs and Windows Event logs and look for any
unexpected messages.
Run a regular 'DBCC CHECKDB' and check the output.
Look at the various ways you can use profiler and perfmon to get more
information on the health of your database. Here are links to two good
articles.
Perfmon Tips
http://www.sql-server-performance.c...onitor_tips.asp
Profiler Tips
http://www.sql-server-performance.c...ofiler_tips.asp
Make sure your tables are not getting too fragmented. If your database is
not that large, you can just set up a full maintenence plan (this will do th
e
dbcc's as well).
If it's too large (ie maint plan takes a couple of hours or more to run).
You can use dbcc showcontig (see BOL) to check table fragmentation and
schedule index rebuilds where applicable.
Hope this helps
John
"Chappers" wrote:

> Hi,
> Does anyone have any good suggestions for things to look at/run etc. in a
> database healthcheck?
> It's purely the database I'm interested in, not the server.
> Many thanks.|||Thanks John.
I have my own list of stuff to do and deliberately left the question open to
see what other people came up with so I could see what I'd missed.
Many thanks for taking the time to reply.
Paul
"John Bandettini" wrote:
[vbcol=seagreen]
> Hi Chappers
> There are a number of things you can do.
> Look at the SQL Error logs and Windows Event logs and look for any
> unexpected messages.
> Run a regular 'DBCC CHECKDB' and check the output.
> Look at the various ways you can use profiler and perfmon to get more
> information on the health of your database. Here are links to two good
> articles.
> Perfmon Tips
> http://www.sql-server-performance.c...onitor_tips.asp
> Profiler Tips
> http://www.sql-server-performance.c...ofiler_tips.asp
> Make sure your tables are not getting too fragmented. If your database is
> not that large, you can just set up a full maintenence plan (this will do
the
> dbcc's as well).
> If it's too large (ie maint plan takes a couple of hours or more to run).
> You can use dbcc showcontig (see BOL) to check table fragmentation and
> schedule index rebuilds where applicable.
> Hope this helps
> John
> "Chappers" wrote:
>|||Regarding index fragmentation - don't just rebuild indexes because they have
fragmentation - whether it will have any effect on performance depends on
the queries you're running. Also, rebuilding an index takes and additional
1.2x the size of the index and makes the index offline for the duration of
the operation - you should consider using DBCC INDEXDEFRAG instead. More
info on all of this can be found in our whitepaper below:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chappers" <Chappers@.discussions.microsoft.com> wrote in message
news:29D074BE-1443-467A-B2F7-0A28691370DC@.microsoft.com...
> Thanks John.
> I have my own list of stuff to do and deliberately left the question open
to[vbcol=seagreen]
> see what other people came up with so I could see what I'd missed.
> Many thanks for taking the time to reply.
> Paul
> "John Bandettini" wrote:
>
is[vbcol=seagreen]
do the[vbcol=seagreen]
run).[vbcol=seagreen]
in a[vbcol=seagreen]

No comments:

Post a Comment