Tag: SQLBP

Replication, SQL

Calculating Replication Schema Options

I recently evaluated using page compression on some of my production data (see my post on estimating data compression). I’m using transactional replication to send this data out to a few other servers for reporting pusposes and I wanted to adjust replication so that the data compression settings on the tables would also be replicated.

Making life a little easier on this was an application change that was going to require me dropping and recreating replication so I figured it would be a simple case to change the replication options when recreating the publications.

Using the GUI I scripted out all of the existing publications and then dropped them.

Now I wanted to adjust the script so that the schema options I wanted replicated were applied. Here’s the sp_addarticle for one of these tables (SQL 2008)

 — Adding the transactional articles

 use [TestDB]

exec sp_addarticle
    @publication = N’TestDB_TestTable’
    , @article = N’TestTable’
    , @source_owner = N’dbo’
    , @source_object = N’TestTable’
    , @type = N’logbased’
    , @description = N”
    , @creation_script = N”
    , @pre_creation_cmd = N’drop’
    , @schema_option = 0x000000000803509F
    , @identityrangemanagementoption = N’none’
    , @destination_table = N’TestTable’
    , @destination_owner = N’dbo’
    , @status = 24
    , @vertical_partition = N’false’
    , @ins_cmd = N’CALL [dbo].[sp_MSins_dboTestTable]’
    , @del_cmd = N’CALL [dbo].[sp_MSdel_dboTestTable]’
    , @upd_cmd = N’SCALL [dbo].[sp_MSupd_dboTestTable]’
GO

 

Now this doesn’t tell you a lot about what schema options are in place. The binary value @schema_option has all the information you need however it’s not exactly user friendly. Books Online has the information available to be able to figure this out, it just takes a little calculation. Somewhat of a pain I’ve created a script to tell me what options are enabled.

All you need to do is pass in the @schema_option value and it will tell you what options are enabled (in this case  0x000000000803509F)

 

/* PROVIDES THE REPLICATION OPTIONS ENABLED FOR A GIVEN @SCHEMA_OPTION IN SYSARTICLES */

declare @schema_option varbinary(8) = 0x000000000803509F  –< PUT YOUR SCHEMA_OPTION HERE

set nocount on
declare @OptionTable table ( HexValue varbinary(8), IntValue as cast(HexValue as bigint), OptionDescription varchar(255))
insert into @OptionTable (HexValue, OptionDescription)
select 0x01 ,‘Generates object creation script’
union all  select 0x02 ,‘Generates procs that propogate changes for the article’
union all  select 0x04 ,‘Identity columns are scripted using the IDENTITY property’
union all  select 0x08 ,‘Replicate timestamp columns (if not set timestamps are replicated as binary)’
union all  select 0x10 ,‘Generates corresponding clustered index’
union all  select 0x20 ,‘Converts UDT to base data types’
union all  select 0x40 ,‘Create corresponding nonclustered indexes’
union all  select 0x80 ,‘Replicate pk constraints’
union all  select 0x100 ,‘Replicates user triggers’
union all  select 0x200 ,‘Replicates foreign key constraints’
union all  select 0x400 ,‘Replicates check constraints’
union all  select 0x800  ,‘Replicates defaults’
union all  select 0x1000 ,‘Replicates column-level collation’
union all  select 0x2000 ,‘Replicates extended properties’
union all  select 0x4000 ,‘Replicates UNIQUE constraints’
union all  select 0x8000 ,‘Not valid’
union all  select 0x10000 ,‘Replicates CHECK constraints as NOT FOR REPLICATION so are not enforced during sync’
union all  select 0x20000 ,‘Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so are not enforced during sync’
union all  select 0x40000 ,‘Replicates filegroups’
union all  select 0x80000 ,‘Replicates partition scheme for partitioned table’
union all  select 0x100000 ,‘Replicates partition scheme for partitioned index’
union all  select 0x200000 ,‘Replicates table statistics’
union all  select 0x400000 ,‘Default bindings’
union all  select 0x800000 ,‘Rule bindings’
union all  select 0x1000000 ,‘Full text index’
union all  select 0x2000000 ,‘XML schema collections bound to xml columns not replicated’
union all  select 0x4000000 ,‘Replicates indexes on xml columns’
union all  select 0x8000000 ,‘Creates schemas not present on subscriber’
union all  select 0x10000000 ,‘Converts xml columns to ntext’
union all  select 0x20000000 ,‘Converts (max) data types to text/image’
union all  select 0x40000000 ,‘Replicates permissions’
union all  select 0x80000000 ,‘Drop dependencies to objects not part of publication’
union all  select 0x100000000 ,‘Replicate FILESTREAM attribute (2008 only)’
union all  select 0x200000000 ,‘Converts date & time data types to earlier versions’
union all  select 0x400000000 ,‘Replicates compression option for data & indexes’
union all  select 0x800000000  ,‘Store FILESTREAM data on its own filegroup at subscriber’
union all  select 0x1000000000 ,‘Converts CLR UDTs larger than 8000 bytes to varbinary(max)’
union all  select 0x2000000000 ,‘Converts hierarchyid to varbinary(max)’
union all  select 0x4000000000 ,‘Replicates filtered indexes’
union all  select 0x8000000000 ,‘Converts geography, geometry to varbinary(max)’
union all  select 0x10000000000 ,‘Replicates geography, geometry indexes’
union all  select 0x20000000000 ,‘Replicates SPARSE attribute ‘
                  
select HexValue,OptionDescription as ‘Schema Options Enabled’
From @OptionTable where (cast(@schema_option as bigint) & cast(HexValue as bigint)) <> 0

 

This is really great and useful. In fact you can use this script to check out the schema options for any article, just plug in the schem_option value from sysarticles to get the data.

Now to take it a step further I wanted to know what the new value would need to be for @schema_option in order to replicate the data compression settings. For this I wrote another query. This time it’s just a case of uncommenting the lines for the schema options that I want and running the select.

select cast(

  cast(0x01 AS BIGINT) –DEFAULT Generates object creation script
| cast(0x02 AS BIGINT) –DEFAULT Generates procs that propogate changes for the article
| cast(0x04 AS BIGINT) –Identity columns are scripted using the IDENTITY property
| cast(0x08 AS BIGINT) –DEFAULT Replicate timestamp columns (if not set timestamps are replicated as binary)
| cast(0x10 AS BIGINT) –DEFAULT Generates corresponding clustered index
–| cast(0x20 AS BIGINT) –Converts UDT to base data types
–| cast(0x40 AS BIGINT) –Create corresponding nonclustered indexes
| cast(0x80 AS BIGINT) –DEFAULT Replicate pk constraints
–| cast(0x100 AS BIGINT) –Replicates user triggers
–| cast(0x200 AS BIGINT) –Replicates foreign key constraints
–| cast(0x400 AS BIGINT) –Replicates check constraints
–| cast(0x800 AS BIGINT)  –Replicates defaults
| cast(0x1000 AS BIGINT) –DEFAULT Replicates column-level collation
–| cast(0x2000 AS BIGINT) –Replicates extended properties
| cast(0x4000 AS BIGINT) –DEFAULT Replicates UNIQUE constraints
–| cast(0x8000 AS BIGINT) –Not valid
| cast(0x10000 AS BIGINT) –DEFAULT Replicates CHECK constraints as NOT FOR REPLICATION so are not enforced during sync
| cast(0x20000 AS BIGINT) –DEFAULT Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so are not enforced during sync
–| cast(0x40000 AS BIGINT) –Replicates filegroups (filegroups must already exist on subscriber)
–| cast(0x80000 AS BIGINT) –Replicates partition scheme for partitioned table
–| cast(0x100000 AS BIGINT) –Replicates partition scheme for partitioned index
–| cast(0x200000 AS BIGINT) –Replicates table statistics
–| cast(0x400000 AS BIGINT) –Default bindings
–| cast(0x800000 AS BIGINT) –Rule bindings
–| cast(0x1000000 AS BIGINT) –Full text index
–| cast(0x2000000 AS BIGINT) –XML schema collections bound to xml columns not replicated
–| cast(0x4000000 AS BIGINT) –Replicates indexes on xml columns
| cast(0x8000000 AS BIGINT) –DEFAULT Creates schemas not present on subscriber
–| cast(0x10000000 AS BIGINT) –Converts xml columns to ntext
–| cast(0x20000000 AS BIGINT) –Converts (max) data types to text/image
–| cast(0x40000000 AS BIGINT) –Replicates permissions
–| cast(0x80000000 AS BIGINT) –Drop dependencies to objects not part of publication
–| cast(0x100000000 AS BIGINT) –Replicate FILESTREAM attribute (2008 only)
–| cast(0x200000000 AS BIGINT) –Converts date & time data types to earlier versions
| cast(0x400000000 AS BIGINT) –Replicates compression option for data & indexes
–| cast(0x800000000 AS BIGINT)  –Store FILESTREAM data on its own filegroup at subscriber
–| cast(0x1000000000 AS BIGINT) –Converts CLR UDTs larger than 8000 bytes to varbinary(max)
–| cast(0x2000000000 AS BIGINT) –Converts hierarchyid to varbinary(max)
–| cast(0x4000000000 AS BIGINT) –Replicates filtered indexes
–| cast(0x8000000000 AS BIGINT) –Converts geography, geometry to varbinary(max)
–| cast(0x10000000000 AS BIGINT) –Replicates geography, geometry indexes
–| cast(0x20000000000 AS BIGINT) –Replicates SPARSE attribute
AS BINARY(8)) as Schema_Option

 

Plugging in that new value and executing the sp_addarticle script created the new article with all the old settings and now included replicating compression options.

Download the script Replication Schema Options.sql give them a try yourself and let me know if they help.

 

 

Uncategorized

What Indexes Are Not Being Used And How Big Are They?

Leafs Stanley Cup Cabinet (or Cubs World Series Trophy Cabinet)Indexes…they are your friends. They order your data nice and neatly, they can dramatically improve your query performance and can have cool names like IX_UseMeToRunFasterLikeATurboButton. 

Indexes…they are your enemy. They slow down your inserts, updates and deletes, and they take up valuable disk space. 

So having indexes that are used is a great thing.  You’ll see query performance improve by an order of magnitude and some freeing up of your server resources. However, if not used, an index just sits there an takes up space kind of like the Toronto Maple Leafs Stanely Cup cabinet. Let’s not forget those unused indexes are still being kept up to date and slowing down your DML statements. 

 

A couple of months ago I was invited to add a large number of indexes to a database in production that is used for reporting. Being a good DBA I asked, quite politely, why they were needed. “They are so the queries run faster” was the response. Much banter flowed back and forth between teams and in the end all of the indexes were applied (this included 20 indexes on a 534 column table, yes, I know). 

I felt these indexes were not going to be used, but as in a lot of cases it comes down to a please just do it situation. So I did.

Three months on and I had a couple of hours to take a look and see how these indexes were doing. A great post by Robert Davis (blog|twitter) entitled “How Do I … Get a List of Tables With Data Modifications in a Specific Timeframe?” got me started with the idea of using sys.dm_db_index_usage_stats to check for this.

When a DML statement gets executed the user_updates column in this DMV gets incremented by one. Likewise, when a query is executed and an index used, be it for a lookup, scan or seek then then user_lookup, user_scan or user_seek value gets incremented.

Armed with this information I was quickly and easily able to identify indexes in the database that had not been used since the last Instance restart (joining against sysindexes provides the index name)

 

SELECT
   object_name(us.object_id) as objname
  , si.[name] as IndexName
FROM
  sys.dm_db_index_usage_stats us
    INNER JOIN sysindexes si
      ON us.[object_id] = si.id
      AND us.index_id = si.indid
WHERE
  us.database_id = db_id()
  AND us.user_lookups = 0
  AND us.user_scans = 0
  AND us.user_seeks = 0

 

 

This gave me a nice list of unused indexes that I could contemplate removing. However I really felt that I needed to put some numbers behind those indexes. Managers love numbers. By pulling the list of indexes into a table and then using sys.dm_db_index_physical_stats I was able to get the size of each index. Adding the user_updates information from sys.dm_db_index_usage_stats provided me with a long list of indexes along with how many times they have been updated since SQL was last started and the size of each index.

 

–CHECKS TO SEE IF INDEXES ARE BEING USED IN A DATABASE
–IF UNUSED THEN GETS THE SIZE OF THE INDEX ALONG WITH UPDATES SINCE LAST SQL START

if object_id(‘tempdb..#indexinfo’) is not null
drop table #indexinfo

–Load up a list of unused indexes for the current database
SELECT
  ROW_NUMBER() OVER (ORDER BY object_name(us.object_id), us.index_id) as ROWNUM
  , object_name(us.object_id) as objname
  , us.[object_id]
  , us.index_id
  , si.[name] as IndexName
  , us.user_updates
  , 0 as IndexSizeKB
INTO
  #indexinfo
FROM
  sys.dm_db_index_usage_stats us
    INNER JOIN sysindexes si
      ON us.[object_id] = si.id
      AND us.index_id = si.indid
WHERE
  us.database_id = db_id()
  AND us.user_lookups = 0
  AND us.user_scans = 0
  AND us.user_seeks = 0

–Going to use a while loop and get the physical info for each index for sizing purposes
DECLARE @object_id bigint
      , @index_id int
      , @rownum int
      , @index_size int
      
SELECT @rownum = min(ROWNUM) from #indexinfo where ROWNUM IS NOT NULL
WHILE @rownum IS NOT NULL
BEGIN

    SELECT @object_id = object_id from #indexinfo WHERE ROWNUM = @rownum
    SELECT @index_id = index_id from #indexinfo WHERE ROWNUM = @rownum

    SELECT @index_size = convert(int, (avg_record_size_in_bytes * record_count) / 1024)
    FROM sys.dm_db_index_physical_stats(db_id(), @object_id, @index_id,  null, ‘detailed’)
    WHERE index_level = 0

    UPDATE #indexinfo
    SET IndexSizeKB = @index_size
    WHERE ROWNUM = @rownum

    SELECT @rownum = min(ROWNUM) from #indexinfo where ROWNUM > @rownum and ROWNUM IS NOT NULL
END

–Give us the results
SELECTobjname as TableName, IndexName, user_updates, IndexSizeKB FROM #indexinfo
SELECTsum(user_updates) as Updates, sum(indexSizeKB) / 1024 as TotalUnusedIndexesMB
FROM #indexinfo

I ended up finding 245 indexes that were not being used. Those indexes had been updated over 15 million times in the last two weeks and they totalled up to over 12GB of utilized disk.

Some nice numbers to add to my request to drop those indexes.

Uncategorized

Moving SQL Datafiles

The Problem

A Sharepoint database (LOCAL_CONTENT_DB) running on a clustered SQL 2005 EE Instance (on Windows 2003 EE) had experienced growth from 10GB to 95GB in a few weeks and there was no more disk for it to grow any further.

I needed to add some disk to the cluster, get it in the cluster so that SQL could see it and then move data around so that there was extra capacity for this database and others.

 

Existing Disk Layout

  • F: – SQL Data 200GB
  • G: – SQL Logs 100GB
  • H: – SQL Backups 200GB
  • I: – SQL System & TempDB 75GB

 

Proposed Disk Layout

  • F: – SQL Data 200GB
  • G: – SQL Logs 100GB
  • H: – SQL Data2 200GB (changed usage)
  • I: – SQL System & TempDB 75GB
  • J: – SQL Backups – 500GB (new)

Deploying The Changes

Attaching the disk

Fortunately we had some capacity on the SAN and a friendly SAN engineer who quickly carved up a new LUN for me and presented it to the clustered nodes. I logged on to each node, opened up the Disk Management console (diskmgmt.msc) and rescanned the disks (Action -> Rescan Disks). Once the scan was complete I scrolled down through the list and verified I could see the newly presented LUN (and made a note of the disk number)

 

Aligning the disk & creating a partition

With the disk number in hand, on one of the nodes, I opened up a Command Prompt window (so want to call this a DOS window) and opened diskpart.

Why not just format the disk? You might ask. Well there’s the disk offset issue in Windows 2003 (thankfully eliminated in Windows 2008) to be aware of which would cause extra writes and impact performance (read all about this at http://support.microsoft.com/kb/929491).

With diskpart open I partitioned the disk and assigned a drive letter (note disk 15 was the newly attached LUN)

select disk 15
create partition primary align = 64 (this is based upon recommended value from the storage provider)
assign letter = J

This done I exited out of diskpart and went back to Disk Management. A refresh and this now showed J good to go. Right click and a quick format later and the disk was ready to be added to the cluster.

 

Clustering the disk

Before working with the disk we need to get it in the cluster. Rather than just add it to the SQL cluster group I wanted to be sure that there were no issues with reading or writing to the disk from either host. To accomplish this I opened cluster administrator and created a new cluster group called temp.

In the cluster group I added a new disk resource for drive J, allowed both nodes to own the resource and brought the disk online.

To check I was able to read and write to the disk I just created a new file and saved it. Then failed over the disk to the other node, opened it there, appended to the file and saved it again. There were no issues so I knew we were good to go with getting this disk attached to SQL. To get the disk ready I set up my basic folder structure ready for the SQL files.

 

Presenting the disk to SQL

Now that I knew the disk was fully accessible and writable from both hosts it was time to actually present it to the SQL Instance. This, unfortunately, required a short period of downtime as the disk needed to be added as a dependency for SQL Server startup.

To do this I performed the following steps

  • opened the Sharepoint cluster group in cluster admin and took offline the SQL Server Agent and SQL Server resources
  • went to the temp cluster group and took “drive J” offline
  • right clicked on “Drive J” and selected “Change Group” from the context menu
  • selected the Sharepoint cluster group as the new group and confirmed
  • went to the Sharepoint cluster group and brought “Drive J” online
  • right clicked on the SQL Server resource, went to properties, dependencies, modified and added “Drive J” as a dependent resource
  • brought the SQL Server & SQL Server Agent resources online
  • checked that SQL was able to access and write to the new disk by performing a simple quick backup of the master database (and deleted this immediately afterwards)
  • performed full backups of all the databases on the instance to the new drive

 

 

Moving the database

With the disk in place, and full backups taken it was time to move the database. In SQL 2000 this would have been done using sp_detachdb & sp_attachdb, but in SQL 2005 and later there is a much more elegant solution whereby you can set the database offline, copy the relevant file and then use a modify database statement to reference the new location before bringing the database back online again. Here are the steps I followed to do this:

  • Set the database offline 
    • ALTER DATABASE LOCAL_CONTENT_DB SET OFFLINE;

  • Copy file F:MSSQLDataLocal_Content_DB.mdf to H:MSSQLDataLocal_Content_DB.mdf Please note that this was a copy, not a move. Moving the file increases the risk of something happening to that file and you not being able to recovery it.
  • Once the copy was complete change the reference for the mdf 
    • ALTER DATABASE LOCAL_CONTENT_DB
      MODIFY FILE
      (
      NAME = Local_Content_DB_Data
      , FILENAME = ‘H:MSSQLDataLocal_Content_DB.mdf’
      );

  • Bring the database back online
    •  ALTER DATABASE LOCAL_CONTENT_DB SET ONLINE;

Final steps

With the database back online I ran a couple of queries against it, just to confirm it was accessible completely and then, just because I am a careful chap I also executed a dbcc checkdb (which also came back clean). Finally I had the application admin check that everything looked good (which it was).

Once I knew that everything was good I changed the backup location to point to the new disk and deleted the old mdf from the F drive, tada, done.

 

 

Uncategorized

SQL Job Notifications

Today on Twitter TJay Belt (blog|twitter) was asking about job notifications, specifically related to being able to add some kind of logic to that notification.

I’m using a trigger on the sysjobhistory in the msdb database to handle this, below is a version with very basic logic, but something easily expanded upon 

 

This process is based upon the script posted by Kenneth Singer back in 2007 on SQL Server Central. You can find that at http://www.sqlservercentral.com/scripts/Miscellaneous/30177/ 

 

USE [msdb]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE   trigger [dbo].[trg_jobstepfailures]

on [dbo].[sysjobhistory]

for insert

as 

declare @strcmd varchar(800),@strRecipient varchar(500),@strMsg varchar(2000), @strServer varchar(255),@strTo varchar(255)

 

Declare @Subject varchar(500)

 

if exists (select * from inserted where run_status = 0 and step_name NOT IN ( '(job outcome)', 'do not notify'))

begin

    select  @strMsg =  '<FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12; font-weight:bold">' + @@servername + '-Job: ' + sysjobs.name + '. Step= ' + 

     inserted.step_name + '</font><br><br><FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12">Message ' + inserted.message + '</font>'

     from inserted

    join sysjobs

    on inserted.job_id = sysjobs.job_id

    where inserted.run_status = 0

    

--subject text

    select @subject = 'Job ' + sysjobs.name + ' Failed on Job Server' +@@Servername

    from inserted 

    join sysjobs 

    on inserted.job_id = sysjobs.job_id

    where inserted.run_status = 0

 

--recipeints text

    IF @subject like '%<USE NAME OF FAILED JOB HERE>%'

        Begin 

            SET @strRecipient= '<RECPIENT LIST>'

        End

    ELSE

        Begin

            SET @strRecipient= '<ALTERNATE RECIPIENT LIST>'

        End

      

    exec msdb.dbo.sp_send_dbmail @profile_name = null --uses the default profile

     ,  @recipients = @strRecipient

     ,  @subject = @subject

     ,  @body =  @strMsg

     ,  @body_format = 'HTML' --default is TEXT

     

end

 

GO

Uncategorized

T-SQL Tuesday #005 – Changing Your Isolation Level

 The current T-SQL Tuesday is being hosted by Aaron Nelson (blog|twitter) who has come up with a great topic, that of “Reporting”.

Reporting is huge in scope, and Aaron was very helpful in throwing out a couple of ideas to get started. I went back and forth on what to write about, ultimately I thought I’d put up something which has made a huge difference to me recently as regards blocking issues in my reporting environment.

 

The Problem

Monitoring is one of the mainstays of the DBA life. Thomas LaRock (blog|twitter) dedicated a good section of his must read book “DBA Survivor: How To Be A Rock Star DBA” to it. Given that I am trying to be a good DBA I have various monitoring routines in place, one of these being to check for blocking on my servers.

The main production reporting server (running SQL 2008 EE) was beating me up with emails to the tune of a couple of hundred a day. Sadly we have a tool that allows folks to drag and drop to add items to queries and execute them. This does not create great query plans, we frequently have long running queries and these block the replication procs.

Suffice to say this couldn’t continue, but what to do?

 

The Solution

Not being able to change any of the code for the reports or do anything with the software I was really at an impass, until over the horizon strode changing the locking method on the database as a solution.

 

Transaction Isolation Levels

SQL Server has 5 different ways that you can hold (or not) a lock when attempting to read data.

  • Read Uncommitted – holds a schema shared lock
  • Read Committed – holds a shared lock that is immediately released
  • Repeatable Read – holds a shared lock for the life of the transaction
  • Serializable – holds a shared range lock for the life of the transaction
  • Snapshot  – holds a schema shared lock and uses row versioning

Within a query or procedure you can specify one of these to use, each will impact how data is accessed.

By default SQL uses the Read Committed isolation level. This holds a lock while the transaction is running to ensure consistency of the data, and is the reason why I was having a blocking nightmare.

Frequently in reporting situations (and I have to admit I am guilty of this) people use the NOLOCK hint, this is the same as the Read Uncommitted isolation level. While it eliminates the locking problem you also run into a situation where you can quite easily get dirty reads and produce the wrong data in a report (because you are reading uncommitted data you could read something someone else is updating within a transaction, they could roll that change back however you would have still read what was in there at the time). I really didn’t want to have dirty reads, and in any case I could not touch the code that was coming in, so that would not have helped. I needed something else.

 

Row Versioning

Row versioning is something not oft used or very well known about for SQL Server, but it can provide huge benefits in situations like this. By changing a couple of database options we can use row versioning to ensure that we get consistent reads and no blocking problems (except in circumstances where we would be looking to change the schema, there are always caveats).

Here’s how it works. When you open a transaction and perform an update an entry is placed into a version store in TempDB. This version store contains the original row. Should anyone come along and attempt to read that data they are redirected to the original row in the version store and so can continue on their merry way without being blocked. Once the transaction is committed the row is removed from the version store and you would be back to reading the actual table again. This is all invisible to the user (in fact this is so invisible that you won’t see reads against the version store or TempDB when turning on statistics or looking at an execution plan).

To enable row versioning on the AdventureWorks2008 database you would run the following script;

ALTER DATABASE AventureWorks2008
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE AventureWorks2008
SET READ_COMMITTED_SNAPSHOT ON
GO

 

In order to enable these options you can’t have any other open transactions in the database, so either kick everyone out or add WITH ROLLBACK IMMEDIATE to the scripts which will kill any open transactions and send them into rollback.

 

Caveats!

See, like I said, there are always caveats.

While this is all good and well you’ll want to keep a close eye on TempDB. The version store lives in TempDB and depending upon the number of transactions flowing in your server and the ongoing activity you might blow it out of the water. Make sure that it’s properly sized and well monitored, use the “Transactions: Free Space In TempDB” perfmon counter to keep track of it. In you perform bulk loads this might not be a good road for you to go down because of this.

Watch your disk performance. A lot more work on TempDB means that those disks are going to get hit more heavily.

Keep an eye on the version store and how big it gets, sys.dm_tran_version_store holds all of those records and can be queried like any other table.

Last, but certainly not least BE WARNED! You will be adding a 14 byte overhead to every row so that it can hold version store information. This might sneak up on you as these 14 bytes are not added until such time as you update a row, so one day you could perform a mass update and actually wind up using a whole lot more disk. The SQLCAT team have posted a really fantastic article at http://blogs.msdn.com/mssqlisv/archive/2009/05/18/why-did-the-size-of-my-indexes-expand-when-i-rebuilt-my-indexes.aspx regarding row versioning and the impact on table size as a result of online and offline indexing. It’s a must read if you are thinking of a trial implementation. 

 

Buck Woody (blog|twitter) has a great disclaimer about never trusting scripts, trying them yourselves in a test environment. I heartily agree with him. Please don’t just throw this on your production server and hope for the best.