SSIS Talk

For those of you that work extensively with sys.dm_exec_query_stats and associated plan cache DMVs, be aware that altering the server’s ‘max degree of parallelism’ setting will flush the query plan cache.

You may see this message in your server’s SQL Log file:
“SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.”

In the case of MAXDOP changing, if you look before the above event happened, you should see that MAXDOP was changed and hence caused the flush.

This makes sense. If you are changing the MAXDOP setting, all current query plans are worthless as they’re out of date with respect to this parameter. Some plans may now be able to benefit from parallelism, whereas before they could not. Or if constraining MAXDOP, some parallel plans may not be able to work anymore. (Or some combination thereof.) The easiest way to rectify this issue is to simply flush the plan cache and let all plans recompile at their next execution.

A common situation for changing MAXDOP could be for maintenance operations, where during normal operations MAXDOP is constrained to 1, yet while doing weekly work during a maintenance window the DBAs may elect to set MAXDOP to 0 (unrestricted).

So, when you’re scratching your head wondering where your plan cache went, it could be due to administrative tasks.

It is quite easy in SQL Server 2005 to accidentally (or intentionally, I suppose) create data corruption. It’s best to just show via a demo, so here you go:

(Ref: http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx. Thanks, Paul White, for doing all of the work for this post. )

-- In SQL Server 2005 only

-- fails
select round(0.5,0) 

-- 0.5 is cast as a numeric(1,1), which is used as the output type as well.
-- Since 0.5 rounds up to 1.0, this value is larger than a numeric(1,1) can store,
-- which is 0.9

-- works!
select round(0.5,0) as rnd
  into testround

-- view metadata of 'testround'
sp_help testround  

-- Observe that the 'rnd' column is defined as a numeric(1,1),
-- which technically means 0.9 is the max value

-- Now let's view the record:
select *
  from testround

-- WHAT?!  "Arithmetic Overflow"?  But, but, but the insert worked

-- Hmm, does this work? (Yes)
select cast(rnd as numeric(2,1)) as rnd
  from testround

-- Well, let's see what's going on here:

DBCC CHECKTABLE('testround') -- also catchable via DBCC CHECKDB, but not
                             -- with PHYSICAL_ONLY

/*
DBCC results for 'testround'.
Msg 2570, Level 16, State 3, Line 1
Page (1:1811349), slot 0 in object ID 2099048, index ID 0,
partition ID 72057594063028224, alloc unit ID 72057594096123904 (type
"In-row data"). Column "rnd" value is out of range for data type "numeric".
Update column to a legal value.
There are 1 rows in 2 pages for object "testround".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'testround' (object ID 2099048).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
*/

-- Okay, now let's see what's *REALLY* going on
DBCC TRACEON(3604)
GO

declare @db int
declare @filenumber int
declare @pagenumber int
set @db = db_id()
set @filenumber = 1       -- From DBCC output, adjust accordingly: Page (1:1811349)
set @pagenumber = 1811349 -- From DBCC output, adjust accordingly: Page (1:1811349)

DBCC PAGE(@db, @filenumber, @pagenumber, 3); -- 3 == full detail with ascii output 

-- Find the entry for slot 0 (according to the DBCC output above), note the out of range
-- value: "rnd = 1.0"

/*
Slot 0 Offset 0x60 Length 12

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x67EAC060

00000000:   10000900 010a0000 000100fe ††††††††††............             

Slot 0 Column 0 Offset 0x4 Length 5

rnd = 1.0
*/

-- Let's fix it

update testround
   set rnd = 0.9;
GO

-- Run CHECKTABLE again
DBCC CHECKTABLE('testround') -- success!  No issues.

-- Okay, example's over.  Turn off our TRACEON command and drop the table.

-- It is important to note that this does not work in 2008.  Whew.

DBCC TRACEOFF(3604)
GO

drop table testround;
GO

When using a Merge or Merge Join component in a data flow, your incoming data is required to be sorted. While it may be easy to drop a Sort component into your data flow, it may make more sense to perform the sort in your source query (if you are using a source such as an OLE DB Source component).

If you decide to use an ORDER BY in your source and want to tell the SSIS Data Flow that your data is sorted, follow the below steps:

(more…)

Starting with SQL Server 2005 RTM and through the latest SQL Server 2008 build at the time of this writing, there is a pretty nasty bug with respect to the COUNT(expression) function and its rule that when using an expression, it filters out NULLs in its count results.
(more…)

Just a quick tutorial on using a script component as a source… Follow the link for screenshots.

(more…)