Query runs very slow
-
Hi,
I have query that ,when I try to run it, takes a very long time. But during the runtime if I cancel the running of that query,then next time the query will run very fast.I do not know what is the reason?When I checked the Execution Plan,it is using a Clustered Index seeking and it has 1 bilion records.AsI said if I cancel running the uery ,then next time it will work pretty fast.
I would appreciate,if someone help me on this.
-
Aspet Golestanian Namagerdi (11/4/2008)
Hi,I have query that ,when I try to run it, takes a very long time. But during the runtime if I cancel the running of that query,then next time the query will run very fast.I do not know what is the reason?When I checked the Execution Plan,it is using a Clustered Index seeking and it has 1 bilion records.AsI said if I cancel running the uery ,then next time it will work pretty fast.
I would appreciate,if someone help me on this.
Normally, when you run a query, the results are cached so when you run the same query again the retrieval is a lot quicker.
Regarding your query, it's a bit difficult to know why it's taking so long without seeing the query or knowing about the underlying data structures.
Are you trying to return one billion rows or just one row? Are the statistics updated, is the index fragmented, etc.... there are lots of possible reasons for a slow running query.
-
Aspet Golestanian Namagerdi (11/4/2008)
Hi,I have query that ,when I try to run it, takes a very long time. But during the runtime if I cancel the running of that query,then next time the query will run very fast.I do not know what is the reason?When I checked the Execution Plan,it is using a Clustered Index seeking and it has 1 bilion records.AsI said if I cancel running the uery ,then next time it will work pretty fast.
I would appreciate,if someone help me on this.
can you post your query
kshitij kumar
kshitij@krayknot.com
www.krayknot.com -
Unless your system is more than one hundred billion records in size, it's unlikely that it's doing a seek and returning one billion records. Can you post the execution plan?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Actually,It is trying to return only one record.my table has clustered index and by checking the execution plan,it is using Clustered-Index-Seek for its operation.But as I said when I run it for the first time,it takes long time and when I cancel and rerun it ,it runs very fast
-
and also I have to add that the table has 1bilion records
-
Sorry, I misunderstood the intent of "it has" in that sentence. Of course 1 row out a billion could be returned by a seek.
Can you post the execution plan?
Have you updated statistics recently?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
No,when I did DBCC showConfig,
This was the result:
DBCC SHOWCONTIG scanning 'TimeAndSale' table...
Table: 'TimeAndSale' (1842105603); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 78514854
- Extents Scanned..............................: 9912016
- Extent Switches..............................: 12685429
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 77.37% [9814357:12685430]
- Logical Scan Fragmentation ..................: 8.02%
- Extent Scan Fragmentation ...................: 4.34%
- Avg. Bytes Free per Page.....................: 138.2
- Avg. Page Density (full).....................: 98.29%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply