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.

 

This arch (in Arches National Park, Ut.) has stood RESOLUTE for milennia

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"]

[/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"]

[/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"]

[/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"]

[/codesyntax]

[codesyntax lang="tsql"]

[/codesyntax]

[codesyntax lang="tsql"]

[/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.

Memory Clerk Usage
Memory Clerk Type SPA Mem, Kb
CACHESTORE_SQLCP 156184
CACHESTORE_PHDR 45904
CACHESTORE_OBJCP 20664
USERSTORE_DBMETADATA 8472
USERSTORE_SCHEMAMGR 6376

 

Pages in Cache
Database Name Cached Size (MB)
msdb 12.265625

 

Plan Cache
DbName dbid TotalPlanCacheSize_in_MB
NULL 32767 42
NULL NULL 150
msdb 4 13
ReportServer$ADMIN 5 0
MDW 28 8
AdminDB 14 0

And the following are the post change results.

Memory Clerk Usage
Memory Clerk Type SPA Mem, Kb
CACHESTORE_SQLCP 109160
CACHESTORE_PHDR 36744
CACHESTORE_OBJCP 9152
USERSTORE_DBMETADATA 8472
USERSTORE_SCHEMAMGR 6296

 

Data Pages in Cache
Database Name Cached Size (MB)
msdb 12.265625

 

Plan Cache
DbName dbid TotalPlanCacheSize_in_MB
NULL 32767 36
NULL NULL 104
ReportServer$ADMIN 5 0
MDW 28 8
AdminDB 14 0

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’!

SSRS Subscriptions Report

As a part of my series leading up to Christmas 2012, I shared a script to Report on SSRS Subscriptions.  It was soon found to have a bug with SQL Server 2008 R2 SP2.  IN the comments on that post, I promised to post an updated script.  Here is that update – without the bug.

[codesyntax lang="tsql"]

[/codesyntax]

The inline code seen above likes to reformat and and will throw an error due to capitalization of the function value and text().  Download the script here: SSRS_SubscriptionsV1_5

page 1 of 1




Calendar
January 2013
M T W T F S S
« Dec   Feb »
 123456
78910111213
14151617181920
21222324252627
28293031  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, August 23, 2014