Presentation Redundancy

As a presenter, it’s hard enough getting up in front of a group of people and talking about a topic.  We run the risk of demo failure, disengaged audiences, and equipment failure.  Practice and preparation can help with the first and second, but sometimes stuff just breaks.  This week’s SQL Saturday Pittsburgh provides a good example of this.

As I set up for my early-morning session, I set up my laptop just like usual and put in my HDMI to VGA adapter.  The adapter worked…sort of.  It would make a connection but then disconnect within a couple seconds, and then re-connect.  This connect-disconnect cycle obviously wasn’t going to fly, so I needed to do something about it.  I checked my laptop out in another room and found that my VGA connection worked fine there, so I went to the help desk technician.  He and I tried to troubleshoot the setup, but somehow, during the process, things ended up getting worse—now I couldn’t connect at all with my adapter, even with a new VGA cable.  I didn’t have a backup adapter and most speakers are moving to Thunderbolt or Display Port adapters, whereas I’ve got HDMI.  But even when I tried a different, working adapter, I just got back to a flickering screen.  I had to use somebody’s laptop which didn’t have SQL Server installed—just Management Studio—to give my talk.  I was glad that I could give a talk, but honestly, I should have done better for the people who woke up early on a rainy Saturday and came to watch me speak.

To fix this, I’m going full-bore with redundancy.  Here’s what I have:

  1. VMs on a separate USB drive.  I had this before, so no major change here.  This means that if I have another computer with vmWare installed, I can swap PCs and be up and running without missing a beat.  Of course, I might need to reboot my VM and change how powerful it is if I’m getting some less-powerful equipment.
  2. Two separate computers available for presentation.  I have my presentation laptop, but I’m also going to start bringing my tablet.  The tablet is pretty weak but it can run SQL Server Management Studio and is powerful enough for me to do some of my talks.  I couldn’t do the Hadoop talk on this tablet, but I should be able to do the rest of them.
  3. Spare adapters and cables.  The failure on Saturday showed me that I have a single point of failure with respect to adapters.  If it were just a simple adapter failure, I might not have found somebody else who has the right adapter.  I ended up purchasing two HDMI to VGA adapters in addition to the one I have now.  I’m going to test my current adapter with a VGA projector I have at home to see if it’s still functional; if so, I’ll have three adapters at my disposal.  I also purchased two Micro-HDMI to HDMI adapters.  My tablet uses Micro-HDMI, so if I end up needing to use it, I need the right adapters.
  4. Portable projector.  This is an emergency projector, not something I’m planning to use very often.  For that reason, I decided to go cheap—I don’t get paid to speak, after all.  I picked up an AAXA LED Pico projector projector.  It’s about the size of a smartphone and fits nicely into my presenter bag.  It also has a built-in battery which should be good enough for a one-hour presentation with some time to spare.  The downside is that it’s a ridiculously weak bulb, putting out just 25 lumens.  This means that my presentation room would need to be more or less dark for people to see the screen clearly, but again, this is a worst-case emergency scenario in which the alternative is not presenting at all.
  5. Azure VM.  I have an Azure subscription, so it’d make sense to grab all of my code and have a VM I can start up before presentations just in case my laptops fail.  That way, I can at least run the presentation remotely.  That Azure VM will have Management Studio and look very similar to my on-disk VM, but probably will be a lot less powerful.  It should be just powerful enough to do my Hadoop presentation.
  6. Phone with data plan.  In case I need to get to my Azure VM and can’t get an internet connection at my presentation location, I need a backup data plan.  Fortunately, I already have this.  Unfortunately, the app I’m using for tethering requires installation on the PC.  I might decide to wait until getting a new phone before getting software which allows my phone to become a wireless access point.

With all of these in place, I’ll have redundancy at every level and hopefully will not experience another scenario like I did in Pittsburgh.  I’m grateful that my reviews were generally good and people I respect said I did a good job recovering, but I’d rather prevent the need to recover quickly.  This isn’t as important as protecting corporate assets, but the principles are the same:  defense in depth, redundancy in tools, and preparation.

How To Troubleshoot Performance Problems

Not too long ago, I had an interesting discussion about how to troubleshoot performance problems in large procedures.  By “large procedure,” I’m talking about something which can be thousands of lines of code, or a procedure which might have dozens of potential choke points.  This is not an exhaustive article, but instead gives an idea of thought processes and acts as a starting point.

The first thing I want to do is format the code.  I’m going to use a tool to format code; that way, I know that I will be able to understand the code more quickly and be less likely to make a mistake due to improper tabbing, multiple columns on a single line, etc.  This obviously won’t improve any performance problems and I might need to maintain historical formatting when I make my changes, but I’ll at least make the changes locally for me to understand.

Once I have formatted code, the next step is to do a quick code review and look for certain patterns.  Any cursors or WHILE loops I see become automatically suspect.  I’m also on the lookout for Common Table Expression chains (especially ones which aggregate data), temp table chains (taking data from one temp table into another into another, etc.), and user-defined function calls.  Looking for cursors and WHILE loops is obvious:  procedural code tends to perform poorly in SQL Server, so those sections of code can act as performance choke points.  Common Table Expression chains can lead to lazy spooling, in which we read a subset of data over and over, and that subset of data can end up in tempdb, which is the single biggest point of contention on a typical SQL Server.  Temp table chains follow a similar logic:  we’re using tempdb over and over here, which might lead to a contention problem, or possibly a problem in which we are not filtering the base data sets well enough.  Finally, I look at user-defined functions because they tend to perform significantly less efficiently than equivalent queries without functions.  These are the types of things I’m looking for when I do my initial review, and when I find them, I mark their locations.

Next, I want to take a look at an execution plan.  Ideally, I want to use SQL Sentry Plan Explorer to run the query and collect all of the relevant plan metrics, including CPU and I/O per operation.  Because this is a very large procedure, I’d expect there to be a fairly large number of independent operations, and I want to focus my efforts on the highest-cost offenders.  Once I narrow down to the one or two calls which are most expensive, I’m going to look for a few specific things first.  First of all, I want to look for lazy spooling, as that tends to be a performance killer.  When I see lazy spooling, I almost always want to re-write the relevant query in such a way as to remove the spooling.  I also want to look for key lookups, which tell me that indexing might need to be updated to handle this query.  That could mean one of three things:  adding a new index, modifying an existing index, or modifying the query to use an existing index (for example, if I know a query should use a particular filtered index but it’s using a different index which doesn’t have all relevant columns).  Key lookups don’t hurt very much when we’re only looking at a few rows in an infrequently-called procedure, but high volume or high frequency can easily harm an otherwise-acceptable procedure.  I also want to see if I can find index scans where I expect seeks—or the other way around.  If I know that I am retrieving less than 0.5% of the data in a non-trivial table, I want a seek.  If I know I’m retrieving more than 20% of the data, I want a scan.  If I’m seeing the wrong type of operation, that could cause performance to suffer.

The next step for execution plans is looking at join types.  If I see a scenario in which I expect to pull in nested loops but am getting a hash match, I might end up spilling over to tempdb, which harms performance.  I also want to look for sort operations, as those tend to be relatively slow to begin with, and they get much worse if they spill to tempdb.  Really, tempdb is a common performance killer, so it’s not surprising to see some of the most expensive, performance-killing operations involve tempdb in some fashion.

After looking at the execution plan, I want to go back to my initial code walkthrough because execution plans can sometimes lie.  Execution plans lie most often when dealing with cursors and functions, because they tend to show you one call rather than all calls, meaning that they severely under-count how much CPU and I/O function calls and cursors/WHILE loops really use.  This means that I want to look at ways of removing those cursors and functions, even if the execution plan tells me that they aren’t very expensive.  If performance doesn’t change, I can always put them back in where they were, but inlining a table-valued function can improve performance of a query by a factor of 3-9x and could potentially improve scalar UDFs by much more (if they’re in the WHERE clause and make separate I/O operations like querying from some other table).

The easiest way to make these changes is to simplify the procedure for testing purposes.  That means hardcoding paramters or incoming data sets and stubbing out child procedure calls.  Obviously this technique can be somewhat limiting, but it does allow you to focus in on the critical section of code without needing to immerse yourself in other points of complexity.  Take care of one critical section at a time, create unit tests to ensure that refactoring doesn’t break existing calls, and focus on the pain points.  That’s the method I use to tackle a very large problem.

What do you do?  I’m interested in hearing how other peoples’ methods may differ from mine, as I consider this a hard problem and am always up for learning more.

Next Year’s Talks

This year, I’ve focused on two talks:  one of the APPLY operator and one introducing Hadoop.  I had a chance to give my Working Effectively with Legacy SQL talk as well, but only once.  I’d love to give my Legacy SQL talk more frequently, but here are my plans:

New Abstracts

I have a few talks sketched out, and some of them build on my prior talks.  I’m pretty excited about all three of them and hope I’ll be able to give all of them next year.

  • Data Migration Using BIML — Many companies scale out their databases horizontally, partitioning data by customer (or some other identifier) into separate databases or instances.  Sometimes, it becomes important to move that data from one database to another.  SQL Server Integration Services is a fantastic tool for ETL, but creating and maintaining dozens or hundreds of data flows by hand is exhaustingly tedious.  Instead of doing it the hard way, use the BI Markup Language (BIML) to automate package development.  This talk will show how we can use metadata tables and BIML to auto-create SSIS packages to migrate data from one database to another, handling foreign key constraints along the way.
  • Peanut Butter & Chocolate:  Integrating Hadoop with SQL Server — So you jumped on the bandwagon and set up a Hadoop cluster…but now what?  Your database developers and app developers know how to integrate with and develop against SQL Server, but the Hadoop world is a completely different experience.  This talk will help bridge the gap between SQL Server and Hadoop, using tools such as SQL Server Integration Services and Sqoop to migrate data between a Hadoop cluster and a SQL Server instance, as well as PolyBase to integrate the two like never before.
  • Big Data, Small Data, and Everything In Between — The breadth of options available to data professionals today is staggering:  between relational, object, graph, and document databases, we have a wide array of storage options, as well as a huge number of access and analysis tools.  This talk will walk you through data platform, data analysis, and data processing options.  We’ll discuss when to use (and when not to use) Spark, R, Storm, and SSAS, as well as a range of other tools and technologies.  By the end of this talk, you may not have MongoDB or Neo4J, but you will know where they might fit.

Cleaning Up Old Talks

In addition to creating new talks, I want to clean up my Tally Tables and In-Memory OLTP talks a bit.  I love Tally Tables, but I think I need to come up with a larger variety of problems and tighten up the discussion; the last time I gave this talk, I was not at all happy with it.

As for In-Memory OLTP, it’s my experience that very few people actually use it (and I’d love to talk to some people at PASS Summit to see if my experience is the same as that of others).  At my company, we do use it and have encountered a couple of major bugs in the product, so I want to include those in my talk, as well as spending some time making sure I really explain the pros and cons as well as I can.  SQL Server 2016 will introduce new features in In-Memory OLTP, so I’ll update the talk to include those as well.

Upcoming Events And Current Happenings

I haven’t posted lately to this blog.  I am remiss in not doing so, but I have been looking at a jam-packed schedule lately.  Here’s a quick summary of big events.

Speaking and Conferences

Right now, I’m in Louisville, Kentucky for Derbycon.  This is year #5 of the event, and my 4th appearance.  Right after that, I’m going to be in Pittsburgh, Pennsylvania on October 3rd for SQL Saturday Pittsburgh.  At that event, I’ll give my talk on the APPLY operator bright and early.  The week after that, I’m going to host SQL Saturday Raleigh.  I won’t present there, but I’ll be running around all day, as I’m the volunteer coordinator and the event coordinator.  Then, the following week, I’m going to SQL Saturday Charlotte.  This time, I’m giving my Hadoop talk, starting at 11:30 AM.

That will be my last SQL Saturday until at least December, but October has a couple of big events left in store for me.  On Monday, October 26th, I’m going to be in Seattle, Washington for FreeCon, put on by Brent Ozar and Kendra Little.  I’m seriously excited about this, as it’s a small group of really passionate data people getting together and talking about ways to do what we do even better.  After that, I’m spending the rest of the week at PASS Summit, which is spring break for database people.

That’s my last event for a while, but it’ll probably take me a few weeks to recover.

SQL Saturday Raleigh

As I mentioned, I’m heading up SQL Saturday Raleigh.  I’m fortunate to have a great team of people working with me, but this is a huge time sink.  I knew it was a lot of time last year, but this year, I have so many other things going on that it’s hard to juggle everything.  Nevertheless, I think we’re going to put on a great event and I don’t plan to lose any hair because of it…


The next bit of big news is that I’m now the chapter leader for TriPASS, the Raleigh-Durham chapter of PASS.  I’m going to be filling some big, big shoes and I know it’s a long-term commitment.  When I started speaking to groups, I decided that I wanted to do as much as I could for the community, and I think that this is a good way of doing that.

If you happen to be in the Raleigh-Durham area on the third Tuesday of the month, we’d love to have you out.  We’re also looking for speakers.


I’ve picked up my first consulting client.  When I started Catallaxy Services, I wanted to grow the company into a full-time consulting company focusing on small businesses.  At this point, I’m not positive that this is the path I really want to take, but I will admit that it’s fun to look at a new code base and tackle some new problems every once in a while.

If you’re interested in talking about some light consulting work, my details are on the company site.

Fun With SQL Equality

T-SQL has some interesting concepts regarding string equality:

		WHEN N'String with spaces          ' = N'String with spaces' THEN 1
		ELSE 0

Trailing spaces are not enough to consider a string to be unique.

		WHEN N'179³' = N'1793' THEN 1
		ELSE 0

Subscripts and superscripts are not enough to consider a string to be unique.

If you need to put a unique index on an NVARCHAR column, the best bet might be to add a SHA1 hash of the string. If you are okay with case-sensitive strings, you can use HASHBYTES(‘SHA1’, @YourString) to do the trick.

Get Row Counts For Partitioned Tables

I’m adding this query to my SQL utility belt.  This query tells you, for a specific table, all of the partitions, the lowest value in that partition’s range, and the (estimated) number of records in that partition.

	OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName, AS TableName,
	ddps.partition_number AS PartitionNumber,
	prv.value AS EndValue,
	ddps.row_count AS [RowCount]
FROM sys.indexes i
	INNER JOIN sys.objects o
		ON i.object_id = o.object_id
	INNER JOIN sys.dm_db_partition_stats AS ddps
		ON i.object_id = ddps.object_id
		AND i.index_id = ddps.index_id
	INNER JOIN sys.partition_schemes ps
		ON i.data_space_id = ps.data_space_id
	INNER JOIN sys.partition_range_values prv
		ON ps.function_id = prv.function_id
		AND ddps.partition_number = prv.boundary_id
	i.index_id < 2
	AND o.is_ms_shipped = 0

This query has a couple of good uses.  The first is that you can monitor a backfill with it.  In my scenario, I’m loading several years worth of data into a new fact table.  SSIS is loading data one day at a time so that I don’t leave a connection open so long that it gets killed.  Running this query shows me approximately how far along I am, as I know the valid date range for data in this table and I can watch the partitions as they fill up.

Another use for this is to check to make sure that your partitions are not skewed too heavily.  For example, my partitions are pretty tightly defined:  they are all (except for the current partition) within a 15-20% of average, meaning that they’re pretty close to a uniform distribution.  That really helps in cases in which I can eliminate partitions, as this means that when the optimizer can throw away 47 out of 48 partitions, it can throw away about 47/48ths of the data, regardless of which partition remains.  I would consider that a good partition.  At the opposite extreme, where almost all data is stored in a single partition, you get zero benefit from partitioning but still have to deal with the DBA overhead involved, and that table might be a candidate for re-thinking a partitioning strategy or eliminating partitioning altogether.

Thoughts On In-Memory OLTP

A Steve Jones editorial from a few weeks ago got me thinking about In-Memory OLTP.  We implemented this in our data warehouse about a month ago to mixed success.  Here are my notes from that forum post as well as a couple of areas in which I figured I could expand upon my comments there.

We use memory-optimized objects in two places: one staging table to load data warehouse fact tables, and various memory-optimized table-valued parameters as part of warehouse loading (streaming certain dimension rows from .NET code into our warehouse).

My original plan was to use memory-optimized queue tables in our OLTP system to feed memory-optimized TVPs into memory-optimized staging tables, and possibly convert a couple of our more heavily-read OLTP tables to become memory-optimized in the process. That fell apart for a few reasons.

The best (i.e., most nefarious) problem that we’ve had with In-Memory OLTP is XTP_CHECKPOINT issues. This happened even before we had a single memory-optimized object; we just had the filegroup. The first time this happened in production, we suffered a 10-hour downtime on a warehouse node and had to re-queue a lot of data because the transaction log had filled up and we couldn’t do a thing due to this XTP_CHECKPOINT.  We ended up recovering to shortly before the database failure and didn’t lose any data, but needless to say, causing a database failure before even a single memory-optimized object was created was…problematic…  We opened a support case with Microsoft and that case is still open.  As best as we can tell, if the transaction log fills up and autogrows at any point in time, the XTP checkpoint mechanism stops and never recovers, meaning that the In-Memory checkpoint mechanism never actually checkpoints and thus your log continues to fill up until you take the database offline and force it to reconcile the log.  Our operations team has been on the lookout for these situations since then and have prevented another disaster, but it’s one more thing that the team needs to consider,

Aside from that, the hard limitation on ~8000 bytes per row and no LOB meant that some of the places where I might have wanted to use a memory-optimized TVP (like TVPs for some of the larger dimensions) wouldn’t work out.

We ended up not pushing the memory-optimized filegroup out to production on our OLTP instances because you cannot take a database snapshot if the database has a memory-optimized filegroup, and we use database snapshots for some system processes. On the OLAP side, we did implement memory-optimized objects, and saw significant performance improvements. The TVPs were significantly faster than their on-disk counterparts and our staging table–which previously had been one of the top sources of deadlocks–zooms. Unfortunately, even with these performance benefits, the limitations have been too strict for adoption across our platform. Even if I got the go-ahead to move memory-optimized objects to our production OLTP system (and not just our OLAP system), there would have been another problem preventing general adoption: the inability, within an explicit transaction, to use memory-optimized tables as well as tables in external databases on the same instance. We use replication to push static data around in our environment, and all of that static data goes into one database on each instance. Many of the queries whose performance I wanted to improve join to this static data database, and I did not want to put all of the in-memory work in a separate transaction from the static data work.

I’m still hopeful that 2016 will solve at least some of these problems and make memory-optimized objects practicable, but to be honest, even coming in knowing most of the code-writing limitations of memory-optimized objects and natively compiled stored procedures (of which we have zero), I think In-Memory OLTP really burned me, and even if 2016’s version is a lot better, I’d have to think long and hard before building a project around it.