|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query is very slow then fast
the first time I run this query select count(id) as total from table It takes over 10 minutes to return a result. Sure, I know now that there are much more efficient methods to retrieve the number of records in a table (some stored procedure), but that is not my point. In fact, I can reproduce this behavior with other queries on the same table. What I don't understand is that after the first run, when I run the same query again, it returns in 2 seconds or less. Not a little bit faster, but 100++ times faster. When I restart the SQL Server service (just for fun :-), no for testing this), the first time I run the query it is back to 10 minutes, the second+ time it is 2 seconds. This is obviously some sort of cache, but I don't know where to look. I have looked into execution plans on a level which is very interesting for sure, but still leaves the problem as is. I know now for example how to see if a execution plan is in the cache, and if it is, the query is fast. So it looks like something with execution plans. However, that is still not my solution. I have tried options to run the query with "execution plan hints", in fact I have hinted the total execution plan, but still, if it isn't in the cache, the query is very slow. I have tried stored procedures since I thought those have execution plans precompiled, but still, first time very slow, second+ time fast. I would really like anyone to point me in the right direction. How can I control this cache behaviour? Which cache are we talking about? And what is good practice? If it is execution plans, is it possible to actively put these in the cache and keep them there? Thanks a lot in advance. Ivo A cached execution plan is not likely to bee the cause of this difference.
The time to create an execution plan for a query like select count(id) from <table> is minimal, and far less than 10 minutes. It is much more likely that the reason for this is that when you execute the query the first time, SQL has to read all the data from disk. For this query, that would be the whole table unless you have a nonclustered index that is useful for this query, in which case, it would read the entire nonclustered index from disk. I presume that this is what is taking the 10 minutes. But if you have enough memory allocated to SQL Server, when the table is read into memory, it can stay in the data cache. Then the next time the query is run the data is retrieved from memory rather than disk. And, of course, memory access can easilly be hundreds of times faster than disk access. Tom <i**@ritense.com> wrote in message Show quoteHide quote news:844ee20b-dd46-489b-a7ac-a3f5d0bd7708@g1g2000pra.googlegroups.com... > Hi, I have a question. I have a rather large table in my database and > the first time I run this query > > select count(id) as total from table > > It takes over 10 minutes to return a result. Sure, I know now that > there are much more efficient methods to retrieve the number of > records in a table (some stored procedure), but that is not my point. > In fact, I can reproduce this behavior with other queries on the same > table. What I don't understand is that after the first run, when I run > the same query again, it returns in 2 seconds or less. Not a little > bit faster, but 100++ times faster. When I restart the SQL Server > service (just for fun :-), no for testing this), the first time I run > the query it is back to 10 minutes, the second+ time it is 2 seconds. > > This is obviously some sort of cache, but I don't know where to look. > > I have looked into execution plans on a level which is very > interesting for sure, but still leaves the problem as is. I know now > for example how to see if a execution plan is in the cache, and if it > is, the query is fast. So it looks like something with execution > plans. However, that is still not my solution. I have tried options to > run the query with "execution plan hints", in fact I have hinted the > total execution plan, but still, if it isn't in the cache, the query > is very slow. I have tried stored procedures since I thought those > have execution plans precompiled, but still, first time very slow, > second+ time fast. > > I would really like anyone to point me in the right direction. How can > I control this cache behaviour? Which cache are we talking about? And > what is good practice? If it is execution plans, is it possible to > actively put these in the cache and keep them there? > > Thanks a lot in advance. > > Ivo In addition to Tom's comments, how often are you restarting SQL Server that
this is a major issue? If your queries really need to pull very large tables then that is all the more reason to not stop and start SQL Server so often. While it is not true in all cases, I have instances of SQL Server 2005 with uptimes measured in months, not days. If this is happening with the exact same queries (and the exact same results) without a restart in between, then perhaps you should consider additional memory so that the data doesn't get pushed out by other queries. Another thing to consider is paging. If you have a result set that takes 10 minutes to generate all of the results, this is likely far too much data for any user to consume as a whole. Does someone sit on a web page and wait for all of this data? Do they really need all of it, and do they complain often after they have come back from getting lunch? On 12/16/08 3:17 PM, in article O5BIat7XJHA.***@TK2MSFTNGP06.phx.gbl, "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote: Show quoteHide quote > A cached execution plan is not likely to bee the cause of this difference. > The time to create an execution plan for a query like select count(id) from > <table> is minimal, and far less than 10 minutes. It is much more likely > that the reason for this is that when you execute the query the first time, > SQL has to read all the data from disk. For this query, that would be the > whole table unless you have a nonclustered index that is useful for this > query, in which case, it would read the entire nonclustered index from disk. > I presume that this is what is taking the 10 minutes. But if you have > enough memory allocated to SQL Server, when the table is read into memory, > it can stay in the data cache. Then the next time the query is run the data > is retrieved from memory rather than disk. And, of course, memory access > can easilly be hundreds of times faster than disk access. > > Tom > > <i**@ritense.com> wrote in message > news:844ee20b-dd46-489b-a7ac-a3f5d0bd7708@g1g2000pra.googlegroups.com... >> Hi, I have a question. I have a rather large table in my database and >> the first time I run this query >> >> select count(id) as total from table >> >> It takes over 10 minutes to return a result. Sure, I know now that >> there are much more efficient methods to retrieve the number of >> records in a table (some stored procedure), but that is not my point. >> In fact, I can reproduce this behavior with other queries on the same >> table. What I don't understand is that after the first run, when I run >> the same query again, it returns in 2 seconds or less. Not a little >> bit faster, but 100++ times faster. When I restart the SQL Server >> service (just for fun :-), no for testing this), the first time I run >> the query it is back to 10 minutes, the second+ time it is 2 seconds. >> >> This is obviously some sort of cache, but I don't know where to look. >> >> I have looked into execution plans on a level which is very >> interesting for sure, but still leaves the problem as is. I know now >> for example how to see if a execution plan is in the cache, and if it >> is, the query is fast. So it looks like something with execution >> plans. However, that is still not my solution. I have tried options to >> run the query with "execution plan hints", in fact I have hinted the >> total execution plan, but still, if it isn't in the cache, the query >> is very slow. I have tried stored procedures since I thought those >> have execution plans precompiled, but still, first time very slow, >> second+ time fast. >> >> I would really like anyone to point me in the right direction. How can >> I control this cache behaviour? Which cache are we talking about? And >> what is good practice? If it is execution plans, is it possible to >> actively put these in the cache and keep them there? >> >> Thanks a lot in advance. >> >> Ivo > > Thanks for your comments guys. This is clear.
Tom, I'm pretty sure you are right, since also other queries like count (*) from table where category = 1 run very fast later, which is because the table data is in cache. I see the memory usage for SQL Server go sky high anyway after running this query the first time. Any change I can see what is in the data cache? Aaron, I'm doing these restarts just to reproduce this behavior. On production I leave it running ofcourse. But this behavior then comes back a different way. There is one query that runs for a specific task, say once a week. The first time it is very very slow, then after this it is fast. I assume the result is then pushed out from the cache before the next time it runs. So, these restarts (on development server) were just to reproduce. And you are both right, there are different ways to solve this. The count can be done through a stored procedure and the other slow query I will solve with an index. It is just that I was curious to know what behavior was causing this, and I thought it had something to do with execution plans. You have now solved this mystery for me. > Aaron, I'm doing these restarts just to reproduce this behavior. You can also flush data cache with:USE MyDatabase CHECKPOINT DBCC DROPCLEANBUFFERS Of course, this is generally something you'd do on a test system rather than in production.  
Other interesting topics
testing 2000 to 2005 update
Problems with NOEXPAND Partial restore without restoring PRIMARY FG Transaction Log Backups selective application of condition in an SQL querry NVARCHAR Limit sql2005 log file is full Lost product key sql server 2005 different sql server 2000 I don't want my DML queries to wait until lock is released, insteadd I want my DML to abort |
|||||||||||||||||||||||
