Gaining access to a query plan, both the estimated and actual, is an incredibly helpful feature for the query tuner, database developer, and DBA.  For the developer it can help them refine their approach to a specific workload and put a scientifically, relationally sound query together to tackle a given problem.  For the DBA it can help identify problems in production after they have occurred by showing actionable areas for focus and refactoring.

What if you could take a query that is running right now and look at the actual query plan (not the estimated plan) as it unfolds.  Sounds kinda cool don’t it.  When I first looked into this “’16 feature” I immediately started day dreaming about watching a query run visually kind of like a visual SSIS package does.  I imagined watching row counts build, watching merges, loops, and joins happen real-time.  I pictured being able to look at reads and memory allocations with a visual plan as it all comes together.

For many of us, seeing something is believing, and reading numbers from a DMV smacks of effort and math.  I absolutely love Machanic’s sp_WhoIsActive, but I mean, come on, visually, you could really gain perspective on bottlenecks, weights, and true latency of areas that would far exceed “gut feel”.  In reality, my vision of LQS isn’t far off – as long as you don’t mind mouse hovering – like always however, there is a catch.

Live query statistics isn’t something that will just be available.  It has to be enabled prior to the query running.

Within SSMS LQS can be turned on just like estimated or actual query plans are now – on a query by query (session) basis.  Behind the scenes if you want this info you have to SET STATISTICS XML ON or SET STATISTICS PROFILE ON for individual sessions.  Or, for the entire instance, by enabling the extended event query_post_execution_showplan .

Per TechNet, this is likely something you’ll want to carefully consider as there is a lot of added overhead in making this insight possible server-wide.  The performance impact of turning this on, on a production server, could be significant.  So, my day dream of being a production DBA and logging onto a server with a current problem and bringing up it’s ongoing query plan probably isn’t a reality in most situations.

Is this still a cool feature ?? For the database developer, query tuner, absolutely.  If you’re lucky enough to have a good dev/test prod like environment, this could definitely be a very cool feature.  It has most of what my initial day dream concocted.  If you have a query that always rolls over and plays dead, then you can also use it there – but for those spur of the moment, what the heck is going on with query “A” type questions, where the dog will never do the same trick twice – it’s probably not going to come to your rescue the way existing tools, like sp_WhoIsActive for example, do.

You can see this feature in action right now by going over to the TechNet Virtual Labs.  This is where I started playing with it.  The TechNet virtual labs, by the way, are super cool.  Thanks for the resource Microsoft!!

BEST PAINT JOB

The award for best paint job goes to Tim Peters whose article on NOLOCK proves that painting flames on a sports car doesn’t make it any faster – but a whole lot of people think it does.  Also a shout-out – per Tim – this was his first TSQL Tue entry.

TSQL Tuesday

BEST CUSTOM ROD

Boris Hristov wins the best custom build award handily with his in depth analysis of both isolation levels and partitioning and performance in a single post.  Boris also gets thumbs up from your host for having the best post in terms of “spirit of the topic”.  Nice!

http://borishristov.com/blog/t-sql-tuesday-64-partitioning-and-the-serializable-isolation-level/

BEST FACTORY STOCK

Aaron Bertrand comes away with the “best factory stock” award.  Whether the production DBA likes it or not – triggers come from the factory as part of the base model.  Aaron pops the hood and does his typically thorough job of demonstrating how business logic across multiple triggers compares to one trigger to rule them all.  Thanks for the entry Aaron.

http://sqlperformance.com/2015/03/t-sql-queries/one-trigger-or-many

BEST EXOTIC

Long time TSQL Tue star Rob Farley comes to the event with his typically excellent post – this time bringing PDW into the ring.  Nothing says exotic like PDW.  I originally thought his would be a funny car entry, but no – PDW is definitely exotic – with the price tag to match.  Thanks for the entry.  I have to admit I’ve seen more Ferrari’s on the road this year than PDW installs and I’m slightly jealous of Rob’s exposure.

http://sqlblog.com/blogs/rob_farley/archive/2015/03/10/tuning-parallel-data-warehouse-queries.aspx

BEST FUNNY CAR

By kicking Rob out of the Funny Car division I had one last spot available for myself to receive a shiny ribbon.  Watching me, a long time DBA attempting to tune SSIS, SSRS, SSAS or SSDT is definitely closer to humor than drama or education.  But, hopefully it was also informative for anyone searching the net for a laundry list of in-memory OLTP errors.

TSQL Tue 64: Tuning SSIS with In-Memory Stage

Images used above under creative commons or fair use (satire)

A few months ago I got a brutal introduction into the world of large scale ETL against legacy format data.  Don’t get me wrong, I’ve done a lot of ETL over the years – but nothing like the project we just finished up.  The final package loads several hundred flat files at a time from a COBOL environment encoded with EBCDIC and comp3 data storing nybbles.  Typical runs process several hundred thousand records at a time; hundreds of millions rows monthly.

A nybble, by the way, is a way to store integers as only four bits instead of the normal eight that make up a byte.  A clever way to save storage space if you’re a 1970’s COBOL developer, after all digits 0-9 don’t need all 8 bits; but it’s a horrible way to make friends with a 2015 SSIS ETL developer working in windows code page 1252.

We recently finished up all the business logic and vetting out the transformations for our warehouse.  The goal now that everything works is to speed it up.  Moving that much data, that often, get’s pretty time consuming.  Enter SQL Server In-Memory OLTP for the staging tables.

Let’s Tune!

I get why Microsoft calls this technology In-Memory OLTP, the vast majority of scenarios it will improve with it’s optimistic-no lock strategy are related to concurrency and other OLTP problems.  ETL however is an excellent use case for this technology; and while OLTP is still technically accurate from a data architecture point of view, people typically relate ETL to the data warehousing side of the fence not OLTP.

In-Memory ETL Lessons Learned

A lot of documents talking about the merits of In-Memory optimizations say something like, “few, if any changes need to be made for existing code to work against memory optimized tables”.  My answer? Not in my experience.

One of the reasons ETL is such a great fit for memory optimized table in the stage layer is these are typically heaps, there are typically varying amounts of business logic and lookups that occur and the schema-only durability can be leveraged as these are short term storage structures anyway.  These all line up really well with the MSDN matrix on what types of tables would benefit most from being memory optimized.

Here goes a play by play of the tuning process I experienced as if you were in my head.

Alright, I’m going to re-create this staging table as “memory optimized”.  I’ll just drop and recreate with the memory_optimized=on and durability=schema_only options.  (pssst, memory optimized file groups are already in place).

Msg 41327, Level 16, State 7
The memory optimized table ‘SampleData’ must have at least one index or a primary key.

Oh yeah, forgot about that requirement.  It WAS a heap but I can figure out something to index it on so it can dropped and re-created as a memory optimized table.  I’ll specify an index on one of the internal reference keys I’ll use later in a lookup.  I re-run like this.

CREATE TABLE [stage].[SampleData](
    [LookupID] [bigint] NULL,
    [LegacyKey] [varchar](50) NOT NULL INDEX idxLegacyKey NONCLUSTERED,
    [SampleData] [varchar](20) NOT NULL
) WITH (memory_optimized=on, durability=schema_only);

Msg 12328, Level 16, State 102
Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.

What the heck is BIN2? I’d never admit I don’t have any idea to my peers – but in my head – that’s what I’m thinking.  After some BING-FU I discover BIN 2 is a binary collation – it makes sense to use binary as an in-memory object. It’s not my default collation though.  Is it anyone’s??

CREATE TABLE [stage].[SampleData](
    [LookupID] [bigint] NULL,
    [LegacyKey] [varchar](50) 
            COLLATE Latin1_General_100_BIN2 NOT NULL INDEX idxLegacyKey NONCLUSTERED,
    [SampleData] [varchar](20) NOT NULL
) with (memory_optimized=on, durability=schema_only);

Hooray – I have a memory optimized table!!!  Let’s do the same to the 30 other staging tables and then try our ETL now.  The table names and structures are the same, let’s just run the ETL – should work no problem.

What happens next

“Invalid object name ‘[stage].[SampleData]’.”

I struggle with it for awhile, check the spelling and existence of the object like 1000 times, finally figure out that the destination simply doesn’t like in-memory tables.  I find the SQL Server destination wired against an OLE connection in the project finally works.  Others might work as well – but for whatever reason the default destination type didn’t.  Ok, run the ETL again.

After wading through endless code page warnings (due to the BIN2 collation) that I fix by selecting “default code page” instead of specifying a specific one in SSIS, I get presented with this error:

Msg 10794, Level 16
The statement ‘TRUNCATE TABLE’ is not supported with memory optimized tables.

One of the most common steps in any ETL process using staging tables is truncating, but In-Memory doesn’t support truncate.  Makes sense considering the architecture of the storage.

So, change all the steps to delete statements instead of truncate and we now see a ton errors telling me this:

Msg 10794, Level 16
The table option ‘tablock’ is not supported with memory optimized tables.

A common performance booster for ETL is to use bulk load and table locks.  Not going to fly here.  Need to remember that locking In-Memory tables is not even remotely like traditional storage objects.  Basically – it’s non existent.  In-Memory is entirely optimistic via row versioning or simply failing conflicts.

So, remove all the table lock requests from SSIS destinations as pictured above and try again.

Cannot resolve the collation conflict
between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_BIN2” in the equal to operation.

Remember that BIN2 violation we got while trying to build the table/index.  We solved it by adding that collation to the column.  Now anywhere I compare that column to another column I’ll have to manually specify what collation to use as well.

In my case I got to replace around 40 separate lookups and other mechanisms using that original column as an equality comparison.  Specifying a collation on a single column now looks like this in my lookup scripts:

UPDATE A SET ID = C.NewID
FROM stage.ATable A INNER JOIN dbo.CTable C ON A.ID = C.NewID
COLLATE Latin1_General_100_BIN2

Mercifully from here on out things started flowing much smoother.  I notice immediate performance gains on the steps that perform heavy operations such as decrypt, encrypt, and transforms.  Not as much performance gain as I would have though in the bulk loads – turns out the bottle neck there was reading from fixed width files – not storing to the heaps.  Overall experience however is noticeably faster, cut ETL times nearly in half – from end to end the stage portions are only about 25% of total work, so 100% improvement is pretty good return on over 1.5 million rows loaded.  Cut run from ~ 20 minutes down to just over 10.

Implementing In-Memory is not for the faint of heart.  It’s a totally different structure with some very specific rules and constraints.  This post has gone long – but in the next one I’ll dig into how we got even better performance with the new Hash Index that In-Memory makes possible.

Image credits:  Creative Commons – http://www.streetsblog.org/2009/12/02/ooh-and-lets-have-traffic-agents-wave-checkered-flags/

By “focused” I mean tackle something head on; the more secret sauce, tricks, code samples, operator costs, or demonstrable metrics against a defined problem the better.  This month is for the gear heads.  Don’t wimp out by avoiding hints, plans, or guides.  General practice is for wusses.  Write something that’ll either go BIG or flame out half-way down the track.  Drag racers aren’t grocery getters.  If your post begins with the phrase… should be implemented with caution – you’re doing it right.  If you’re an SSIS, SSDT, SSRS person – we’ve got a funny car division for you too.  Tell us edge cases where you’ve make your stuff scream.

Need some ideas?

• Tackle a non set based problem you’ve improved in the wild
• Discuss hardware settings or IO – anyone using a flash array?
• Got a solution to a niche wait type?
• Tackle indexing edges (good or bad)
• Columnstore !!!
• Hash buckets !!!
• Parallelism !!!
• Break down and improve a peculiar query plan operator

Remember the rules?  Your post must go live on Tuesday March 10th between 00:01 and 23:59 hours.  Once you’re post has gone up, drop a comment with a link on this page back to your post.  Finally, your post should contain the original TSQL Tuesday logo above and it should link back to here.

Ok, got your launch control settings ready?  I’m looking for engine noise that’ll scare children.  I want code blocks that’ll do a quarter mile in under nine seconds.  I want the irresistible smell of top fuel, rubber, nitrous, and IOPS in the air.

GO!!

Image credits: Super Charger used under Creative Commons – https://www.flickr.com/photos/k-ribou/8367209091