Ken Kaufman

SQL Server Time-Dates

Ken Kaufman — Tue, 24 Jun 2008 01:09:00 GMT

The other day in our architecture group meeting the question was raised on how SQL Server handled future dates in relation to lights savings time. For example if a play was scheduled in January for a date in July at 1:00 PM would reflect this time or 2:00 PM because of the jump in a hour for day light savings time. This is a common issue with many applications, but not SQL Server. It all relates to the time source the application is getting the time from. Applications use one of two sources the CPU which works on UTC or it uses an OS api which reflects the time zone setup in the OS. Of course you’re simply transferring the burden from one program to the next (OS), but it has its advantage to work off the OS time rather then the CPU, but it also has its disadvantages as well.

Before going into how SQL uses its source, you need to understand how SQL maintains its date. The “datetime” datatype is eight bytes, or 2 ints, divided between date and time. The first int represents the date where the base date is 1900 and increments are set at one day. The second int or four bytes is the time with a base of 12:00 AM, increments are 1/300 of a second. If you want to see how this works run the following queries:

--Convert date to a binary

select convert(varbinary(100), getdate())

--Take the first 8 hex values and convert ensure the "0x" is prefix

select convert(int, 0x00009AC4)

--Do a date diff with 1/1/1900 The dates should be the same

select datediff(day, '1/1/1900', getdate())

Now that we know how data is stored there is the critical nature of what source does sql use. As I said earlier applications can use the converted OS time or do the coversion itself against the cpu’s time that’s using UTC. SQL uses both known as “High Resolution Timer” and “Low Resolution Timer”. The High resolution timer works off the cpu timer or UTC, which SQL queries for internal processes such as workers, locks and data cleanup. The Low resolution timer calls the windows api GetTickCount which returns the OS time. This is used by external functions such as Getdate(). Since these external needs are based on the OS time, there unaffected by changes in day light savings time, all time is still based on delta of midnight as the OS represents the time zone.

This type of tradeoff between High and Low resolution seems to fit most needs, unless you get into time comparisons, such as Dateadd or Datediff functions. These are unaware of day light savings time or UTC, there given a value, and compute the deltas and assume no day light time. As with SQL your application will probably require different time values based on regional time and UTC. SQL provides this functionality in the form of the function getutcdate.

Maintenance Plan Quirks

Ken Kaufman — Thu, 12 Jun 2008 14:21:00 GMT

The move to maintenance plans based on SSIS was a very impressive step. As an administrator it provides the ease and flexibility of creating and maintaining plans through an object based interface. There are several differences in the Maintenance plans implementation of SSIS compared to the full blown product, primarily in its extensibility, as it doesn't expose all the features. However where it beats SSIS is on the management of DBA specific tasks and ease of use. Not to say SSIS is difficult to use, but changing and troubleshooting can be cumbersome. If you need to change a SSIS package it requires opening up Visual Studio, making the changes then saving to the appropriate location where a job (Assuming your using jobs to schedule) expects it. Under maintenance plans all setup and changes occur within Management Studio. If you're trying to determine what a co-worker setup it's much easier then tracking down the package, moving to a dev environment then reviewing.

There are some quirks with SSIS which I'm not sure will be addressed with 2008, predominately in the way the Maint jobs are tied to Jobs. If you don't understand how this works, these quirks could cause headaches. First unlike SSIS maintenance plans are tied to sql jobs, or to be more specific sub plans. Every Maint plan has at least one sub-plan, which is represented as a job after it's created. If you try to delete the job, you'll get a foreign key violation, which ties the sysmaintplan_subplans and the sysjobs table. The main oddity comes in the form of saving plans. When you create a sub-plan it creates a new job, however when you edit a plan then save it overrides the job, by wiping out all the steps then rewriting only the single step that represents the sub plan. In essence this doesn't allow you to append to that job, if you need other processes to occur after the maint plan runs, because every edit wipes out your appended steps. An example of this is after backing up locally, you might want a second step to copy your data to a backup server.

There are a few ways to get around this, the first is the obvious, which is put your serialized processes into the package. The issue here is that maintenace plans is a scaled down version of SSIS, and doesn't provide the same functionality, such scripting or running executables without hacky workarounds. The second is to put your serialized processes into a seperate job that performs these other processes, and as part of the maint plan run the execute job task that points to this new job as the last step. The issue here is it takes away your ability to separate processes. For example in the backup/copy example, this prevents you from backing up without copying and vice-versa.

My preferred solution is to call the maint plan with a hand rolled job. The key is to remember the plan is simply an SSIS package stored in msdb, in a psuedo directory "Maintenance Plans". To call the plan simply select the "SQL Server Intergration Service Package" type, navigate to the Maintenance Plans directory, and select your package. Since sub plans are actually individual SSIS packages under the umbrella of the Maint plan you need to specify this package. This is accomplished by navigating to the "Set Values" tab. Under "Property Path" type "\package\[name of sub plan].disabled", then under the corresponding "value" type "false". For Example if your sub plan is called sub_plan2 the path would be \package\sub_plan2.disabled. Once in place any saving and/or editing will not effect this job since it references the plan and not the job. The downside here is you have two jobs, the one created through Maint plans and the one hand rolled.

Before ending there's one more point to be made about scheduling through a separate job, which is the ability to run multiple sub plans through using the set values. If you have multiple sub plans you want to run serially, under the default behavior you need to call one sub plan job then the other. However using the Set values allows you to run serially within one job step by simply adding the two sub plans into the set values tab.

Performance Effects on Column Type Changes Another Point

Ken Kaufman — Tue, 10 Jun 2008 18:28:00 GMT

In a previous blog http://blogs.sqlservercentral.com/ken_kaufman/archive/2008/05/14/performance-effects-on-column-type-changes.aspx I talked about how SQL handled switching data types and lengths of the same data type, in particular increasing a varchar length, as well as the cost of moving a varchar to an int. One of the points left out is moving from a varchar to a char and vice-versa. Even though these look similar from a high level to be the same data type, there stored completely different at the record level. Storage of data in a row involves two primary locations for data. The first in where your fixed data types resides, this is all data that is fixed in size resides, including the char data type. The second is your variable length section. This second section contains some critical meta-data about your variable data, there's a global 2 byte section that tracks all the variable columns in your record, and there is an additional 2 bytes for each fields that has the offset of each variable field. What this all means for converting char into varchar and the reverse is that it requires moving data within the record, even if your going from a char(5) to a varchar(10). Depending on the type of conversion and underlying data could also mean significant page splits

Leveraging Clustered Indexes in a Covered Index

Ken Kaufman — Sat, 07 Jun 2008 01:28:00 GMT

In my last blog I talked about covering indexes. In doing so I slightly touched up non-clustered indexes containing a pointer to the clustered index of the table. There are often times when you want to cover a portion of the a composite clustered index. When doing so SQL Server is smart enough to realize this, and makes adjustment.

When creating a non clustered index on a table that is built on a clustered index (Not a heap) the leaf level of the index has a pointer to the back to the clustered index. For example if you have an index clustered index of col1, col2 and you put a non-clustered index on col3, this index would contain all three fields within the non clustered index, col3 for sorting and col1/2 to point back to the clustered index, again this is only at the leaf level and not the root or intermediate levels.

Where you can leverage this is when your query looks something like this:

Select col1, col2, col3 from table1

Where col3 > 5.

In this case assuming selectivity is high, SQL would use a partial scan against col3, and pick up the rest of the data without having to go back to clustered index, hence covering it. Where SQL gets smart is when you want to use a field inside your composite index. If you need to satisfy the below query

Select col1, col2, col3 from table1

Where col3 > 5 and col2 < 3

The following index might be optimal assuming col3 has a higher selectivity then col2

Create index nonclusteredtest on table1(col3, col2)

In this case at the root and intermediate levels col3 and col2 are included in that sort order. When you get down to the leaf level SQL knows not to add a double entry for col2 one for the index and one for the clustered index. Rather it's only in there one time and represents both indexes. This is a significant space savings particularly if you have a wide field.

Covering Indexes

Ken Kaufman — Thu, 05 Jun 2008 23:26:00 GMT

Covering an index allow you to use a subset of data in an underlying table to answer queries without having to go to the underlying table, whether the underlying table is based on a clustered index or a heap. Using the “include” feature allows you to add to the non-clustered index without having to worry about sorting as it comes in. It also provides the benefit of not storing unneeded columns in the intermediate level, as well as going above the 900 byte limit placed on indexes. Much of following refers to working with b-trees and assumes the reader has a general understanding of how they work. The best way to visualize this is through example by working inside the Adventureworks DB on the SalesOrderDetail which has the below table definition. The clustered index (b-tree) is sorted on Salesorderid, salesorderdetailid. What this means that each row at the leaf level (bottom level) of the index contains all the data within each record. However the data on this leaf level is sorted in ascending order by SalesOrderID, and SalesOrderDetailID. The intermediate levels of the clustered index contain only the fields used in the clustered index, and serve the sole purpose to allow SQL to traverse (walk down) the tree based on the index sorted fields. One note before moving on is the CarrierTrackingNumber field has been modified from a nvarchar 25 to a char 200, as well as additional records have been added to the table. This was done to make a point.

CREATE TABLE [Sales].[SalesOrderDetail](

[SalesOrderID] [int] NOT NULL,

[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,

[CarrierTrackingNumber] [char](200) NULL,

[OrderQty] [smallint] NOT NULL,

[ProductID] [int] NOT NULL,

[SpecialOfferID] [int] NOT NULL,

[UnitPrice] [money] NOT NULL,

[UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)),

[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_SalesOrderDetail_rowguid] DEFAULT (newid()),

[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()),

CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED

(

[SalesOrderID] ASC,

[SalesOrderDetailID] ASC

)

This structure is optimally sorted if you want to grab a subset of data based on salesorderid. If you look at the query below, the physical path would be:

Traverse the tree vertically and find where salesorderid = 46321
Once at the root level go horizantal until until you hit salesorderid 47321
While moving horizontal grab the “CarrierTrackingNumber” value from each row it passes over. This is accomplished by the fact that in a clustered index all the data is contained at the root level

select CarrierTrackingNumber from Sales.SalesOrderDetail

where salesorderid between 46321 and 47321

This query retrieves 6228 rows using on 212 reads. The rows to reads ratio is 29 to 1. Meaning every IO to memory is grabbing 29 rows. To prove out the effiency, the size of the row is approximately 258 bytes which translates into 31 rows per page. If we’re picking up 29 rows for every write almost all the IO is working out the leaf level and not traversing the tree on the intermediate levels.

Tables are often used for multiple requests. For example if you want to look up all orders between Aug 1^st and Sept 1^st in 2001 that had an orderqty greater then three, and was only concerned with gathering the CarrierTrackingNumber You would run the following query:

select CarrierTrackingNumber

from Sales.SalesOrderDetail

where modifieddate between '2001-08-01' and '2001-09-01'

and orderqty > 3

The problem here is there is no index, and therefore would scan the entire table. On this small table of only 120K rows a scan would do 4055 reads, to retrieve only 301 rows. This is almost 15 reads per row. Quite a difference from the partial scan above. The quick fix here is to place a non-clustered index on the predicates (modifieddate, orderqty). By creating this index your now moving from a scan to a seek. This performs 937 reads, and get’s your ratio down to 3 reads per row. This is a much better then the scan, but doesn’t come close to the first query.

create index testnonclst

on Sales.SalesOrderDetail(modifieddate, orderqty)

Before going any further you need to understand the structure of a non-cluster index so we can see the benefits of covering and additionally leveraging the “include” clause. The last query performed a seek, and a key lookup. For each value found on the index meeting the predicates requirements it would traverse back to the clustered index to retrieve the actual data, hence the 3:1 ratio of reads to rows. The 3 reads represent the the levels of the index in the b-tree of the clustered index, and an additional read of the the non-clustered leaf data. Every value found in the non-clustered index requires traversing the levels of the clustered index to get the data (CarrierTrackingNumber).

A non-clustered index has the same b-tree structure as a clustered index. The primary difference is within the record. Each record has an additional field or fields that point back to the clustered index. For example inside the testnonclst index it contains both “modifieddate, orderqty” the data being indexed and the clustered index “SalesOrderID, SalesOrderDetailID” fields that it references. Understanding this is a b-tree structure you can put all your data inside this non-clustered index that satisfiys your query. Another way of saying this is any fields used by your query will reside within your index. The outcome is the loss of the two IO’s back to the clustered index, for each record since all your data is covered inside your non-clustered index.

drop index testnonclst on Sales.SalesOrderDetail

create index testnonclst_Covered

on Sales.SalesOrderDetail(modifieddate, orderqty, CarrierTrackingNumber)

If you examine the query below and compare it to the above index creation, your covering all three fields in the query, CarrierTrackNumber which is being selected, and your two predicates, Modifieddate and orderqty. Determining which field goes on the left should be based on selectivity of each field. The highest selectivity of the field used in your predicates should go to the left.

select CarrierTrackingNumber

from Sales.SalesOrderDetail

where modifieddate between '2001-08-01' and '2001-09-01'

and orderqty > 3

When we run the query above our IO’s drop to 31 IO’s to return 301 records, almost 10 records per IO. This is because it does a partial scan on the leaf level of the index rather then needing to due a seek. The numbers are close to the 29:1 ratio of the first query run against the clustered index. However because your carrying the extra fields of the pointer back to the clustered index, you’ve added 8 bytes to each record.

The final improvement to be made is remove the carrierTrackingNumber field from the index and using the “include” clause as part of the index. By doing so you’re appending this field to the non-clustered index at the leaf level, and ignoring any sorting of this field. This serves two purposes the first is space savings on the intermediate levels of the b-tree which will give a small performance gain, and the second is fact that you don’t have to sort your data as it comes in, and avoid page splits at every level. In the following example splitting and sorting are not an issue, but if modifieddate and orderqty had a significant amount of duplicates it would have a good size impact during inserts and updates.

drop index testnonclst_Covered on Sales.SalesOrderDetail

create index testnonclst_Covered_include

on Sales.SalesOrderDetail(modifieddate, orderqty)include( CarrierTrackingNumber)

After running the same query with this structure there’s a 7 percent gain dropping to 29 IO’s. The benefits gained through using include are dependent on sorting as mentioned earlier, and the size of the included field. The larger this field the greater the benefits.

Performance Effects on Column Type Changes

Ken Kaufman — Thu, 15 May 2008 01:47:00 GMT

The other day I was given a request to change a field from varchar(6) to varchar(10). Not thinking it was a big deal I said just give me the checked in code and I'll make the change. One of our sharper dev's (Bill Carlson) was concerned with the lock caused by this statement. I'd never actually took a deep dive on how this would occur so I decided to run a quick test to see what would happen. Making this change on a 2.5 million row table took 10 ms. What this means is SQL is smart enough to understand that since it's growing a varchar field from 6 to 10 it didn't need to go to the data to verify this statement would succeed. It simply had to make the meta data changes in the system tables. However when I reverted this back from 10 to 6 it took 32 seconds. In this case SQL had to verify each value of each record to ensure it was less then 7 characters.

This brought on the realization of how SQL Server verifies data during inserts and updates. If you look at the structure of a record with variable lengths there is no meta data on the length of variable fields it simply knows to create offsets within the row. Checking to determine whether a field is too long occurs at a higher level as data comes in. Once it passes this check data streams to the data page without any additional verification.

After wetting my interest on varchars the next logical choice was to look at int's. Starting out with an unfragmented table I changed a field from int to bigint, then back again from bigint to int. Moving from an int to a bigint was almost 7x longer then the reverse. This is was due to page splitting and allocation which reared it's head in perfmon. The simple explanation here is that unlike varchar items, every value takes a defined space 4 bytes/8 bytes for int/bigint respectively. Going from int to bigint added 4 bytes to each record, and forced pages to be allocated and split. In my case the initial int was 4 bytes of a 13 byte row almost 30%, growing it to 8 bytes made it almost 50%. In terms of page size it went from one page holding 620 rows to 474. I guess the moral of this is whenever you're looking at running ddl statements you need to consider what the real effects on the table are, and have someone bright looking over your shoulder.

Log Shipping with Security

Ken Kaufman — Wed, 14 May 2008 00:18:00 GMT

Many DBA's struggle with maintaining their security infrastructure while providing the ability for dev to access the delivery environment. A common approach is to setup a NRT (Near Real Time) environment on a warm server. There are several approaches to this. If you're looking just to push data SSIS or replication seems to fit the needs. The problem with these approaches is that it's not a true representation of delivery. Since you're only shipping data it often leaves out schema items such as stored sprocs and views that might have changed in the last release. In order to ensure development gets an actual representation of the DB the best method is to ship the entire database. This requires replicating all transactions both dml and ddl in the db.

This need leaves you with one of two choices, log shipping or mirroring with a snapshot. Either approach requires maintaining a read-only copy of the db, with occasional refreshes. For example in log shipping you might restore every hour in norecovery standby mode. Or in mirroring every hour you peal off another snapshot.

Although moving the database provides users with most of their needs, there is the issue of security. SQL Server has two levels of security the first is in the login where the user authenticates at the server level. When a user authenticates against the server there corresponding SID in the syslogins table is cached. It is this SID that gives the user access to the DB's on the server. If a user's login SID at the server level, is identical to a user SID in the database, the user has the permissions of that user inside the DB. If you create a user called "DEV", that user is assigned a random SID, on the server it was created on. When you give that user access to a database the login SID is added to the sysusers table in the database. It's the SID that's tracked not the user name, hence why you can have a different user name then login name. If you log ship or mirror to another server the database in which the user Dev was created, requires an identical SID on that server. This is because the user (DB) SID, resides inside the DB. Having a database with users not corresponding to Login SIDs or users with a different SIDs is referred to as orphaning a user.

There are a few ways to sync the user and login SID. The first is to execute the sp_change_users_login system stored procedure. This is well documented in Books Online, however the issue here is that it changes the SID inside the DB, and since log shipping or snapshots are read-only, you can't write, even to a system table. A different approach is to create the user on both servers with the same SID. For example if on your source server you create a user called "orphan", the server will generate a SID at the server and database level that match. To see this run the following commands.

CREATE LOGIN orphan WITH PASSWORD = 'Password123456';

use Adventureworks

CREATE USER orphan FOR LOGIN orphan;

select sid from sys.sysusers where name = 'orphan';

select sid from sys.syslogins where name = 'orphan';

Once created on the source server take the SID you queried for and create the same user on your destination server.

CREATE LOGIN orphan WITH PASSWORD = 'Password123456',

sid = 0x61BECB8A1CB0394FB5FC7393F6C0C3A2

Now any database moved from the source server to secondary server that contains this user can be accessed by this account. However this isn't the full fix quite yet. If the user's SID and password is the same on both servers, then anyone that knows the password can access both servers. The simple change is to have different passwords on each server. This is the nice part of this two phase authentication, you're not tied to a password but a sid. So on the second server run the following command.

CREATE LOGIN orphan WITH PASSWORD = 'NewPassword123456',

sid = 0x61BECB8A1CB0394FB5FC7393F6C0C3A2

With this in place data can be logged shipped or mirror/snapshot without any security issues in place. One other note this is based on sql server authentication. Using windows authentication presents a different problem, in that the SID is the NT SID, and hence no matter what server you're on changing the password is not a possibility.

Transferring Data In Memory using SQLBulkCopy Class

Ken Kaufman — Fri, 09 May 2008 00:21:00 GMT

In a SQL Server enterprise there are several ways to move data, choosing the best method, is often more of a challenge then actually implementing. The key factors involved are performance, Dev time, and ease of implementation. A nice utility in the .Net framework is the SqlBulkCopy class. This leverages the sturdy dll's of the command line tool BCP, with one major advantage, it's done in memory. Using BCP to import data requires a disk location. If you want to use BCP to move data from one server to the next, you must drop to disk from the source, then import from disk into the destination. Until solid state drives become a reality, this method comes with a heavy overhead tax. This is where the bulk copy class comes into play. It eliminates this bottleneck, by working within memory. This satisfies the performance requirement. Leveraging Sql Server's support for assemblies, gives the ability to execute from within a SQL Stored Sproc, and satisfies the ease of implementation.

The core under workings of using this class is attaching to the source using ADO with a command reader to the data, then transferring this data to the SqlBulkCopy class which pushes to the destination. At its simplest implementation you could have a working assembly in less the ten lines of code, which satisfies Dev Time requirements. In the technical world every alternative solution has its own cost. Using this method transfers your data buffering to a more expensive storage in the form of memory. This is not as big an issue as one would expect. Using the BCP command line tool requires transferring the entire data set to disk, then picking it up again. Using a data reader allows for a smaller foot print, were data is fetched and inserted in a pipelined operation. Because it's pipelined you can easily transfer a GB of data with less then 20 MB of memory.

There are several samples of this on the net, and in Visual Studio documentation of how to do this. I've included a recent code snippet I used. As with most code it has it's own flair, essentially focusing on data verification, by counting rows at the source and destination and allowing for the ability to either sync (truncate the destination) or append data.

Managed Code

public static void pushData(string sourceTable, string destServer,

string destDB, string destTable, int batchSize, int tearDown)

{

//Declare Variables

string _destServer;

string _destDB;

string _query;

string _destTable;

int _batchSize;

string destConnString;

string _cntDestTable;

string _cntSourceTable;

int _cntDestResult;

int _cntPriorDestResult;

int _cntDestReturn;

int _cntSourceResult;

string _cntError;

//Set Variables

_destDB = destDB;

_destServer = destServer;

_destTable = destTable;

_batchSize = batchSize;

//Source Query

_query = "select * from " + sourceTable;

//Verify Queries

_cntDestTable = "select count(*) from " + destTable;

_cntSourceTable = "select count(*) from " + sourceTable;

_cntError = "RAISERROR ('Source and Dest Counts Dont Match', 16, 1)";

//Build Destination ConnString

destConnString = "Data Source=" + _destServer + ";Initial Catalog=" +

_destDB + ";Integrated Security=True";

//connect Locally get data

SqlConnection oConn = new SqlConnection("context connection=true");

oConn.Open();

//Gets Count of Source used to verify at end of assembly

SqlCommand cmdCntSource = new SqlCommand(_cntSourceTable, oConn);

SqlDataReader sourceCntReaderPrior = cmdCntSource.ExecuteReader();

sourceCntReaderPrior.Read();

_cntSourceResult = (int)sourceCntReaderPrior[0];

sourceCntReaderPrior.Close();

//Get Rows into reader to push

SqlCommand cmd = new SqlCommand(_query, oConn);

//pull into reader

SqlDataReader reader = cmd.ExecuteReader();

//Open Connection to Destination

SqlConnection destConn = new SqlConnection(destConnString);

destConn.Open();

//Truncate Destination if needed else Get Existing row count

if (tearDown == 1)

{

string tearQuery = "truncate table " + _destTable;

SqlCommand cmdTear = new SqlCommand(tearQuery,destConn);

cmdTear.ExecuteNonQuery();

//Set seed count to 0

_cntPriorDestResult = 0;

}

else

{

//Get Seed Count in Dest table

SqlCommand cmdCntDest =new SqlCommand(_cntDestTable, destConn);

SqlDataReader destCntReaderPrior = cmdCntDest.ExecuteReader();

destCntReaderPrior.Read();

_cntPriorDestResult = (int)destCntReaderPrior[0];

destCntReaderPrior.Close();

}

//Move Data to desting

SqlBulkCopy sbc = new SqlBulkCopy(destConn);

sbc.DestinationTableName = _destTable;

sbc.BatchSize = _batchSize;

sbc.WriteToServer(reader);

reader.Close();

//Get count of Destination after insert

SqlCommand dstCmd = new SqlCommand(_cntDestTable, destConn);

SqlDataReader destCntReader = dstCmd.ExecuteReader();

destCntReader.Read();

_cntDestResult = (int)destCntReader[0];

destCntReader.Close();

//Calculate Rows Copied

_cntDestReturn = _cntDestResult - _cntPriorDestResult;

//Compare Source/Dest for Accuracy If they don't match send error

if (_cntSourceResult != _cntDestReturn)

{

SqlCommand cmdCntError = new SqlCommand(_cntError, oConn);

SqlContext.Pipe.ExecuteAndSend(cmdCntError);

}

else

{

//Send results and Cleanup

SqlContext.Pipe.Send(_cntSourceResult.ToString() + " Rows sent :)");

oConn.Close();

destConn.Close();

}

The stored sproc to call this would look like this:

CREATE PROCEDURE [dbo].[sp_BCP]

@sourceTable [nvarchar](50),

@destServer [nvarchar](50),

@destDB [nvarchar](50),

@destTable [nvarchar](50),

@batchSize [int],

@tearDown [int]

WITH EXECUTE AS CALLER

EXTERNAL NAME [bcpTest].[BCPData].[pushData]

Table Variables, Temp Tables, Recompilations

Ken Kaufman — Fri, 21 Dec 2007 01:58:00 GMT

The general rule of thumb on "variable tables" VS "temp tables" is if your working with a small datasets or complex query plans use a temp table. When working with simple queries or small datasets use variable tables. This was built by design. Variable tables are light in memory structures, locking is minimal, statistics aren't created, and plans don't recompile with changes to the table. Temp tables are real table structures, and behave almost identical to normal tables, with a shorter life span. Therefore there is an overhead, in creating and tearing down, with temp tables. The question is why use temp tables at all to hold temporary storage.

The trick is in recompilation. Recompilation occurs mid stream during a batch, when circumstances change, from the initial plan, where SQL determines to recompile a more optimal plan. This is often observed when a subplan is dependent on a previous subplan. For example if a query inserts data into a work table, then selects off the work table. If the precompiled plan expects 100 rows in the table, but gets 100K, the plan might need to be changed. This is where the difference lays between the two data structures. Since SQL keeps statistics on a temp table, it can recognize major changes and make the appropriate changes to the plan through a recompile. However with that said a recompile is expensive and gains from the new plan might not justify the cost of the recompile.

Recompiles on temp tables occur when the cardinality becomes greater then 6, and then again greater then 500. I always look at 1000 being a threshold on whether to use a temp table over a table variable. This is a personal rule of thumb, and you should always look at the complexity of the query itself which in the end has the final say on whether a query plan will be recompiled. The following example uses a union join to force this situation.

There are three portions to this script, the first creates the tables and populates them. The second and third create stored procedures based on a temp table and a table variable. The @rowcount variable in part one will be used to throttle the query into different states.

--Part 1 Create table

Declare @rowcount int

set @rowcount = 5

if exists(select name from sys.objects where name = 'table1')

Begin

drop table table1

end

if exists(select name from sys.objects where name = 'table2')

Begin

drop table table2

end

if exists(select name from sys.objects where name = 'table3')

Begin

drop table table3

end

--Create tables

create table table1

(PKInt int primary key,

charfield varchar(20))

create table table2

(PKInt int primary key,

charfield varchar(20))

create table table3

(PKInt int primary key,

charfield varchar(20))

Declare @Interval int

set @interval = 1

while @interval < @rowcount

Begin

Insert into table1

values(@interval, 'Table1')

set @interval = @interval + 2

end

set @interval =2

while @interval < @rowcount

Begin

Insert into table2

values(@interval, 'Table2')

set @interval = @interval + 2

end

set @interval = 1

while @interval < @rowcount

Begin

Insert into table3

values(@interval, 'Table3')

set @interval = @interval + 3

end

--Part2 create SP with table variable

Create Procedure tableVariable

Declare @tableVar table

(PKInt int primary key,

charfield varchar(20))

insert into @tableVar

select * from table1

union

select * from table2

select t.Pkint, t.charfield, tv.charfield

from table3 t inner join

@tableVar tv on

t.pkint = tv.pkint

--Part2 create SP with table variable

Create Procedure temptable

create table #temptable

(PKInt int primary key,

charfield varchar(20))

insert into #temptable

select * from table1

union

select * from table2

select t.Pkint, t.charfield, tt.charfield

from table3 t inner join

#temptable tt on

t.pkint = tt.pkint

Once these are in place run the following commands. Display the execution plan for the newly created stored procedures.

exec tablevariable

exec temptable

You'll notice that both SP's are using a nested loop to join the temporary storage with table3, with a scan of table3 and a seek on the temp structure. Now go ahead and execute them and turn on the display actual execution plan. This will allow you to see if the plan is recompiled with a new plan. In addition execute "set statistics io on" so we can see how many IO's this is performing. The freeproccache is for good measure so everything is clean when compiled

dbcc freeproccache

set statistics io on

exec tablevariable

exec temptable

Both held there plan, and if you look at the "select join" portion of the statement for reads both have an identical cost of 6. This is inline with the statement above that if under 6 rows change then no recompile will take place. Since we only had 5 no recompile occurred. So lets prove out that theory by running the first set of statements to recreate the tables and set the @rowcount variable to 6. Once complete repeat the above exercise of executing the SP's.

Declare @rowcount int

set @rowcount = 6

You'll notice the estimated plan is again identical nested loop with the temp storage doing a scan and table3 using a seek. When you look at the actual plan the tablevariable SP kept the precompiled plan while the temptable SP recompiled and chose a different plan. Still using a nested loop but this time using a seek against the temptable and a scan against table3. The IO's also show the recompile improved IO's on the select join with the variable having 12 IO's while the temptable used only 6.

As stated before 500 is also a benchmark where a recompile will take place. Lets take a look at what the difference is here. Recreate the tables by setting @rowcount variable to 500. By this time you should be convinced that the precompiled plan for both SP's will stay the same, so lets only focus on the output. This time the temptable SP is no longer using a nested loop, but rather a merge join. The comparitable difference is 1002 Reads for the tablevariable and 6 for the temptable. I played this out a little further to get some more numbers. Notice the expotential growth difference below. As you can see the larger the changes to temp storage the more disparity occurs, which equals resources for the server, and user time waiting.

Insert Rows	Temp Reads	Variable Reads
5	6	6
6	6	12
500	6	1002
1000	7	2003
10000	43	20029

Before you go out and change all your variable tables into temp tables, there are a few things to consider. As mentioned above temp tables are real tables and have the associated overhead with them. There's the metadata creation and maintenance. There's heavier locking, and transactions are logged. Of course the biggest overhead comes in the form of recompilations. The reason execution plans are cached is because they're costly to recreate. The gains made in IO's can easily be swallowed up by the cost of the recompilation.

Workaround for Monitoring Replication

Ken Kaufman — Tue, 18 Dec 2007 03:00:00 GMT

I’ve put in too many hours trying to get monitoring working from transactional replication latency. I was unable to find anything online that mimicked my issue. The issue was that in replication monitoring I was able to generate warnings for latency, but they weren’t writing to the application log to kick the alert. As a work around I used a sql alert based on Perfmon sql counters, that looked for latency over 60 secs. The counters I used were:
sql server:Replication Dist/Dist Latency
sql server:Replication Logreader/Logreader:Delivery Latency

I know this is a hack but it brought me down an untraveled path in the vast landscape of sql server which is leveraging the alerts. My only issue now is that they need to include all the Perfmon counters as it will save me a bundle in sitescope licenses. I know this can be done using the system monitor alerts, but too much maintenance and email infrastructure to work.

Formulating the Costs of Index Views

Ken Kaufman — Fri, 07 Dec 2007 04:29:00 GMT

Using an index view or materialized view (Oracle) allows for the denormalization of data, by storing joins, aggregates, and other runtime queries in permanent storage. The benefit of this is that rather then performing these operations on demand, they’re already stored in a single flat data structure for retrieval. The result is much faster return times. There are plenty of articles out there for example http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx on the benefits of indexed views. This blog will talk to determining if this approach is right for you by explaining how to perform quantitative and qualitative analysis of the costs.

First off there’s no free lunch, it takes work to build this flat structure, as well as work to maintain it. Every time a DML statement is run against an underlying table, data is pushed to the indexed view. This process not only involves the IO of the write but CPU costs of any joins or computations. There’s no better way to examine these costs then using the “showplan_all” command. The following example uses the Northwind database, consisting of a two table join with an aggregation. The purpose is to display the last three orders and the associated costs totals. Lets start with the original query:

select top 3
o.orderDate, sum(od.UnitPrice * od.Quantity) as OrderCost
from Orders o inner Join [Order Details] OD on
o.OrderID = Od.OrderID
where customerid = 'vinet'
group by orderdate
order by orderdate desc

Now lets get the cost of this by running a showplan_all.

set showplan_all on
go
select top 3
o.orderDate, sum(od.UnitPrice * od.Quantity) as OrderCost
from Orders o
inner Join
[Order Details] OD on
o.OrderID = Od.OrderID
where customerid = 'vinet'
group by orderdate
order by orderdate desc

Explaining this command (showplan_all) is out of scope, so I’ll stay focused on the primary result field we’re concerned with “TotalSubtreeCost”. Every statement is made up of different operators to perform it’s task. The total subtreeCost is the estimation SQL Server makes on how expensive the operator will be. This number is derived as a time value in seconds each operation is estimated to take. An important note here is that it doesn’t relate to the time on your machine, but time run on standard equipment at Microsoft. It’s simply an unchanging baseline to work against. Scroll through the result sets to find this field. In the following output the sum of all the subtree’s is a cost of 0.29155515 seconds.

TotalSubtreeCost
0.04338021
0.04338021
0.04337971
0.04336942
0.04336812
0.02707165
0.01569226
0.0032875
0.01238386
0.01624221
0.29155515 - Total

With a normalized baseline in place the following statement applies an index view. Notice no predicate is given, since this will normally be applied as a parameter.

Create view order_summary_view
with schemabinding
as
select
count_big(*) as Cnt, o.customerid, o.orderDate, sum(od.UnitPrice * od.Quantity) as OrderCost
from dbo.Orders o
inner Join
dbo.[Order Details] OD on
o.OrderID = Od.OrderID
group by orderdate, customerid
go
create unique clustered index order_summary_view_index
on order_summary_view(customerid asc, orderDate desc)

Now execute a query to return the same results as the previous select statement, using the showplan_all.

select top 3 OrderDate, OrderCost from order_summary_view with (noexpand)

where customerid = 'vinet'

You’ll notice only a few operations are used with a total on the “TotalSubtreeCost” results, of 0.009864. Compared to the normalized result it is only 3% of the cost. It’s easy to see the advantages of an indexed view for retrieving data. However the point of this blog is to determine the overall cost, not just on retrieving.

We need to determine the costs of data modifications so let’s first use the index view by examining the cost of an insert into an underlying table.

set showplan_all on
go
insert into [order details]
(orderid, productid, unitprice, quantity, discount)
values(10248, 17, 19.24, 10, 0)

The plan looks much more inline with the first select statement, and so do the total cost results coming in at 0.746337 which is over double the cost of the original select statement. Now I’ll drop the index view and rerun the above query.

set showplan_all off
go
Drop view order_summary_view
go
set showplan_all on
go
insert into [order details]
(orderid, productid, unitprice, quantity, discount)
values(10248, 18, 19.24, 10, 0)
select top 1 * from [order details]

The cost of inserting without an indexed view is 0.339465 more then 50% less then an insert with a clustered index. With these numbers in hand we can now make a quantitative analysis of the cost of having an indexed view. The way to do this is to take a sampling of your current activity and gather how many calls are made to retrieve the data and how many calls are made for data modification. Once armed with this information use the following formula on each set, then compare the results:

(Select_Calls * Select_totalcost) + (DML_Calls * DML_totalcost) = TrueCost

In our case lets assume in a hour period we see 1000 select calls and 100 DML calls the comparison formula should look like so

No Indexed View
(1000 * .29155515) + (100 * 0.339465) = 325.50165

Indexed View
(1000 * 0.009864) + (100 * 0.746337) = 84.4977

In this situation the quantitative analysis shows better overall performance by using an indexed view. This exercise was fairly vanilla, one insert statement and one select statement. Unfortunately the world doesn’t work this way, often you have several dml statements that will affect the underlining tables and several queries that will benefit from an indexed view. In this case the formula would just incorporate all the calls, by adding on. For example:

(Select_Calls1 * Select_totalcost) + (Select_Calls’n’ * Select_totalcost) + (DML1_Calls * DML_totalcost) + (DML1_Calls’n’ * DML_totalcost) = TrueCost

Before moving on to the qualitative analysis I must call out few items to be fair. The above is for demonstration only. For example the first query without an indexed view would benefit significantly from a covered index, and a 10 to 1 ratio might not be realistic. This last point is an excellent segway into qualitative analysis.

Quantifying costs is a great measure but should not be your final determinant on which direction to go. In the end run it’s all about user experience. If you can slash 100 ms on a select statement with an index view, but means timeouts when a user inserts data, it provides a poor user experience. Along those lines submitting data to a web interface is expected to take longer, users have just been trained that way. So penalizing your writes for read speed might be acceptable.

When deciding on which direction to go, you need to run in a stress environment, and analyze the numbers for a balance. In addition the previous example only analyzes a single query and not the entire environment. You need to look at what the user experience is with 100 clients hitting the server. With an indexed view your writing to two objects, and doubling your locking on both objects (Writes are in a transaction). So you need to answer, how this effects the other 99 users?

Parameter Sniffing

Ken Kaufman — Wed, 28 Nov 2007 14:42:00 GMT

Parameter sniffing is the default strategy used by SQL Server for caching query plans. The concept is that when a paramatized query is compiled for the first time, it uses the input parameters to build an optimal plan. This plan is then cached. Reuse of the query will always be based on the cached plan whether it is optimal for incoming parameters or not. This explanation is a little convoluted and the best way to explain is through example.

In the following example I’ve created a subset table based off of the “person.address” table in the AdventureWorks database. The primary field to be concerned with here is the “City” field where there has been a non-clustered index created. The subsetted data has a breakdown of the following city information,

cityCount   city
----------- ------------------------------
1           Crossville
1           Denby
1           Escondido
213         Beaverton
214         Concord
215         Burien
398         Paris
434         London

To start out I’ve created the following SP to query the data by providing a parameter on the city

CREATE procedure [dbo].[snifftest]
@city varchar(100)
as
select * from testaddress where city = @city

Once created we’ll look at the following:
1. What the plan looks like for London (29% of rows)
2. What the plan looks like for Denby (Less then 1% of rows)
3. How many reads for London
4. How many reads for Denby

set showplan_text off
go
dbcc freeproccache
go
set showplan_text on
go
exec snifftest 'London'
go
exec snifftest 'Denby'

|--Clustered Index Scan

The above returned a cluster index scan for both queries. This is because London was the first parameter in, and it is more efficient to use a scan with such a high density. Now view the IO’s by running the following queries.

set showplan_text off
go
set statistics IO on
go
exec snifftest 'London'
go
exec snifftest 'Denby'

Each query uses the same method which is a scan and therefore has the same IO’s which is 24 in this case.

set showplan_text off
go
set statistics IO on
go
exec snifftest 'London'
go
exec snifftest 'Denby'

Scan count 1, logical reads 24

Now reverse the order, have Denby go first. The results show an index seek.

set showplan_text off
go
dbcc freeproccache
go
set showplan_text on
go
exec snifftest 'Denby'
go
exec snifftest 'London'

|--Index Seek

The next query is where the rubber meets the road, when we execute this query to look at the IO’s for each value.

set showplan_text off
go
set statistics IO on
go
exec snifftest 'London'
go
exec snifftest 'Denby'

Scan count 1, logical reads 871

Scan count 1, logical reads 4

For Denby it’s 4 reads, but for London it’s 871. This is a huge difference. With London making up such a large percentage of the data it only follows that the amount of calls to retrieve this data for London should be inline. What this means is 29% of your calls will do 857 more reads then needed. If London or another value with a high density is executed first parameter sniffing is optimal here. Even though calls to a lower densitity value, loose a small amount of performance, it more then makes up for the overall system performance.

So now the question is why does SQL by default use parameter sniffing. This is because of query compilation. There’s a cost involved in compiling the correct plan for a query. Rather then compiling on every query, SQL cheats by caching the plans. When the call for a SP comes in it simply reuses the plan already in memory, forgoes the cost of compilation. As with any cheat there’s always a risk in that on the first compilation an edge case value could come in, and force the wrong plan. In the case above this would be a value requiring an index seek rather then a scan. In a future blog I’ll talk about troubleshooting and avoiding these scenerios.

Renaming Tables in a Transaction

Ken Kaufman — Fri, 16 Nov 2007 05:25:00 GMT

A common method of ETL is to bulk load tables from a staging area into your production environment, Of course the issue here is that if your production environment is 24X7, it means taking the table offline, which can cause significant issues for your users. One commonly used work around is to load this data into a holding table and rename. The rename is instantaneous, and usually has no to little effect on your users. To do so you would often issue a command similar to this.

execute sp_rename 'table1', 'table1_old'

execute sp_rename 'table1_new', 'table1'

The issue arrises with the above statement is what happens if the first command succeeds and the second command fails. You’ve now orphanned your users with no table to hit. The work around is to put this into a transaction. Pre 2005 transactions meant using the @@error return. For example after each statement you would need something like this:

Begin Tran rename

execute sp_rename 'table1', 'table1_old'

if @@error > 0

begin

goto Errorhandler

end

execute sp_rename 'table1_new', 'table1'

if @@error > 0

begin

goto Errorhandler

end

commit tran rename

errorhandler:

Rollback tran rename

I’m not a big fan of complexity or typing, so the easier way around is to use the new try/catch feature of sql server. Put all your transactions in the try, and rollback in the catch

Begin Tran rename

begin try

execute sp_rename 'table1', 'table1_old'

execute sp_rename 'table1_new', 'table1'

Commit Tran rename

end try

Begin Catch

rollback tran rename

end catch