SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Slow returning query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Robowski
Posting Yak Master

101 Posts

Posted - 02/14/2013 :  10:55:45  Show Profile  Reply with Quote
Sorry if this should go in the administrating forum.

I'm running a simple select which is taking around 20 seconds each time when usually it would be 1 second

SELECT * FROM table (nolock) where datetime = '' and area = ''

datetime & area are part of a clustered index on the table, and the query plan shows that 99% of the batch cost is on the clustered index seek.

Also, I would expect the query to be cached, so even if it is slow the first time it shouldn't be the second, third etc but this is 20 seconds each time!

DBCC SHOWCONTIG scanning table...
Table: (907150277); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 216286
- Extents Scanned..............................: 27041
- Extent Switches..............................: 27105
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.74% [27036:27106]
- Logical Scan Fragmentation ..................: 0.04%
- Extent Scan Fragmentation ...................: 42.07%
- Avg. Bytes Free per Page.....................: 703.9
- Avg. Page Density (full).....................: 91.30%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


P.S the index can't be changed in case anyone is looking to advise on that. Just wondering what sort of things to check to find out why this is so slow.


there are 7266271 and a simple select 8 from table returns in the same time as the when using a where!

Another table with the same amount of records returns in 1 second.

Abu-Dina
Posting Yak Master

United Kingdom
206 Posts

Posted - 02/14/2013 :  11:48:00  Show Profile  Reply with Quote
Perhaps the disk needs to be defraged?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.47 seconds. Powered By: Snitz Forums 2000