Friday, February 11, 2011

Procedure cache, what is it?

SQL Server uses pooled memory. Within this pool SQL Server stores both data and execution plans. You execute a query, SQL Server looks through the procedure cache to see if a plan exists. If not, that query plan is added to the procedure cache, and references to the data in the table are stored in the remainder of the pool. From this you can see that as both the procedure cache and buffer pool grow you could start to run out of memory, or encounter memory pressure. So how big is your procedure cache?
SQL Server provides the dm_exec_chached_plans DMV that can be used to get details on cached plans.

select SUM( size_in_bytes )/1024/1024. [Cache Size in MB]
from sys.dm_exec_cached_plans
select refcounts, usecounts, 
size_in_bytes/1000./1000. [Obj Plan Size in MB],
objtype
from sys.dm_exec_cached_plans
order by size_in_bytes desc
These queries will give you the total size of your procedure cache and which plan\query is using the most.
Next blog in the series will be how poor design can bloat the cache and how to avoid it.

Thursday, February 10, 2011

Professionalism

I've been a consultant at a respectable Microsoft partner for the last few years. We make our living off our professionalism and reputation. With that in mind I've become more active in the SQL Server blog world in order to "increase my brand". There is a lot of information regarding SQL Server starting with Books Online (BOL) and several publications from Microsoft Press to WROX and O'Reilly. That said, while doing tech review of a training class I came across some content that was copied directly from BOL without citation. Even more astounding, one paragraph was found to have referenced someone elses blog directly. However, that blog itself was taken from a Microsoft technet article. (Fortunately not a well known blogger in the SQL community).
In oder to make what could be a long blog entry short I just want to say, "Come on folks, if it's not your idea or thoughts, CITE IT!". A good example would be when recommending an index strategy. The first line is a best practice you could recommend, then follow up with a reference to SUPPORT your idea.
"Because a copy of the data is stored at the leaf level it is a best practice to avoid using wide columns as the key column of a non-clustered index and use them as part of an INCLUDE clause.
'Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query included nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.' 1 MSDN index guidlines"
As opposed to
"Because a copy of the data is stored at the leaf level it is a best practice to avoid using wide columns as the key column of a non-clustered index and use them as part of an INCLUDE clause. Therefore, redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query included nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient."

Keep blogging and stay professional!
D