January 25, 2011 at 5:57 pm
I am trying to findout is there a specific figure which would specify the % of cache being allocated from the memory. i did total of procedural cache on of the servers and it was only about 20MB? How is that possible , i have assigned sql server 43 GB? there server is just dedicated to sql. This is 64 bit box?
January 26, 2011 at 5:57 am
There's no way to directly manage the size of cache. If that's all the cache that is getting used, that's all SQL Server needs. I'd double check to be sure that SQL Server really has allocated all the memory, but, assuming it has, you may not have accessed much data on the system yet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2011 at 9:08 am
Thanks..Is there a way to calculate total cache used over span of 8 hours?
January 26, 2011 at 9:26 am
To calculate usage over time, you'd need to gather metrics over time. You can use Permon to do this.
I can also suggest (full disclaimer, I work there) Red Gate's SQL Monitor as a means of gathering & reporting on those metrics and more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2011 at 2:04 pm
I really always ask this question to my self. If there is a native tool like perfmon to capture all the data why do people spend $$$ on third party tools?
January 26, 2011 at 2:57 pm
sqldba_icon (1/26/2011)
I really always ask this question to my self. If there is a native tool like perfmon to capture all the data why do people spend $$$ on third party tools?
Simplicity usually. And because for some, buying these tools can save money over what it might cost time wise to build the same thing. I am big on building my own tools personally, and in reality there is nothing that any of the third party tools do that you absolutely can't do (you may have to know how to write some code outside of SQL to do so, but it is always possible), but the fact that they package them up and make it easy to add/remove servers, load to central repositories quickly (again all things you can do but take time) can make these tools worth the price if your time is worth a great deal to you.
But really, to each their own when it comes to this kind of thing.
January 26, 2011 at 3:57 pm
Lack of technical knowledge, Lack of interpretational abilities, Lack of resources, Save time, money and effort, readymade monitoring alerts, Better functionality than perfmon.
Thank You,
Best Regards,
SQLBuddy
January 26, 2011 at 4:58 pm
sqldba_icon (1/26/2011)
I really always ask this question to my self. If there is a native tool like perfmon to capture all the data why do people spend $$$ on third party tools?
I'm perfectly capable of building my own tools, but I'd rather spend my time & effort in fixing the problems I have in my databases and applications. So, I purchase a tool that does stuff I could do for myself but have decided not to. Generally, I've been able to do that when working with larger companies (or Dot Com's, but those days are gone). When working for a smaller company, I go to work building out the tools I need. It just depends on the funds and time available and where you want to spend them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2011 at 5:00 pm
sqldba_icon (1/26/2011)
I really always ask this question to my self. If there is a native tool like perfmon to capture all the data why do people spend $$$ on third party tools?
And another thing occurred to me, the vendor that makes a tool for monitoring your system is thinking about that a lot. They're putting lots of effort into collecting the best information and presenting it to you at costs that are less than the competition and in ways that the competition can't. That frequently, but not always, means they're probably going to be building a better tool than you can build on your own.The one area I've always noted is reporting. I can gather metrics as well as anyone, but I'm not always good about building out reports.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2011 at 10:13 am
Grant Fritchey (1/26/2011)
sqldba_icon (1/26/2011)
I really always ask this question to my self. If there is a native tool like perfmon to capture all the data why do people spend $$$ on third party tools?And another thing occurred to me, the vendor that makes a tool for monitoring your system is thinking about that a lot. They're putting lots of effort into collecting the best information and presenting it to you at costs that are less than the competition and in ways that the competition can't. That frequently, but not always, means they're probably going to be building a better tool than you can build on your own.The one area I've always noted is reporting. I can gather metrics as well as anyone, but I'm not always good about building out reports.
I agree with you. Yeah right now i think we have reached to a point where we need some good tool. I will be refering back to this post for the tools..thanks for your suggestion
January 28, 2011 at 8:04 am
sqldba_icon (1/25/2011)
I am trying to findout is there a specific figure which would specify the % of cache being allocated from the memory. i did total of procedural cache on of the servers and it was only about 20MB? How is that possible , i have assigned sql server 43 GB? there server is just dedicated to sql. This is 64 bit box?
The following DMV query will summarize your cache by object type, number of plans, memory used, and average re-use count.
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
CacheType Total Plans Total MBs Avg Use Count Total MBs Total Plans
-------------------- ------------ -------------- ------------- ----------- -----------
Adhoc 1029 172.375000 6 23.015625 335
Proc 45 34.343750 292 13.296875 15
Prepared 18 3.406250 79 3.078125 11
View 90 8.820312 218 0.000000 0
Check 2 0.039062 8 0.000000 0
Trigger 1 0.062500 2 0.000000 0
UsrTab 1 0.117187 6 0.000000 0
The following will return the object name, usage count, and text for cached plans.
select cp.usecounts
,object_schema_name(objectid) as schemaname
,object_name(objectid) as objectname
,cacheobjtype
,objtype
,[text] as querytext
from sys.dm_exec_cached_plans as cp
cross apply sys.dm_exec_sql_text (cp.plan_handle) as t
where cp.usecounts > 1
order by cp.usecounts desc;
usecounts schema cacheobjtype objtype querytext
--------- ------ --------------- ------- --------------------------
25 dbo Compiled Plan Proc select name, desc from ...
4 sales Compiled Plan View select * from ...
... ... ... ... ...
... ... ... ... ...
Credit for original source of scripts and also a great resource for SQL Server internals and performance related articles:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 28, 2011 at 8:44 am
Eric M Russell (1/28/2011)
sqldba_icon (1/25/2011)
I am trying to findout is there a specific figure which would specify the % of cache being allocated from the memory. i did total of procedural cache on of the servers and it was only about 20MB? How is that possible , i have assigned sql server 43 GB? there server is just dedicated to sql. This is 64 bit box?The following DMV query will summarize your cache by object type, number of plans, memory used, and average re-use count.
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
CacheType Total Plans Total MBs Avg Use Count Total MBs Total Plans
-------------------- ------------ -------------- ------------- ----------- -----------
Adhoc 1029 172.375000 6 23.015625 335
Proc 45 34.343750 292 13.296875 15
Prepared 18 3.406250 79 3.078125 11
View 90 8.820312 218 0.000000 0
Check 2 0.039062 8 0.000000 0
Trigger 1 0.062500 2 0.000000 0
UsrTab 1 0.117187 6 0.000000 0
The following will return the object name, usage count, and text for cached plans.
select cp.usecounts
,object_schema_name(objectid) as schemaname
,object_name(objectid) as objectname
,cacheobjtype
,objtype
,[text] as querytext
from sys.dm_exec_cached_plans as cp
cross apply sys.dm_exec_sql_text (cp.plan_handle) as t
where cp.usecounts > 1
order by cp.usecounts desc;
usecounts schema cacheobjtype objtype querytext
--------- ------ --------------- ------- --------------------------
25 dbo Compiled Plan Proc select name, desc from ...
4 sales Compiled Plan View select * from ...
... ... ... ... ...
... ... ... ... ...
Credit for original source of scripts and also a great resource for SQL Server internals and performance related articles:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Procedure-cache-and-optimizing-for-adhoc-workloads.aspx
Thanks Erick. So as per the first dmv in first block sum of data under "Total Mb's" should give the total amount of procedure Cache used from buffer cache? I have 7 gb assigned to sql server and the total from the dmv which you gave is 2 gb, so does that mean at the moment sql proc cache is using only 2GB?
January 28, 2011 at 9:08 am
sqldba_icon (1/28/2011)
Thanks Erick. So as per the first dmv in first block sum of data under "Total Mb's" should give the total amount of procedure Cache used from buffer cache? I have 7 gb assigned to sql server and the total from the dmv which you gave is 2 gb, so does that mean at the moment sql proc cache is using only 2GB?
Part of the buffer cache is for execution plans, but most of it should be set aside for data pages. Not all applications need 2 GB of execution plans cached. Having a large number of plans with a low re-use count is a bad thing; you want a few plans as possible that are used again and again.
Have you looked at DBCC MEMORYSTATUS ?
This will give you detailed breakdown of how your total cache is allocated.
http://support.microsoft.com/kb/907877
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 30, 2011 at 7:31 am
I always believed that maximum memory used for the cache will be 25% of the Non-Awe memory(if enabled).Also remember perform monitor wont give proper memory status if you have AWE enabled.I use the following query to check the status for the size of cache.
SELECT objtype AS 'Cached Object Type',
count(*) AS 'Number of Plans',
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
avg(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
If o/p for the average use planned cache in the above query is less in that no of plans then cache plans are not reused.You can use DBCC FREESYSTEMCACHE('SQL Plans') to free the cache memory.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply