Database "corruption" causing terrible performance?

We have a database that has (until recently) exhibited great performance
[SQL Server 2000, Data (MDF) size = 50GB].
The schema has not changed for months, the only thing that changes is the
data.
The other day, performance of the web-site dropped to almost zero...30
seconds for a page to appear that normally would appear in milliseconds.
I first checked the schema by comparing it against a similar database. The
schema (indexes etc) were identical.
I then closed the website to all users except me. I switched on SQL
profiler and opened the site. As soon as a few queries hit the machine, the
CPU flat-lined at 100% and stayed there for a minute or two.
I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything untoward.
However, I ran a tool to completely rebuild every index (took most of the
day). After that, performance had not improved. I increased the size of
the data and log files for both this and the tempdb - no effect. I updated
the statistics etc, but again still to no avail. I considered defragmenting
the disc, but the log and data file were hardly fragmented at all. What
else to try?
In desperation, I created a brand new database with identical schema and
using DTS, copied all the data into this database from the original database
(took overnight). I am pleased to say that the web site now has blistering
performance once again!
OK - so what's wrong with my original database that the DBCC diagnostic
tools fail to show? Investigating further this morning, I found the
following:
When I right click on [newDB].[tableA] and select properties, it informs me
that there are 2365000 rows. Select count(*) ... agrees with this value.
However, if I do the same with [originalDB].[tableA] the properties tell me
that there are 0 (zero) rows, but select count(*) shows there to be 2365000
rows.
I don't know enough of the internals of SQL Server, but I'm guessing that
the code that informs the Properties page that there are zero rows might be
the same code that helps the Optimiser decide whether to use an index or
not.
If this is the case, then I'm hoping that there is a tool that I've
overlooked that can correct these statistics. If so, can someone let me
know what this tool is? Also, why would SQL Server get into this state (and
has it been fixed in 2005/2008)?
Thanks to everyone who reads this
Griff
Griff
Did you identify those long ruinning queries by Profiler, did not you? See
an execution plan of these queries, do they perfom table/index scan?
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:%23PTxDaNiIHA.4320@.TK2MSFTNGP06.phx.gbl...
> We have a database that has (until recently) exhibited great performance
> [SQL Server 2000, Data (MDF) size = 50GB].
> The schema has not changed for months, the only thing that changes is the
> data.
> The other day, performance of the web-site dropped to almost zero...30
> seconds for a page to appear that normally would appear in milliseconds.
> I first checked the schema by comparing it against a similar database.
> The schema (indexes etc) were identical.
> I then closed the website to all users except me. I switched on SQL
> profiler and opened the site. As soon as a few queries hit the machine,
> the CPU flat-lined at 100% and stayed there for a minute or two.
> I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything
> untoward. However, I ran a tool to completely rebuild every index (took
> most of the day). After that, performance had not improved. I increased
> the size of the data and log files for both this and the tempdb - no
> effect. I updated the statistics etc, but again still to no avail. I
> considered defragmenting the disc, but the log and data file were hardly
> fragmented at all. What else to try?
> In desperation, I created a brand new database with identical schema and
> using DTS, copied all the data into this database from the original
> database (took overnight). I am pleased to say that the web site now has
> blistering performance once again!
> OK - so what's wrong with my original database that the DBCC diagnostic
> tools fail to show? Investigating further this morning, I found the
> following:
> When I right click on [newDB].[tableA] and select properties, it informs
> me that there are 2365000 rows. Select count(*) ... agrees with this
> value. However, if I do the same with [originalDB].[tableA] the properties
> tell me that there are 0 (zero) rows, but select count(*) shows there to
> be 2365000 rows.
> I don't know enough of the internals of SQL Server, but I'm guessing that
> the code that informs the Properties page that there are zero rows might
> be the same code that helps the Optimiser decide whether to use an index
> or not.
> If this is the case, then I'm hoping that there is a tool that I've
> overlooked that can correct these statistics. If so, can someone let me
> know what this tool is? Also, why would SQL Server get into this state
> (and has it been fixed in 2005/2008)?
> Thanks to everyone who reads this
> Griff
>
|||> Did you identify those long ruinning queries by Profiler, did not you?
> See an execution plan of these queries, do they perfom table/index scan?
A very good question. Sadly, the answer is "no" and unfortunately, the site
is now live again and I'm not allowed to perform such tests on this database
as it will degrade the live DB's performance. I'll have to copy this rather
large DB to our DEV area to perform such a test.
As I mentioned, TableA shows "zero rows" when one pulls up the properties
page. Where is this information held?
I looked in the sysindexes table at the rows value. From my understanding,
if there is a clustered index, then the true row count should be returned
and then this value used for all non-clustered indexes. Now, this table
does not have a clustered index. There are two "indexes". The first one is
a non-clustered index and the rows value = 0 (which is what I'd expect).
The second is a primary key and the rows value is not 0 (which is what the
MSDN documentation would lead me to expect) but instead 258067. However,
the ACTUAL row count is 2365232 [select count()) from ...], so where the
rows value came from I've really no idea.
I've another example from TableB. This DOES have a clustered index. The
rows value here is 6180637 which agrees with Count(*). However, there are 5
other non-clustered indexes which all have the same rows value of 6177790 -
MSDN leads me to expect that this should equal the rows value for the
clustered index, but evidently it doesn't. And yes, I had completely rebuilt
the indexes and then run 'update statistics TableB' command before running
the query on sysindexes.
Griff
|||Interesting (well, I guess that depends...)
When I run the command:
DBCC SHOW_STATISTICS (TABLEA, TableA_IndexName)
On the new database, it returns three data sets (using query analyser here)
and the following message:
Statistics for INDEX 'TableA_IndexName'.
(1 row(s) affected)
(2 row(s) affected)
(200 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However...
When I run this on the original database, it returns NO data sets, just the
following message:
Statistics for INDEX 'TableA_IndexName'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Griff
|||Griff
Create a clustered index on TableA, and again run SQL Server Profile to see
what is going on the server.
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:OusS1iOiIHA.5724@.TK2MSFTNGP05.phx.gbl...
> A very good question. Sadly, the answer is "no" and unfortunately, the
> site is now live again and I'm not allowed to perform such tests on this
> database as it will degrade the live DB's performance. I'll have to copy
> this rather large DB to our DEV area to perform such a test.
> As I mentioned, TableA shows "zero rows" when one pulls up the properties
> page. Where is this information held?
> I looked in the sysindexes table at the rows value. From my
> understanding, if there is a clustered index, then the true row count
> should be returned and then this value used for all non-clustered indexes.
> Now, this table does not have a clustered index. There are two "indexes".
> The first one is a non-clustered index and the rows value = 0 (which is
> what I'd expect). The second is a primary key and the rows value is not 0
> (which is what the MSDN documentation would lead me to expect) but instead
> 258067. However, the ACTUAL row count is 2365232 [select count()) from
> ...], so where the rows value came from I've really no idea.
> I've another example from TableB. This DOES have a clustered index. The
> rows value here is 6180637 which agrees with Count(*). However, there are
> 5 other non-clustered indexes which all have the same rows value of
> 6177790 - MSDN leads me to expect that this should equal the rows value
> for the clustered index, but evidently it doesn't. And yes, I had
> completely rebuilt the indexes and then run 'update statistics TableB'
> command before running the query on sysindexes.
> Griff
>