SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql server 2008 R2 --- procedure Cache


Sql server 2008 R2 --- procedure Cache

Author
Message
wannabe1
wannabe1
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 489
Hi

We have a sql server 2008 R2 on physical server with RAM-- 512 gb.
we have around 300 gb of data in disk, the sql server memory has been set to 485gb.
sql server is using around 300gb of the memory.

the question here is, the plan cache hit ratio is only 65%.

can u please help me resolve this and increase the plan cache hit ratio to 100%

we are doing around 7000 batches/sec
we have enabled optimize for ad-hoc workloads to true and forced parameterization.

thanks for the help,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87253 Visits: 45272
Are you having performance problems due to the compiles?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


wannabe1
wannabe1
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 489
kind of..

the compiles/sec are almost half of the batches/sec
but as we don't have much load on the system, it is not really affecting.
the end user may see a delay if the query that came in was huge and has to be compiled again.

but we need to fix the compiles. i feel as the proc cache is being cleared, the compiles are going up.
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4657 Visits: 715
I guess then that you are not using stored procedures?
I've looked at a couple of apps we have which don't use procs and I notice that often plan reuse just doesn't happen.
You probably need to examine the queries in the cache to see what's not being reused, probably a fairly tedious task. The proc cache will only grow to a certain %age of size so maybe you're actually getting what can be best described as cache paging.
Although the reuse of sql plans/ad hoc queries is much better now it's still not perfect - it's amazing sometimes to see the volume of queries which parametise for msdb.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
wannabe1
wannabe1
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 489
That's true, we don't use SP's a lot. Most of the queries are ad-hoc.

why i am concerned about this memory is, we see CMEMTHREAD waits only in this server( version sql server 2008 R2).the other servers( version sql server 2008 sp1) have the same application code and the plan cache hit is low, but there is no CMEMTHREAD wait.


can you please help resolving the CMEMTHREAD waits.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28853 Visits: 9671
I'm not an expert by any means on waits, but you could find this very useful : http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Eddie Wuerch
Eddie Wuerch
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 2200
Having 512GB of memory lets me think you have a lot of CPUs in that box as well.

If you're seeing loads of CMEMTHREAD waits in SQL Server 2008 R2 on ad-hoc compiles on a server with more than 16 CPU cores, you are likely suffering from a bug in R2. (We dealt with this on a 48-core box)

Update to the latest hotfix. If that doesn't solve it, you may need a specific hotfix from MS Support.

-Eddie

Eddie Wuerch
MCM: SQL
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28853 Visits: 9671
Eddie Wuerch (8/10/2011)
Having 512GB of memory lets me think you have a lot of CPUs in that box as well.

If you're seeing loads of CMEMTHREAD waits in SQL Server 2008 R2 on ad-hoc compiles on a server with more than 16 CPU cores, you are likely suffering from a bug in R2. (We dealt with this on a 48-core box)

Update to the latest hotfix. If that doesn't solve it, you may need a specific hotfix from MS Support.

-Eddie


Do you have the kb link?
Eddie Wuerch
Eddie Wuerch
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 2200
Ninja's_RGR'us (8/10/2011)
Eddie Wuerch (8/10/2011)
Having 512GB of memory lets me think you have a lot of CPUs in that box as well.

If you're seeing loads of CMEMTHREAD waits in SQL Server 2008 R2 on ad-hoc compiles on a server with more than 16 CPU cores, you are likely suffering from a bug in R2. (We dealt with this on a 48-core box)

Update to the latest hotfix. If that doesn't solve it, you may need a specific hotfix from MS Support.

-Eddie


Do you have the kb link?


This looks similar, it may be the actual problem. I don't remember the particulars, but we did get the PSS hotfix around the time this KB article was published:

http://support.microsoft.com/kb/2492381
FIX: SQL Server 2008 R2 performs poorly when most threads wait for the CMEMTHREAD wait type if the threads use table variables or temp tables to load or update rowsets

Eddie Wuerch
MCM: SQL
wannabe1
wannabe1
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 489
We have concluded the same

we are planning to apply SP1.

Thanks for the help, I will let you know about the results after applying the service pack.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search