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.co...nitor_tips.asp
Profiler Tips
http://www.sql-server-performance.co...filer_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:

> 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.co...nitor_tips.asp
> Profiler Tips
> http://www.sql-server-performance.co...filer_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/pro.../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]

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]

Database Design

Dear All,
I have just visited a colleague who advised me that the best way to design a
database (speed maximisation for end user be it web or vb) is not to have one
large database but to have your data split into 4 databases where the 4
divisions data is stored separately, rather than one larger database that
stores all data that has to be queried through Stored Procedures. The
databases are likely to be stored on the same hard disk so I don't think
there is a Serial I/O benefit to be gained.
These are not going to be large databases (only about 20,000 records). I
just don't know if my colleague is correct. The logic seems correct but where
is the evidence?
Thanks again.
Alastair MacFarlane
Hi Alastair,
You would get more benifits from ensuring you have the required indexes and
constraints in the database model then partioning the database.
The biggest benifit any databse can get is indexes and correct design. From
there and only after should you think about partionaing.
So while partionaing of Very large databases is good , first do the normal
stuff and then worry about it.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:D9B3A0AD-A0EB-4066-A91A-80B9BD0FA4AB@.microsoft.com...
> Dear All,
> I have just visited a colleague who advised me that the best way to design
> a
> database (speed maximisation for end user be it web or vb) is not to have
> one
> large database but to have your data split into 4 databases where the 4
> divisions data is stored separately, rather than one larger database that
> stores all data that has to be queried through Stored Procedures. The
> databases are likely to be stored on the same hard disk so I don't think
> there is a Serial I/O benefit to be gained.
> These are not going to be large databases (only about 20,000 records). I
> just don't know if my colleague is correct. The logic seems correct but
> where
> is the evidence?
> Thanks again.
> Alastair MacFarlane

Database Design

Dear All,
I have just visited a colleague who advised me that the best way to design a
database (speed maximisation for end user be it web or vb) is not to have on
e
large database but to have your data split into 4 databases where the 4
divisions data is stored separately, rather than one larger database that
stores all data that has to be queried through Stored Procedures. The
databases are likely to be stored on the same hard disk so I don't think
there is a Serial I/O benefit to be gained.
These are not going to be large databases (only about 20,000 records). I
just don't know if my colleague is correct. The logic seems correct but wher
e
is the evidence?
Thanks again.
Alastair MacFarlaneHi Alastair,
You would get more benifits from ensuring you have the required indexes and
constraints in the database model then partioning the database.
The biggest benifit any databse can get is indexes and correct design. From
there and only after should you think about partionaing.
So while partionaing of Very large databases is good , first do the normal
stuff and then worry about it.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:D9B3A0AD-A0EB-4066-A91A-80B9BD0FA4AB@.microsoft.com...
> Dear All,
> I have just visited a colleague who advised me that the best way to design
> a
> database (speed maximisation for end user be it web or vb) is not to have
> one
> large database but to have your data split into 4 databases where the 4
> divisions data is stored separately, rather than one larger database that
> stores all data that has to be queried through Stored Procedures. The
> databases are likely to be stored on the same hard disk so I don't think
> there is a Serial I/O benefit to be gained.
> These are not going to be large databases (only about 20,000 records). I
> just don't know if my colleague is correct. The logic seems correct but
> where
> is the evidence?
> Thanks again.
> Alastair MacFarlane