Clear all caches for index performance testing, every time?
-
I'm in the middle of an index optimization project and beginning to think the way to compare queries "before index change to after index change" is to clear all caches every time. There's no guarantee a given query's data is going to be found in the buffer cache at run-time, and due to data skew and other issues a plan pulled from cache may be totally inappropriate ( client with many records runs a report which pulls an existing plan for that report created by a small client ).
Other than option(recompile) there isn't a whole lot I can do about inappropriate plan re-use ( we do clear the cache daily ), but it does seem that I'm not getting a true picture of things without clearing the data and plan cache before each test run. Without that, in my test environment the query I'm testing may find most or all of it's data in memory, leading to a misleading elapsed time.
Prod has 448 gb of memory with 7 TB of data so less than 6 % of data can be held in cache. In QA, with 50GB of memory but a full-sized copy of the prod DB, it's less than 1%
-
I never clear caches, and run queries twice before measuring to ensure caches are hot.
Reason: my application doesn't run with cold caches.Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes, and if you want to compare queries before and after, you can look at logical reads as well as elapsed time.
John
-
Isn't your cache essentially cold whenever a given query has to do physical reads? Unless you can hold the entire DB in memory. That's the trouble with testing in QA environments where my load is pretty much the only load. Even with a lot less memory, nobody else is trying to kick "mydata" out of the buffer cache.
In production I would think that, at a minimum, there's no guarantee a given query is going to find it's data in memory. Testing index changes with a hot cache, I may find little difference in run-time, but I can see the number of rows read/estimated etc is much higher without the index improvement. Almost an argument to discount run-time and focus on how lowered IO will benefit other queries.
Spotlight:
Page Life Expectancy
This shows the currentPage Life Expectancy.
SQL Server holds recently accessed database pages in a memory area called the Buffer Cache. If a SQL process needs to access a database page and that page is already in the buffer cache, then SQL Server does not need to read the page from disk. Thiscan save a significant amount of disk I/O and can speed up queries significantly.
Page Life Expectancy is the length of time in seconds that a database page will stay in the buffer cache without references. Large values mean that pages are staying in the buffer cache longer and that the buffer cache is behaving efficiently. Small values mean that pages are being flushed out of the cache within a small period of time and that the buffer cache is not being effective. Microsoft recommends 300 seconds as the minimum value; any less is indicitave of a shortage of memory.
-
I try to test as if it's real life. Yes, I clear cache at the start of a test because it's important to know what will happen and how long a compile takes if a suitable execution plan isn't already in cache. But then I also test at least 4 more times in rapid succession to see what happens when such a thing happens in real life.
As for PLE, it can be an indication that something happened but I place very low value in it. There are other indicators (buffer hits, for example) that will provide a whole lot more insight. Paul Randall has a couple of articles on the subject.
https://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy
https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
In my case real life would be a buffer cache stuffed with over 400GB of data ( data my query needs? who knows? ) Also real life would be a 5-7GB plan cache containing many plans that not only won't help, but may very well hurt my report when a large client runs it.
I'm not saying Microsoft's sql server developers could do any better, but with ORMs and skewed data, the plan cache may not be your friend. Stop using ORM and get rid of skewed data? Really?
Anyway I'm going to try totally hot cache testing, logging the run times pre/post index changes, but also looking at the change in logical reads.
-
Well our whole production sql server system is on Netapp Enterprise solid state drives now, and of course QA isn't. Actually we do have one QA environment quite similar to prod in terms of memory and the same solid state drives.
Now that I'm testing without clearing any caches, I'm finding definite improvements to indexing don't always yield big run-time drops, but they do yield a big reduction in logical and read-ahead reads. I suspect the run-times wold drop more with a prod-like load. ( just makes it harder to sell the changes to various review boards )
- Indianrock - Saturday, June 10, 2017 8:24 AM
It means you need to sell it differently. Tuning is not always about pure duration of a query
Reduction in logical reads means that less data has to be processed, which will make the query faster, will reduce CPU usage as well, and will make the buffer pool usage more efficient, meaning increased chance that relevant data will be in cache, reduced usage on the IO subsystem & fewer erratic spikes in performance as the query has to go to disk.Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks, that's just the "ammunition" I needed. You South Africans have an influence way beyond what the size of your country might suggest 🙂 I'm also a fan of Peter Hanmer http://www.peterhanmer.co.za/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply