CLR Errors – Follow Up

A few weeks ago I posted about an issue I was having with CLR failing on one of my production servers. While a reboot fixed the problem it was a temporary fix and happened on a couple of further occasions. Still not being able to find the root cause Microsoft recommended a complete removal and reinstall of the .NET framework.

One of the Microsoft support team advised running the following query which loads CLR and then traverses through all the registered assemblies. This quickly shows up any problems with CLR and SQL Server integration even if the problem is not showing elsewhere.

1. select * from sys.assemblies

Seeing the following is not a good thing:

Msg 6512, Level 16, State 27, Line 1

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL Server to use CLR integration features.

Removing the .NET Frameworks

Firstly I downloaded the .NET Framework Cleanup Tool from http://blogs.msdn.com/b/astebner/archive/2008/08/28/8904493.aspx

With the tool installed on the server and all SQL services shut down and set to manual start I performed an uninstall of all of the .NET Frameworks in the order

• 3.5
• 3.0
• 2.0

The removal complete a server restart was in order. Once restarted it was time to

Reinstall the .NET Frameworks

Here’s the interesting thing, there’s actually no need to go through and reinstall each individual Framework, in fact just download and install .NET Framework 3.5 SP1 from Microsoft.

Once the install was complete and the server restarted I brought the SQL services back online (and reset their services to start automatically)

Checking sys.assemblies again gave me positive results

name      principal_id           assembly_id          clr_name               permission_set     permission_set_desc          is_visible                create_date                modify_date         is_user_defined

Microsoft.SqlServer.Types 4              1              microsoft.sqlserver.types, version=10.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil          3              UNSAFE_ACCESS  1              2008-07-09 16:20:00.070                2008-07-09 16:20:00.413 0

Since the reinstall I’ve not experienced further issues. It’s a shame that Microsoft were unable to give me a root cause to the problem, however it’s great to have it resolved.

T-SQL Tuesday #10 - Applying Indexes To Replicated Tables

It's T-SQL Tuesday time again! This month Michael J. Swart (blog|twitter) is hosting and has given us the topic of indexes.

I've previously written about how you can find indexes that are not being used and get their sizes. Not being allowed to reuse that meant that I had to come up with something else.

In my previous post on calculating replication schema options I documented a way of being able to change article options in transactional replication so that you could apply all the non-clustered indexes on your subscribing table that exist on the publishing one. But what if you are using replication to offload reporting and you need to create indexes that don't exist on the publishing table?

You could always manually apply these indexes any time that you perform a snapshot but this requires manual work and can easily be forgotten.

Fortunately there is another option, you can have replication automatically apply a post-snapshot SQL script which will create those indexes for you. Here's how...

Create a basic publication (snapshot or transactional) and add a table article. Perform the snapshot and compare indexes between the tables (in this example I am using the person.address table from AdventureWorks2008).

After the snapshot here's how the indexes stand

For this example we're going to create a new index on Admindb.person.address. To do this first we need to generate the index creation script

USE AdminDB
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[person].[address]') AND name = N'NCIX_Zip')
CREATE NONCLUSTERED INDEX [NCIX_Zip] ON [person].[address]
(
    [PostalCode] ASC
) WITH (ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Save this script to C:\ReplicationScripts\CreateReportingIndex.sql

Now we change the existing publication so that whenever a snapshot is taken it will apply this script when applied to the subscriber. This change can be done in a couple of ways.

The quick and easy way is to use sp_changepublication and just pass in the publication name, property change and location of the script.

USE AdventureWorks2008
GO
EXEC sp_changepublication
    @publication = 'Post Snapshot Test'
  , @property = N'post_snapshot_script'
  , @value = 'C:\ReplicationScripts\CreateReportingIndex.sql'
  , @force_invalidate_snapshot = 1 --must invalidate existing snapshots for this to take effect
GO

This can also be done using the GUI (right click on the publication and select Properties)

When you next perform a snapshot then the script will be applied. We can check that this actually worked first by looking at replication monitor where it will tell you that the script was applied.

And then you can check the table itself and confirm that the index exists

There are a couple of final things to note.

Usually you will specify a unc path for the SQL script so that you just have to maintain a single copy. You just need to ensure that the subscriber is able to access the post-snapshot script location.

Post-snapshots are not limited to creating indexes, in fact you can do pretty much anything just so long as the Distribution Agent has the access;

• Send yourself an email to let you know that the snapshot has been applied on the subscriber
• Apply table permissions
• Create views

If you use replication and find yourself manually reapplying indexes it's worth evaluating whether using post-snapshot scripts might save you some work.

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.

CLR Errors In Replication

Did you know that .NET components are used in replication? I sure didn't until I tried to add a new publication and perform a snapshot.

I had not troubles creating the publication or adding the subscribers but when I attempted to run the snapshot I got the following error:

Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x800

Not a whole lot to go on there. There happens to be a DMV which will provide you with the status of CLR in SQL.

select * from sys.dm_clr_properties

I was expecting to see

name    value
-----------------------
directory    C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\
version    v2.0.50727
state    CLR is initialized

but instead I saw

name    value
-----------------------
directory  
version   
state    CLR initialization permanently failed

Time to open a support case with Microsoft.

After running through some troubleshooting steps with the MS engineers, including verifying the .NET install was good (see http://blogs.msdn.com/b/astebner/archive/2008/10/13/8999004.aspx) the decision was ultimately made to restart SQL to see if this resolved the problem.

After the restart everything was running smoothly and there were no issues performing snapshots. Sadly the restart eliminated any chance of getting a root cause for the problem. I've got a filtered dump setup to try and capture something in the event that it happens again. I kind of hope that it does just so we can find out exactly what the cause was.

Here are a couple of interesting notes that I found from this:

• .NET Framework 3.5 is just a wrapper for .NET 2.0
• You can log all .NET events to disk (see http://blogs.msdn.com/b/junfeng/archive/2004/02/14/72912.aspx)
• In the event SQL encounters a .NET/CLR error then it will fail and set a flag internally. That flag will not be reset unless the SQL Instance is restarted and so any CLR functionality will fail
• You want to have your SQL Instance configured to write dumps to a drive with more free disk space than you have memory on your server. By default dumps will be written to the log directory. You can change this by opening "Error and Usage Settings" from the start menu, going to options and changing the error reporting location. Note, you have to restart SQL for this to take effect as this information is read in at startup

Update on Wednesday, September 29, 2010 at 9:14PM by Nic

I've posted a follow-up to this post with steps for the permanent resolution to the issue at http://englishtosql.squarespace.com/english-to-sql-blog/2010/9/29/clr-errors-follow-up.html

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.