Something I have a hard time by-passing is a good R2 unit.  I have R2 units in so many different forms, including a standing R2 cake one year for my birthday.  So when I cam across this R2 unit, I just had to share it.

That is a pumpkin carved into the resemblance of R2-D2.  I think it is a mighty fine job too.  It’s amazing how many good Star Wars related pumpkin carvings there are out there.  You probably wouldn’t have too difficult a time finding three or four hundred if you tried a little google-fu.

Each year I try to have something for the Halloween Holiday such as this one or this one.  I failed to provide something in 2012, and this is getting back on the right track.

Despite the ease to find haunting Halloween effects related to SQL Server, I am amazed at how few have even heard of “Halloween Protection” which stems from the “Halloween Problem.”

I am not going to dive into the problem or the protection of it.  I think that has been covered plenty and even quite masterfully by Paul White (blog | twitter).  I recommend that you read his four part series on the topic starting here.

With all of the COSPLAY going about here in the States, I find some of the scarier things to be about either stuff I have previously fixed or about which I have written or tweeted or all of the above.

Take for instance this article about the blatant disregard by some vendors and clients in regards to security.  I still can’t figure out why the public role would ever need to be dbo for a database – at least not a legitimate reason.

Or we can take on the recent time I tweeted about a cursor that I fixed.  I took that scary cursor down from a 30+ hour run time to a mere 50 seconds.  Here is a segment of the execution plan (plan is roughly 4mb in size to give a little scale) zoomed out to 1/5th.

The query was much uglier than that originally.  Imagine that beast looping through on your server for 30 hrs, and that is not even the entire thing.  It is little wonder why things started to drag on the server.

Another scary item I like is the effect of implicit conversions.  That is a topic that can be viewed pretty easily through the use of google-fu.  Here is a short demo on the implications of implicit conversions.

Code block

CREATE TABLE #T1 (SomeInt INT, SomeReal REAL)
CREATE CLUSTERED INDEX T1_SomeReal ON #T1(SomeReal)
 
CREATE TABLE #T2 (SomeInt INT, SomeReal REAL)
CREATE CLUSTERED INDEX T2_SomeInt ON #T2(SomeInt)
 
CREATE TABLE #T3 (SomeInt INT, SomeReal NVARCHAR(MAX))
--name mismatch and datatype are intentional
CREATE CLUSTERED INDEX T3_SomeInt ON #T3(SomeInt)
GO
 
SET NOCOUNT ON
DECLARE @I INT
SET @I = 0
WHILE @I < 10000
  BEGIN
    INSERT #T1 VALUES (@I, @I)
    INSERT #T2 VALUES (@I, @I)
    INSERT #T3 VALUES (@I, @I)
    SET @I = @I + 1
  END
 
SET STATISTICS TIME ON
 
/* Query 1 
No implicit Conversion */
SELECT COUNT(*)
FROM #T1 T1 INNER LOOP JOIN #T2 T2 ON T1.SomeInt = T2.SomeInt
OPTION(MAXDOP 1);
GO
 
/* Query 2 
Pretty bad implicit Conversion */
SELECT COUNT(*)
FROM #T1 T1 INNER LOOP JOIN #T3 T3 ON T1.SomeInt = T3.SomeReal
OPTION(MAXDOP 1);
GO
SET STATISTICS TIME OFF

In this demo I have created three temp tables.  Each is pretty simple in nature and each is to receive 10,000 records.  The insert statement just inserts an integer into each field of each table through the while loop.  Notice that I intentionally named a column in #T3 to be SomeReal but the datatype is an NVARCHAR.  This is to underscore a pet peeve of mine that I have seen over and over again – naming the field in the table after the datatype and the datatype doesn’t even match.

When this query runs, I get the following timing results.

The thing that stands out to me is the huge difference in time between the implicit-free query and the query replete with an implicit conversion.  The implicit conversion query

was about 930 times slower than the query free of implicit conversions.  Granted that query was against a cold cache, so let’s see what happens to an average of five runs each against a warm cache.

With a warm cache I see an average of 51ms for the implicit free query.  On the other hand, the implicit conversion query runs at an average of 84525ms.  That equates to about 1644 times slower.

Sure this was a contrived example.  But keep in mind the table sizes, the datatypes and the number of records in each table.  Had this been a more true to life example with larger tables and millions of records, we could be seeing a query that is far more devastating due to the implicit conversions.  Let’s just call it the grim reaper of your database. (Grim Reaper from www.mysticalpassage.com/grim_reaper.html)

With these horrifying things to haunt your database, I leave you with this new hope as you battle the dark side and grim that is in your database.