slow queries for a particular database

[Marc Baker]

Bear with me here folks, I don't know much MS SQL performance.
Wondering if someone can point me in the right direction. I have 1
particular database where queries are slow. To test this theory, I am
running a SELECT COUNT(*) on some of the tables that have many rows
(2-4 million rows). Example: A table with approx 3.5 million rows, a
count takes over 10 mins. to run initially. If I just do a count on
the indexed field, SELECT COUNT(ID), it takes about 2 minutes. I have
other similiar databases, same tables, with just as much data, with
the same indicies/constraints, etc., and it is much quicker (1 to 2
secs. to run). I've moved the db with slow queries to another server,
and it is still slow there (single processor server). Other db's on
that server are just fine.

Any help/hints are appreciated.

Thanks,

[Plamen Ratchev]

Outdated statistics can cause such performance issues. Update the statistics
and see if that helps:
http://msdn.microsoft.com/en-us/library/ms187348.aspx
http://msdn.microsoft.com/en-us/library/ms173804.aspx
http://www.sql-server-performance.co...istics_p1.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Marc Baker]

On Jun 30, 4:37*pm, "Plamen Ratchev" <[e-mail]:

Outdated statistics can cause such performance issues. Update the statistics
and see if that helps:http://msdn.microsoft.com/en-us/libr...istics_p1.aspx

HTH,

Plamen Ratchevhttp://www.SQLStudio.com

There was a maintenance plan to update the statistics already for this
db. I deleted, and reset it up, and executed manually. Completed
successfully in approx. 7 mins., but a count is still taking an
extremely long time.

[Plamen Ratchev]

Can you try SELECT COUNT(*) on a table that has a narrow non-clustered index
and see how long it takes to complete? If there is no non-clustered index on
the table all data pages have to be scanned to get the count.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

Marc Baker (mb****@selectsa.com) writes:

Bear with me here folks, I don't know much MS SQL performance.
Wondering if someone can point me in the right direction. I have 1
particular database where queries are slow. To test this theory, I am
running a SELECT COUNT(*) on some of the tables that have many rows
(2-4 million rows). Example: A table with approx 3.5 million rows, a
count takes over 10 mins. to run initially. If I just do a count on
the indexed field, SELECT COUNT(ID), it takes about 2 minutes. I have
other similiar databases, same tables, with just as much data, with
the same indicies/constraints, etc., and it is much quicker (1 to 2
secs. to run). I've moved the db with slow queries to another server,
and it is still slow there (single processor server). Other db's on
that server are just fine.

SELECT COUNT(*) on a table will use a non-clustered index if there is
one. But the first query is likely to drag data into cache, so it runs
faster the next time.

10 minutes for four million rows is a lot, but then again, that depends
on how wide the rows are. My gut reaction is that you have bad case of
fragmentation. Can you run DBCC SHOWCONTIG on this table?

Which version of SQL Server are you using?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marc Baker]

On Jun 30, 6:05*pm, Erland Sommarskog <[e-mail]:

Marc Baker ([e-mail]) writes:

Bear with me here folks, I don't know much MS SQL performance.
Wondering if someone can point me in the right direction. *I have 1
particular database where queries are slow. *To test this theory, I am
running a SELECT COUNT(*) on some of the tables that have many rows
(2-4 million rows). *Example: *A table with approx 3.5 million rows,a
count takes over 10 mins. to run initially. *If I just do a count on
the indexed field, SELECT COUNT(ID), it takes about 2 minutes. *I have
other similiar databases, same tables, with just as much data, with
the same indicies/constraints, etc., and it is much quicker (1 to 2
secs. to run). *I've moved the db with slow queries to another server,
and it is still slow there (single processor server). Other db's on
that server are just fine.

SELECT COUNT(*) on a table will use a non-clustered index if there is
one. But the first query is likely to drag data into cache, so it runs
faster the next time.

10 minutes for four million rows is a lot, but then again, that depends
on how wide the rows are. My gut reaction is that you have bad case of
fragmentation. Can you run DBCC SHOWCONTIG on this table?

Which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, [e-mail]

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

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.

This is on a SQL 2000 server SP4.

[Erland Sommarskog]

Marc Baker (mb****@selectsa.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marc Baker]

On Jul 1, 5:48*pm, Erland Sommarskog <[e-mail]:

Marc Baker ([e-mail]) 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, [e-mail]

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

There is 3GB of memory. Dynamically assigned by SQL. The other db's
do have similar size, and the exact same schema.

[Gert-Jan Strik]

Erland Sommarskog wrote:

>
Marc Baker (mb****@selectsa.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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

[Erland Sommarskog]

Marc Baker (mb****@selectsa.com) writes:

There is 3GB of memory. Dynamically assigned by SQL. The other db's
do have similar size, and the exact same schema.

I'm starting to run of ideas. One more last straw: check with
sp_helpdb whether autoclose is on for the database.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marc Baker]

On Jul 2, 5:30*pm, Erland Sommarskog <[e-mail]:

Marc Baker ([e-mail]) writes:

There is 3GB of memory. *Dynamically assigned by SQL. *The other db's
do have similar size, and the exact same schema.

I'm starting to run of ideas. One more last straw: check with
sp_helpdb whether autoclose is on for the database.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

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

This is on a non-production server that has hardly any activity. The
sp_helpdb did not show anything that stated autoclose. Thanks to all
for assistance and suggestions.

name
db_size
owner
dbid created
status
compatibility_level
--------------------------------------------------------------------------------------------------------------------------------
-------------
--------------------------------------------------------------------------------------------------------------------------------
------ -----------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------
FISH_MNT_FC
20415.19 MB
sa
40 Jun 26 2008 Status=ONLINE, Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
Collation=Latin1_General_BIN, SQLSortOrder=0,
IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics 80
name
fileid
filename
filegroup
size maxsize growth usage
--------------------------------------------------------------------------------------------------------------------------------
------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
------------------ ------------------ ------------------ ---------
MNT_SYS_DATA
1 F:\FishTest
\FISH_MNT_FC.mdf
PRIMARY
7552 KB Unlimited 10% data only
MNT_LOG
2 F:\FishTest
\FISH_MNT_FC_log.ldf
NULL
6965376 KB Unlimited 5% log only
MNT_DATA1
3 F:\FishTest
\FISH_MNT_FC_1.mdf
MNT_DATA_GROUP
13932224 KB Unlimited 10% data only

[Sybaseguru]

Marc Baker wrote:

On Jul 2, 5:30Â*pm, Erland Sommarskog <[e-mail]:

>Marc Baker ([e-mail]) writes:

There is 3GB of memory. Â*Dynamically assigned by SQL. Â*The other db's
do have similar size, and the exact same schema.

I'm starting to run of ideas. One more last straw: check with
sp_helpdb whether autoclose is on for the database.

If there are 29k+ extents (of 64k) scanned then at 100 random ios/sec thats
5 minutes. If the disks are cheaper/slower then 50 ios/sec is not
unreasonable, which gives you your 10 mins.

As previously posted a count(id) on a non-clustered index should just do an
index scan which depends on the size of the index and whether it is cached
or not.

Whilst you are running your query have perfmon running to check cpu and io

[Erland Sommarskog]

Marc Baker (mb****@selectsa.com) writes:

This is on a non-production server that has hardly any activity. The
sp_helpdb did not show anything that stated autoclose. Thanks to all
for assistance and suggestions.

Still clutching at straws...

That F: disk is not a USB disk, is it?

Could you run first run SET STATISTICS PROFILE ON, and then a a SELECT
COUNT(*) in the slow database, and a SELECT COUNT(*) on the same table in
other database and where the number of rows are the same and then post
the output?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx