I’ve been writing a set of routines to control my database backups recently and one part of this was a CLR User Defined Data Type I’d created that allows some rudimentary file handling (with the appropriate security). By it’s very nature this assembly required the EXTERNAL ACCESS privilege and this is where the head-scratching came about.

Liking clean install scripts with minimal payloads (single scripts are easier to check into Source Code and deploy across multiple environments) I needed some method of encapsulating the deploy and ensuring the appropriate asymmetric key, users etc. were created. However normally this would require either the key file or the assembly DLL to be copied along with the .Sql script – and this would break my 1 script rule! So next idea was to create the asymmetric key from the assembly directly within Sql server – but I couldn’t do that until I’d installed the assembly which would fail because it required the EXTERNAL ACCESS privilege; Thus leading to another game of Chicken and Egg.

The solution was remarkably obvious – use a temporary SAFE assembly to create the asymmetric key then dispose of it directly afterwards – after all the assembly is only required at this juncture to create the asymmetric key.

The first section of the script deals with previous execution cleanups etc:

And the second part deploys the assembly with the requisite privilege:

I’ve been fiddling around with some jobs recently that required values to be kept from one job step to another. Not finding anything much exciting I went for plan Z – roll my own.

They’re simple procedures but do work nicely.

First I create a Schema that everything will be bundled under:

Now we’ll need a backing store:

Next I create the SessionDestroyer Procedure (since it can be called from the next one this keeps the dependencies happy and calm):

Along comes the SessionSaverStore next:

Finally the SessionRetriever:

Use is pretty straightforward. As an Proof of concept I created a Sql Agent Job with two steps – Store’ and ‘Fetch’

Store contained the following:

And Fetch:

Granted it’s missing some bells and whistles (like exception handling) and I’d wager I could stuff the Retriever in as a function. However as a first hit I was pretty chuffed with it!

Whilst it’s not immediately obvious, one can perform a Bulk Copy operation as part of a Sql Server CLR Stored Procedure. There are however some gotcha’s.

First off is that you need to stay away from Context Connections for the actual operation. Whilst you can technically use a Context Connection for the first part, I’d personally not recommend you do for reasons that will become apparent shortly.

The code below is taken from one of my procedures using a homebrew class that provides me with information regarding a table – including the various elements of a fully qualified 4 part name (Server.Database.Schema.Table).

The first part of the code handles the bulk import:

As you can see I’m using two SSPI connections in place of the Context based Connection that’s normally provided. I decided to use SSPI to avoid needing to store user names and passwords. Your mileage and methodology may vary.

The next section is the reason why I advise not using a Context Connection for the DataReader:

In order for this event to work correctly the Context Connection must be free – which will not be the case if you use it for the DataReader. Running the RaisError via ExecuteAndSend ensures the results are not gobbled up by the any special pipe that SqlCommand.ExecuteNoQuery creates (see Vadim Tryshev’s excellent post on RAISERROR in CLR Routines). Finally I use a 0 for both the State and Severity to avoid any subsequent messages that RaisError would otherwise produce.

Sql Server 2008, SP1

Wow, it’s been another time and a bit since my last post. Still here, still banging my head against the proverbial wall…

Came across this Oddness today.

I had the following script segment:

    If Exists ( Select 1 From sys.tables Where Object_Id = Object_Id('Security.Dictionary') )
        Drop Table [Security].Dictionary;
    
    Create Table [Security].Dictionary
        (
                 Id                        Int Identity(1, 1) Not Null
                ,HashValue                 Int Not Null
                ,ItemType                  TinyInt Null
                ,TextData                  NVarChar(MAX)
        );

    Alter Table [Security].Dictionary With NoCheck
        Add Constraint PK_Dictionary Primary Key NonClustered (
             HashValue
        )
        With
            (
                 FillFactor = 50
                ,Online = On
                ,Pad_Index = On
                ,Allow_Row_Locks = On
                ,Allow_Page_Locks = Off
            )
    ;

    If Exists ( Select 1 From sys.tables Where Object_Id = Object_Id('Security.Dictionary') )
        Drop Table [Security].Dictionary;
    
    Create Table [Security].Dictionary
        (
                 Id                        Int Identity(1, 1) Not Null
                ,HashValue                 Int Not Null
                ,ItemType                  TinyInt Not Null
                ,TextData                  NVarChar(MAX)
        );

    Alter Table [Security].Dictionary With NoCheck
        Add Constraint PK_Dictionary Primary Key NonClustered (
             HashValue
            ,ItemType
        )
        With
            (
                 FillFactor = 50
                ,Online = On
                ,Pad_Index = On
                ,Allow_Row_Locks = On
                ,Allow_Page_Locks = Off
            )
    ;

    If Exists ( Select 1 From sys.tables Where Object_Id = Object_Id('Security.Dictionary') )
        Drop Table [Security].Dictionary;
    
    Create Table [Security].Dictionary
        (
                 Id                        Int Identity(1, 1) Not Null
                ,HashValue                 Int Not Null
                ,ItemType                  TinyInt Not Null
                ,TextData                  NVarChar(MAX)
        );

Go

    Alter Table [Security].Dictionary With NoCheck
        Add Constraint PK_Dictionary Primary Key NonClustered (
             HashValue
            ,ItemType
        )
        With
            (
                 FillFactor = 50
                ,Online = On
                ,Pad_Index = On
                ,Allow_Row_Locks = On
                ,Allow_Page_Locks = Off
            )
    ;

Off topic, however worthy of keeping for posterity. From the Department of Homeland Security comes this press release from the Office of the Press Secretary, and a statement on just how serious the US Government is on eradicating the 2009 H1N1 ‘Swine Flu’ virus.

And just incase they redact their statement, here’s the most chilling section:

One hopes this was a simple typo!

Our resident architect came over today and posed me this little puzzler – he wanted a query that would return if 1 row or nothing based of a certain criteria. In other words if two rows met the criteria he wanted nothing pulled back.

A strange request I’ll grant ye, and one that took me some time to bang my head against, however the answer was pretty neat:

Create Schema Test;
Go

Create Table Test.Only1RowTest
(
         Key1    Int
        ,Key2    Int
)
Go

Insert into Test.Only1RowTest Values (1,1),(2,2),(3,3),(1,2);


-- Demonstrate pulling back no rows because 2 rows meet the criteria

With Cte As ( Select Top(2) * From Test.Only1RowTest where Key1 = 1  )
    Select Cte.* from Cte Inner Join  ( Select Count(*) As c From Cte) As CteC On CteC.c = 1;

-- Expand the criteria to further limit the row count and we get 1

With Cte As ( Select Top(2) * From Test.Only1RowTest where Key1 = 1 And Key2 = 1 )
    Select Cte.* from Cte Inner Join  ( Select Count(*) As c From Cte) As CteC On CteC.c = 1;

To break it down further, we create a common table expression and start off on the basis that returning 2 rows is just as bad as returning 2 million, so we limit the rows pulled back with a Top(2), we then bring back the results of the CTE as long as the # of rows returned =1 (no-one ever said an Inner Join had to join two columns together).

Sql Server 2008 Build 10.0.1798.0 (RTM-CU4)

It’s a feature I for one have been screaming desperate for – the Sql Server 2008 Resource Governor – the means to finally provide a T-SQL based process to throttle how much CPU and Memory certain activities can utilize.

For me I need to it because I hate bringing the server down every time I need to run a fragmentation scan; with a 1.5TB DB on an undersized server, I need most of the resources dedicated to keeping the application happy. However I don’t want anything I do to kill the performance – and likewise I don’t want some schmuck running a badly written adhoc query in Management Studio preventing me from doing my job.

And this is where the Resource Governor comes in to play by offering Resource Pools and Work Groups. Each Pool can be though of almost as an individual Sql Server instance operating on shared data.And to a certain extent this is true, however the pools do share some meta-data which I’ll explain later.

Out of the box there are 2 Pools and 2 Groups pre-defined – One called Internal which is static and immutable and where Sql Server processes its internal processes, the other called Default which contains a undeletable but otherwise configurable group, also called Default.

In this configuration and user processing is performed in the Default Pool and all internal processing is carried on in the Internal pool – even to the detriment of any other pool out there. This allows Sql Server to continue processing without other pools pressurizing it and as a result, no user processes are allowed in the Internal pool.

Pools are the first level of control and are used to define the minimum and maximum CPU and memory workloads running inside them are ever allowed to use. Work Groups take it to the next level and allow not only a finer level of control but also the ability to move them between pools. Groups, for example can be defined to specify how much of a % they take out of the pool, the priority level and also the MAXDOP setting. Finally, users are associated with pools via use of a Classification Function.

However, it is important to understand that the Resource Governor really only limits resources when there is a pinch point in total resource availability. Running identical queries on an otherwise silent server, one in a High priority pool and the other in a low one will have no obvious effect. However once the server is being pushed the resource governor provides a nice guarantee of resource availability to those sessions that require it and a good throttle to those that don’t.

In the examples that follows I use two new pools and one associated work group in each pool. One pool will be used to process high priority tasks and the other low priority. Two users will be created, one will be assigned to the High Work Group, the other the Low Work Group.

(NB: for brevities sake the users I’m creating here do not have any passwords; it goes without saying that this is exceptionally bad form and should not be practiced on any server that you value data, schema or any other objects on!)

Create Resource Pool Low With ( Min_CPU_Percent = 0, Max_CPU_Percent = 30 ) ;
Create Resource Pool High With ( Min_CPU_Percent = 60, Max_CPU_Percent = 100 ) ;

Create Workload Group Low With ( Importance = Low ) Using Low;
Create Workload Group High With ( Importance = High, Request_Max_Memory_Grant_Percent = 100 ) Using High;

Alter Resource Governor Reconfigure;

Use AdventureWorks2008;
Create Login LowUser With Password=N'', Default_Database=AdventureWorks2008, Check_Expiration=Off, Check_Policy=Off;
Create User LowUser For Login LowUser;
Exec sp_AddRoleMember N'db_owner', N'LowUser'
Create Login HighUser With Password=N'', Default_Database=AdventureWorks2008, Check_Expiration=Off, Check_Policy=Off;
Create User HighUser For Login HighUser;
Exec sp_AddRoleMember N'db_owner', N'HighUser'

Use master;
Go

Create Function    dbo.ResourceGovernorWorkgroupClassifier()
   Returns    sysname
   With SchemaBinding
As
   Begin

       Declare         @WorkgroupName                SysName            = 'default'
       ;
      
       If SUser_Name() = 'LowUser'
           Set @WorkgroupName = 'Low';
      
       If SUser_Name() = 'HighUser'
           Set @WorkgroupName = 'High';

       Return @WorkGroupName;
      
   End
Go

Alter Resource Governor With ( Classifier_Function = dbo.ResourceGovernorWorkgroupClassifier);

Select COUNT(*)
From Sales.SalesOrderDetail As Sod1
Inner Join Sales.SalesOrderDetail As Sod2 On Sod1.SalesOrderDetailID != Sod2.SalesOrderDetailID;

Set Statistics Time On;
Select COUNT(*)
From Sales.SalesOrderDetail As Sod1
Inner Join Sales.SalesOrderDetail As Sod2 On Sod1.SalesOrderDetailID != Sod2.SalesOrderDetailID;
And Sod1.SalesOrderID < 43800;

Alter Workload Group Low Using High;
Alter Resource Governor Reconfigure;

Msg 10904, Level 16, State 2, Line 2

Resource governor configuration failed. There are active sessions in workload groups being dropped or moved to different resource pools. Disconnect all active sessions in the affected workload groups and try again.

Select    Session_Id, Status
 From    sys.dm_exec_sessions As S
       Inner Join sys.dm_resource_governor_workload_groups As G
           On S.group_id = G.group_id
 Where     G.name = 'Low';

Sql Server 2008 Build 10.0.1600.22 (RTM)