Prev: sql agent JOB to Backup Database and Maintain Backup Files
Next: Yellowfin Business Intelligence Release 4.0 Announced to Industry
From: Marc Baker on 30 Jun 2008 15:59
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,
From: Plamen Ratchev on 30 Jun 2008 16:37
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.com/tips/update_statistics_p1.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Marc Baker on 30 Jun 2008 17:35
On Jun 30, 4:37 pm, "Plamen Ratchev" <Pla...(a)SQLStudio.com> wrote:
> Outdated statistics can cause such performance issues. Update the statistics
> and see if that helps:http://msdn.microsoft.com/en-us/library/ms187348.aspxhttp://msdn.microsoft.com/en-us/library/ms173804.aspxhttp://www.sql-server-performance.com/tips/update_statistics_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.
From: Plamen Ratchev on 30 Jun 2008 17:58
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

From: Erland Sommarskog on 30 Jun 2008 18:05
Marc Baker (mbaker(a)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, esquel(a)sommarskog.se

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
 |  Next  |  Last
Pages: 1 2 3
Prev: sql agent JOB to Backup Database and Maintain Backup Files
Next: Yellowfin Business Intelligence Release 4.0 Announced to Industry