A Firm Foundation

Last week I sent out an invite for the monthly TSQL Tuesday party.

The theme for the party is a take on the words resolve or resolution.  I was hoping the theme would encourage some reflection and sharing of real life experiences that have led to a difference being made.

I have resolved on two stories to share.  Both are rather short and simple in nature.

Story the First

Near the end of the year in 2012, I inherited a database that had not had a consistency check done on it – ever!  In checking the page_verify setting, I found that it was set to none as well.  Both of these should be alarming to any DBA – unless you are completely unconcerned by corrupt data and the potential for corrupt data.  Never-mind the potential business repercussions of having corrupt or lost data.

To find what level of page verification you have enabled, it is a matter of a quick script like the following.

[codesyntax lang=”tsql”]

Select name, page_verify_option_desc
From sys.databases;

[/codesyntax]

You can have any one of three settings for your page_verify.  The recommended option is to have CHECKSUM enabled.  If you see NONE or TORN_PAGE_DETECTION, you really need to consider changing that.  Keep in mind if you are still running SQL 2000, CHECKSUM is not an option and the query provided will fail.

Changing the verify option is very simple as well.  It only requires an Alter Database to be run such as the following.

[codesyntax lang=”tsql”]

ALTER DATABASE [msdb]
SET PAGE_VERIFY CHECKSUM;

[/codesyntax]

You will probably notice that I am using the msdb in my sample script.  There is a reason for this that will be shown later.  Just keep in mind that msdb should not need to be changed because it should already be using the CHECKSUM option.

What if you have numerous databases that are not using the CHECKSUM method?  It can become rather tedious to change each of those manually.  That is why we might come up with a cursor such as the following.

[codesyntax lang=”tsql”]

DECLARE
@DBName SYSNAME,
@SQL    varchar(512);
DECLARE dbchecksum CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR Select name
from sys.databases
where name not in ('tempdb')
AND state_desc = 'online'
AND page_verify_option_desc <> 'Checksum';
Open dbchecksum;
Fetch Next From dbchecksum into @DBName;
While @@Fetch_Status = 0
Begin
Set @SQL = 'ALTER DATABASE [' + @DBName +'];' +char(10)+char(13)
SET @SQL = @SQL + 'SET PAGE_VERIFY CHECKSUM;' +char(10)+char(13)
Execute (@SQL);
Set @SQL = ''
Fetch Next From dbchecksum into @DBName;
End
Close dbchecksum;
Deallocate dbchecksum;

[/codesyntax]

This script is only checking for databases that are not using CHECKSUM.  Then it loops through and changes the setting to use CHECKSUM.

I strongly caution about running this in production without an outage window!  I make that recommendation for very simple reasons.  First, the change is to a production system.  Second, the change can have a temporary adverse effect.  Now before you get too excited about it, I have a short demonstration.

Here is a script broken out into three sections.

[codesyntax lang=”tsql”]

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767 -- ResourceDB
--AND database_id > 4 -- system databases
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
SELECT DB_NAME(dbid) AS DbName,dbid,SUM(size_in_bytes)/1024/1024 AS TotalPlanCacheSize_in_MB
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
GROUP BY dbid

[/codesyntax]

[codesyntax lang=”tsql”]

ALTER DATABASE [msdb]
SET PAGE_VERIFY CHECKSUM;

[/codesyntax]

[codesyntax lang=”tsql”]

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767 -- ResourceDB
--AND database_id > 4 -- system databases
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
SELECT DB_NAME(dbid) AS DbName,dbid,SUM(size_in_bytes)/1024/1024 AS TotalPlanCacheSize_in_MB
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
GROUP BY dbid

[/codesyntax]

Sections one and three are the same.  This script is used to measure various memory components within SQL Server.  The second section is the change we will make to the msdb database.  The queries in the first and third section perform the following: retrieve memory clerk usage (aggregated to memory clerk type), retrieve total data pages stored in cache (aggregated by database), and retrieve the plan cache use (aggregated by database).

Now on to some pre and post change results.  First with what my results were prior to the change.

And the following are the post change results.

First observation I want to point out is with the second result for both the pre and post run.  Making this change will not affect the pages in cache.  This goes along with what we have been taught by Paul Randal – that a CHECKSUM is not performed immediately (I paraphrased).  You can read more about the CHECKSUM and some misconceptions about it here.

If we now turn our attention to the first and third result sets, we will see that there are changes in the memory clerks used and the plan cache.  Starting with the the third result set (both pre and post) we see that the ResourceDB decreased in total plan cache size.  The NULL item (adhoc queries not associated to a specific database) also decreased.  After that, the only change in size is the msdb database – disappeared from the results due to no plan cache in use associated to this database.  (Starting to see why I chose the msdb database for this demo?)

If you now look closer at the results for the first query on both sides of the change, you will see correlating changes to the plan cache.  Notice that CACHESTORE_SQLCP dropped by about 46MB (correlates to the null entry from query 3).  But of those clerks listed, you will see that only USERSTORE_DBMETADATA did not change in size.

Looking at these results should demonstrate why this change should be performed during a maintenance window.  There will be an effect on performance and I would rather you let the business know what is coming down the pipe.  This change is akin to running DBCC FLUSHPROCINDB(<db_id>);.  There are other database settings that will have the same effect.  You can read a little about that from Kalen Delaney – here.

Story the Second

This story is far less interesting and a whole lot shorter.  This falls into the category of professional development and fine tuning my skills.  I took the MCM lab exam during the PASS Summit.  I failed, not unlike many who have attempted it.  That is all fine and well. I learned some things about myself and I learned some areas that may need some resolution (sharpened focus).

So as more of a resolution upon which I have greater resolve than a New Years resolution, I will be retaking the Lab exam.  And I will be getting my MCM in the near future.  Just sayin’!