best practises for avoiding cache miss

anushahanna
anushahanna used Ask the Experts™
on
What steps do you take in your SQL Code to avoid cache misses?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope you referred to remove all Caches available in your datbase cache.

Then you have to issue

DBCC FREEPROCCACHE

to remove all cached elements. More info below

http://msdn.microsoft.com/en-us/library/ms174283.aspx
Commented:
1. Always prefix your procedures with schema name:

Correct: EXEC dbo.YourProc
Wrong: EXEC YourProc

2. Always use the same case for the procedure name:

Correct:
CREATE PROC dbo.YourProc ...
EXEC dbo.YourProc

Wrong:
CREATE PROC dbo.YourProc ...
EXEC dbo.yourproc

3. Do not use sp_ prefix for your stored procedures (SQL Server will look for such a procedure in master database first).

More to come...

Commented:
4. Minimize the use of temporary tables. They will generate cache misses (but not necessarily recompilations).

5. Do not change SET options when running your procedures (but this will usually cause a recompilation as well).
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
rrjegan17,
Actually, I asked about cachemiss that goes along with Cachehit.
thanks

Author

Commented:
brejk
can you kindly explain point #5
thanks

Author

Commented:
brejk.
Following these steps, have you estimated how low of a % of cachemiss & ExecContextHit have you been able to achieve?

Thanks

Author

Commented:
what % of misses are acceptable, as a standard?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
To check the number of cache hits and misses on your system, you can use SQL Profiler.

1. Launch SQL Profiler
2. Create a New Trace using a BLANK template
3. Click on the Events tab and select the following event classes
      * SP:CacheHit
      * SP:CacheMiss
4. Run the trace.

If you Issue DBCC FREEPROCCACHE then all the elements would create a new execution plan in the cache meaning that CacheMiss for that particular execution.

When you execute it again, then this time it would be executed from the cache.

So for first Cachehit, its a CacheMiss and for the second hit its fetched from Cache.

>> what % of misses are acceptable, as a standard?

In order to answer your question, % ideally doesn't represents the Acceptable standards.
You have to check the CacheMiss events and Analyse whether it is actually required ( In case of Distributed queries, Its recommended to create Execution plans freshly. Hence procedure will have WITH RECOMPILE option for it) or not.

Analyze those objects which needs to be fixed and work on it.

Hope this clarifies.

Author

Commented:
rrjegan17
Thanks for the helpful analysis. (FREEPROCCACHE will initially cause lot of misses)

Yes, I used SP:CacheHit and SP:CacheMiss and also a couple of other ones like ExecutionContextHits, CacheInserts, CacheRemoves (I believe I used all the cache options I could see )

Out of 100 SP's compiled, I get 43 MIsses, 17 Hits, 82 ExecutionContextHits, 1 CacheInserts, 2 CacheRemoves. (to make it for easier discussion, I reduced the numbers proportionally)

To see if the SPs have 'WITH RECOMPILE' option, I ran this:

exec sp_MSforeachtable
'
SELECT DISTINCT ''?'',  sysobjects.name, sysobjects.xtype
FROM syscomments
INNER JOIN sysobjects  ON syscomments.id=sysobjects.id
WHERE syscomments.TEXT like''%RECOMPIL%'' and sysobjects.xtype = ''p''
'

But it returned nothing. So will it be safe to assume there is no Distributed queries?

Can you please give your analysis on the Cache counts I mentioned above.

Thanks again.!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> So will it be safe to assume there is no Distributed queries?

Yes.. you can assume.
As for Distributed transactions or queries in Stored Procedures, Its recommended to have WITH RECOMPILE option.

>> Can you please give your analysis on the Cache counts I mentioned above.

You compiled 100 SP's out of which

43 MIsses -- Denotes that Cache plans for 43 Executions are not found in Cache.

1 CacheInserts -- 1 stored procedure has been inserted into the procedure cache.
2 CacheRemoves -- 2 stored procedure has been removed from the plan cache
82 ExecutionContextHits -- ExecutionContextHit was replaced by CacheHit in SQL Server 2005 and see definition for CacheHit below.
17 Hits -- indicates that a stored procedure is in the plan cache.

That means 82 + 17 cache was available in Procedure / plan Cache and 1 Insert to it with 2 Plans removed from cache either because of non-optimal cache plans.

Total is 82 + 17 + 1 = 100 ( 2 being removed )

Hope this clarifies.

Author

Commented:
rrjegan17
Thanks.

It is on a SS 2000, but was collected from a client server which runs SS 2005. If I had used SS2000 server to receive the profiler data, would the distinction between ExecutionContextHits  & Cachehits been more clearer?

As ExecutionContextHits  and Cachehits are the same, then we are talking about a high rate of hits, aren't we?

The 82 + 17 + 1 = 100 matches beautifully with the 100 SP's compiled. So where would the 43 misses relate to this equation?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> If I had used SS2000 server to receive the profiler data, would the distinction between ExecutionContextHits  & Cachehits been more clearer?

Yes.. CacheHits would not have appeared in 2000 making everything to be ExecutionContextHits.

>> So where would the 43 misses relate to this equation?

When you compile your procedure, it would check whether plan exists for that object in cache or not.
If yes, then it will take it from Cache.

Else while compiling, it would identify an Miss and hence trigger another event called CacheInsert which would insert the plan into cache making it as either ExecutionContextHits or CacheHits

You would have missed mentioning any other statistics there or it would have been an update to Cache because of any index statistics change too.

Author

Commented:
>>You would have missed mentioning any other statistics there or it would have been an update to Cache because of any index statistics change too.

rrjegan17,
Are you suggesting another profiler parameter that i may be missing.

What I am also understanding is the change in statistics can trigger misses?
thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> Are you suggesting another profiler parameter that i may be missing.

Something was missed out in calculating either CacheInsert / CacheRemoves

>> What I am also understanding is the change in statistics can trigger misses?

Yes.. It will.
If any optimal plan was found out then it would be a miss.

Author

Commented:
Thanks rrjegan17.

When you say "Something was missed out in calculating either CacheInsert / CacheRemoves", I assuming you are talking about human errors (i nhow I presented the data)?

is change in optimal plan a norm or an exception? thanks again.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I assuming you are talking about human errors (i nhow I presented the data)?

Yes.. Thought so..

>> is change in optimal plan a norm or an exception?

Its normal.. Because as and when records in your database increases, query plan to fetch records at optimum level also varies. It also varies based on the level statistics are updated..

Author

Commented:
rrjegan17,
What can I do in the 'level statistics' part, for better performance?
thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Can you kindly explain what you meant by 'level statistics'

Author

Commented:
that question was based on your observation/statement:

"It also varies based on the level statistics are updated.."
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Missing Punctuation:

It also varies based on the level, statistics are updated..

Author

Commented:
rrjegan17
What affects the level in which the statistics are updated?

Is it design or code in the SPs, queries against the DB, read/writes etc?

Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Is it design or code in the SPs, queries against the DB, read/writes etc?

Its with the Inserts, Updates, Deletes of records in the table.
And Index Recompilations, Re-organizations if Auto_Update statistics are set to ON.

Hope this clarifies.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial