March 12, 2014 at 6:00 pm
Lynn Pettis (3/12/2014)
william.rees.howells (3/12/2014)
That is correct. I run it every time I run the query. The query speeds only go back to normal once I drop and recreate the database, table and days.That means you are clearing the buffer cache before running the query. This means the data has to be read from disk every time the query is run.
It appears to be intentional because he is doing testing and more or less a POC from the appearances of what has been said in the thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2014 at 6:03 pm
SQLRNNR (3/12/2014)
Lynn Pettis (3/12/2014)
william.rees.howells (3/12/2014)
That is correct. I run it every time I run the query. The query speeds only go back to normal once I drop and recreate the database, table and days.That means you are clearing the buffer cache before running the query. This means the data has to be read from disk every time the query is run.
It appears to be intentional because he is doing testing and more or less a POC from the appearances of what has been said in the thread.
That means the data never gets cached and query won't get the benefits from caching.
March 12, 2014 at 6:12 pm
Lynn Pettis (3/12/2014)
SQLRNNR (3/12/2014)
Lynn Pettis (3/12/2014)
william.rees.howells (3/12/2014)
That is correct. I run it every time I run the query. The query speeds only go back to normal once I drop and recreate the database, table and days.That means you are clearing the buffer cache before running the query. This means the data has to be read from disk every time the query is run.
It appears to be intentional because he is doing testing and more or less a POC from the appearances of what has been said in the thread.
That means the data never gets cached and query won't get the benefits from caching.
And that is ok as long as the process is the same every time. It's a baseline with a cold cache.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2014 at 8:14 pm
SQLRNNR (3/12/2014)
Lynn Pettis (3/12/2014)
SQLRNNR (3/12/2014)
Lynn Pettis (3/12/2014)
william.rees.howells (3/12/2014)
That is correct. I run it every time I run the query. The query speeds only go back to normal once I drop and recreate the database, table and days.That means you are clearing the buffer cache before running the query. This means the data has to be read from disk every time the query is run.
It appears to be intentional because he is doing testing and more or less a POC from the appearances of what has been said in the thread.
That means the data never gets cached and query won't get the benefits from caching.
And that is ok as long as the process is the same every time. It's a baseline with a cold cache.
Yeah, I agree. It doesn't explain what's happening. Especially since the same thing is happening locally. This is decidedly odd behavior assuming we have the full set of fact.s
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2014 at 8:56 pm
It could be that you're clearing the cache but not the execution plan. Try adding a CHECKPOINT and DBCC FREEPROCCACHE before the DBCC DROPCLEANBUFFERS and see what you get.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2014 at 2:11 am
Yes that is right. This is for performance testing. Originally I was trying to run this query on a database with just one file, and run the exact same query on a replica database with 8 files over 8 disks to see the speed difference. However, this nice little surprise has come up along the way and it has got me scratching my head!
March 13, 2014 at 3:43 am
Hi again,
I have found out what the problem was. Basically it boils down to me now using CHECKPOINT. I think that the following is what was happening:
1) INSERT DATA
2) PAGES NOW DIRTY
3) RUN DBCC DROPCLEANBUFFERS. This did not clear the cache because pages were dirty
4) Run Query over and over again with DROPCLEANBUFFERS. This is nice and quick because pages still in cache and DROPCLEANBUFFERS not actually clearing the cache at this point because pages still dirty.
5) BACKUP DB causing a checkpoint to happen.
6) All pages written to disk and are now clean.
7) RUN DROPCLEANBUFFERS. Now the cache is actually getting cleared because the pages are now CLEAN.
8) Run query with DROPCLEANBUFFERS. Data now being read from disk every time and not from cache.
Tell me if you think my theory is off?
March 13, 2014 at 3:48 am
Hi thanks for the response.
I added ran CHECKPOINT as well before the first query before the backup and both queries before and after the backup now take the same length of time. I guess that I was pretty stupid to not realize that dropCLEANbuffers would only clear clean pages and not dirty ones!
Please read my response to user SQLRNNR to get a rundown of what I think was happening.
Again, thanks.
March 13, 2014 at 7:33 am
william.rees.howells (3/13/2014)
Hi again,I have found out what the problem was. Basically it boils down to me now using CHECKPOINT. I think that the following is what was happening:
1) INSERT DATA
2) PAGES NOW DIRTY
3) RUN DBCC DROPCLEANBUFFERS. This did not clear the cache because pages were dirty
4) Run Query over and over again with DROPCLEANBUFFERS. This is nice and quick because pages still in cache and DROPCLEANBUFFERS not actually clearing the cache at this point because pages still dirty.
5) BACKUP DB causing a checkpoint to happen.
6) All pages written to disk and are now clean.
7) RUN DROPCLEANBUFFERS. Now the cache is actually getting cleared because the pages are now CLEAN.
8) Run query with DROPCLEANBUFFERS. Data now being read from disk every time and not from cache.
Tell me if you think my theory is off?
Yup - the checkpoint difference would be critical.
Good to hear that it is performing equally now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2014 at 10:03 am
william.rees.howells (3/13/2014)
Hi again,I have found out what the problem was. Basically it boils down to me now using CHECKPOINT. I think that the following is what was happening:
1) INSERT DATA
2) PAGES NOW DIRTY
3) RUN DBCC DROPCLEANBUFFERS. This did not clear the cache because pages were dirty
4) Run Query over and over again with DROPCLEANBUFFERS. This is nice and quick because pages still in cache and DROPCLEANBUFFERS not actually clearing the cache at this point because pages still dirty.
5) BACKUP DB causing a checkpoint to happen.
6) All pages written to disk and are now clean.
7) RUN DROPCLEANBUFFERS. Now the cache is actually getting cleared because the pages are now CLEAN.
8) Run query with DROPCLEANBUFFERS. Data now being read from disk every time and not from cache.
Tell me if you think my theory is off?
You should be able to verify this if you run the query wrapped with SET STATISTICS IO ON/SET STATISTICS IO OFF. If the pages are being retrieved from the buffer cache, you should see 0 physical reads and some n>0 logical reads in the statistics. If indeed you have successfully flushed dirty pages in the buffer cache to disk and freed all the buffers, you should see some n>0 physical reads and some n>0 logical reads.
Jason Wolfkill
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply