July 16, 2013 at 9:40 am
July 16, 2013 at 10:35 am
Local variables inside sprocs get you plans that use either average density for equality or 30% of all rows for inequality. This can help in some cases, but it is also almost guaranteed to get you a SUBOPTIMAL plan at least some of the time!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2013 at 10:57 am
Outstanding article! Very informative and clear. Really good writing.
July 16, 2013 at 12:23 pm
Excellent article. The best part was the demo. I think in 2012 the actual execution plan it self shows tempDB spills with a warning sign on the SELECT operator.
I have one question about the statement "Memory grants can take up to 75% of the buffer pool".
Q1: Is it the max memory for all Mem Grants that can be allocated to all currently running queries at a point in time?
Q2: You said memory grants are allocated from buffer pool. I thought buffer pool sole purpose was to cache data pages. So in case high memory grants there might be lot of disk flushing of cached data pages happen?
Memory is one of the most confusing things for me to understand. I will be grateful if you could answer the above questions?
Regards,
Nawaz.
July 17, 2013 at 2:07 am
dedicatedtosql (7/16/2013)
Excellent article. The best part was the demo. I think in 2012 the actual execution plan it self shows tempDB spills with a warning sign on the SELECT operator.I have one question about the statement "Memory grants can take up to 75% of the buffer pool".
Q1: Is it the max memory for all Mem Grants that can be allocated to all currently running queries at a point in time?
Q2: You said memory grants are allocated from buffer pool. I thought buffer pool sole purpose was to cache data pages. So in case high memory grants there might be lot of disk flushing of cached data pages happen?
Memory is one of the most confusing things for me to understand. I will be grateful if you could answer the above questions?
Regards,
Nawaz.
The MAX memory allocated to SQL server makes up the size of the buffer pool. If there is not enough memory for a query, the query will go into a waiting state with a wait type of "RESOURCE SEMAPHORE"
The buffer pool is used for data pages as well as other caches such as the plan cache.
July 17, 2013 at 10:07 am
Thank you very much for the reply. So all the memory that SQL Server process is taking up is called the Buffer Pool. In other words when we configure Max Server memory to sql server we are actually configuring its buffer pool.
I have one question about the wait type. So when threads are waiting for Memory Grant they accumulate "RESOURCE SEMAPHORE" wait type. There is another wait type called "CMEThread" which I am observing in our Prod servers. It is the 4 most aggregated wait type. I have google about it. I could not get a clear understanding of what that is. I know it also has to something to do with memory. But how exactly are the two wait types different? How do u explain CMEThread wait type to a complete amateur like me. Explanations I found online are very complex in nature.
I know this is not thread on Memory questions. If you think that this question is in-appropriate here I will post it is a proper thread.
Again sorry for the non-related question.
Regards,
Nawaz.
July 17, 2013 at 11:45 am
dedicatedtosql (7/17/2013)
Thank you very much for the reply. So all the memory that SQL Server process is taking up is called the Buffer Pool. In other words when we configure Max Server memory to sql server we are actually configuring its buffer pool.I have one question about the wait type. So when threads are waiting for Memory Grant they accumulate "RESOURCE SEMAPHORE" wait type. There is another wait type called "CMEThread" which I am observing in our Prod servers. It is the 4 most aggregated wait type. I have google about it. I could not get a clear understanding of what that is. I know it also has to something to do with memory. But how exactly are the two wait types different? How do u explain CMEThread wait type to a complete amateur like me. Explanations I found online are very complex in nature.
I know this is not thread on Memory questions. If you think that this question is in-appropriate here I will post it is a proper thread.
Again sorry for the non-related question.
Regards,
Nawaz.
You probably should start a new thread about your CMEThread wait. I will say that a) you see "very complex" explanations online because it is a "very complex" topic, b) if this is the 4th highest wait (and is of any sizeable value) I would STRONGLY recommend you get a professional on board to give your system a review. I am also curious what are the 3 wait types above it?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 17, 2013 at 12:17 pm
dedicatedtosql (7/17/2013)
Thank you very much for the reply. So all the memory that SQL Server process is taking up is called the Buffer Pool. In other words when we configure Max Server memory to sql server we are actually configuring its buffer pool.I have one question about the wait type. So when threads are waiting for Memory Grant they accumulate "RESOURCE SEMAPHORE" wait type. There is another wait type called "CMEThread" which I am observing in our Prod servers. It is the 4 most aggregated wait type. I have google about it. I could not get a clear understanding of what that is. I know it also has to something to do with memory. But how exactly are the two wait types different? How do u explain CMEThread wait type to a complete amateur like me. Explanations I found online are very complex in nature.
I know this is not thread on Memory questions. If you think that this question is in-appropriate here I will post it is a proper thread.
Again sorry for the non-related question.
Regards,
Nawaz.
As far as I know - CMEM waits is commonly associated with a high plan cache insertion rate. Do you have lots of Adhoc SQL statements?
Before anything - Get yourself a copy of this book. This is a must have for any DBA
July 17, 2013 at 2:43 pm
There is at least one bug fix for CMEThread waits: http://support.microsoft.com/kb/2492381
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 17, 2013 at 3:17 pm
Thank you all for answering my questions.
TheSQLGuru (7/17/2013)
dedicatedtosql (7/17/2013)
Thank you very much for the reply. So all the memory that SQL Server process is taking up is called the Buffer Pool. In other words when we configure Max Server memory to sql server we are actually configuring its buffer pool.I have one question about the wait type. So when threads are waiting for Memory Grant they accumulate "RESOURCE SEMAPHORE" wait type. There is another wait type called "CMEThread" which I am observing in our Prod servers. It is the 4 most aggregated wait type. I have google about it. I could not get a clear understanding of what that is. I know it also has to something to do with memory. But how exactly are the two wait types different? How do u explain CMEThread wait type to a complete amateur like me. Explanations I found online are very complex in nature.
I know this is not thread on Memory questions. If you think that this question is in-appropriate here I will post it is a proper thread.
Again sorry for the non-related question.
Regards,
Nawaz.
You probably should start a new thread about your CMEThread wait. I will say that a) you see "very complex" explanations online because it is a "very complex" topic, b) if this is the 4th highest wait (and is of any sizeable value) I would STRONGLY recommend you get a professional on board to give your system a review. I am also curious what are the 3 wait types above it?
The other waits are CXPACKET, LATCH_EX, ONDEMAND_TASK_QUEUE. Though out system is a OLTP system our business requires more rows to be returned. Hence there are many stored procs that run in parallel which return a lot of rows. More over we have some bulk batches running which run Stored Procs in parallel. That is the reason for such high CXPACKET waits. We r in the process of tuning them as much as possible in order to avoid SPs run in parallel.
July 17, 2013 at 3:22 pm
SQLSACT (7/17/2013)
dedicatedtosql (7/17/2013)
Thank you very much for the reply. So all the memory that SQL Server process is taking up is called the Buffer Pool. In other words when we configure Max Server memory to sql server we are actually configuring its buffer pool.I have one question about the wait type. So when threads are waiting for Memory Grant they accumulate "RESOURCE SEMAPHORE" wait type. There is another wait type called "CMEThread" which I am observing in our Prod servers. It is the 4 most aggregated wait type. I have google about it. I could not get a clear understanding of what that is. I know it also has to something to do with memory. But how exactly are the two wait types different? How do u explain CMEThread wait type to a complete amateur like me. Explanations I found online are very complex in nature.
I know this is not thread on Memory questions. If you think that this question is in-appropriate here I will post it is a proper thread.
Again sorry for the non-related question.
Regards,
Nawaz.
As far as I know - CMEM waits is commonly associated with a high plan cache insertion rate. Do you have lots of Adhoc SQL statements?
Before anything - Get yourself a copy of this book. This is a must have for any DBA
Actually our system does not does not have Adhoc statements embedded in the application code but. But lot of developers who have read only access run a lot queries all day for their reports and other purposes. Even I run a lot of DMVs. Should that be causing a lot of Plan cache pollution? If so I will have to instruct the developers to start using the recompile hint when ever they want to run some Adhoc. Please advice.
Regards,
Nawaz.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply