Re: Extremely Slow Table

garydevstore (GaryDataStore@xxxxxxxxx) writes:
> Which contains 5.6 Million rows and has non clustered indexes on Date,
> ReasonID, maj_Reason, Connection. Compared to other tables on the same
> server this one is extremely slow. A simple query such as :

Maybe some terminology is in order here. A road can be fast, but that
does not help you, if you car has a steering wheel out of order causing
you to zig-zag over the road. A car can be fast, but that does not help
if the road is in poor condition, so you cannot driver faster than 30 km/h
anyway.

In this case, the table is the road, and the query plan is the car. A
table itself does not move, but it can be badly fragmented in which case
it can be slow to drive through.

More likely, the query plan is not the best for the query. This is your
query:

> SELECT
> SD.reason_desc,
> SD.Duration,
> SD.maj_reason_desc,
> SD.[Connection],
> SD.aolEnteredBy
> FROM dbo.[Sherlock Data] SD
> Where SD.[Date] > Dateadd(Month,-2,Getdate())


There is a non-clustered index on Date. Assuming that rows are added
to this table regularly, there are presumably quite a few rows that
fits this condition. There are two ways for the optimizer to evaluate
this query: using the index, or scanning the table. The index is good
if only few rows are hit, but if many rows are hit the table scan is
faster. This is because, with the index you will need to read the same
page more than once.

The optimizer makes it choice of plan from the statistics SQL Server
has sampled about the table. The statistics may be out of date (even
if by default SQL Server auto-updates statistics). Try an UPDATE
STATISTICS WITH FULLSCAN, to see if this makes any difference.

But the road can also be in poor condition, that is the table can be
badly fragmented. This can be analysed with DBCC SHOWCONTIG and
remedied with DBCC DBREINDEX.

As suggested in other posts, you should look at the query plan, and see
if it says Clustered Index Scan or Index Seek + Bookmark Lookup.




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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

.

Relevant Pages

  • Re: Planning multiple queries
    ... > so it seems to me a new plan should be prepared for the second query, ... If you submit them all in one batch, SQL Server generates a query plan ... If no statistics get updated as result of the query, ...
    (comp.databases.ms-sqlserver)
  • Re: WHERE clause question
    ... > no reason that select * with where should run slower. ... There are two parts to executing a query. ... SQL Server can start spooling rows as soon as it finds matches and has the ... if the statistics is outdated, ...
    (microsoft.public.sqlserver.programming)
  • Re: Optimizer not using the good index
    ... I would guess that the statistics are inaccurate. ... use Query Analyzer and look at the estimated execution plan ... Open another window and get the actual execution plan. ... not work always since SQL Server only maintains distribution stats for 200 ...
    (microsoft.public.sqlserver.server)
  • Re: WHERE clause question
    ... There are two parts to executing a query. ... > SQL Server can start spooling rows as soon as it finds matches and has the ... > "Quentin Ran" wrote in message ... if the statistics is outdated, ...
    (microsoft.public.sqlserver.programming)
  • RE: Heavy performance slowdown after migrating from SQL Server 200
    ... What I'm, really wondering about is, why SQL Server 2008 queries every part ... as it was a single query on its own without any linkage. ... that the query plan optimizer would have produced a query plan first querying ...
    (microsoft.public.sqlserver.fulltext)