Re: slow queries for a particular database

Erland Sommarskog wrote:

Marc Baker (mbaker@xxxxxxxxxxxx) writes:
The tables in question both have a clustered and non-clustered index
(unique identifier only), and these are the DBCC SHOWCONTIG statistics
for one of those tables.

TABLE level scan performed.
- Pages Scanned................................: 234847
- Extents Scanned..............................: 29518
- Extent Switches..............................: 29517
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.45% [29356:29518]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 2.87%
- Avg. Bytes Free per Page.....................: 493.5
- Avg. Page Density (full).....................: 93.90%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

From what I am reading, this does not seem to be bad.

Yes, there is no problem with fragmentation here. But I notice that the
table is quite big. 1.9 GB (234847*8192), so if there are only 3.5 million
rows, the rows are fairly wide, around 550 bytes in average.

Still several minutes to do a SELECT COUNT(*) seems a bit excessive.
How much memory do you have in the machine?

Does the other databases that you don't think are slow have similar
size and schema?

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Yes, several minutes seems excessive, but only if the accessed disk is
used exclusively by SQL Server, and there is no other significant
database activity going on. If there is other database activity going
on, the OP could have blocking, and the available I/O might have to be
shared. If (because of all that) the storage engine decides not to Read
Ahead, and you have all these interruptions, then I can imagine reaching
10 minutes for (just) 2 GB.

--
Gert-Jan
SQL Server MVP
.

Relevant Pages

  • Re: Consequences of Error 644?
    ... If you get the errr while running DBCC - The error is telling you that a row ... but the index entry for some index is missing... ... It says that the problem is a SQL Server ... will happen repeatedly when the customer does the same work which caused the ...
    (microsoft.public.sqlserver.server)
  • Re: sqlservr
    ... > controller's firmware version affecting SQL Server. ... > made a firmware upgrade and aplied patch 8.00.973 on SQL ... > and must activate flag 818. ... DBCC TRACESTATUS ...
    (microsoft.public.sqlserver.server)
  • Re: Error: 16954, Severity: 10, State: 1 and Timeout problem
    ... On this DB are runing lot of threads from different applications. ... Probably, other aplication are locking resources, so the first one need ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Server Indexes
    ... set for faster Search. ... You can examimne this with DBCC SHOWCONTIG. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: INDEXDEFRAG FOR SQL SERVER 7.0
    ... I only implemented DBCC INDEXDEFRAG in SQL Server 2000. ... option is to rebuild the index. ... Can you post before and after results from DBCC SHOWCONTIG? ...
    (microsoft.public.sqlserver.server)