In SQL Server page swapping is one of the most destructive events that can occur within your environment. The reason is that SQL Server maintains several caches with its largest one being the buffer pool, which it uses for fast data access. By costing out the expense and frequency of retrieving data, SQL does it’s best to make sure those pages that are accessed frequently, or take a heavy toll to retrieve from disk are sitting in memory. When swapping occurs Windows takes data supposed to be in memory and places it on to disk. When SQL Server asks for data from windows which it assumes to be in memory, the OS goes to disk to get it. Doing so blows all the memory management algorithms SQL is designed to use. To compound this issue, windows algorithm for swapping is much different then SQL Servers for flushing to disk. Hence the cost of retrieving data from the swap file is often more expensive in comparison to SQL Server simply flushing it to disk, and retrieved it in a normal IO read. Once your server starts getting into a swapping condition, it’s difficult to recover, but there are a few tricks to recover and prevent this.
Setting Up Your SQL Server to Avoid Page Swapping
First you need to understand the Min/Max memory settings within SQL Server. This is actually the buffer pool setting. Most of your memory used in SQL Server comes out of here but not all of it, and manipulating this setting is the key to having an efficient system. When setting up your memory in SQL Server do not use the default or affinity setting as the max. The reason being is if SQL Server believes it has this much memory it will quickly surpass the amount of physical memory you have on the box. The word from Redmond will be use the default and they’ve figured out not to page with it, but I’ve yet to see a system get it right. This is a statement not out of arrogance but experience.
Understand that the other processes on your box that needs memory. The Windows OS takes 2 GB off the bat. If there are other apps running on the box make sure they are accounted for. As an enterprise DBA I make sure only SQL Server is running on the server, but budgetary constraints might not make this possible. However put only what you need. Don’t chew up 200 MB of memory running a web browser. Understand that your memory setting is for the SQL Server engine, not it’s add-on features that come at of the box. Reporting Services, Analysis Services, SSIS, Full-Text search, all run in there own memory space and should be accounted for if they’re sharing the environment. Also understand processes within SQL Server will run outside the memory set for your buffer pool (Max Memory) setting, such as extended stored procedures, CLR’s, linked servers and even large query plans. In SQL Server 2005 you can actually see how different caches use the different portions of memory. The best tool to use is the “sys.dm_os_memory_cache_counters” DMV which gives a breakdown of memory usage. The single_page_kb fields points directly to the buffer pool or the max memory setting in your server configuration. The multi_page_kb field points to memory being used outside the buffer pool, or reserved memory. In SQL 2000 and before this was known as the MTL (Memory to Leave) area. By using this DMV you can determine how much of your data is residing outside the buffer pool.
Unfortunately 64 bit systems aren’t as forgiven as 32 bit systems. Under 32 bit systems, changing the max memory system down to a reasonable level, will often kick the system into making the proper adjustments on the fly. The 64 bit systems seem to have more trouble in this area. Once a SQL Server data page gets mapped to a swapped page, the OS wants to continue to map that page and tweaking the buffer pool, doesn’t provide the immediate relief you need. The ultimate fix here is to restart SQL Services, with the new max memory configuration, as doing so wipes out any mapping between SQL and Windows for memory mapping. However this is a tall task to do on a production system with dollars on the line. The cheat here is too flush your buffer cache until it’s appropriate to restart SQL Services. The command to do this is “DBCC DROPCLEANBUFFERS”, for good measure I’d drop the proc cache as well. Make sure prior to running this you’ve made your max memory changes that are appropriate for your system. There is of course a hit here, which is all data that once resided in memory needs to be reloaded, so you’ll see some sluggishness after the command, but data is quickly reloaded. Before leaving you I’d like to give some suggestions in setting the Max Memory configuration. My personal preference is to give 2 GB’s to the OS, and an additional ½ GB for every 8 GB’s of memory. On a system with 8 total GB’s I give the Max Memory config 5.5, for 16 then it’s 13GB, for 32 it’s 29GB, and so on.
Showing posts with label SQL Server Memory. Show all posts
Showing posts with label SQL Server Memory. Show all posts
Monday, April 27, 2009
Thursday, February 26, 2009
SQL Server Memory Usage With Text Fields
In a recent blog I talked about covering indexes for memory optimization within the buffer pool. I used a fictitious comments table to make my point, about how one large field could destroy querying for particular values. After posting I got the thinking about the example I used, and in the real world would I actually design it, in that fashion. The waffling answer is maybe. The reason I say this is that there is another solution that works with the clustered index, without the need to create a second covering index. Rather then using a Char or a Varchar to hold the actual comments field, you could use a text column. Text fields by default reside in separate data pages then the other fields within the row. Rather then these text fields residing in the same b-tree as the clustered index the data row contains a pointer to the text data that lives in a separate page. The effect this has on your memory consumption is, if the text field is not specified within your query it is never brought into memory. Lets go through some code examples similar to my last blog to see the actual impact.
The following code sets up 2 tables, one using a char field for comments and one using a text field for comments.
Create table CommentsText
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText text,
CONSTRAINT Pk_intText PRIMARY KEY CLUSTERED (PkComment))
Create table CommentsChar
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText char(2000),
CONSTRAINT Pk_intChar PRIMARY KEY CLUSTERED (PkComment))
Declare @int int
set @int = 1
While @int <>
Begin
insert into CommentsText(CommentType, CommentDate, CommentText) select datepart(ms, getdate()), Getdate(), replicate('A', 2000)
set @int = @int + 1
end
Insert into CommentsChar
Select CommentType, CommentDate, CommentText from CommentsText
Once setup I’ll free the memory, and run the same aggregation query of my last blog, where we aggregate based on types of comments for each day.
checkpoint
dbcc Dropcleanbuffers
select count(*), CommentType, CommentDate from CommentsText
group by CommentType, CommentDate
select count(*), CommentType, CommentDate from CommentsChar
group by CommentType, CommentDate
Once complete, we’re going to run a memory allocation script very similar to the one found in BOL under the sys.dm_os_buffer_descriptors section
--Memory Consumption Script
SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC
The results show a difference of a 10 to 1 difference in memory usage favoring the table using a text field. As explained in the last blog this is due to fact that the comments field does live with the rest of the row data. However when we add a covered index, this changes for the char table by eliminating the comments field from the queried data structure.
Create index commentschartest on commentschar(CommentType, CommentDate)
checkpoint
dbcc Dropcleanbuffers
select count(*), CommentType, CommentDate from CommentsText
group by CommentType, CommentDate
select count(*), CommentType, CommentDate from CommentsChar
group by CommentType, CommentDate
--Memory Consumption Script
SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC
The results now show both tables consuming reasonable, with slightly better performance going to the table using the covered index. The difference in size can be attributed to the overhead of maintaining pointers to the text fields in the root data, which is 24 bytes of data per row. The question as a DBA you need to make within your envirornment is; Is it more efficient to use a covered index or simply put the data in a text field. In most situations the covered index, allows for more flexibility, and better overall performance.
Before leaving the subject I’d like to touch on the idea of using Varchar(max) as in comparison to a Text field. Varchar(max) tries to provide the best of both worlds. The rule of thumb on varchar(max) is data that will fit on a single page with the other data in the row, will live side by side with the data. However if the whole field exceeds 8KB then the field will be placed in overflow pages. From a memory consumption point of view, with a similar query above, the text field will be more efficient, for the queries we’ve been looking at.
The following code sets up 2 tables, one using a char field for comments and one using a text field for comments.
Create table CommentsText
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText text,
CONSTRAINT Pk_intText PRIMARY KEY CLUSTERED (PkComment))
Create table CommentsChar
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText char(2000),
CONSTRAINT Pk_intChar PRIMARY KEY CLUSTERED (PkComment))
Declare @int int
set @int = 1
While @int <>
Begin
insert into CommentsText(CommentType, CommentDate, CommentText) select datepart(ms, getdate()), Getdate(), replicate('A', 2000)
set @int = @int + 1
end
Insert into CommentsChar
Select CommentType, CommentDate, CommentText from CommentsText
Once setup I’ll free the memory, and run the same aggregation query of my last blog, where we aggregate based on types of comments for each day.
checkpoint
dbcc Dropcleanbuffers
select count(*), CommentType, CommentDate from CommentsText
group by CommentType, CommentDate
select count(*), CommentType, CommentDate from CommentsChar
group by CommentType, CommentDate
Once complete, we’re going to run a memory allocation script very similar to the one found in BOL under the sys.dm_os_buffer_descriptors section
--Memory Consumption Script
SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC
The results show a difference of a 10 to 1 difference in memory usage favoring the table using a text field. As explained in the last blog this is due to fact that the comments field does live with the rest of the row data. However when we add a covered index, this changes for the char table by eliminating the comments field from the queried data structure.
Create index commentschartest on commentschar(CommentType, CommentDate)
checkpoint
dbcc Dropcleanbuffers
select count(*), CommentType, CommentDate from CommentsText
group by CommentType, CommentDate
select count(*), CommentType, CommentDate from CommentsChar
group by CommentType, CommentDate
--Memory Consumption Script
SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC
The results now show both tables consuming reasonable, with slightly better performance going to the table using the covered index. The difference in size can be attributed to the overhead of maintaining pointers to the text fields in the root data, which is 24 bytes of data per row. The question as a DBA you need to make within your envirornment is; Is it more efficient to use a covered index or simply put the data in a text field. In most situations the covered index, allows for more flexibility, and better overall performance.
Before leaving the subject I’d like to touch on the idea of using Varchar(max) as in comparison to a Text field. Varchar(max) tries to provide the best of both worlds. The rule of thumb on varchar(max) is data that will fit on a single page with the other data in the row, will live side by side with the data. However if the whole field exceeds 8KB then the field will be placed in overflow pages. From a memory consumption point of view, with a similar query above, the text field will be more efficient, for the queries we’ve been looking at.
Tuesday, February 24, 2009
SQL Server Memory Usage With Covered Indexes
In my last blog I talked about clearing memory caches to free up memory. Most of the time this is a bit of a hack. The true solution lies in shrinking how much data actually goes into memory, which falls upon the size of your data structures. In particular how much data can you fit into a SQL Server data page. SQL Server maintains a one to one relation between it’s data pages on disk, and the data pages brought into memory. It’s also an all or nothing deal, meaning if you need one column of 4 bytes from one row, you need to bring the entire 8KB data page up into memory. In this blog I’ll talk about how to use covered indexes to maximize these data pages, so you’re not using memory on unneeded datum.
Before getting going I want to provide a data setup script as well as memory consumption query that will be used through out this post. The memory consumption query is a knock off of the one found in BOL under sys.dm_os_buffer_descriptors, with a couple of tweaks.
--Data Setup
Create table Comments
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText char(2000),
CONSTRAINT Pk_intT1 PRIMARY KEY CLUSTERED (PkComment))
Declare @int int
set @int = 1
While @int <>
Begin
insert into Comments(CommentType, CommentDate, CommentText) select datepart(ms, getdate()), Getdate(), replicate('A', 2000)
set @int = @int + 1
end
--Memory Consumption Script
SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC
Now that we’re ready to go, let me explain, what we have. The newly created comments table is a fairly common structure for holding user comments. The bulk of the table is the comment itself with some metadata to describe it. CommentType might map to; Question, Comment, Authors reply, Etc, while commentDate is the Date of the comment. In this current setup the root pages of the clustered index is capable of holding only 3 rows per data page, because of the large size of the comments field. Before starting let’s flush the memory.
checkpoint
dbcc Dropcleanbuffers
Assume that in the application one of the calls aggregates the count of this table, by type and date. Notice in this query that text is never referenced.
select count(*), CommentType, CommentDate from Comments
group by CommentType, CommentDate
Once complete run the memory consumption query. You’ll notice 23279 data pages or 181MB’s of memory are being consumed here. The ironic part is most of the data allocated is not needed. This is where covering your index comes in. By covering your index you’re creating a b-tree data structure with data only required to fullfill the data requirements of one or more specific queries. Let me preference the last statement in that your index will contain the values of your primary key for reference. Below is the statement to create the index.
create index coveredtest on Comments(CommentType, CommentDate)
Once Complete rerun the following Queries, by clearing the memory, running the aggregate statement, then running the memory consumption script. The consumption script is left out below to save space.
checkpoint
dbcc Dropcleanbuffers
select count(*), CommentType, CommentDate from Comments
group by CommentType, CommentDate
Not only did the query return considerably faster but the data page usage is down to 211 or 2 MB. Over the entire server this will become a significant savings. Before ending this, I need to add fair warning, as know your application before implementing. If modifications are occuring regularly, or your comments are being queried in such a way that the commentText column is being read regularly, then it does belong in memory, and your savings will not be that significant, and might actually hurt your memory pool. On that my last word in the real world you’d use a varchar over a char, but this was easiest to make a point.
Before getting going I want to provide a data setup script as well as memory consumption query that will be used through out this post. The memory consumption query is a knock off of the one found in BOL under sys.dm_os_buffer_descriptors, with a couple of tweaks.
--Data Setup
Create table Comments
(pkcomment int identity(1,1),
CommentType int,
CommentDate datetime,
CommentText char(2000),
CONSTRAINT Pk_intT1 PRIMARY KEY CLUSTERED (PkComment))
Declare @int int
set @int = 1
While @int <>
Begin
insert into Comments(CommentType, CommentDate, CommentText) select datepart(ms, getdate()), Getdate(), replicate('A', 2000)
set @int = @int + 1
end
--Memory Consumption Script
SELECT count(*)AS cached_pages_count,
(count(*) * 8)/1024 as SizeInMb, name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC
Now that we’re ready to go, let me explain, what we have. The newly created comments table is a fairly common structure for holding user comments. The bulk of the table is the comment itself with some metadata to describe it. CommentType might map to; Question, Comment, Authors reply, Etc, while commentDate is the Date of the comment. In this current setup the root pages of the clustered index is capable of holding only 3 rows per data page, because of the large size of the comments field. Before starting let’s flush the memory.
checkpoint
dbcc Dropcleanbuffers
Assume that in the application one of the calls aggregates the count of this table, by type and date. Notice in this query that text is never referenced.
select count(*), CommentType, CommentDate from Comments
group by CommentType, CommentDate
Once complete run the memory consumption query. You’ll notice 23279 data pages or 181MB’s of memory are being consumed here. The ironic part is most of the data allocated is not needed. This is where covering your index comes in. By covering your index you’re creating a b-tree data structure with data only required to fullfill the data requirements of one or more specific queries. Let me preference the last statement in that your index will contain the values of your primary key for reference. Below is the statement to create the index.
create index coveredtest on Comments(CommentType, CommentDate)
Once Complete rerun the following Queries, by clearing the memory, running the aggregate statement, then running the memory consumption script. The consumption script is left out below to save space.
checkpoint
dbcc Dropcleanbuffers
select count(*), CommentType, CommentDate from Comments
group by CommentType, CommentDate
Not only did the query return considerably faster but the data page usage is down to 211 or 2 MB. Over the entire server this will become a significant savings. Before ending this, I need to add fair warning, as know your application before implementing. If modifications are occuring regularly, or your comments are being queried in such a way that the commentText column is being read regularly, then it does belong in memory, and your savings will not be that significant, and might actually hurt your memory pool. On that my last word in the real world you’d use a varchar over a char, but this was easiest to make a point.
Thursday, February 19, 2009
SQL Server Cleaning Memory Caches
If you’re ever running a trace and you notice the occasional sproc taking unexpected longer periods then it should, such as doing 20 reads and taking 500 ms, you most likely have an IO bottleneck. Depending on the frequency determines the severity of the problem. For example if a sproc does 20 IO’s and returns in under 1 ms, 999 out of 1000, with the one going 500 ms, it’s expected behavior. The reason for this, is that under most systems you’re not keeping all your data in memory, and the call needs to go down to disk occasionally. SQL Server does a good job of read-aheads on scans, to avoid this problem, but not such a good job when it comes to seeks with key and Rid lookups. Hence it’s usually on the seeks when this is most common. Continuing with the trace analysis, if you’re seeing this problem once for every 10 calls then you’ve got an IO problem, which is you’re not able to keep enough data in memory.
There are three ways to approach this issue. The first is to buy more memory. By increasing the memory footprint, you allow for more data in the buffer pool, and less disk access. The second method around this is speed up your disk IO, by putting faster drives and or more spindles in raid, access times become much faster, and maybe you get the occasional call of 500 MS to 50 MS. These two above approaches have a hardware cost associated with, and often isn’t an option for a budget strapped shop such as a startup.
So how does the budget tight shop handle this: By ensuring that the system memory being used is most efficient? Over the next few blogs I’ll be discussing different strategies. In this blog I’ll talk about reviewing your memory clerks/cachestores and looking for areas that can be trimmed and maintained.
Getting into the internals of these memory stores is beyond the scope of this article, please reference http://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx if you want a deeper understanding. When troubleshooting, think of these Stores as carved out pieces of memory where sql server stores different types of data. The most popular one is the procedure cache where plans are kept. To get off subject for a second, the old procedure cache of SQL Server 2000 is now broken up into two cache stores “CACHESTORE_SQLCP” and “CACHESTORE_OBJCP”. One for ad-hoc queries and one for object code such as Stored sprocs and functions. SQL Server tracks it’s different cachestores using algorithms for costing and pruning. It’s these pruning processes that ensure the sizes don’t grow out of control. Unfortunately these algorithms aren’t what I consider optimal and therefore I regularly clean them manually.
With all the explanation out of the way, lets talk about getting at these stores and cleaning them up. The easiest way to view this data is to query the dmv sys.dm_os_memory_clerks. BOL has a descent explanation of this, but I want to point out the difference between single_pages_KB and Multi_pages_kb. Single_pages are those of 8 MB, or standard data pages. These reside within the SQL Server buffer pool. Multi_Pages are data pages that are larger then 8 MB, and reside in SQL Server’s reserved memory (Under 2000 this was called Memeory to Leave area). Our concern in the query below is looking at Single_pages or the buffer pool hence the sort.
select * from sys.dm_os_memory_clerks
order by single_pages_kb desc
Once you review the output you need to decide if any of these cache stores are larger then you like. For example in July 2007 I ran into a memory leak with TokenAndPermUserStore cache, where this cache was consuming 4 GB’s of the buffer pool and growing. This issue was fixed in a SP2 culumative patch. What led me down the trail to this blog was this morning the lock manager cache was 800 MB. Although the latter is not considered a memory leak by Microsoft, my personal feeling is it shouldn’t be that large, and I’d rather give the memory back to the buffer pool for better use.
In order to clear a particular cache you want to leverage the dbcc freesystem command, and paramiterize with the name of the cache. The name is the return “name” column in the query above. For example if I wanted to clear the lock manager cache my command would look like this:
DBCC FREESYSTEMCACHE ('Lock Manager : Node 0')
Once your store is clear, sql will start rebuilding, that store as if the service was restarted. Before leaving the subject a couple or notes. First the command above works on Node 0 which for all intensive purposes links to a CPU on a Numa system. There are some variations to how Numa works and would suggest reading the following blog.
http://blogs.msdn.com/psssql/archive/2008/01/24/how-it-works-sql-server-2005-numa-basics.aspx
Second the two largest caches you’ll see are those that represent the procedure cache “CACHESTORE_SQLCP” and “CACHESTORE_OBJCP”. It your system is architected properly then all calls should be through an object, but you’ll often see you’re ad-hoc cache (CACHESTORE_SQLCP) is larger. This is because the ad-hoc cache is often full of system statements primarily run in msdb. It makes sense to only clear the ad-hoc cache, for 3 reasons. First the msdb calls very rarely leverages cache, since its dynamic based on timestamps. Second if you clear the object cache all your objects will require recompilation which hurt your system in resources used as well as the chance of parameter sniffing occurring. Finally your object store has a ceiling based on how many SP’s you have on your server.
There are three ways to approach this issue. The first is to buy more memory. By increasing the memory footprint, you allow for more data in the buffer pool, and less disk access. The second method around this is speed up your disk IO, by putting faster drives and or more spindles in raid, access times become much faster, and maybe you get the occasional call of 500 MS to 50 MS. These two above approaches have a hardware cost associated with, and often isn’t an option for a budget strapped shop such as a startup.
So how does the budget tight shop handle this: By ensuring that the system memory being used is most efficient? Over the next few blogs I’ll be discussing different strategies. In this blog I’ll talk about reviewing your memory clerks/cachestores and looking for areas that can be trimmed and maintained.
Getting into the internals of these memory stores is beyond the scope of this article, please reference http://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx if you want a deeper understanding. When troubleshooting, think of these Stores as carved out pieces of memory where sql server stores different types of data. The most popular one is the procedure cache where plans are kept. To get off subject for a second, the old procedure cache of SQL Server 2000 is now broken up into two cache stores “CACHESTORE_SQLCP” and “CACHESTORE_OBJCP”. One for ad-hoc queries and one for object code such as Stored sprocs and functions. SQL Server tracks it’s different cachestores using algorithms for costing and pruning. It’s these pruning processes that ensure the sizes don’t grow out of control. Unfortunately these algorithms aren’t what I consider optimal and therefore I regularly clean them manually.
With all the explanation out of the way, lets talk about getting at these stores and cleaning them up. The easiest way to view this data is to query the dmv sys.dm_os_memory_clerks. BOL has a descent explanation of this, but I want to point out the difference between single_pages_KB and Multi_pages_kb. Single_pages are those of 8 MB, or standard data pages. These reside within the SQL Server buffer pool. Multi_Pages are data pages that are larger then 8 MB, and reside in SQL Server’s reserved memory (Under 2000 this was called Memeory to Leave area). Our concern in the query below is looking at Single_pages or the buffer pool hence the sort.
select * from sys.dm_os_memory_clerks
order by single_pages_kb desc
Once you review the output you need to decide if any of these cache stores are larger then you like. For example in July 2007 I ran into a memory leak with TokenAndPermUserStore cache, where this cache was consuming 4 GB’s of the buffer pool and growing. This issue was fixed in a SP2 culumative patch. What led me down the trail to this blog was this morning the lock manager cache was 800 MB. Although the latter is not considered a memory leak by Microsoft, my personal feeling is it shouldn’t be that large, and I’d rather give the memory back to the buffer pool for better use.
In order to clear a particular cache you want to leverage the dbcc freesystem command, and paramiterize with the name of the cache. The name is the return “name” column in the query above. For example if I wanted to clear the lock manager cache my command would look like this:
DBCC FREESYSTEMCACHE ('Lock Manager : Node 0')
Once your store is clear, sql will start rebuilding, that store as if the service was restarted. Before leaving the subject a couple or notes. First the command above works on Node 0 which for all intensive purposes links to a CPU on a Numa system. There are some variations to how Numa works and would suggest reading the following blog.
http://blogs.msdn.com/psssql/archive/2008/01/24/how-it-works-sql-server-2005-numa-basics.aspx
Second the two largest caches you’ll see are those that represent the procedure cache “CACHESTORE_SQLCP” and “CACHESTORE_OBJCP”. It your system is architected properly then all calls should be through an object, but you’ll often see you’re ad-hoc cache (CACHESTORE_SQLCP) is larger. This is because the ad-hoc cache is often full of system statements primarily run in msdb. It makes sense to only clear the ad-hoc cache, for 3 reasons. First the msdb calls very rarely leverages cache, since its dynamic based on timestamps. Second if you clear the object cache all your objects will require recompilation which hurt your system in resources used as well as the chance of parameter sniffing occurring. Finally your object store has a ceiling based on how many SP’s you have on your server.
Subscribe to:
Posts (Atom)
