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

1

2

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

1

2

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

DECLARE

@DBName SYSNAME,

@SQL varchar(512);

DECLAREdbchecksum CURSOR

LOCAL STATICFORWARD_ONLY READ_ONLY

FORSelect name

from sys.databases

where name notin('tempdb')

ANDstate_desc='online'

ANDpage_verify_option_desc&lt;&gt;'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.

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

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.

SQL Server is full of good stuff. There are plenty of features to be used. Plenty of applications to help it. And there is even plenty of metadata within SQL Server to help you better understand your data and the queries that are being run. It just so happens that a couple of clients requested[…]

Today we have another installment in what is known as TSQL Tuesday. This month we have an invitation and topic given to us by the infamous Kenneth Fisher ( blog | twitter). Today, the invitation is for us to share our stories on how we like to manage security. Or at least that is the[…]

Recently I wrote an article about Capturing Online Index Operations. In that article, I discussed a problem that I had encountered. Well, there were multiple problems. One was an issue with a vendor app that had some hidden module that was performing online index defrags that was causing corruption in a couple of indexes every[…]

One of the things that DBAs love to do is keep their servers running and healthy. A healthy server, after all, is your ticket to a stress free day and a full night’s sleep. Granted this not a guarantee but it sure helps make life easier. We are always looking for the big ticket items[…]

What a fun week we have tuned up for the folks in Las Vegas. It is the first full week of January and there is this huge convention going on near the strip. And as timing would have it, this week is also the perfect time to have our User Group meeting. What major conference[…]

Tis the season for TSQL Tuesday. Not only is it that season again, but it is also the Holiday season. During this season, many people start to think about all of the things for which they are thankful. Many may start to think about their families and friends. And many others will focus more of[…]

This past weekend I had the opportunity to go visit Washington DC. It was just the second time I got to stay in the Nation’s capitol for more than just a few hours. The previous opportunity came with last years event which I talked about here. Sadly, my time was far too limited this trip and[…]

I am about to set sail on a new venture with my next official whistle stop. This year has been plenty full of whistle stops and I plan on continuing. You can read (in full) about previous whistle stops and why they are called whistle stops here. Suffice it to say at this point that it all[…]