DaveWentzel.com            All Things Data

September 2010

TSQL Autonomous Transactions or Another Reason I Like PLSQL over TSQL

Since I have both an MCDBA and OCP I often get asked which RDBMS I like better, Oracle or SQL Server?  The stock answer is, "it depends" (what a cop-out).  But for sure, PL/SQL is light years ahead of TSQL.  Some of my complaints with TSQL:

  • terrible error handling.  Yes I know all about TRY/CATCH.  I also know that there are still plenty of error conditions that occur before TRY/CATCH is invoked.
  • When assigning potentially multiple values to a variable, no error is thrown.  Consider this example:

      DECLARE @variable varchar(200)

      SELECT @variable = Name
                  (1,'Dave Wentzel'),
                  (1,'Angie Wentzel')
            ) AS tbl (ID,Name)
      WHERE ID = 1     
      SELECT @variable

If you've never seen the goofy VALUES syntax it is Row Constructors syntax.  Don't get caught up in the weeds, basically the variable will always be set to the last value returned.  For this example it will likely be Angie Wentzel every time, but can't be guaranteed and should generate an error.  Oracle does this via the error directive TOO_MANY_ROWS (or OTHERS at a minimum).  We should not be allowed to assign a non-scalar query result to a scalar variable

  • No autonomous transactions.  You can also consider this inconsistent temporary object behavior. 

Oracle has these things called PRAGMAs which is a fancy term for a "compiler directive".  In Oracle these are often misused and misunderstood.  There's really only one good use for them...logging error messages.  Assume a long running, multi-step batch process with a single transaction where I wish to log status messages as I go along.  Now assume near the end of the process it fails and I need to ROLLBACK.  I can of course catch the error and write out informational messages, but I cannot log anything to a table, especially those intermediate steps, since the transaction that rolls back will also ROLLBACK the logging.  Darn.  In Oracle we can write our logging routine using PRAGMA AUTONOMOUS_TRANSACTION which essentially means that routine does not participate in any existing transaction.  Problem solved.

For the longest time we didn't have this...until the introduction of table variables.  This "feature" is not well documented.  You can in fact do this:

      DECLARE @variable varchar(200)

      SELECT @variable = Name
                  (1,'Dave Wentzel'),
                  (1,'Angie Wentzel')
            ) AS tbl (ID,Name)
      WHERE ID = 1     
      SELECT @variable

Well, that's pretty cool and solves our logging problem, but if you didn't fully understand that behavior and continued to reuse a table variable after a ROLLBACK you will get unexpected results.

it also violates ACID properties of a transaction.  Some think a table variable isn't really a part of the db, it is a temporary object, but then why is the behavior different for temp tables in tempdb?  TSQL needs more consistency here.  

Rebuild Indexes

I'm generally not an advocate of rebuilding indexes, I discuss why here.  Basically a REORG will make your pages logically ordered which is all that is really required to make a SCAN operation perform optimally (for a SEEK fragmentation really shouldn't matter if you think about it).  However, if you really want your pages to be contiguous then you have to go for a REBUILD. 

I just ran into a situation recently where a REBUILD really helped.  We have a utility SQL Server with a bunch of dbs for smaller applications.  There was no thought as to how the box should be setup and performance was not really a concern at the time.  Then it started performing poorly with avg sec/disk read sometimes going over 500ms with a few cases of 15 sec IO stalls.  So I took a look.  Every db file was set to autogrow in 1MB increments.  And someone had a build process writing and deleting many files twice a day (causing NTFS fragmentation).  And no log backups were being done and the log files were huge, but the vlogs were tiny.  And the largest db wasn't designed correctly and had terrible page densities due to bad FILLFACTORs.  After I methodically fixed all of these issues I noted that extent fragmentation was still very high on the poorest performing db (also the largest).  The cause was likely a culmination of the causes listed above, plus some other design issues that have not yet been determined.  A REBUILD can often solve extent fragmentation, at least temporarily (if there is a data design problem it will slowly creep back in due to page splits). 

So I wrote a little script that handles the REBUILD.  You can find lots of these scripts on the net but I thought I'd post mine since it's special to the situation at hand.  I also annotated it to show some nifty tricks with TSQL/SQL Server. 

Here we must use sp_MSForeachdb since we want to cycle through all user databases on the instance.  

Here we check the Edition to determine if we can do an ONLINE rebuild.  

We need to ensure that we are not running an ONLINE rebuild if the table has a LOB since that is also disallowed.  

The [?] syntax is a replacement parameter for sp_MSforeachdb.  

We can't run a REBUILD on a heap for obvious reasons.

We also want to ensure we do our REBUILDs in an orderly fashion, do the clustereds first since this will affect the non-clustered indexes.
USE master
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RebuildIndexes_AllDbs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].sp_RebuildIndexes_AllDbs
--EXEC sp_RebuildIndexes_AllDbs
CREATE PROCEDURE sp_RebuildIndexes_AllDbs
performs an ALTER INDEX REBUILD for all tables/indexes in all dbs.  This is not something I would
put into production normally, but it is needed due to TFS/SharePoint having tons of dbs for their apps
and the fragmentation being incredibly high such that REORG is not working.  This also may be due to the
fact that our data files were set to autogrowth of 1MB for quite awhile (not my fault) and the drive
the mdfs live on is *highly* fragmented.  We see lots of I/O Stalls and this should help us with that. 
Attempts using WITH ONLINE = ON when it can (Enterprise Edition and tables without LOBs)
EXEC sp_MSforeachdb 'declare @db varchar(100) select @db = ''[?]''
IF (ltrim(rtrim(@db)) not in (''[master]'',''[model]'',''[msdb]'', ''[tempdb]''))
IF databasepropertyex(''?'',''IsInStandby'') = 0  AND databasepropertyex(''?'',''Status'') = ''ONLINE'' AND databasepropertyex(''?'',''Updateability'') = ''READ_WRITE''
      DECLARE @Edition varchar(200) , @exec_str varchar(4000)
      SELECT @Edition = convert(varchar(200),serverproperty(''Edition''))  --Enterprise Edition
            select ''alter index '' + quotename(i.name) + '' ON [?].'' + quotename(s.name) +
                  ''.'' + quotename(o.name) + '' rebuild'' +
                  CASE WHEN ISNULL(TablesWithLobs.object_id,0) = 0 AND @Edition = ''Enterprise Edition''
                        THEN '' with (online = on)''
                        ELSE '' ''
            from [?].sys.indexes i
            join [?].sys.objects o
                  on i.object_id = o.object_id
            join [?].sys.schemas s
                  on o.schema_id = s.schema_id
            LEFT JOIN
                        select distinct c.object_id, object_name(object_id) as name
                        from [?].sys.types t
                        join [?].sys.columns c
                              on t.System_type_id = c.system_type_id
                        where (t.name IN (''image'',''text'',''ntext'')
                              c.max_length = - 1
                  )     TablesWithLobs
                  ON o.object_id = TablesWithLobs.object_id
            where o.type = ''U''
            and i.index_id <> 0 --no heaps
            order by i.object_id,i.index_id --gets the clustereds, then non-clustereds, then other
      OPEN Dave
      FETCH NEXT FROM Dave INTO @exec_str
      WHILE (@@FETCH_STATUS = 0)
            PRINT ''Running: '' + @exec_str
            BEGIN TRY
                  EXEC (@exec_str)
            END TRY
            BEGIN CATCH
                  PRINT ERROR_MESSAGE()
            END CATCH
            FETCH NEXT FROM Dave INTO @exec_str
      CLOSE Dave
END;  --procedure

Rebuild Indexes Part 2


How to Paste Formatted Code into Drupal and CKEditor

One of the (very few) things that bugs me with Drupal is pasting computer code into CKEditor while still maintaining your syntax highlighting.  This isn't unique to Drupal/CKEditor, it seems everyone complains about copy/paste of source code into their CMS/blogging tool.  Some people use a code formatter which attaches a stylesheet inline for syntax highlighting.  The problem is the end result likely will not look like the original if the tool doesn't have the same highlighting rules as your IDE. 

Here's how I do it and this works with TOAD, SQL Server Management Studio, VS2010, and Eclipse:


Programmatically Determining if Antivirus Is Running

Some corporate AV management tools (EPO for example) don't report when certain other vendors' AV software is installed/running.  Who cares?  Well, many software packages just won't install depending on your AV settings.  For instance, the SQL Server FILESTREAM functionality will actually hang the machine if certain AV products are installed.  Here is the connect bug I filed on the issue.  Where I work we actually install SQL Server as part of our product's installation routine so we can't guarantee what AV product the customer has installed.  You might think it would be simple to determine if AV is installed/running (a PowerShell script, a reg key, WMI query, etc)...well, you'd be wrong.  Here is what I learned and a little script that pieces it all together. 

ADSI programming...removing extra ProxyAddresses

I haven't done an ADSI programming in a very long time.  But I was asked today to write a script that would identify and delete all of the instances of extraneous smtp proxy addresses.  Formerly we used Lotus Notes and somewhere during the migration we had a bunch of garbage notes aliases added to everyone's Exchange proxy smtp addresses.  So, here's a simple vbscript that will identify and remove them.  Yes I know it could've been written as a PowerShell script, but when you are under the gun you use what is most familiar.  


Auxiliary Table of Numbers and spt_values

If you've worked with the SQL language long enough you've encountered many uses for an auxiliary table of numbers.  Some people call this a "tally" table or a "Numbers" table.  I call my table "Nums" and this is one of the first tables I create in any database I work with.  Here is the script I use to create my Nums table.  It generates a one column table (the Number col) where every row is a single monotonically increasing number from 1 to 10000.  I make the assumption here that I will never need numbers higher than 10000 which may not be valid for everyone's needs.  There is a clustered index and the FILLFACTOR should be 100.

What is the purpose of an auxiliary table of numbers?

  1. Parsing a string
  2. finding gaps in your identities
  3. generating date ranges
  4. Custom sorting and ordering
  5. tons more uses

Really, once you understand why a table of numbers is important you start to find interesting uses for it almost daily in your querying.

There is actually some disagreement regarding whether a "physical" numbers table is needed or whether we can create one on the fly in our code.  Although I understand that it may be more elegant to code a dynamic creation of your numbers table in your procedures, it does lead to possibly bloated execution plans, duplicated code everywhere, copy/paste errors, etc.  Some people even argue how to create a Nums table in the most efficient manner.  Do we use CTE's, CROSS JOINs, WHILE loops, GO statement with a batch argument, something else?  (My script uses a combination of a CTE and CROSS JOIN).  This is all frankly academic since I physically materialize my Nums table so I run the code once and forget about it.

An alternative:  spt_values

It's possible there is a compromise if you don't want your own Nums table.  Use master..spt_values.  spt_values is a table that SQL Server uses to map constants to integers.  It is used heavily if you look at the text (sp_helptext) of many MS-supplied procedures in master, for instance, sp_configure.

Running this command

select * from master..spt_values

order by numbers

…produces output similar to this:



…which indicates the number col is not unique.  Instead, spt_values is really a table of constant mappings for named-value pairs.  So to get a table of numbers the query we should use is really this:

select * from master..spt_values

where type = 'P'

order by number

…which generates output like this


…which is exactly what we want.  A few caveats…as of SQL Server 2008 the highest number in spt_values is 2047, if you need more numbers, you need a different solution.  Also, it may not be wise to use spt_values since it is undocumented and may change in future releases of the product.  I still think a physically materialized Nums table is preferable, but for those who *really* have an aversion to doing this, then spt_values may be an acceptable solution.

Displaying .sql files in Drupal

The more I use Drupal and WAMP the more I like it.  One thing that was frustrating me was that any .sql file saved as an attachment generated a 403 forbidden out of Apache.  I like to blog about interesting data architecture topics so I tend to publish a lot of .sql files.  I couldn't find anything on google regarding a fix for this.  Here's what I found:

  • the .htaccess file in your drupal root will have this entry which basically is restricting .sql files from being served.


<FilesMatch "\.(engine|inc|info|install|make|module|profile|test|po|sh|.*sql|theme|tpl(\.php)?|xtmpl|svn-base)$|^(code-style\.pl|Entries.*|Repository|Root|Tag|Template|all-wcprops|entries|format)$">
  Order allow,deny
  • we want to override this for .sql files, but NOT for drupal root, only for /sites/default/files (where the attachments live.  
  • simply add this entry to .htaccess in /sites/default files


<FilesMatch "\.(sql)$">
  Allow from all

That was easy.  


Everything you need to know about GUIDs

I thought it was time to repost some information on GUIDs since I'm seeing them being proposed more and more for solutions that don't require them, such as ORMs.  

What's a GUID?

It's short for Globally Unique Identifier, which is Microsoft's implementation of the UUID (Universally Unique Identifier).  The UUID concept is an ISO standard that enables distributed systems to identify a piece of generated information uniquely without coordination from a central entity or piece of software.  This means that I can generate  a UUID using SQL Server on my machine at the same time as you generate a UUID using Sybase and we can rest assured that our identifiers will not clash if we ever merged the data elements together.  

A UUID (from here on out referred to as a GUID to ease confusion), is a 16byte number (or 36 characters when viewed more commonly as a string) that does have some degree of logic built into the number...it's not just monotonically increasing.  In fact, some vendors have allocated certain values to mean certain things which is totally acceptable and akin to the fact that certain IP address ranges are set aside for specific purposes, 127.x.x.x and 192.168.x.x for example.  

Here is a GUID I just generated on my machine using SQL Server and SELECT NEWID().


Notice it is split into 5 sections.  

GUID "versions"

There are actually different "versions" of GUIDs.  And just by looking at a generated GUID you can tell it's version by looking at the first number of the 3rd section, in our case above it is a 4, meaning it is a Version4 GUID.  In MS-land you'll usually only see Version 1 and 4 GUIDs so I'll focus solely on those two.  

A Version 1 GUID guarantees uniqueness by, and I'm oversimplifying here, concatenating your machine's network card's MAC address with a current timestamp.  This method was criticized since it actually reveals the provenance of the data "too much".  As a side note, if you remember the Melissa virus, they actually caught the writer because his code had an embedded Version 1 GUID in it, which means they could link it to his machine.  

Version 4 GUIDs are generated in the latest releases of SQL Server.  Version 4 GUIDs use only random numbers to guarantee uniqueness.  Many people think that there is a timestamp component to GUIDs based on the fact that the generated numbers tend to look "similar" to each other.  They look similar because of the Version and reserved use bits built into the GUID, not because of a timestamp component.  

Are GUIDs really unique?

This depends on your definition of unique.  A Version 1 GUID is actually fairly easy to demonstrate duplicates.  Assume you build two VMs.  On many virtualization platforms, such as MS Virtual Server, you can monkey around with the MAC address of the guest.  It is not inconceivable that you could build 2 guests with the same MACs, synch their times, install a Version 1 GUID generator on both, and start generating GUIDs.  You'll eventually get a clash.  

As for Version 4, well, I guess for all intents and purposes a GUID is unique, but in theory, you could get a clash since no random number generator is really and truly random.  

When *MUST* I use a GUID?

  1. If you plan on using SQL Server FILESTREAM storage then you must have a GUID col with the ROWGUIDCOL property set.  
  2. Merge Replication has the exact same requirement.  

Where else are they commonly used?

Many data architects insist on using them when designing distributed systems.  Rarely is this necessary however.  Using an Oracle sequence is a better choice where a separate SEQUENCE, using a different seed perhaps, can be assigned to each system in the distributed design.  We don't have SEQUENCEs in SQL Server (yet, hopefully) so we can create the equivalent using a "KeyTable" which is simply a table with at least 2 cols, one called TableName, and one called NextID.  A distributed system makes a call to KeyTable and says, as an example, "Give me 1,000 IDs for me to use locally" and KeyTable responds with the IDs and then reserves those entries by incrementing NextID.  The distributed system then caches and uses those 1,000 IDs until more are required and the process repeats.  Many distributed systems can also utilize the concept of a "smart key" which is an IDENTITY with a SystemID appended to it.  This is useful when a KeyTable cannot be used because there is no central system for *any* coordination.  This is very rare though.  There are many other schemes that you can use as well, based on your requirements.

Another use is by ORM Tools.  They like GUIDs because they can generate parent/child table row values entirely locally without the db specifying the ParentTableID first, for use on the children rows.  In other words, I can batch all of my INSERTs into one db call roundtrip.  An ORM can't do this with IDENTITIES.  Again, a KeyTable is a much better solution.  As a side note, although the roundtrip avoidance in an ORM is nice, I've never seen an ORM where they actually do batch the calls.  

When are GUIDs good and bad when used as a key? 
Pros Cons
Easy to create.  There are tons of GUID generators out there. HUGE.  16 bytes which is 2^128 unique values.  Compare this to a 4 byte INT.  
No intelligence built into the key (we shouldn't really ever do this anyway, this ensures we can't). Performance can be poor (see below for specifics)
  Due to their length they are not easy for users to remember (like an SSN).  You'll probably still want a natural key.  
Special SQL Server Concerns
Be careful.  GUIDs can cause fragmentation in your indexes/tables.  Obviously a clustered index based on a GUID will relieve hotspots (since they are random) but you will need to think carefully about your FILLFACTOR, or face terrible fragmentation problems.  A good rule of thumb is to NEVER base your clustered indexes off of a GUID col.  Here's another reason...if non-clustered non-unique indexes are built off a clustered index based on a GUID, even if the non-clustered index does not contain the GUID, the non-clustered, non-unique index will fragment.  Why?  There *must* be something that makes each non-clustered index key unique internally to SQL Server.  Even non-unique indexes have this requirement.  How does SQL Server find this uniqueness in this scenario?  By appending the GUID from the clustered index to the new index, hence even more fragmentation.  
Many proponents of GUIDs insist that the NEWSEQUENTIALID() function in recent SQL Server releases eliminates the fragmentation issues with GUIDs and NEWID().  I disagree.  This function creates a GUID that is greater than any previously created GUID, but only since your last reboot.  The proponents fail to remember that last part.  So, yes, fragmentation is a relieved a little bit, but less so if you reboot often (but Windows never requires that thankfully).  One additional wrinkle...you can't call this directly like NEWID()...it can only be used as a DEFAULT constraint on your table, which will limit its effectiveness in ORM applications.  

SQL Backups using 3rd Party Tools

Occasionally I'll see a post to a newsgroup similar to this, "I just started a new job as a DBA and they use <NetBackup/BackupExec/LiteSpeed/some other 3rd party backup tool> to back up the mdf/ldf files.  I want to do a standard SQL BACKUP but they don't want me to do this for some reason.  Should I worry?"  The answers then range from "find another job" to "this is totally safe these days."  Some facts:

  1. You can't make blanket statements about any of these tools without understanding the underlying technologies employed.  For instance, a valid, consistent, mdf/ldf backup *might* be possible using NetBackup *if* you use the OFA.  It would probably be better to use the SQL Agent though.  
  2. Your exact requirements need to be taken into consideration whenever you deploy a tool.  
  3. When using *any* 3rd party tool you should test thoroughly to ensure you can recover to meet the requirements. 

My Experiences, or Things They Never Think About 

  1. Some people swear by LiteSpeed and I'm sure it works just fine, but be aware that you will need to have LiteSpeed installed on any other servers you wish to restore a LiteSpeed backup to.  This also complicates bare-metal restores since I need to install another package before getting my data back.  
  2. With VSS (Volume Snapshot Service or Shadow Copy for short) you can avoid file lock issues and consistency problems.  Many vendors have implemented this as their method to handle open file backups.  I use VSS with virtual machines and love it, but I could never get it to work with XP correctly, although apparently this was changed in SP2.  Always test.  
  3. When someone proposes using NetBackup/BackupExec they usually do this as part of a "direct-to-tape" backup strategy.  Common sense dictates the tape will be sent offsite immediately.  So what happens when we need to do a quick restore from yesterday to see some accidentally deleted data?  That requires waiting for the backup tape to return from offsite.  Would seem like a nice file-based .bak file would avoid this problem.  
  4. Restoring a backup from ServerA to ServerB might be difficult.  Do you have a tape drive in ServerB?  Even if you use a backup media library, can you restore quickly to a different server?  How does it handle sync'ing logins/users?  
  5. How are transaction log restores handled?  Make sure you know.  Many 3rd party tools want to put your db in simple recovery mode or make you click through advanced settings to find the log backup options.  Most network admins won't know how (or even when) to do this.  And we all know what happens when a db is in FULL recovery mode but log backups are not taken regularly.  
  6. How are new databases backed up?  BackupExec requires you to manually select which dbs should be backed up.  But your server may have many databases added/dropped regularly.  Are they being backed up?  
  7. Have you ever popped a tape into your tape drive and found it was blank/corrupt/was a tape for some other server?  I've had this happen many times.  Someone didn't catalog the tape properly.  This is why I don't like using tape as my primary backup media.  
  8. When the going gets tough, are you sure you can rely on your 3rd party solution vendor? 

My Best Practices

First, thankfully I'm not responsible for recoverability and haven't been for a long time.  But if you are, tread lightly through the new technologies.  They are enticing, but be aware that you need to understand them and test them thoroughly.  For my money I would rather not add another layer or two on top of Microsoft's SQL backup technologies.  When I have had issues with sql backups in the past (for instance, corruption at the page level caused restores to not work) it was nice to be able to go to one vendor (MS) for resolution, rather than experiencing the blame game/pass-the-buck between vendors.  Disk space is cheap and fast.  The simplest solution is also probably the best.  For me that is simple SQL-level backups (full, filegroup, differential, logs, whatever) to disk files.  Those disk files are then hoovered to alternative media/locations using the 3rd party tools which should then have no issue with a standard file (no OFA agents are needed).  The last step is making sure the files are purged occasionally after they are hoovered and verified.  


Upgrading a SQL Server 2005 Cluster to 2008

In the past upgrading a db cluster from one release to the next was a little painful if you expected near zero downtime.  When we migrated from 2000 to 2005 I came up with a method to reduce downtime by creating a second instance (already upgraded), a new set of drives, some settings changes to cluadmin, and then some finagling to synch the data which could be done with a minimum of downtime.  This is kludgey at best and dangerous if you really care about your data.  But it was unavoidable if you have reduced downtime requirements.  Others have come up with different solutions to the problem.  

Luckily the days of doing this are over.  We now have rolling upgrades to SQL Server 2008.  Now the cluster never needs to be taken down at all, although it will require 1+n failovers where n is the number of nodes in your active/passive cluster.  Steps assuming a simple 2 node cluster:

  1. Upgrade the passive node
  2. Failover
  3. Upgrade the new passive node.  
  4. Fail it over again.  

How does it do this?

In previous versions of SQL Server you installed cluster nodes by doing a remote install from the active node.  In 2008 each node is installed separately so the version dependency is gone essentially.  This means that when you install the first node you set the clustering parameters/properties at that time.  In essence this means that after installing that first "node" you now have a cluster where the node simply fails over to itself.  I really like this concept and it makes the rolling upgrades much easier.  Even ongoing patching/service packing will require less downtime.