Query the Buffer Pool

DBAs are known for asking for more memory, but often can’t say what’s in memory.  While I agree that many database servers can use more memory, I’m a firm believer in knowing how you’re using your resources before asking for more.  The script below allows me to do just that.

What It Returns

This will return every index that is using at least 1 MB of memory for every database on your server.  It also returns space in memory that is associated with unallocated space in the tables which shows up as NULL for everything except the size of the space and the table name.

I’ll warn you now that the unallocated space can be surprisingly high for TempDB, and I talk about that in TempDB Memory Leak?.  Hopefully we can get a good comment thread going on that post to talk through what we’re seeing and how common the issue really is.

The Script

IF OBJECT_ID('TempDB..#BufferSummary') IS NOT NULL BEGIN
	DROP TABLE #BufferSummary
END

IF OBJECT_ID('TempDB..#BufferPool') IS NOT NULL BEGIN
	DROP TABLE #BufferPool
END

CREATE TABLE #BufferPool
(
	Cached_MB Int
	, Database_Name SysName
	, Schema_Name SysName NULL
	, Object_Name SysName NULL
	, Index_ID Int NULL
	, Index_Name SysName NULL
	, Used_MB Int NULL
	, Used_InRow_MB Int NULL
	, Row_Count BigInt NULL
)

SELECT Pages = COUNT(1)
	, allocation_unit_id
	, database_id
INTO #BufferSummary
FROM sys.dm_os_buffer_descriptors 
GROUP BY allocation_unit_id, database_id 
	
DECLARE @DateAdded SmallDateTime  
SELECT @DateAdded = GETDATE()  
  
DECLARE @SQL NVarChar(4000)  
SELECT @SQL = ' USE [?]  
INSERT INTO #BufferPool (
	Cached_MB 
	, Database_Name 
	, Schema_Name 
	, Object_Name 
	, Index_ID 
	, Index_Name 
	, Used_MB 
	, Used_InRow_MB 
	, Row_Count 
	)  
SELECT sum(bd.Pages)/128 
	, DB_Name(bd.database_id)
	, Schema_Name(o.schema_id)
	, o.name
	, p.index_id 
	, ix.Name
	, i.Used_MB
	, i.Used_InRow_MB
	, i.Row_Count     
FROM #BufferSummary AS bd 
	LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
	LEFT JOIN sys.partitions p ON (au.container_id = p.hobt_id AND au.type in (1,3)) OR (au.container_id = p.partition_id and au.type = 2)
	LEFT JOIN (
		SELECT PS.object_id
			, PS.index_id 
			, Used_MB = SUM(PS.used_page_count) / 128 
			, Used_InRow_MB = SUM(PS.in_row_used_page_count) / 128
			, Used_LOB_MB = SUM(PS.lob_used_page_count) / 128
			, Reserved_MB = SUM(PS.reserved_page_count) / 128
			, Row_Count = SUM(row_count)
		FROM sys.dm_db_partition_stats PS
		GROUP BY PS.object_id
			, PS.index_id
	) i ON p.object_id = i.object_id AND p.index_id = i.index_id
	LEFT JOIN sys.indexes ix ON i.object_id = ix.object_id AND i.index_id = ix.index_id
	LEFT JOIN sys.objects o ON p.object_id = o.object_id
WHERE database_id = db_id()  
GROUP BY bd.database_id   
	, o.schema_id
	, o.name
	, p.index_id
	, ix.Name
	, i.Used_MB
	, i.Used_InRow_MB
	, i.Row_Count     
HAVING SUM(bd.pages) > 128  
ORDER BY 1 DESC;'  

EXEC sp_MSforeachdb @SQL

SELECT Cached_MB 
	, Pct_of_Cache = CAST(Cached_MB * 100.0 / SUM(Cached_MB) OVER () as Dec(20,3))
	, Pct_Index_in_Cache = CAST(Cached_MB * 100.0 / CASE Used_MB WHEN 0 THEN 0.001 ELSE Used_MB END as DEC(20,3))
	, Database_Name 
	, Schema_Name 
	, Object_Name 
	, Index_ID 
	, Index_Name 
	, Used_MB 
	, Used_InRow_MB 
	, Row_Count 
FROM #BufferPool 
ORDER BY Cached_MB DESC

Where’d the Script Come From

I’ve had a script similar to this one around for a while.  It’s originally based off of Jonathan Kehayias’s script on his post Finding What Queries in the Plan Cache Use a Specific Index, and I couldn’t have done this without having his script to start with.

Then I originally posted a version of this script on my post Cleaning Up the Buffer Pool to Increase PLE, which was great to see the index usage for a single database.  It runs slower than this, only returns a single database, and does not show unallocated space in memory.  Those changes warranted either an update to that post or a completely new post…I opted for the latter.

What It Means

Now you can see what’s in your memory. Hopefully you’ll see one or two things that stand out on here that don’t make sense; those are your easy tuning opportunities.

If an index is 100% in cache then you’re scanning on it, and that may be an issue.  Yes, you can find when you did scans on indexes using the scripts in my Indexes – Unused and Duplicates post, but it helps to have another view of what that means in your memory.

One thing the index monitoring scripts in the post I just mentioned can’t do is tell you when you’re doing large seeks as opposed to small seeks.  With the typical phone book example, you could ask for all the names in the phone book where the last names begins with anything from A to Y, giving you 98% of the phone book as results.  Index usage stats will show you did a seek, which sounds efficient.  The script on this post will show that you have 98% of your index in cache immediately after running the query, and that gives you the opportunity to find the issue.

When you see an index that looks out of place here, dive back into the scripts on Cleaning Up the Buffer Pool to Increase PLE to see what’s in cache using that index.  If the query isn’t in cache for any reason, you may be able to look at the last time the index had a scan or seek against it in sys.dm_db_index_usage_stats and compare that to results from an Extended Events session you had running to see what it could have been.

The main point is that you have something to get you started.  You have specific indexes that are in memory, and you can hunt down when and why those indexes are being used that way.  It’s not always going to be easy, but you have a start.

We’re All On a Budget

It’s not too uncommon for this process to end in asking for more memory, and I view memory like being on a budget.  The amount of memory you have right now is your current budget.  Asking for more memory should be viewed like asking for more money in a financial budget.  For a financial budget increase, here are the questions I’d be prepared to answer:

  1. What did you spend the money we already gave you on?
  2. Did you spend that money as efficiently as possible?
  3. What else do you want to spend money on?

Now you can answer these questions in database form:

  1. Here’s what I have in cache at multiple times, specifically right after PLE dropped.
  2. I went through the queries that pulled the data into cache and tuned what I could.
  3. When I checked what’s in cache multiple times, these indexes fluctuated a lot in how much was in there.  I believe adding more memory would allow them to stay in cache instead of kicking each other out to make room.

Be it Virtual or Physical environments, there’s only so much memory that can be made available to us.  We’re on budgets of how much memory the host has, how many memory slots a server has, and how large the memory chips are that those slots can handle.  Prove you’re doing it right and it’s a lot harder to say no to you.

I have an odd habit of getting the memory I ask for because I answer these questions up front in the initial request for memory.

Thank You for More Than You Realize

Blogging and presenting have changed me for the better, and in ways I didn’t see coming.  However, I know I wouldn’t have had them if it wasn’t for three things.  Getting it all started, having help getting established, and having an audience for my work were supported by several groups of people, with a couple people really standing out.

Getting it started

This all started because Tom Zimmerman noticed I had an answer for everything, even if I didn’t know it right away.  I learned which bloggers and forums to trust, and would look up any issue quickly and easily.  However, he pointed out that I was a “leech”, always taking and never contributing.

Tom was right, so I eventually started what I thought was an altruistic path to find ways to contribute.  I settled on blogging even though I thought it would be complex to set it up and maintain.  Although it turned out it was as easy as writing an email, I wouldn’t have gotten past the hurdle of starting if it wasn’t for Tom.

Thank you, Tom.

Establishing the blog

So I created my blog, decided that wording things towards more junior level DBAs was the best thing for me and an area that could use more content, and was on my way.  However, I didn’t know how to promote myself, get people to read what I wrote, or really much of anything.  If only a couple people were reading it then I would have given up, quickly.

To get established, as I blogged in the past, I was very fortunate to come across Brent Ozar’s blogs on bogging and presenting.  To me it’s even more significant than his work blogging SQL Server.  Some of the stuff he talked about:

  • Having your blog reach established audiences.
  • Improve search engine results with SEO.
  • Writing consistently to establish your own audience.
  • Picking an audience and talking to a person (2010 version of me).

People were reading my syndicated work from the start, and some of my posts were creeping their way up in the search engines to get readers there, too.  My words were being heard, and I needed that to keep going.

That part worked but he was also talking about presenting.  How to do it, how to get better at it, where to do it, and how it changes you.  Well, if one part worked for me, lets try the next part.  That, too, worked out rather well.

Thank you, Brent.

Having an audience

So I mentioned syndicating my blog and doing presentations, but those both required an established audience where unknown people were accepted and encouraged to participate.  For that I found a couple places.

SQL Server Central and Toad World (formerly SQLServerpedia.com) were the two places to syndicate that I found very early on, and to this day the only two I use.

SQL Server Central is the more important of the two for me for several reasons.  I used and abused this site in the past to the point I was called a leech, so it was an honor to be there.  Then they do daily and weekly emails highlighting blogs and articles with both of them selecting a couple blog posts syndicated to SQL Server Central.  To this day it makes my day seeing my work on there.  The vote of confidence and boost in readers is a real boost to my morale and dedication to write more, better content.

You can’t discount Toad World which is slow to get reads, but gets many of them over time.  It’s a lot like the SEO work for search engines where you need to do good work and be patient.  I didn’t start with patience (still not doing too well there, either), but I’m very glad I have this going as well.

Then there were presentations, which I jumped right into doing at SQL Saturdays without ever presenting to a user group first.  I didn’t do too well the first time or two, but I’m also an introvert to the point that my oldest brother told me he would have bet money against me publically speaking.  However, the audience was there, the organizers encouraged new people to participate, and I was able to refine myself to the point of standing toe-to-toe with names I’ve known for years.  Better yet, now they’re not names to me, they’re people I’ve met and continue to know better.

Yeah, you know that these things started somewhere by a couple people, but I never put much thought into that.  Not until I read a post thanking Steve Jones.  SQL Server Central and SQL Saturday, the two places I thank the most for hooking me up with an audience, were both started by the same person.  Sure, he had help, such as Andy Warren and Brian Knight for SQL Saturday, but he was key in starting two organizations that are still key to me.

Thank you, Steve.

Skipping people

Oh the people I would name if I thought I could name them all.  Coworkers, mentors, SQL Saturday volunteers, bloggers, presenters, readers, attendees, and more should all be mentioned.  Even if I was overconfident enough to think I could name them all, the readers would move along like people in a theater during the credits.  To keep it short and simple….

Thank you.

Where am I now?

I am SOOO glad that I had all of this established.  This past year has been the worst.  I wish that last sentence needed a filter or something saying it was the worst for certain areas, but it was pretty all-inclusive.  Work, home, life in general beat me down to nothing and I needed to be picked back up.  I didn’t blog for 17 months, skipped submitting to some SQL Saturdays I love, and in general completely fell apart.

When I started to get it back together, I had some things established that helped more professionally than I could have ever hoped for.  My blog was still pulling in views, especially with being in the top 5 search results for a common search term on Google.  I didn’t really have an established audience for my blog because I was inconsistent before I took 17 months off, but it was syndicated to an audience happy to see me return.  SQL Saturdays were coming up waiting for an abstract.

My confidence was just waiting for me to say that I’m back.  So, confidence, I’m back.

Now I’m ending the year with a blog that has 125,000 lifetime views not counting syndication that takes it closer to a total of 200,000.  I’m also pulling together a presentation for SQL Saturday Cleveland that I’ll blog about in January, and I’ll pin their speaker evaluation forms on my cube wall, too.

I have goals of blogging better to make a bigger difference for the readers, blogging more consistently to earn followers, and do more with presentations to reach more people with better content.  The details I have for these goals that I didn’t list here aren’t easy, but it’s not easy to describe what these accomplishments do to a person, either.

I understand what it really means to me, especially when I’m down.  Hopefully, I’ll never need it like that again, but I’m so glad all of this was there when I did.

Thank you.

Advent of Code – Day 4

Day 4 of the Advent of Code has us mining for data in MD5 hashes, which is out of the comfort zone for a SQL DBA to say the least.

Some would say it’s a poor use for SQL Server, and I’d have to agree.  However, sometimes things just need to get done, so I’m playing along with a single tool approach.

Now SQL Server isn’t supposed to do well with row-by-row processing, right?  Andy Warren was posing the same question in his post pondering possible solutions to this exact problem. Well…the problem is that row-by-row processing is typically referencing a table every row.  Earlier today I mentioned that I didn’t have a good example of set based being slower, but now I found a way.  Sure, some could say that it’s poor examples on poor ways to use SQL Server, but come on…we can all find production code that meets that criteria.

So, typical DBA fashion, I made it set-based evaluating values in batches of up to 50,000.  It worked.

Here’s my code for Part 2.  It’s so close to Part 1 that you’ll have trouble telling the difference.

IF OBJECT_ID('TempDB..#Numbers') IS NOT NULL BEGIN
	DROP TABLE #Numbers 
END

CREATE TABLE #Numbers
(
	ID INT IDENTITY(1,1) PRIMARY KEY
	, b BIT
)

DECLARE @StartString varchar(100) = 'iwrupvqb'
DECLARE @RowCount Int 

INSERT INTO #Numbers (b)
SELECT 0

SELECT @RowCount = @@RowCount 

WHILE 0 = (SELECT COUNT(1) 
			FROM (SELECT TOP (@RowCount) ID 
					FROM #Numbers 
					ORDER BY ID DESC
				) X 
			WHERE CONVERT(VarChar(10), HASHBYTES('MD5', @StartString + CAST(ID as VarChar(1000))), 2) LIKE '000000%') 
	BEGIN

	INSERT INTO #Numbers (b)  
	SELECT TOP (50000) b
	FROM #Numbers
	
	SELECT @RowCount = @@RowCount 
END

SELECT ID = MIN(ID)
	, MD5_Hash = CONVERT(VarChar(1000), HASHBYTES('MD5', @StartString + CAST(min(ID) as VarChar(1000))), 2) 
FROM (SELECT TOP (@RowCount) ID 
		FROM #Numbers 
		ORDER BY ID DESC
	) X 
WHERE CONVERT(VarChar(10), HASHBYTES('MD5', @StartString + CAST(ID as VarChar(1000))), 2) LIKE '000000%'

Then I realized I didn’t even need a table and just ran the code. As you can see, the code was much easier to write, and it also worked. More importantly, it worked in 36 seconds as opposed to 113 seconds in the set-based approach for part 2. Part 1, in case you’re wondering was 1 second compared to 3 seconds, so the non-set based was still about 3 times faster.

DECLARE @i Int = 0
	, @StartString varchar(100) = 'iwrupvqb'

WHILE CONVERT(VarChar(10), HASHBYTES('MD5', @StartString + CAST(@i as VarChar(1000))), 2) NOT LIKE '000000%' BEGIN
	SET @i = @i + 1
END

SELECT @i 

I’m saving the link to my own post to give to others as an example of when set-based is worse. Hint: it’s ONLY because I wasn’t referencing a table in the loop, and I could probably still find a way to make it more efficient in a set.  It’s still true that row-by-row would be slower, I’m cheating by not using rows.

Advent of Code – Day 3

Continuing with the language-independent code challenge on Advent of Code – Day 3, I’m tasked with figuring out how well a drunken elf can guide Santa to houses.  Since his instructions had Santa stumbling into the same house multiple times they then added in a robot to help Santa using the same instruction set, which went on to prove automation can be harmful if not done properly.

Wow, I bet you never thought you’d read that paragraph…

The elf gave Santa instructions on how to get around a grid of houses using ^, v, <, and > to point the way.  When that failed, they tried again with a robot assistant using the same instructions with Santa taking the odd numbered instructions and his automated assistant taking the even numbered instructions.

My code did not have to change much to partially automate Santa’s job, which is actually kinda disturbing.  However, since it didn’t work out so well I went back and added a parameter for them to easily roll back the change.  Now it’s easier to go back to happy and jolly instead of cold and…terminator.

SET NOCOUNT ON

IF OBJECT_ID('TempDB..#visited') IS NOT NULL BEGIN
	DROP TABLE #visited
END

DECLARE @input varchar(max) = '>^^v^<>v<<<v<v^>>v^^^<v<>^^><^<<^vv>>>^<<^>><vv<<v^<^^><>>><>v<><>^^<^^^<><>>vv>vv>v<<^>v<>^>v<v^<>v>><>^v<<<<v^vv^><v>v^>>>vv>v^^^<^^<>>v<^^v<>^<vv^^<^><<>^>><^<>>><><vv><>v<<<><><>v><<>^^^^v>>^>^<v<<vv^^<v<^<^>^^v^^^^^v<><^v><<><^v^>v<<>^<>^^v^<>v<v^>v>^^<vv^v><^<>^v<><^><v^><><><<<<>^vv^>^vvvvv><><^<vv^v^v>v<<^<^^v^<>^<vv><v<v^v<<v<<^^>>^^^v^>v<><^vv<<^<>v<v><><v^^><v<>^^>^^>v^>^<<<<v><v<<>v><^v>^>><v^^<^>v<vvvv<>>>>>^v^^>v<v<^<vv>^>^vv^>vv^^v<<^<^^<>v>vv^v>><>>>v^>^>^^v<>^<v<<>^vv>v^<<v>v<<><v>^vvv<v<vvv^v<vv<v^^^>v><<^<>><v^^>^v^>>^v<^<><v<>>v^<>>v<>>v^^^><^>>vvvv>^v<^><<>>^<>^>vv><v<<>>^^>v^^^><^<<^^v>v<^<<>v>^^vvv^v^>v^<>^^<>v^v>v>v<v^>vv>^^v<>v>>^<>><>v>v^<<vvvv<vvv><v^<^>^v<>>^><v>><>^<v>v<v>vv^>>vvv<>v>v<v^>>^>>v<<>^<>^<>>>^v<<<^<^v>vv^>><<><v^>^v^^^v<>^^vv><>><>>^>v^<v<>v<>>^<<^v>^^^<>^v^><>v<<v>vv^>vv<<>>><<^v^<>v<vv>>>^^<>^><<^>vv>>^<<v^^vv<>>><v>v><^<v<<>>>^^<>>^<^v><>vv^^^v>vvv>^><<>^^>^<<v^<v<^v<<>vvv<^<<>^>^v<vv<^>vvv>v>vv^<v^><>>^vv<^^^vv><^vv<v^<><v^vvv><<^>^^><v<<vv^>v<vv<v>^<>^v<<>v<v^v^>^>^>v<<^vvv<<<v>^^>^<<<<>vv>>^<>^>>>v<v>^^<v^<v<>>>vv>^^v<<>>>^^v><<<v<v<^v<>^^><v<^v<<v^><><^<><v<^^v>>><v^^v<<v^><^<><<v^>><^<>v>v^<><^<v>^v^>^>^vv^>^^<<vv^>vv<^vvv<>>^^<^>v^>^>^<v^><v<v>>>v<<<><^v<<><^<vv^v^^^>v<^^<v^vvv<v<><v<vv<^vv<>vv<v^<>>vvvvv<<>^v^v>vv>>>vvv^^<^<^<><>v<v>><^v><^<<<>><<<v>^>v<>^>^v>>^<>v^<^>><<>^<v>^>^^^>^^<v>>>><>^v^v><<<<vv^<vv<>vv>v<>v^<v^>v><>>>v^<><^vvv>vv^<^<<^<^^v>^>>>v<^<^v^^<^<^>>><v>vv>^<<><>^>>v>^<<>><^<>v<>vv^^>^>vvv^v<<^<^^<vv<>^vvv<^^v^vv^>>v<^>^^<v^<>v<^<^vv>v<<vv>vv>^>vvv>>>^^>v<>^v>v^<^>>v>^^v>>>>v^<v>v<^>v<v<<>>^v<^^<v><^<>>^<<vv^>>v<<v>^v<>><^>vv<v<^>>^^<vvvvvvvvv>>>v<v<>v^<>>^vv<v^^v<<^vvv^<<^><>vv<><<>>v>vv^><>>^^v^>>v^v^><<<>>^^<^v<<^<>>>>^<^>v^><<^>v<^v<^>>^^<<<<><^<^v^v<>>^v<^<<vv^<><^^vv><v^v^v>^>>^>^vv^>^v<v^v<<vvv^><>>^v^^><>v>vv><^>>vv<vvv<<<<^<>vvv^v<v>^<v<^>^<^<v<><>v^^^^<<vv<^^vv<v>><<v^><>>><v^>^v><^>^><vv^<><^<v>><<^vv<>>v^<<v<>v><v<><><vv>^>>v^<^<v>^><>>><^><v^v<>>>^^<^>v<v>vvv<>^<<><v^^>^>>v<^v>^>v>>>vv>v>>v^^^<^<vvv^<>^>^<v^<v^v>v>^>vv>vvv<>v<^>v>^^>>^<vv^^v>v^^^^^v^vv><^<><>^>vv<^>>^vvvv^^^>^<vv>^v<<^><^^>^<>^^>^<<v<^>>>^><<^^>v^v>>^>vvvv>^^v><v>>vv><<<vv<^>v>^^^<v>v^vvv<^><<^>^<>^><<<<<v^<<vv^v>^<>v<v>^>^>><>v^v<^vv^^>vv<<v^v>vv^vvv<<<<>^v<v^^v^v>v<<v>^^<>^vv^^>^>^v^vv^>>v^vv^^<vv><<v^v^^v><vv<^vvv<vv^^<<v>v^v^^^^v<^<^>v>^>v>^vv^v^^<v<^vvvv<<<>^<^^^<^^<>^<><vv<^^<<^>>><v^vvvv>^<>>^^>v^^v^<<v^^^<<<><^<v^v^^v<v^<>v><<v<>^v>v<^><^>vv^^<vvv<^v>>v>^<><v^><^^^<v^>>vv<<<<<^<>^v^v>^vv^<>v>v<^>vv<<^vv>vv<v<><>>v>><v<^<^^>><<v^v<<^><v<^<vv<v<<vv^>^<<><^^>^<^>>^<vv>><v<<vvv<^^v^>^^<^v>^v<v<>v><v^v^<<^<><<v<<^v>v<<>>^>v>>v>>v<^<<^<^>>>v>^^^v><^>^^>>v<<>^v><v>vvv^vv<<<>vvv<<>^>>>v<v<v^<^<^>^<^>v^^v<^^<v<>v<>>^^>^v^>v<<<<^<>v^><<<v>>>><<v^<^vv>v>><>>^<<<^<^^>v<>>v<>vv<<^<<><<^>v^^^vv^>vvvv>>v>v^><<v<>vv^<<><<vvv>^>>>^<<<^<^<<v>^>v<>>v>>vv^^><<<<^^^v>><<^><v><v^^><v<<v^^v^^v>>v<><><<>^><v><^<vv>><^v<>v<vvv<>^>><v>>v<^><<v>^<>^v><^><^^<v>^><^^v^<<><>>^>v^<^v^vv<><^>vv^>v^vvv^<>>^><^<^<>^<<v^v<^v><>^v<v>>^>>^v^vv>><vv><v^^<<^v^<>^v<<>^><^>><v>>v<<<v^^vv<>^^v>>><><><<v^<<<v^<^^><v^>v^^vv<v^<>>vv^<^v<>^v>>v^v>v<^^vv><>^v<<>v^<>v^>>v>vvv<^><><^^>^vv^>>v^>^<^^<><>><<>^^^><^v^v><<<><<^v^vv>v>><^>>><v^>v<v><><v^v<>v^^>>v<<>v>v<v<v<^^<><>v^^<>>v<^v<v>v<><v<v>^<<>v>vv^^<>>^^^<>^^>^v>v>>>^v^v><v^^<><v>^^v^v<^<^^><<v<^<^<>^<>><<>^>>^>^^><v><>v<><>><<<>>>>vv>>>^>>^v<^>v^^^v<<vv>><<<^<<<>>>>>^>vv<^v^<>^<v^>^v><v>vvv<>>>^v^^^v<<<<>>^^<vv<^<^^>^<>v<^<<<>><>>v<^<>^<vvv<^<>><><<v>^^^>^^<<v<v^>^^v^>><<^vv><v>^v>>^<v>v>^^>^v>^vvv<>v^v^^<><vv>vv^>>><>v<^><v<v^<><<<>^v>^v<<<^>^>^>v^v<<><vvv<<v^^<><v>^>>><vv>><v>>v^<vv>>vv<<^v^v<<><^v<vv>>>vv<>>>>^vv>v^<>vv>v^v<v^><v<^^^^^>vv<><<vvv^<v><^<vv><^^^vv^<>^^^^<^><^<>v^<v^v<<^v<<^^<>>^<v^^>>>vv<vvv<>v<<>><^vvv^<<^^<<>>>^<>>>v^^><>><<>><v^v>>>>>><>>><v^<<vvv^>v<>>v^<>vv<><^^^^v^<<^<v^vv><<^^>v<^vvv^v>>v>^>>v>^^><<v^<>v<>vv<^v^vv><v><<vv^v>>v^>>v<^^^>^><<v<>^><>v>>>vvv<v<vv<^>>^v<v>^<^^^^^v><>v><>v^v^v<v^vv^v>vvvv<>vv<<<vv<v<<>^<^>^^v^<<>^<v><^><v<v<><<>v^<<^<><vv>v<<^v>>^v<><v>^>>^^><>v^<^<vvv^>^>^<<<<>vv>^v^v<^^^<vv>><>^^<<v<^<^^>>>v^v<<^^^<v<v<^<>^v<v><v^vv^^v^^v^^<vv<>^<><vv^<^v^<<^><<vvv>^^<^^^<^v>^>^vv><<<^v<v>vv>v<>v^v<v^>v^>>>v^v<>^v<<>^vv>v>v>v^<^>v^^<^>^^^^vv>^^><^>vv^>>^^v>><<<<^><>v<>^<v<vv^>^^><<^><v>v^>^^<^>>><>><v^v<v^<v<vv^v^<<^<vvv>>><vv<^^>>^>^><<v^<>>v>v^v^^><<>vv^v>v^<v><^<>^^<^>v>^<><<<v>^<^<^>^>^>^^v^<<^^v^^<^<>><^>v>>^^<>^^^<<<<v^>^v<^vv>^<<<v<><<v<>vv>>>v><>>><>>v<<<vv><>^v>v<^>><^><><v<>^v^>^v>^v<<><<^<>>v>^><>^>><>><^<v^><v^^<><v><^^>^v^^<>v^<v^<^v<v^^^^^v^<<^>^^^<^v><>^^<<<><<<<<^^>v^vvvv>v<>>vv<^>^v^>v<^vv^v<<><<v>v^v>^^><><^<v^>v><vv><>>><<>^vv<>v>>v<^v>>>v<v>v>v>^vv<<>^^vv<v<^v^<v<v>vv<>^<^<vv<v^<^v^^><<>^>><^v>vv^^v<<^^><<>v^^<><><v^^<v^v>^>^>^>v<^<v>^v^^>v<>vvv<^v<v^v><<v^><<^^><^<<v^v^>v<>^>v><><v>^<v<v>^<^^^>^v<<><<><>vv>v^<>v^><v^v<v><><<v>v<vv><<v>>v>^<<<>vv>>vvv>^^vv^v^^<^^<>v^^<>v>>^^>^>^>v>><^>><>>^<<>><^>v<<<<<<<^v^v<v^<v^^>^<><<v<^>v^>v^vv<<^^vv^>>>>^<>v<^v<>v<vv<^>>v^vv>vv><vv<<^>v>><vv>>>vv^<<<<vv^>v<<<<^^>^^v^><<^<v^>v^>^^<v<>vvv^>^<>vvv<v<^^>v^<<v>><>v<v<>^^<vvv>^>vv><><<<^^vv<v^<v<>v<>><<v><^vv^>^<^>^^^<<<v>vv^<^<<>^>^<vv>v><v<<^><^>^^<vv^v^^>>>>vv^><^^vv><>^<v^v>v<vv>v><<<v>v<v>^><v^^><v>v<^v^>>^^<v^>^^>vv>>vv^><^vv^vv<<^>vv>^v<v><vv><v<vvvvv>^^v^v><v>>>^vv<>v>^^^^<^>><>^v^^^>v<^^<<^^v<vv<>vvv<^>><><^>>^><^<>v<v<<><<v><v^v<>><^>v><<v^<v>v<^<vv^v^v^>vvv^^>v>^<vv^>v^v^<>v>^>>vv>><^^<v<<>^vv<><><<^v<v>v<<vv><>><^v<v>>v^>vvv^v^<<^><v<>^vv^>v^<v<^>>v<v><v><v>>^<<<v^<><<>v>^>^^<v<>>^<>^>^><<<^<<^<<^>^v>>><vvv>><<<<v>>>>>>>^<^v<^>v<>vv<><>v>>^>>^>vv^^><<^<v<v>>^^<<^>v<^>>vv>^<>v><^>v<vv>>>>>>^v<^<<<v^><vv<<>>vv<<><v<><<<v<^<v<>>v<^^^^v^^<^^^<^<vv><<^>><>v<<>v<v<>>>><>v^vv>^>^>>vv^v<v<<><^v>vv^><v<<>v^v<^>vv<<^^v><^>>^^vv<^<>>v^^>><v>^v>>>^>>v>v<>v<^vv><>^<<^>vv>>><><>v^><>v^>v>v><^v<><v<v>^v<<^vv^><^^>><^^^<<<^>v>^v>>><^>><^>>>^^^<^>vv<><<<v^>^<^^>>^^^v^v^v>v<v>>>><^>>>v>^vv<<^^^<^^vv>v<<><v<<^^>v>><<v^^><^>^<^>^v^>v><^<^vv>v>><>^<<vv<<v>v<vv<v>^>^>><^^<v>^v^v<><<>vvv<^<v>^><>^>vvv>>>^><<>><v^^<^<<^v>>^v<v<vv>vv^v^>v<<vvv<^^v^v>^<^>>^>v<^>^v<<><<<^>^<^^^>vv<^^^^vv<v<^^v<<<<v<^v^<><v<<^><<>vv>>><^<^<>>>^>^>>^<<<<<^^v>^>^<>vvv^^<^><^>^^v>^vv^><v^<^<<v^<vvv<<^v<><^><^>>>v>^v>^>^v<vv^v>><v><^><v^^>v^>^<><<><>v<v^>vvv^>^>>v<>^><^>^><vvv>^^v^v>v<>^v^><^>>v>v^><<<^>>^<>^<>>v><>>v^>^>^^<>>v^>^<vvvv<^vvvv^>>vv^<v^v>^vv<>v<>^<v<v>v>^^><^>vv^<^v^<<^<^<><vv<^v<^v><>>>^v^<<^><^>vv<v>v<^>vv^>v<<<>^<><v<^^^>v><^^<>^<^<v^vv^<<^>><<v^v<^vvv<<<>>vvvv^v^^^>v<>>><<>vvv<<^^^>v>v>>v<<v<v^v^>^^v>^><^<><<v^<v<v^^^><>v^^^<v>vv<>^>^^vv>^<<^v<^v><v>>>^>>><^<<>^v>>^>vv<<<v<>^<v><v^<^<>v>v^^v^>><<^v<<<<>v>v>v^^<^><>^^<<<v>vv<>>>^>>v<><v^>^<><vv>v>v^v<v^<^>>^>><<^^<^^v<vv<>><<<v<^<<^^^>vvv^<vvv<^>vv><>><<<^<v^v^^<<^vvv^^<^<><<>^<^<>>vvv<>^<>v^v<><>>v^v><<>>>vvv>v<>^>>^><^>vv<<>>v<<^><>v>>^^<v>^>^<<>><^<<vv<^<vv^vv><>>>><^<v>^>vv<v><>^<>vvvvv^vv<<v<>>>^<<><>^^vvv>>>vv<<^^><^v^^v<>^^>^><^>v^^^^v<^<<vv<vv<>vv^^>v^vv>v><>>vv>^<^<v^v^>>v^v^^v>^>vv^>v<vvvv<^v<^v>^v>^^v<<^>^^<<>^><^v>>>vv^>^^>vvvv>>v<^<v>^>>>v^<><^<^^<v>vv^^><v>v^<>^^^>>><^^v>v>^<<>^<v^>vvv^>^^^><v<^>>v<v>>^v><<><<>v<^<<>^><>^>vv>^<v>^^v<<^v^vvv^^>^vv^<^>^>^^v>v^>^<<><<^>v>>vv^vv><v>>^<<^<v^^<^<v^^vv^><^^<^^><v^^>v^^^<^<>^<>>^v<^vvv^^v^<><^>>>>>v><><<<>vv<^v>><<>vvv<><<vv<<<^>v^^>>^>^v>><><^^v<>><>>v^>^<vv><<<>><><<v>^^<>>v<><^<vv>vv<^v>^<<<<v<^<<^^>>^<><^>><<>^>v>^^^v>>^<^^v><v^v>^><<><>>^>>^<<v<>^v<>^>^<v>>vv>^vvv<<v<<^>^>^<<^^<>^^^^vvv<>^vv<vvvvv^^>^^<^>>><>v^<><^<<^>v^^v<>>^vv<>v^^<>>v^vvvvv<<v^<v^^>>><vvvvv>><^>vv>v^v^<v<^>^^><^>^^^^v<><^v<<>v^>v>>vv<<>^<v^^>vvv>^^<v^<>vv^><>><v^^v<>^>>^>v><>>^^v>^>^>>>^>v<^v>v>^<^^^^^>>v<v<>>v<<^>^<v<<>^^>><<^><>v<>^^^vv<>^^>><<^^>v>vv>vv>v^>^v>v^^<>>><<v><v<<>>v><>vvv^^v>^^>^vvvv^>^<>^vvvv><v><v<>>><>^<^vv<>^v<^v<>^vvv<<>><vvv^>>^><<vv^<v^>^<v<<^^>^^<^^v^>v<>v^v><>><v^^>>^vvv><^vv>v^<^<^v>>v^^>^vvv^<v^^v^^>v<^<>>^<>>>^^<><^^vv<>^vv^<>>>>^^<<^^<>vv^^><>^^<v<<v>^<v^^>^v<><><>vvv>^v^>>vv<<^v<<>><v>^><^>>>^<^<^^>vv^<<^<>>^^><><<v>^^<v>>v<<vvvv>^v^vv>><^^<<^>>v>v<^^^<^><^^vv>^vv<^<vv<>v><^<><v><^^^>>^<><^<v>>>>v^<v>>>>>v<><^^>v<^<^>><v<>^>vv>^^v^v^<<v<><<<^v^><<^<><<<<v<^>><<<>v>>vv><vv<><<^<^<><vv>^^^^<>v<<<<v>vv<>vv^^^>><>vv^><>>^vv<<><^^vv<>v^>>^<<>^<v^<^>v<'

DECLARE @sx smallint = 0
	, @sy smallint = 0
	, @rx smallint = 0
	, @ry smallint = 0
	, @useRobot bit = 0
	, @x smallint = 0
	, @y smallint = 0
	, @i smallint = 0
	, @i_max smallint = 0
	, @char char(1)

SELECT @i_max = len(@input)

CREATE TABLE #visited
(
	x smallint
	, y smallint
	, PRIMARY KEY (x,y)
)

WHILE @i <= @i_max BEGIN
	SELECT @char = substring(@input, @i, 1)

	IF @i % 2 = 0 AND @useRobot = 1 BEGIN
		SELECT @x = @sx
			, @y = @sy
	END ELSE BEGIN
		SELECT @x = @rx 
			, @y = @ry
	END

	IF @char = '>' BEGIN
		SELECT @x = @x + 1
	END ELSE IF @char = '<' BEGIN
		SELECT @x = @x - 1
	END ELSE IF @char = '^' BEGIN
		SELECT @y = @y + 1
	END ELSE IF @char = 'V' BEGIN
		SELECT @y = @y - 1 
	END

	IF 0 = (SELECT COUNT(1) FROM #visited WHERE x = @x AND y = @y) BEGIN
		INSERT INTO #visited(x, y) SELECT @x, @y 
	END

	IF @i % 2 = 0 AND @useRobot = 1 BEGIN
		SELECT @sx = @x
			, @sy = @y
	END ELSE BEGIN
		SELECT @rx = @x 
			, @ry = @y
	END

	SELECT @i = @i + 1
END

SELECT COUNT(1)
FROM #visited 

I’m not the only one doing this with T-SQL.  Andy Warren also posted his very different, yet same results, version in separate posts for the two parts: Day 3 Part 1 and Day 3 Part 2.  His came out more complex, but more set-based.

My problem is that I develop T-SQL for the problem at hand with the expected size of the data.  Andy is looking ahead and developed a method that will probably scale much better than mine.  I’m hedging my bets and hoping the elf in charge of this either sobers up or gets fired.

Advent of Code

If you want your skills to be sharp, you practice.  If you want to get yourself to actually do practice, you call it a “challenge”.  This is what the Advent of Code is.

Now let me be perfectly clear about how I feel about practice.  I saw Jeremiah’s (b|tpost on this and ignored it.  Then I saw Steve Jones’s (b|tpost and ignored it.  Then I saw Andy Warren’s (b|tpost on it that also contained the answer, so I took a look.

It was intriguing and refreshing to have a question like that asked.  The refreshing part was all the requirements were all there in one place.

The intriguing part was me sitting there asking myself how I’d answer that question, realizing I would have done SOOO much more work than Andy.  However, I had to read Andy’s answer 3 times for me to realize I wasn’t able to read it, which increase my level of intrigue.  I rewrote it adding in some legibility for my sake and posted it on his comments.

Now there’s more to it.  I cheated.  I only rewrote someone else’s answer and didn’t really do the work.  Yeah, that’s what half of computer science is, copy/paste others work intelligently, add just enough to call it your own, then ask for a raise.  However, could I do one on my own?

Well, there’s 25 days on the Advent calendars I’m used to, and this one is no different.  So I tried it out on Day 2.

SPOILER ALERT: My answer to Day 2 is below.  Don’t cheat like I did.

The one odd thing you’ll see is that I copy/pasted the dimensions from the Advent of Code site, then I held in ALT and drug the mouse to write on more lines than one at a time.  (even I read that as I drugged the mouse…oh well)  However, that introduced an issue where there were trailing spaces and tabs I had to take care of as well, which I fixed in code as well.

IF OBJECT_ID('tempdb..#input') IS NOT NULL BEGIN
	DROP TABLE #input;
END

CREATE TABLE #input  
(
	dims varchar(100)
);

INSERT INTO #input 
SELECT '4x23x21'
UNION ALL SELECT '22x29x19 '
UNION ALL SELECT '11x4x11	 '
UNION ALL SELECT '8x10x5	 '
UNION ALL SELECT '24x18x16 '
UNION ALL SELECT '11x25x22 '
UNION ALL SELECT '2x13x20	 '
UNION ALL SELECT '24x15x14 '
UNION ALL SELECT '14x22x2	 '
UNION ALL SELECT '30x7x3	 '
UNION ALL SELECT '30x22x25 '
UNION ALL SELECT '29x9x9	 '
UNION ALL SELECT '29x29x26 '
UNION ALL SELECT '14x3x16	 '
UNION ALL SELECT '1x10x26	 '
UNION ALL SELECT '29x2x30	 '
UNION ALL SELECT '30x10x25 '
UNION ALL SELECT '10x26x20 '
UNION ALL SELECT '1x2x18	 '
UNION ALL SELECT '25x18x5	 '
UNION ALL SELECT '21x3x24	 '
UNION ALL SELECT '2x5x7	 '
UNION ALL SELECT '22x11x21 '
UNION ALL SELECT '11x8x8	 '
UNION ALL SELECT '16x18x2	 '
UNION ALL SELECT '13x3x8	 '
UNION ALL SELECT '1x16x19	 '
UNION ALL SELECT '19x16x12 '
UNION ALL SELECT '21x15x1	 '
UNION ALL SELECT '29x9x4	 '
UNION ALL SELECT '27x10x8	 '
UNION ALL SELECT '2x7x27	 '
UNION ALL SELECT '2x20x23	 '
UNION ALL SELECT '24x11x5	 '
UNION ALL SELECT '2x8x27	 '
UNION ALL SELECT '10x28x10 '
UNION ALL SELECT '24x11x10 '
UNION ALL SELECT '19x2x12	 '
UNION ALL SELECT '27x5x10	 '
UNION ALL SELECT '1x14x25	 '
UNION ALL SELECT '5x14x30	 '
UNION ALL SELECT '15x26x12 '
UNION ALL SELECT '23x20x22 '
UNION ALL SELECT '5x12x1	 '
UNION ALL SELECT '9x26x9	 '
UNION ALL SELECT '23x25x5	 '
UNION ALL SELECT '28x16x19 '
UNION ALL SELECT '17x23x17 '
UNION ALL SELECT '2x27x20	 '
UNION ALL SELECT '18x27x13 '
UNION ALL SELECT '16x7x18	 '
UNION ALL SELECT '22x7x29	 '
UNION ALL SELECT '17x28x6	 '
UNION ALL SELECT '9x22x17	 '
UNION ALL SELECT '10x5x6	 '
UNION ALL SELECT '14x2x12	 '
UNION ALL SELECT '25x5x6	 '
UNION ALL SELECT '26x9x10	 '
UNION ALL SELECT '19x21x6	 '
UNION ALL SELECT '19x4x27	 '
UNION ALL SELECT '23x16x14 '
UNION ALL SELECT '21x17x29 '
UNION ALL SELECT '24x18x10 '
UNION ALL SELECT '7x19x6	 '
UNION ALL SELECT '14x15x10 '
UNION ALL SELECT '9x10x19	 '
UNION ALL SELECT '20x18x4	 '
UNION ALL SELECT '11x14x8	 '
UNION ALL SELECT '30x15x9	 '
UNION ALL SELECT '25x12x24 '
UNION ALL SELECT '3x12x5	 '
UNION ALL SELECT '12x21x28 '
UNION ALL SELECT '8x23x10	 '
UNION ALL SELECT '18x26x8	 '
UNION ALL SELECT '17x1x8	 '
UNION ALL SELECT '2x29x15	 '
UNION ALL SELECT '3x13x28	 '
UNION ALL SELECT '23x20x11 '
UNION ALL SELECT '27x25x6	 '
UNION ALL SELECT '19x21x3	 '
UNION ALL SELECT '30x22x27 '
UNION ALL SELECT '28x24x4	 '
UNION ALL SELECT '26x18x21 '
UNION ALL SELECT '11x7x16	 '
UNION ALL SELECT '22x27x6	 '
UNION ALL SELECT '27x5x26	 '
UNION ALL SELECT '4x10x4	 '
UNION ALL SELECT '4x2x27	 '
UNION ALL SELECT '2x3x26	 '
UNION ALL SELECT '26x29x19 '
UNION ALL SELECT '30x26x24 '
UNION ALL SELECT '8x25x12	 '
UNION ALL SELECT '16x17x5	 '
UNION ALL SELECT '13x2x3	 '
UNION ALL SELECT '1x30x22	 '
UNION ALL SELECT '20x9x1	 '
UNION ALL SELECT '24x26x19 '
UNION ALL SELECT '26x18x1	 '
UNION ALL SELECT '18x29x24 '
UNION ALL SELECT '1x6x9	 '
UNION ALL SELECT '20x27x2	 '
UNION ALL SELECT '3x22x21	 '
UNION ALL SELECT '4x16x8	 '
UNION ALL SELECT '29x18x16 '
UNION ALL SELECT '7x16x23	 '
UNION ALL SELECT '13x8x14	 '
UNION ALL SELECT '19x25x10 '
UNION ALL SELECT '23x29x6	 '
UNION ALL SELECT '23x21x1	 '
UNION ALL SELECT '22x26x10 '
UNION ALL SELECT '14x4x2	 '
UNION ALL SELECT '18x29x17 '
UNION ALL SELECT '9x4x18	 '
UNION ALL SELECT '7x22x9	 '
UNION ALL SELECT '19x5x26	 '
UNION ALL SELECT '27x29x19 '
UNION ALL SELECT '7x13x14	 '
UNION ALL SELECT '19x10x1	 '
UNION ALL SELECT '6x22x3	 '
UNION ALL SELECT '12x21x5	 '
UNION ALL SELECT '24x20x12 '
UNION ALL SELECT '28x2x11	 '
UNION ALL SELECT '16x18x23 '
UNION ALL SELECT '2x13x25	 '
UNION ALL SELECT '11x7x17	 '
UNION ALL SELECT '27x21x4	 '
UNION ALL SELECT '2x10x25	 '
UNION ALL SELECT '22x16x17 '
UNION ALL SELECT '23x22x15 '
UNION ALL SELECT '17x13x13 '
UNION ALL SELECT '23x24x26 '
UNION ALL SELECT '27x18x24 '
UNION ALL SELECT '24x7x28	 '
UNION ALL SELECT '30x12x15 '
UNION ALL SELECT '14x28x19 '
UNION ALL SELECT '2x15x29	 '
UNION ALL SELECT '12x13x5	 '
UNION ALL SELECT '17x22x21 '
UNION ALL SELECT '27x10x27 '
UNION ALL SELECT '17x6x25	 '
UNION ALL SELECT '22x2x1	 '
UNION ALL SELECT '1x10x9	 '
UNION ALL SELECT '9x7x2	 '
UNION ALL SELECT '30x28x3	 '
UNION ALL SELECT '28x11x10 '
UNION ALL SELECT '8x23x15	 '
UNION ALL SELECT '23x4x20	 '
UNION ALL SELECT '12x5x4	 '
UNION ALL SELECT '13x17x14 '
UNION ALL SELECT '28x11x2	 '
UNION ALL SELECT '21x11x29 '
UNION ALL SELECT '10x23x22 '
UNION ALL SELECT '27x23x14 '
UNION ALL SELECT '7x15x23	 '
UNION ALL SELECT '20x2x13	 '
UNION ALL SELECT '8x21x4	 '
UNION ALL SELECT '10x20x11 '
UNION ALL SELECT '23x28x11 '
UNION ALL SELECT '21x22x25 '
UNION ALL SELECT '23x11x17 '
UNION ALL SELECT '2x29x10	 '
UNION ALL SELECT '28x16x5	 '
UNION ALL SELECT '30x26x10 '
UNION ALL SELECT '17x24x16 '
UNION ALL SELECT '26x27x25 '
UNION ALL SELECT '14x13x25 '
UNION ALL SELECT '22x27x5	 '
UNION ALL SELECT '24x15x12 '
UNION ALL SELECT '5x21x25	 '
UNION ALL SELECT '4x27x1	 '
UNION ALL SELECT '25x4x10	 '
UNION ALL SELECT '15x13x1	 '
UNION ALL SELECT '21x23x7	 '
UNION ALL SELECT '8x3x4	 '
UNION ALL SELECT '10x5x7	 '
UNION ALL SELECT '9x13x30	 '
UNION ALL SELECT '2x2x30	 '
UNION ALL SELECT '26x4x29	 '
UNION ALL SELECT '5x14x14	 '
UNION ALL SELECT '2x27x9	 '
UNION ALL SELECT '22x16x1	 '
UNION ALL SELECT '4x23x5	 '
UNION ALL SELECT '13x7x26	 '
UNION ALL SELECT '2x12x10	 '
UNION ALL SELECT '12x7x22	 '
UNION ALL SELECT '26x30x26 '
UNION ALL SELECT '28x16x28 '
UNION ALL SELECT '15x19x11 '
UNION ALL SELECT '4x18x1	 '
UNION ALL SELECT '20x14x24 '
UNION ALL SELECT '6x10x22	 '
UNION ALL SELECT '9x20x3	 '
UNION ALL SELECT '14x9x27	 '
UNION ALL SELECT '26x17x9	 '
UNION ALL SELECT '10x30x28 '
UNION ALL SELECT '6x3x29	 '
UNION ALL SELECT '4x16x28	 '
UNION ALL SELECT '8x24x11	 '
UNION ALL SELECT '23x10x1	 '
UNION ALL SELECT '11x7x7	 '
UNION ALL SELECT '29x6x15	 '
UNION ALL SELECT '13x25x12 '
UNION ALL SELECT '29x14x3	 '
UNION ALL SELECT '26x22x21 '
UNION ALL SELECT '8x3x11	 '
UNION ALL SELECT '27x13x25 '
UNION ALL SELECT '27x6x2	 '
UNION ALL SELECT '8x11x7	 '
UNION ALL SELECT '25x12x9	 '
UNION ALL SELECT '24x30x12 '
UNION ALL SELECT '13x1x30	 '
UNION ALL SELECT '25x23x16 '
UNION ALL SELECT '9x13x29	 '
UNION ALL SELECT '29x26x16 '
UNION ALL SELECT '11x15x9	 '
UNION ALL SELECT '11x23x6	 '
UNION ALL SELECT '15x27x28 '
UNION ALL SELECT '27x24x21 '
UNION ALL SELECT '6x24x1	 '
UNION ALL SELECT '25x25x5	 '
UNION ALL SELECT '11x1x26	 '
UNION ALL SELECT '21x4x24	 '
UNION ALL SELECT '10x5x12	 '
UNION ALL SELECT '4x30x13	 '
UNION ALL SELECT '24x22x5	 '
UNION ALL SELECT '26x7x21	 '
UNION ALL SELECT '23x3x17	 '
UNION ALL SELECT '22x18x2	 '
UNION ALL SELECT '25x1x14	 '
UNION ALL SELECT '23x25x30 '
UNION ALL SELECT '8x7x7	 '
UNION ALL SELECT '30x19x8	 '
UNION ALL SELECT '17x6x15	 '
UNION ALL SELECT '2x11x20	 '
UNION ALL SELECT '8x3x22	 '
UNION ALL SELECT '23x14x26 '
UNION ALL SELECT '8x22x25	 '
UNION ALL SELECT '27x1x2	 '
UNION ALL SELECT '10x26x2	 '
UNION ALL SELECT '28x30x7	 '
UNION ALL SELECT '5x30x7	 '
UNION ALL SELECT '27x16x30 '
UNION ALL SELECT '28x29x1	 '
UNION ALL SELECT '8x25x18	 '
UNION ALL SELECT '20x12x29 '
UNION ALL SELECT '9x19x9	 '
UNION ALL SELECT '7x25x15	 '
UNION ALL SELECT '25x18x18 '
UNION ALL SELECT '11x8x2	 '
UNION ALL SELECT '4x20x6	 '
UNION ALL SELECT '18x5x20	 '
UNION ALL SELECT '2x3x29	 '
UNION ALL SELECT '25x26x22 '
UNION ALL SELECT '18x25x26 '
UNION ALL SELECT '9x12x16	 '
UNION ALL SELECT '18x7x27	 '
UNION ALL SELECT '17x20x9	 '
UNION ALL SELECT '6x29x26	 '
UNION ALL SELECT '17x7x19	 '
UNION ALL SELECT '21x7x5	 '
UNION ALL SELECT '29x15x12 '
UNION ALL SELECT '22x4x1	 '
UNION ALL SELECT '11x12x11 '
UNION ALL SELECT '26x30x4	 '
UNION ALL SELECT '12x24x13 '
UNION ALL SELECT '13x8x3	 '
UNION ALL SELECT '26x25x3	 '
UNION ALL SELECT '21x26x10 '
UNION ALL SELECT '14x9x26	 '
UNION ALL SELECT '20x1x7	 '
UNION ALL SELECT '11x12x3	 '
UNION ALL SELECT '12x11x4	 '
UNION ALL SELECT '11x15x30 '
UNION ALL SELECT '17x6x25	 '
UNION ALL SELECT '20x22x3	 '
UNION ALL SELECT '1x16x17	 '
UNION ALL SELECT '11x5x20	 '
UNION ALL SELECT '12x12x7	 '
UNION ALL SELECT '2x14x10	 '
UNION ALL SELECT '14x27x3	 '
UNION ALL SELECT '14x16x18 '
UNION ALL SELECT '21x28x24 '
UNION ALL SELECT '14x20x1	 '
UNION ALL SELECT '29x14x1	 '
UNION ALL SELECT '10x10x9	 '
UNION ALL SELECT '25x23x4	 '
UNION ALL SELECT '17x15x14 '
UNION ALL SELECT '9x20x26	 '
UNION ALL SELECT '16x2x17	 '
UNION ALL SELECT '13x28x25 '
UNION ALL SELECT '16x1x11	 '
UNION ALL SELECT '19x16x8	 '
UNION ALL SELECT '20x21x2	 '
UNION ALL SELECT '27x9x22	 '
UNION ALL SELECT '24x18x3	 '
UNION ALL SELECT '23x30x6	 '
UNION ALL SELECT '4x18x3	 '
UNION ALL SELECT '30x15x8	 '
UNION ALL SELECT '27x20x19 '
UNION ALL SELECT '28x29x26 '
UNION ALL SELECT '2x21x18	 '
UNION ALL SELECT '1x23x30	 '
UNION ALL SELECT '1x9x12	 '
UNION ALL SELECT '4x11x30	 '
UNION ALL SELECT '1x28x4	 '
UNION ALL SELECT '17x10x10 '
UNION ALL SELECT '12x14x6	 '
UNION ALL SELECT '8x9x24	 '
UNION ALL SELECT '8x3x3	 '
UNION ALL SELECT '29x8x20	 '
UNION ALL SELECT '26x29x2	 '
UNION ALL SELECT '29x25x25 '
UNION ALL SELECT '11x17x23 '
UNION ALL SELECT '6x30x21	 '
UNION ALL SELECT '13x18x29 '
UNION ALL SELECT '2x10x8	 '
UNION ALL SELECT '29x29x27 '
UNION ALL SELECT '27x15x15 '
UNION ALL SELECT '16x17x30 '
UNION ALL SELECT '3x3x22	 '
UNION ALL SELECT '21x12x6	 '
UNION ALL SELECT '22x1x5	 '
UNION ALL SELECT '30x8x20	 '
UNION ALL SELECT '6x28x13	 '
UNION ALL SELECT '11x2x23	 '
UNION ALL SELECT '14x18x27 '
UNION ALL SELECT '6x26x13	 '
UNION ALL SELECT '10x24x24 '
UNION ALL SELECT '4x24x6	 '
UNION ALL SELECT '20x8x3	 '
UNION ALL SELECT '23x11x5	 '
UNION ALL SELECT '29x5x24	 '
UNION ALL SELECT '14x15x22 '
UNION ALL SELECT '21x17x13 '
UNION ALL SELECT '10x10x8	 '
UNION ALL SELECT '1x11x23	 '
UNION ALL SELECT '21x19x24 '
UNION ALL SELECT '19x9x13	 '
UNION ALL SELECT '21x26x28 '
UNION ALL SELECT '25x11x28 '
UNION ALL SELECT '2x17x1	 '
UNION ALL SELECT '18x9x8	 '
UNION ALL SELECT '5x21x6	 '
UNION ALL SELECT '12x5x2	 '
UNION ALL SELECT '23x8x15	 '
UNION ALL SELECT '30x16x24 '
UNION ALL SELECT '7x9x27	 '
UNION ALL SELECT '16x30x7	 '
UNION ALL SELECT '2x21x28	 '
UNION ALL SELECT '5x10x6	 '
UNION ALL SELECT '8x7x1	 '
UNION ALL SELECT '28x13x5	 '
UNION ALL SELECT '11x5x14	 '
UNION ALL SELECT '26x22x29 '
UNION ALL SELECT '23x15x13 '
UNION ALL SELECT '14x2x16	 '
UNION ALL SELECT '22x21x9	 '
UNION ALL SELECT '4x20x3	 '
UNION ALL SELECT '18x17x19 '
UNION ALL SELECT '12x7x9	 '
UNION ALL SELECT '6x12x25	 '
UNION ALL SELECT '3x30x27	 '
UNION ALL SELECT '8x19x22	 '
UNION ALL SELECT '1x9x27	 '
UNION ALL SELECT '23x20x12 '
UNION ALL SELECT '14x7x29	 '
UNION ALL SELECT '9x12x12	 '
UNION ALL SELECT '30x2x6	 '
UNION ALL SELECT '15x7x16	 '
UNION ALL SELECT '19x13x18 '
UNION ALL SELECT '11x8x13	 '
UNION ALL SELECT '16x5x3	 '
UNION ALL SELECT '19x26x24 '
UNION ALL SELECT '26x8x21	 '
UNION ALL SELECT '21x20x7	 '
UNION ALL SELECT '15x1x25	 '
UNION ALL SELECT '29x15x21 '
UNION ALL SELECT '22x17x7	 '
UNION ALL SELECT '16x17x10 '
UNION ALL SELECT '6x12x24	 '
UNION ALL SELECT '8x13x27	 '
UNION ALL SELECT '30x25x14 '
UNION ALL SELECT '25x7x10	 '
UNION ALL SELECT '15x2x2	 '
UNION ALL SELECT '18x15x19 '
UNION ALL SELECT '18x13x24 '
UNION ALL SELECT '19x30x1	 '
UNION ALL SELECT '17x1x3	 '
UNION ALL SELECT '26x21x15 '
UNION ALL SELECT '10x10x18 '
UNION ALL SELECT '9x16x6	 '
UNION ALL SELECT '29x7x30	 '
UNION ALL SELECT '11x10x30 '
UNION ALL SELECT '6x11x2	 '
UNION ALL SELECT '7x29x23	 '
UNION ALL SELECT '13x2x30	 '
UNION ALL SELECT '25x27x13 '
UNION ALL SELECT '5x15x21	 '
UNION ALL SELECT '4x8x30	 '
UNION ALL SELECT '15x27x11 '
UNION ALL SELECT '27x1x6	 '
UNION ALL SELECT '2x24x11	 '
UNION ALL SELECT '16x20x19 '
UNION ALL SELECT '25x28x20 '
UNION ALL SELECT '6x8x4	 '
UNION ALL SELECT '27x16x11 '
UNION ALL SELECT '1x5x27	 '
UNION ALL SELECT '12x19x26 '
UNION ALL SELECT '18x24x14 '
UNION ALL SELECT '4x25x17	 '
UNION ALL SELECT '24x24x26 '
UNION ALL SELECT '28x3x18	 '
UNION ALL SELECT '8x20x28	 '
UNION ALL SELECT '22x7x21	 '
UNION ALL SELECT '24x5x28	 '
UNION ALL SELECT '23x30x29 '
UNION ALL SELECT '25x16x27 '
UNION ALL SELECT '28x10x30 '
UNION ALL SELECT '9x2x4	 '
UNION ALL SELECT '30x2x23	 '
UNION ALL SELECT '21x9x23	 '
UNION ALL SELECT '27x4x26	 '
UNION ALL SELECT '2x23x16	 '
UNION ALL SELECT '24x26x30 '
UNION ALL SELECT '26x1x30	 '
UNION ALL SELECT '10x4x28	 '
UNION ALL SELECT '11x29x12 '
UNION ALL SELECT '28x13x30 '
UNION ALL SELECT '24x10x28 '
UNION ALL SELECT '8x12x12	 '
UNION ALL SELECT '19x27x11 '
UNION ALL SELECT '11x28x7	 '
UNION ALL SELECT '14x6x3	 '
UNION ALL SELECT '6x27x5	 '
UNION ALL SELECT '6x17x14	 '
UNION ALL SELECT '24x24x17 '
UNION ALL SELECT '18x23x14 '
UNION ALL SELECT '17x5x7	 '
UNION ALL SELECT '11x4x23	 '
UNION ALL SELECT '5x1x17	 '
UNION ALL SELECT '26x15x24 '
UNION ALL SELECT '3x9x24	 '
UNION ALL SELECT '5x3x15	 '
UNION ALL SELECT '5x20x19	 '
UNION ALL SELECT '5x21x2	 '
UNION ALL SELECT '13x5x30	 '
UNION ALL SELECT '19x6x24	 '
UNION ALL SELECT '19x17x6	 '
UNION ALL SELECT '23x7x13	 '
UNION ALL SELECT '28x23x13 '
UNION ALL SELECT '9x1x6	 '
UNION ALL SELECT '15x12x16 '
UNION ALL SELECT '21x19x9	 '
UNION ALL SELECT '25x5x5	 '
UNION ALL SELECT '9x7x9	 '
UNION ALL SELECT '6x5x8	 '
UNION ALL SELECT '3x11x18	 '
UNION ALL SELECT '23x25x11 '
UNION ALL SELECT '25x4x6	 '
UNION ALL SELECT '4x27x1	 '
UNION ALL SELECT '4x3x3	 '
UNION ALL SELECT '30x11x5	 '
UNION ALL SELECT '9x17x12	 '
UNION ALL SELECT '15x6x24	 '
UNION ALL SELECT '10x22x15 '
UNION ALL SELECT '29x27x9	 '
UNION ALL SELECT '20x21x11 '
UNION ALL SELECT '18x10x5	 '
UNION ALL SELECT '11x2x2	 '
UNION ALL SELECT '9x8x8	 '
UNION ALL SELECT '1x26x21	 '
UNION ALL SELECT '11x11x16 '
UNION ALL SELECT '2x18x30	 '
UNION ALL SELECT '29x27x24 '
UNION ALL SELECT '27x8x18	 '
UNION ALL SELECT '19x3x17	 '
UNION ALL SELECT '30x21x26 '
UNION ALL SELECT '25x13x25 '
UNION ALL SELECT '20x22x1	 '
UNION ALL SELECT '10x1x12	 '
UNION ALL SELECT '11x17x15 '
UNION ALL SELECT '29x11x30 '
UNION ALL SELECT '17x30x27 '
UNION ALL SELECT '21x22x17 '
UNION ALL SELECT '13x6x22	 '
UNION ALL SELECT '22x16x12 '
UNION ALL SELECT '27x18x19 '
UNION ALL SELECT '4x13x6	 '
UNION ALL SELECT '27x29x10 '
UNION ALL SELECT '3x23x10	 '
UNION ALL SELECT '26x16x24 '
UNION ALL SELECT '18x26x20 '
UNION ALL SELECT '11x28x16 '
UNION ALL SELECT '21x6x15	 '
UNION ALL SELECT '9x26x17	 '
UNION ALL SELECT '8x15x8	 '
UNION ALL SELECT '3x7x10	 '
UNION ALL SELECT '2x28x8	 '
UNION ALL SELECT '1x2x24	 '
UNION ALL SELECT '7x8x9	 '
UNION ALL SELECT '19x4x22	 '
UNION ALL SELECT '11x20x9	 '
UNION ALL SELECT '12x22x16 '
UNION ALL SELECT '26x8x19	 '
UNION ALL SELECT '13x28x24 '
UNION ALL SELECT '4x10x16	 '
UNION ALL SELECT '12x8x10	 '
UNION ALL SELECT '14x24x24 '
UNION ALL SELECT '19x19x28 '
UNION ALL SELECT '29x1x15	 '
UNION ALL SELECT '10x5x14	 '
UNION ALL SELECT '20x19x23 '
UNION ALL SELECT '10x7x12	 '
UNION ALL SELECT '1x7x13	 '
UNION ALL SELECT '5x12x13	 '
UNION ALL SELECT '25x21x8	 '
UNION ALL SELECT '22x28x8	 '
UNION ALL SELECT '7x9x4	 '
UNION ALL SELECT '3x20x15	 '
UNION ALL SELECT '15x27x19 '
UNION ALL SELECT '18x24x12 '
UNION ALL SELECT '16x10x16 '
UNION ALL SELECT '22x19x8	 '
UNION ALL SELECT '15x4x3	 '
UNION ALL SELECT '9x30x25	 '
UNION ALL SELECT '1x1x6	 '
UNION ALL SELECT '24x4x25	 '
UNION ALL SELECT '13x18x29 '
UNION ALL SELECT '10x2x8	 '
UNION ALL SELECT '21x1x17	 '
UNION ALL SELECT '29x14x22 '
UNION ALL SELECT '17x29x11 '
UNION ALL SELECT '10x27x16 '
UNION ALL SELECT '25x16x15 '
UNION ALL SELECT '14x2x17	 '
UNION ALL SELECT '12x27x3	 '
UNION ALL SELECT '14x17x25 '
UNION ALL SELECT '24x4x1	 '
UNION ALL SELECT '18x28x18 '
UNION ALL SELECT '9x14x26	 '
UNION ALL SELECT '28x24x17 '
UNION ALL SELECT '1x26x12	 '
UNION ALL SELECT '2x18x20	 '
UNION ALL SELECT '12x19x22 '
UNION ALL SELECT '19x25x20 '
UNION ALL SELECT '5x17x27	 '
UNION ALL SELECT '17x29x16 '
UNION ALL SELECT '29x19x11 '
UNION ALL SELECT '16x2x4	 '
UNION ALL SELECT '23x24x1	 '
UNION ALL SELECT '19x18x3	 '
UNION ALL SELECT '28x14x6	 '
UNION ALL SELECT '18x5x23	 '
UNION ALL SELECT '9x24x12	 '
UNION ALL SELECT '15x4x6	 '
UNION ALL SELECT '15x7x24	 '
UNION ALL SELECT '22x15x8	 '
UNION ALL SELECT '22x1x22	 '
UNION ALL SELECT '6x4x22	 '
UNION ALL SELECT '26x1x30	 '
UNION ALL SELECT '8x21x27	 '
UNION ALL SELECT '7x1x11	 '
UNION ALL SELECT '9x8x18	 '
UNION ALL SELECT '20x27x12 '
UNION ALL SELECT '26x23x20 '
UNION ALL SELECT '26x22x30 '
UNION ALL SELECT '24x3x16	 '
UNION ALL SELECT '8x24x28	 '
UNION ALL SELECT '13x28x5	 '
UNION ALL SELECT '4x29x23	 '
UNION ALL SELECT '22x5x8	 '
UNION ALL SELECT '20x22x3	 '
UNION ALL SELECT '9x9x17	 '
UNION ALL SELECT '28x3x30	 '
UNION ALL SELECT '10x13x10 '
UNION ALL SELECT '10x25x13 '
UNION ALL SELECT '9x20x3	 '
UNION ALL SELECT '1x21x25	 '
UNION ALL SELECT '24x21x15 '
UNION ALL SELECT '21x5x14	 '
UNION ALL SELECT '13x8x20	 '
UNION ALL SELECT '29x17x3	 '
UNION ALL SELECT '5x17x28	 '
UNION ALL SELECT '16x12x7	 '
UNION ALL SELECT '23x1x24	 '
UNION ALL SELECT '4x24x29	 '
UNION ALL SELECT '23x25x14 '
UNION ALL SELECT '8x27x2	 '
UNION ALL SELECT '23x11x13 '
UNION ALL SELECT '13x4x5	 '
UNION ALL SELECT '24x1x26	 '
UNION ALL SELECT '21x1x23	 '
UNION ALL SELECT '10x12x12 '
UNION ALL SELECT '21x29x25 '
UNION ALL SELECT '27x25x30 '
UNION ALL SELECT '24x23x4	 '
UNION ALL SELECT '1x30x23	 '
UNION ALL SELECT '29x28x14 '
UNION ALL SELECT '4x11x30	 '
UNION ALL SELECT '9x25x10	 '
UNION ALL SELECT '17x11x6	 '
UNION ALL SELECT '14x29x30 '
UNION ALL SELECT '23x5x5	 '
UNION ALL SELECT '25x18x21 '
UNION ALL SELECT '8x7x1	 '
UNION ALL SELECT '27x11x3	 '
UNION ALL SELECT '5x10x8	 '
UNION ALL SELECT '11x1x11	 '
UNION ALL SELECT '16x17x26 '
UNION ALL SELECT '15x22x19 '
UNION ALL SELECT '16x9x6	 '
UNION ALL SELECT '18x13x27 '
UNION ALL SELECT '26x4x22	 '
UNION ALL SELECT '1x20x21	 '
UNION ALL SELECT '6x14x29	 '
UNION ALL SELECT '11x7x6	 '
UNION ALL SELECT '1x23x7	 '
UNION ALL SELECT '12x19x13 '
UNION ALL SELECT '18x21x25 '
UNION ALL SELECT '15x17x20 '
UNION ALL SELECT '23x8x9	 '
UNION ALL SELECT '15x9x26	 '
UNION ALL SELECT '9x12x9	 '
UNION ALL SELECT '12x13x14 '
UNION ALL SELECT '27x26x7	 '
UNION ALL SELECT '11x19x22 '
UNION ALL SELECT '16x12x21 '
UNION ALL SELECT '10x30x28 '
UNION ALL SELECT '21x2x7	 '
UNION ALL SELECT '12x9x18	 '
UNION ALL SELECT '7x17x14	 '
UNION ALL SELECT '13x17x17 '
UNION ALL SELECT '3x21x10	 '
UNION ALL SELECT '30x9x15	 '
UNION ALL SELECT '2x8x15	 '
UNION ALL SELECT '15x12x10 '
UNION ALL SELECT '23x26x9	 '
UNION ALL SELECT '29x30x10 '
UNION ALL SELECT '30x22x17 '
UNION ALL SELECT '17x26x30 '
UNION ALL SELECT '27x26x20 '
UNION ALL SELECT '17x28x17 '
UNION ALL SELECT '30x12x16 '
UNION ALL SELECT '7x23x15	 '
UNION ALL SELECT '30x15x19 '
UNION ALL SELECT '13x19x10 '
UNION ALL SELECT '22x10x4	 '
UNION ALL SELECT '17x23x10 '
UNION ALL SELECT '2x28x18	 '
UNION ALL SELECT '27x21x28 '
UNION ALL SELECT '24x26x5	 '
UNION ALL SELECT '6x23x25	 '
UNION ALL SELECT '17x4x16	 '
UNION ALL SELECT '14x1x13	 '
UNION ALL SELECT '23x21x11 '
UNION ALL SELECT '14x15x30 '
UNION ALL SELECT '26x13x10 '
UNION ALL SELECT '30x19x25 '
UNION ALL SELECT '26x6x26	 '
UNION ALL SELECT '9x16x29	 '
UNION ALL SELECT '15x2x24	 '
UNION ALL SELECT '13x3x20	 '
UNION ALL SELECT '23x12x30 '
UNION ALL SELECT '22x23x23 '
UNION ALL SELECT '8x21x2	 '
UNION ALL SELECT '18x28x5	 '
UNION ALL SELECT '21x27x14 '
UNION ALL SELECT '29x28x23 '
UNION ALL SELECT '12x30x28 '
UNION ALL SELECT '17x16x3	 '
UNION ALL SELECT '5x19x11	 '
UNION ALL SELECT '28x22x22 '
UNION ALL SELECT '1x4x28	 '
UNION ALL SELECT '10x10x14 '
UNION ALL SELECT '18x15x7	 '
UNION ALL SELECT '18x11x1	 '
UNION ALL SELECT '12x7x16	 '
UNION ALL SELECT '10x22x24 '
UNION ALL SELECT '27x25x6	 '
UNION ALL SELECT '19x29x25 '
UNION ALL SELECT '10x1x26	 '
UNION ALL SELECT '26x27x30 '
UNION ALL SELECT '4x23x19	 '
UNION ALL SELECT '24x19x4	 '
UNION ALL SELECT '21x11x14 '
UNION ALL SELECT '4x13x27	 '
UNION ALL SELECT '9x1x11	 '
UNION ALL SELECT '16x20x8	 '
UNION ALL SELECT '4x3x11	 '
UNION ALL SELECT '1x16x12	 '
UNION ALL SELECT '14x6x30	 '
UNION ALL SELECT '8x1x10	 '
UNION ALL SELECT '11x18x7	 '
UNION ALL SELECT '29x28x30 '
UNION ALL SELECT '4x21x8	 '
UNION ALL SELECT '3x21x4	 '
UNION ALL SELECT '6x1x5	 '
UNION ALL SELECT '26x18x3	 '
UNION ALL SELECT '28x27x27 '
UNION ALL SELECT '17x3x12	 '
UNION ALL SELECT '6x1x22	 '
UNION ALL SELECT '23x12x28 '
UNION ALL SELECT '12x13x2	 '
UNION ALL SELECT '11x2x13	 '
UNION ALL SELECT '7x1x28	 '
UNION ALL SELECT '27x6x25	 '
UNION ALL SELECT '14x14x3	 '
UNION ALL SELECT '14x11x20 '
UNION ALL SELECT '2x27x7	 '
UNION ALL SELECT '22x24x23 '
UNION ALL SELECT '7x15x20	 '
UNION ALL SELECT '30x6x17	 '
UNION ALL SELECT '20x23x25 '
UNION ALL SELECT '18x16x27 '
UNION ALL SELECT '2x9x6	 '
UNION ALL SELECT '9x18x19	 '
UNION ALL SELECT '20x11x22 '
UNION ALL SELECT '11x16x19 '
UNION ALL SELECT '14x29x23 '
UNION ALL SELECT '14x9x20	 '
UNION ALL SELECT '8x10x12	 '
UNION ALL SELECT '18x17x6	 '
UNION ALL SELECT '28x7x16	 '
UNION ALL SELECT '12x19x28 '
UNION ALL SELECT '5x3x16	 '
UNION ALL SELECT '1x25x10	 '
UNION ALL SELECT '4x14x10	 '
UNION ALL SELECT '9x6x3	 '
UNION ALL SELECT '15x27x28 '
UNION ALL SELECT '13x26x14 '
UNION ALL SELECT '21x8x25	 '
UNION ALL SELECT '29x10x20 '
UNION ALL SELECT '14x26x30 '
UNION ALL SELECT '25x13x28 '
UNION ALL SELECT '1x15x23	 '
UNION ALL SELECT '6x20x21	 '
UNION ALL SELECT '18x2x1	 '
UNION ALL SELECT '22x25x16 '
UNION ALL SELECT '23x25x17 '
UNION ALL SELECT '2x14x21	 '
UNION ALL SELECT '14x25x16 '
UNION ALL SELECT '12x17x6	 '
UNION ALL SELECT '19x29x15 '
UNION ALL SELECT '25x9x6	 '
UNION ALL SELECT '19x17x13 '
UNION ALL SELECT '24x22x5	 '
UNION ALL SELECT '19x4x13	 '
UNION ALL SELECT '10x18x6	 '
UNION ALL SELECT '6x25x6	 '
UNION ALL SELECT '23x24x20 '
UNION ALL SELECT '8x22x13	 '
UNION ALL SELECT '25x10x29 '
UNION ALL SELECT '5x12x25	 '
UNION ALL SELECT '20x5x11	 '
UNION ALL SELECT '7x16x29	 '
UNION ALL SELECT '29x24x22 '
UNION ALL SELECT '28x20x1	 '
UNION ALL SELECT '10x27x10 '
UNION ALL SELECT '6x9x27	 '
UNION ALL SELECT '26x15x30 '
UNION ALL SELECT '26x3x19	 '
UNION ALL SELECT '20x11x3	 '
UNION ALL SELECT '26x1x29	 '
UNION ALL SELECT '6x23x4	 '
UNION ALL SELECT '6x13x21	 '
UNION ALL SELECT '9x23x25	 '
UNION ALL SELECT '15x1x10	 '
UNION ALL SELECT '29x12x13 '
UNION ALL SELECT '7x8x24	 '
UNION ALL SELECT '29x30x27 '
UNION ALL SELECT '3x29x19	 '
UNION ALL SELECT '14x16x17 '
UNION ALL SELECT '4x8x27	 '
UNION ALL SELECT '26x17x8	 '
UNION ALL SELECT '10x27x17 '
UNION ALL SELECT '11x28x17 '
UNION ALL SELECT '17x16x27 '
UNION ALL SELECT '1x8x22	 '
UNION ALL SELECT '6x30x16	 '
UNION ALL SELECT '7x30x22	 '
UNION ALL SELECT '20x12x3	 '
UNION ALL SELECT '18x10x2	 '
UNION ALL SELECT '20x21x26 '
UNION ALL SELECT '11x1x17	 '
UNION ALL SELECT '9x15x15	 '
UNION ALL SELECT '19x14x30 '
UNION ALL SELECT '24x22x20 '
UNION ALL SELECT '11x26x23 '
UNION ALL SELECT '14x3x23	 '
UNION ALL SELECT '1x28x29	 '
UNION ALL SELECT '29x20x4	 '
UNION ALL SELECT '1x4x20	 '
UNION ALL SELECT '12x26x8	 '
UNION ALL SELECT '14x11x14 '
UNION ALL SELECT '14x19x13 '
UNION ALL SELECT '15x13x24 '
UNION ALL SELECT '16x7x26	 '
UNION ALL SELECT '11x20x11 '
UNION ALL SELECT '5x24x26	 '
UNION ALL SELECT '24x25x7	 '
UNION ALL SELECT '21x3x14	 '
UNION ALL SELECT '24x29x20 '
UNION ALL SELECT '7x12x1	 '
UNION ALL SELECT '16x17x4	 '
UNION ALL SELECT '29x16x21 '
UNION ALL SELECT '28x8x17	 '
UNION ALL SELECT '11x30x25 '
UNION ALL SELECT '1x26x23	 '
UNION ALL SELECT '25x19x28 '
UNION ALL SELECT '30x24x5	 '
UNION ALL SELECT '26x29x15 '
UNION ALL SELECT '4x25x23	 '
UNION ALL SELECT '14x25x19 '
UNION ALL SELECT '29x10x7	 '
UNION ALL SELECT '29x29x28 '
UNION ALL SELECT '19x13x24 '
UNION ALL SELECT '21x28x5	 '
UNION ALL SELECT '8x15x24	 '
UNION ALL SELECT '1x10x12	 '
UNION ALL SELECT '2x26x6	 '
UNION ALL SELECT '14x14x4	 '
UNION ALL SELECT '10x16x27 '
UNION ALL SELECT '9x17x25	 '
UNION ALL SELECT '25x8x7	 '
UNION ALL SELECT '1x9x28	 '
UNION ALL SELECT '10x8x17	 '
UNION ALL SELECT '4x12x1	 '
UNION ALL SELECT '17x26x29 '
UNION ALL SELECT '23x12x26 '
UNION ALL SELECT '2x21x22	 '
UNION ALL SELECT '18x23x13 '
UNION ALL SELECT '1x14x5	 '
UNION ALL SELECT '25x27x26 '
UNION ALL SELECT '4x30x30	 '
UNION ALL SELECT '5x13x2	 '
UNION ALL SELECT '17x9x6	 '
UNION ALL SELECT '28x18x28 '
UNION ALL SELECT '7x30x2	 '
UNION ALL SELECT '28x22x17 '
UNION ALL SELECT '14x15x14 '
UNION ALL SELECT '10x14x19 '
UNION ALL SELECT '6x15x22	 '
UNION ALL SELECT '27x4x17	 '
UNION ALL SELECT '28x21x6	 '
UNION ALL SELECT '19x29x26 '
UNION ALL SELECT '6x17x17	 '
UNION ALL SELECT '20x13x16 '
UNION ALL SELECT '25x4x1	 '
UNION ALL SELECT '2x9x5	 '
UNION ALL SELECT '30x3x1	 '
UNION ALL SELECT '24x21x2	 '
UNION ALL SELECT '14x19x12 '
UNION ALL SELECT '22x5x23	 '
UNION ALL SELECT '14x4x21	 '
UNION ALL SELECT '10x2x17	 '
UNION ALL SELECT '3x14x10	 '
UNION ALL SELECT '17x5x3	 '
UNION ALL SELECT '22x17x13 '
UNION ALL SELECT '5x19x3	 '
UNION ALL SELECT '29x22x6	 '
UNION ALL SELECT '12x28x3	 '
UNION ALL SELECT '9x21x25	 '
UNION ALL SELECT '10x2x14	 '
UNION ALL SELECT '13x26x7	 '
UNION ALL SELECT '18x23x2	 '
UNION ALL SELECT '9x14x17	 '
UNION ALL SELECT '21x3x13	 '
UNION ALL SELECT '13x23x9	 '
UNION ALL SELECT '1x20x4	 '
UNION ALL SELECT '11x4x1	 '
UNION ALL SELECT '19x5x30	 '
UNION ALL SELECT '9x9x29	 '
UNION ALL SELECT '26x29x14 '
UNION ALL SELECT '1x4x10	 '
UNION ALL SELECT '7x27x30	 '
UNION ALL SELECT '8x3x23	 '
UNION ALL SELECT '1x27x27	 '
UNION ALL SELECT '7x27x27	 '
UNION ALL SELECT '1x26x16	 '
UNION ALL SELECT '29x16x14 '
UNION ALL SELECT '18x6x12	 '
UNION ALL SELECT '24x24x24 '
UNION ALL SELECT '26x2x19	 '
UNION ALL SELECT '15x17x4	 '
UNION ALL SELECT '11x7x14	 '
UNION ALL SELECT '14x19x10 '
UNION ALL SELECT '9x10x1	 '
UNION ALL SELECT '14x17x9	 '
UNION ALL SELECT '20x19x13 '
UNION ALL SELECT '25x20x8	 '
UNION ALL SELECT '24x20x21 '
UNION ALL SELECT '26x30x2	 '
UNION ALL SELECT '24x2x10	 '
UNION ALL SELECT '28x4x13	 '
UNION ALL SELECT '27x17x11 '
UNION ALL SELECT '15x3x8	 '
UNION ALL SELECT '11x29x10 '
UNION ALL SELECT '26x15x16 '
UNION ALL SELECT '4x28x22	 '
UNION ALL SELECT '7x5x22	 '
UNION ALL SELECT '10x28x9	 '
UNION ALL SELECT '6x28x13	 '
UNION ALL SELECT '10x5x6	 '
UNION ALL SELECT '20x12x6	 '
UNION ALL SELECT '25x30x30 '
UNION ALL SELECT '17x16x14 '
UNION ALL SELECT '14x20x3	 '
UNION ALL SELECT '16x10x8	 '
UNION ALL SELECT '9x28x14	 '
UNION ALL SELECT '16x12x12 '
UNION ALL SELECT '11x13x25 '
UNION ALL SELECT '21x16x28 '
UNION ALL SELECT '10x3x18	 '
UNION ALL SELECT '5x9x20	 '
UNION ALL SELECT '17x23x5	 '
UNION ALL SELECT '3x13x16	 '
UNION ALL SELECT '29x30x17 '
UNION ALL SELECT '2x2x8	 '
UNION ALL SELECT '15x8x30	 '
UNION ALL SELECT '20x1x16	 '
UNION ALL SELECT '23x10x29 '
UNION ALL SELECT '4x5x4	 '
UNION ALL SELECT '6x18x12	 '
UNION ALL SELECT '26x10x22 '
UNION ALL SELECT '21x10x17 '
UNION ALL SELECT '26x12x29 '
UNION ALL SELECT '7x20x21	 '
UNION ALL SELECT '18x9x15	 '
UNION ALL SELECT '10x23x20 '
UNION ALL SELECT '20x1x27	 '
UNION ALL SELECT '10x10x3	 '
UNION ALL SELECT '25x12x23 '
UNION ALL SELECT '30x11x15 '
UNION ALL SELECT '16x22x3	 '
UNION ALL SELECT '22x10x11 '
UNION ALL SELECT '15x10x20 '
UNION ALL SELECT '2x20x17	 '
UNION ALL SELECT '20x20x1	 '
UNION ALL SELECT '24x16x4	 '
UNION ALL SELECT '23x27x7	 '
UNION ALL SELECT '7x27x22	 '
UNION ALL SELECT '24x16x8	 '
UNION ALL SELECT '20x11x25 '
UNION ALL SELECT '30x28x11 '
UNION ALL SELECT '21x6x24	 '
UNION ALL SELECT '15x2x9	 '
UNION ALL SELECT '16x30x24 '
UNION ALL SELECT '21x27x9	 '
UNION ALL SELECT '7x19x8	 '
UNION ALL SELECT '24x13x28 '
UNION ALL SELECT '12x26x28 '
UNION ALL SELECT '16x21x11 '
UNION ALL SELECT '25x5x13	 '
UNION ALL SELECT '23x3x17	 '
UNION ALL SELECT '23x1x17	 '
UNION ALL SELECT '4x17x18	 '
UNION ALL SELECT '17x13x18 '
UNION ALL SELECT '25x12x19 '
UNION ALL SELECT '17x4x19	 '
UNION ALL SELECT '4x21x26	 '
UNION ALL SELECT '6x28x1	 '
UNION ALL SELECT '23x22x15 '
UNION ALL SELECT '6x23x12	 '
UNION ALL SELECT '21x17x9	 '
UNION ALL SELECT '30x4x23	 '
UNION ALL SELECT '2x19x21	 '
UNION ALL SELECT '28x24x7	 '
UNION ALL SELECT '19x24x14 '
UNION ALL SELECT '13x20x26 '
UNION ALL SELECT '19x24x29 '
UNION ALL SELECT '8x26x3	 '
UNION ALL SELECT '16x12x14 '
UNION ALL SELECT '17x4x21	 '
UNION ALL SELECT '8x4x20	 '
UNION ALL SELECT '13x27x17 '
UNION ALL SELECT '9x21x1	 '
UNION ALL SELECT '29x25x6	 '
UNION ALL SELECT '7x9x26	 '
UNION ALL SELECT '13x25x5	 '
UNION ALL SELECT '6x9x21	 '
UNION ALL SELECT '12x10x11 '
UNION ALL SELECT '30x28x21 '
UNION ALL SELECT '15x6x2	 '
UNION ALL SELECT '8x18x19	 '
UNION ALL SELECT '26x20x24 '
UNION ALL SELECT '26x17x14 '
UNION ALL SELECT '27x8x1	 '
UNION ALL SELECT '19x19x18 '
UNION ALL SELECT '25x24x27 '
UNION ALL SELECT '14x29x15 '
UNION ALL SELECT '22x26x1	 '
UNION ALL SELECT '14x17x9	 '
UNION ALL SELECT '2x6x23	 '
UNION ALL SELECT '29x7x5	 '
UNION ALL SELECT '14x16x19 '
UNION ALL SELECT '14x21x18 '
UNION ALL SELECT '10x15x23 '
UNION ALL SELECT '21x29x14 '
UNION ALL SELECT '20x29x30 '
UNION ALL SELECT '23x11x5	 ';

WITH 
TrimmedDims AS
(
	SELECT Dims = RTRIM(REPLACE(Dims, '	', ''))
	FROM #Input
)
, XLoc AS 
(
	SELECT Dims
		, FirstX = charindex('x', Dims)
		, SecondX = LEN(Dims) - charindex('x', REVERSE(Dims)) + 1
	FROM TrimmedDims
)
, Dims AS 
(
	SELECT BoxL = CAST(LEFT(Dims, FirstX - 1) as Int)
		, BoxW = CAST(SUBSTRING(Dims, FirstX + 1, SecondX - FirstX - 1) as Int)
		, BoxH = Right(Dims, LEN(Dims) - SecondX)
	FROM XLoc
)
, Measurements As 
(
	SELECT LW = BoxL * BoxW 
		, LH = BoxL * BoxH 
		, WH = BoxW * BoxH 
		, Slack = CASE WHEN BoxL >= BoxW AND BoxL >= BoxH THEN BoxH * BoxW 
				WHEN BoxW >= BoxH THEN BoxL * BoxH 
				ELSE BoxL * BoxW 
				END
		, RibbonWrap = CASE WHEN BoxL >= BoxW AND BoxL >= BoxH THEN BoxH * 2 + BoxW * 2
				WHEN BoxW >= BoxH THEN BoxL * 2 + BoxH * 2
				ELSE BoxL * 2 + BoxW * 2
				END
		, RibbonBow = BoxL * BoxW * BoxH 
	FROM Dims 
)
SELECT Wrapping =  SUM(LW * 2 
		+ LH * 2 
		+ WH * 2 
		+ Slack)
	, Ribbon = SUM(RibbonWrap + RibbonBow)
FROM Measurements;

*Update – I realized there was a part 2 to each day of code, so I updated the code above to figure out how much ribbon to order, too.  It was just adding the last two columns to the last CTE and the last column to the final SELECT statement.  BTW, I have never voluntarily written so many CTE’s in my life…this is about as normal as helping elves with math.

Let me know what you’d do differently. Also, if there’s interest, I also did the next couple days and could make posts for those as well.

Use Compression to Combine Data Quality and Performance

We’ve been using the wrong data types for all the wrong reasons.  DBAs, developers, data architects, etc. have all been told to keep data as narrow as possible, using the smallest data type to get the job done.  We sacrificed the integrity of our data for the performance of our data, and I’d like to thank Karen López (b|t) for pointing out that performance is NOT our #1 Job.

Disclaimer – Enterprise-ONLY

I’m talking about compression again, so I’m talking about my disclaimer again.  Compression is an Enterprise-ONLY feature that affects the structure of your data.  That means you can’t even restore a backup of your database to anything other than enterprise or developer editions unless you remove compression before the backup.

Also, this post is about maximizing the benefits of compression to reduce the costs of proper data architecture, not implementing compression blindly.  Test it on non-prod, test it again on non-prod, then only implement a documented change with a rollback plan.

Karen López Made Me Do It!

Wait a minute, it’s Lopez, not López, isn’t it?  Nope, she actually spells her name differently because there are so many programs out there that think we can get by with 26 letters in the alphabet.  To keep Karen happy (it’s in your best interest), we need to change our LastName column from VarChar(50) to NVarChar(50).  I know full well that I’m doubling the size of my data for every row, not just Karen’s, to make sure people don’t have to change their names to make me happy.

I’m wrong about half of that last sentence…. Yes, Unicode data is 2 bytes per character uncompressed while non-Unicode data is only 1 byte per character.  The key word being uncompressed, because that’s not your only option starting in SQL Server 2008 R2 (as opposed to 2008 when we got compression in general).  Look at what BOL has to say about Unicode Compression Implementation for more info on that, with the most important part being that you have to implement at least row-level compression to gain this advantage.

Now we can accommodate Karen without complaints.  After all, why should we complain?  She’s a great person to have around.

Running Out Of Numbers

Keeping up with my Karen López theme, she made a great presentation with Tom LaRock (b|t) called The Ticking Timebombs in Your Database that focused a lot on picking the right data type for identity columns.  Spoiler Alert…know your data well enough to choose either BigInt or Int for an identity column, then start with the maximum negative value for that type.

Look at your existing databases to see if you’re running out of numbers using Tom’s post SQL Server Identity Values Check.  You might find that you already have a case where there’s a 4-byte column about to hit the maximum number for an Int.  Maybe it wasn’t seeded with a big negative number, or maybe it’s because the database was just more active than it was designed for.  The important part is that this timebomb is quietly ticking down to the 3 AM phone call Tom just warned us about.

Now I’d like to stand in opposition to Karen and Tom a bit by suggesting starting a BigInt at the maximum negative value for Int.  Say you’re pretty sure an Int is big enough, but you’re not willing to bet your career on it.  However, you really like the idea of a 4-byte column as opposed to an 8-byte column.  Here’s what you do…  Make the column a BigInt (you’re not betting your career here), start it at -2,147,483,648 (maximum negative value for Int), increment by 1, and use compression.

Since the value is in the range of a 4-byte signed Int, the compressed stored value of this column will be 4 bytes.  If you happen to go over 2,147,483,647 then you keep your job (probably a good thing) and compression just loses a little of its magic when this column starts using up 8 bytes.

By the time you get to the point that the data can’t be compressed going forward, row-level compression for this single column only considering a single index only at the leaf level has saved you 16 GB.  Because it’s likely to be part of the clustered index key, it’s going to be part of all of your nonclustered indexes.  Say you have 4 nonclustered indexes on this table, each compressed giving you another 16 GB of savings.  So you saved 80 GB of disk space which is also less index space fighting for space in memory on your server.

You lose about half of your potential values when you use this method, but how much does that matter when I’d have to look up the name of my maximum value which is 9,223,372,036,854,775,807.  The important part is you avoided the extra cost of a BigInt while not failing miserably if you exceeded the maximum value of an Int.

I have kids and don’t get enough sleep as it is.  A compressed BigInt lets me sleep knowing I have both great performance and a large pool of numbers.

Why the Obsession with Karen?

Karen happens to be the perfect example.  She’s a great data architect who spells her name wrong to accommodate for not-so-great data architects.  Follow that up by her making presentations on using larger numeric data types to avoid running out of numbers.  Then I stumbled across her post about my #1 Job when I had compression on my mind, which cause me to make the correlation between compressing the data to get the performance along with the data quality.  Add to all this that she’s one of the most outspoken people (end of sentence).

It’s worth giving her (b|t) links again, with a little flare this time.  Her blog can be found at DataModel.com, and it’s a must-read for anyone creating or altering a column.  If you’re creating or altering lots of columns, then she’s a must-hire consultant.  She tweets like a bot with ADHD as @DataChick.  Unlike a bot, her tweets are pertinent and interesting…sometimes too interesting.

Just don’t ask her about her dad’s middle name, Canadian “zip codes”, the joys of air travel, or shoes.

Indexing Strategy

What do I care about when I’m playing with indexes? That’s easy. I want as few indexes as possible efficiently referenced by as many pertinent, well-tuned, consistently written queries as is reasonable. It’s explaining that last sentence that’s the hard part.

The thing that will jump out to most people is that my goal doesn’t mention a specific, single query that you want to run great.  Although that’s something I hope to achieve, it only becomes a priority as a last resort.  I’m more concerned with already having that data in memory because the index is being used by many queries, and also having fewer indexes to make data modifications more efficient. There’s more to it than that, but the detail belongs in the body of this post, not the intro.

If I was writing how to make a perfect index for a single reference to a table in a single query, this post could be done rather well in a couple paragraphs. Even though I’m focusing only on OLTP (ruling out columnstore indexes), in 99.999% of environments (ruling out in-memory hash indexes), and not getting into details of filtered indexes or indexed views, there’s still a lot to consider to the point that the first few paragraphs will just be what we’re going to keep in mind. I hope you didn’t have other plans today…

Does this advice apply to you?

It depends! Just kidding, I hate that (non) answer.

There are two targets audiences for this.  While it’s useful to everyone, you’d have to hit both of them for this to be perfect for you. First target is the person; this is written for someone who is comfortable working with indexes for single queries but wants a better view of the big picture. Second target is the database, which is a rather typical in-house OLTP database.

  • More data than you have memory
  • Writes throughout the day, especially in your larger tables
  • Read and write performance matter more than disk space
  • No extreme write loads, such as truncating and repopulating your largest table (easy fix, disable and rebuild your indexes around this action)
  • You have the ability to make indexing changes (this goes beyond what you can typically do with vendor databases)
  • Hopefully, you have the ability to make some code changes

If you or your database aren’t the perfect audience, don’t worry about it.  The most important things to know are what to keep in mind and how those things are interconnected.  The real goal is making more intelligent decisions for your databases, not fitting into a specific mold.

PreReqs!!!

Because this is an advanced look at the fundamentals of indexing strategy, you shouldn’t take offense if you have to do some prerequisite work for all of this to come together. If I give you a severe headache consider reading this stuff first, and the book on the list is well worth a second read cover-to-cover.

PreReqs:

Even with that I’ll probably still give you a headache (a common theme for me), and you’ll probably have questions. Keep in mind that some of the greatest compliments you can give someone are asking them a question and offering them large sums of cash. Although I don’t hand out my LLC’s address to send checks to unless I’ve actually looked over your indexes, I do have a free comments section below that I’d like to see used more often.

Something to consider

Here are all the things we’ll be considering. This is a great list, but nothing is ever going to be 100% all-inclusive or perfect.

Tune your queries: The ideal set of indexes for a poorly written query may be 100% different than the same query after it’s refactored.  We don’t want to constantly tinker with indexes, so this process is best if you tune your most expensive queries first.  An old, unpublished concept for this post had creating an index as a 13 step process with steps 1-11 avoiding indexes through tuning, step 12 making an index, and step 13 seeing if you could delete any other indexes.  That really wasn’t too different from Brent Ozar’s (b|t) Be Creepy method.  Indexing is not the only answer, and not the first answer either.

Query Importance: Some queries just need to complete, some need to run decently well, and some need to run as close to instantly as possible. Knowing how critical your query is will weigh in on how you index, but none of them, not even the most critical queries, will have their performance be the only deciding factor on what indexes you should have. Some outliers will require you to go back and create a specific index for them as a last resort, but there’s no reason to concern ourselves with last resorts before we get started.  If an index would work, even somewhat inefficiently, and it’ll already be in cache, do we want to create another index that will fight it for space in cache?  Don’t create another index to get your most critical query to 0.1 seconds when it’s running at 0.2 seconds and the business is happy with anything under 1.0 second.

Query Consistency: Are you querying the table the same way in all of your queries? If not, do you know you are stressing me out? Do you not care?  Using the same columns to join where possible matters, even if you could live without one of them in some cases because consistent queries mean index reusability.

Query Frequency: Some queries run five times a second, some run once a year. They aren’t even close to the same, and shouldn’t be treated the same.

Query Timing: It is different from frequency. Once-a-day is not just once-a-day. An “8-5 is critical” shop where all your users are sleeping at 3 AM means that we care much less about collateral damage from a 3 AM query. If we do a table scan on a huge clustered index at 3 AM that kicks everything out of cache it might not matter much, but do that same thing at 3 PM and we may want to consider an index even if it’s only used for a single query.

Query Justification: That query timing example threw up a red flag to me. Do we need to run that query at all? Does it need to run in prod, or is there a reporting database I can run it against? Should I consider making a reporting database? Does it need to run at 3 PM? Question the outliers that would change your indexing strategy for both if they need to run and if they could use a little T-SQL help.

Insert / Update / Delete performance: The more indexes you have, the slower your data modifications will be because they have to be written more than once. Wider indexes will be more overhead due to updates hitting it more often, larger index to maintain, and fewer rows per page of data.

Reusability: How many queries can use this index, and how will each of them use it? Is a query using it differently because it’s not referencing the table consistently or because it’s legitimately doing something different? This part is HUGE, and is really going to be a major focus. I didn’t give you a hard time on that query consistency point asking if you cared for no reason!

Memory usage: How much memory is being used, where is it being used, and why? Is that memory being used to fulfill multiple queries (see Reusability, which references Query Consistency, which goes back to Query Tuning)? Could we use less memory if we had a descent index? Is the query that requires all that memory justified and timed properly? These points are starting to mix together and reference themselves, aren’t they? Is indexing strategy an infinite loop?!?!? Yes, yes it is.

Key Lookups: For the queries that use this index, is this a covering index? If not, what would it need to be a covering index? We’ll look at these things: how critical is each query, how often is that query run, how many key lookups does it do, how wide are the total columns we would need to add to be covering, how often are each of those columns updated, what other queries would take advantage of having those columns in there, and is there any filtering being done on those columns?

Maintenance: It’s easy to see having fewer, more narrow indexes would make index rebuilds, index reorgs, and database backups quicker and easier. How about key column order and compression?

TDE: What’s this feature doing in an indexing article?

SQL Edition: Index compression is going to be the biggest one for us today. Online rebuilds can make a big difference, too, but it rarely makes a big difference in what indexes you want.

Pick a table, any table

We’re not going to change the entire database at once.  Partially because it’s overwhelming to you, but mostly because it’s lower risk that’s easier to troubleshoot and roll back if needed. So we’ll pick a single table that we want to have run more efficiently, make a change or two to it, then do it again with either the same table or a different one.

I’m not consistent on how I pick tables.  Although I usually pick one of the ones that’s the largest in the buffer pool that I haven’t made as efficient as I could already, which you can find using the query in  my Cleaning Up the Buffer Pool post.  However, that’s not always how I pick a table.  I’ll also start this off with a query that I wish was running faster, run it with SET STATISTICS IO, TIME ON to see what tables are getting hit in the slowest part, and work on a table that’s getting hit inefficiently here.  There’s no magic to it, just pick a table and reassure the other tables that they’ll get their turn later.

It looks like an infinite loop at first glance because I’ll keep picking tables and loop back to revisit table later, but it’s better to think of it as an upward spiral. That first trip around will give you all kinds of awesome, the second trip will add on to that, and each trip around yields less of an improvement. You could stop wherever you wanted if it wasn’t addictive.

Also as the size of your data changes, the queries hitting your database change, and more, it’s unreasonable to expect your indexing strategy to stay 100% the same.  This isn’t a job where you can ever say you’re really done, just in a better place than you were last week.

I have a table, now what?

At the times of day you want your database to perform great, what’s happening with your table? This may be anything that ever runs on the database for some places, and it may be anything that runs between 8 AM and 5 PM for others.

I’m being as all inclusive as possible by looking at everything that touches the table, so this won’t be as quick and easy as you’d think. Yes, my target audience for this post can create an index for a single query in minutes while I typically spend well over an hour on a single table; how fast you make it through this project isn’t my primary concern.

Once you picked a table to work on look in the proc cache to see what references the indexes on the table.  My query to do that in the same Cleaning Up the Buffer Pool post is good for this, but the one in Querying the Plan Cache is better for viewing an entire table at once. This has every cacheable plan that ran since the last restart of services and hasn’t been forced out of memory. Remember this is a really CPU intensive query that will take several minutes to run and needs to run against prod at a time of day you care about to provide what you need. If you have an extremely busy hour or two, run this as soon as things start to calm down.

Note, there were three different ways something could have avoided you seeing it in the proc cache, and that wasn’t counting if you turned on the typically recommended setting “Optimize for ad-hoc workload” that Kimberly Tripp (b|t) wrote about where you can miss the first run of ad-hoc queries in favor of keeping your memory cleaner. It’s also possible that a query is getting a different plan each time it gets compiled due to stats changing or parameter sniffing, but that affects us a little less since we’re going to make index changes that will change those plans anyways.

The proc cache query is also only capturing index usage. It does capture RID lookups, but not table scans against a heap. You’ll have to look at the modified scripts I put in Querying the Plan Cache to see the table scans because they’re stored differently in the XML.

For now, let’s focus on how things can sneak past our cache and how we can find them.

1 & 2: Was not run since the last restart of services or was forced out of memory. It can be in cache, it’s just not there right now. For that, we’re going to check back on the cache multiple times, and we’re also going to make one or two index changes at a time which will again have us checking back multiple times.

3: Uncacheable plans can happen for several reasons, with the most popular in my experience being temp tables where data was loaded into it then an index was created on the temp table. Whatever the reason, start up extended events or a trace and watch for sql_statement_recompile to help hunt them down. Take this list and search for references of your table to know which ones are relevant. To get bonus points (I’m not keeping score), find out why it’s not getting along with your cache and see if it’s something that should be fixed.

To make things a little more difficult in that step, you’ll also have to look for references to views and functions that reference the table. The views and functions will show up in my Proc Contains Text query, and you’ll have to iterate through that process again.

Keep in mind, this will never be perfect and 100% all-inclusive. I know I said that before, but I need some of the important details repeated to me before they sink in and I have to assume there are others like me. It will be very rare for this to pick up an ad-hoc query that runs for year-end processing. You can use your imagination to find 10 other ways you’ll miss something and still be shocked when a new way pops up.

However, we have enough to move forward, and we’re going to accept the rest as acceptable risk. If you don’t accept some risk you’ll never accept any rewards, it’s just a matter of reducing the risk and knowing enough to make an intelligent decision.

Now that you know what’s running, how is each one referencing the table? Looking at the proc cache, you’ll see predicates and seek predicates, which you’ll combine on a list. You’re going to have to run the stuff that didn’t make it into the proc cache manually on a test server and add them to the list as well.

This is completely overwhelming to do all of it.  The more you do, the more accurate your results will be, but it’s not actually reasonable.  Do what’s reasonable, understand that you’re trading off some level of accuracy for time, and also understand that if you don’t make that tradeoff then you’ll never have time for anything else…not even going home at night.

Here’s what the list could use:

  • Proc or name of SQL Batch
  • How important is it
  • How often does it run
  • When does it run
  • Predicates and Seek Predicates (let’s just call them predicates)
  • Equality columns
  • Range columns
  • Inequality columns
  • Column’s returned
  • Rows returned

If there was a RID or Key Lookup on a reference to a nonclustered index, add the output columns and predicate (not the seek predicate for this case only) from the lookup on here as well.  The seek predicate on a lookup is just the clustered index key or RID bookmark added as hidden key columns on your nonclustered index; they will not help you here.

Now look at this list and look for consistencies. What equality predicates are used a lot? You should be able to find different groups of equality predicates that can accommodate most of your queries, and those are going to be the key columns you’ll consider for your indexes. The first key column is going to be the column all of the queries you want to use this index have in common as an equality column, then iterate through them as the columns are used less and less.

This is not the traditional “order of cardinality” advice I’m sure you’ve heard when creating an index for a specific query, but we’re designing an index for your database, not your query. I’ll go one step further and say if it’s a toss-up between what’s the first key column, consider making it one that’s added sequentially such as DateAdded or ID on tables that see more updates because that will reduce page splits and fragmentation.

A query can take advantage of the chain of key columns starting with the first one. The chain can continue after each equality use. An inequality or range can take advantage of a key column as well, but the first one of these is the end of your chain. Once the chain is broken, everything else can be useful, but only as unordered values that don’t matter if they’re key columns or included columns.

You can stop putting in key columns when either queries stop being able to take advantage of them being ordered or the values you’re getting are either unique or close enough. These key columns aren’t free as Paul Randal (b|t) points out in his post On index key size, index depth, and performance.  If a key column is not very useful, then it’s very useful not to have it there.

I should note that if you’re using an index to enforce uniqueness then it will use all the key columns and none of the included columns to do so.  Based on the last paragraph you don’t want any key columns after it’s unique anyways, so don’t even consider that.  However, included columns aren’t used to calculate uniqueness, so you can make this a covering index if it helps you without hurting the unique constraint.

This process, like any other indexing process, isn’t going to be perfect. You’ll have to weigh your decisions with queries that are more critical or are called more often carry more weight in your decision.

Now that you have your key columns figured out, look at the queries that use more than just those columns. When they reference this index how many rows are they going to return where they have to get more information from the table itself through a lookup? How wide are those columns, and how many other queries are going to do the same? This is the balancing act between adding included columns and accepting key lookups. A key lookup is going to be a nested loop operation making separate calls to get the missing columns from the clustered index (or heap, for those who wish to anger me), so 10,000 key lookups is 10,000 separate calls in a loop. If you have to add a large number of columns to eliminate 10 key lookups then it’s almost never worth it. If you have to add one small column to eliminate 1,000,000 key lookups then it’s almost always worth it. Notice I didn’t use determinate language on those…you’ll have to find your balance, test it, and balance it again.

Some things like adding a column to avoid key lookups may make more of a difference to you than the user, but that doesn’t make it less important. For instance, I just said adding a small column to eliminate 1,000,000 key lookups is almost always worth it. If those 1,000,000 key lookups were from a single execution of a query then the user would probably notice, they might even buy you lunch if you’re lucky. If it was a single key lookup on a query run 1,000,000 times that day it’s still a drop in CPU utilization and a potential 1,000,000 pages from the clustered index that didn’t have to be read into cache. You’re doing this because it adds up to a better running server, not always because it’s noticed.

Your goal here is making an index as reusable as is reasonable. Here are the reasons you’re doing that:

  • Every index will fight to be in cache (assuming you don’t have vastly more memory than databases to fill it), an index that is reusable by many queries will be more likely to already be in cache and that space in cache will be more versatile.
  • Every index is another write process in an Insert, Update, and Delete, you’re trying to cut down on those.
  • Every index needs to be maintained, you’re cutting down on that, too.
  • Every index adds disk space, backup size, backup duration, restore durations, etc..
  • If you use TDE, every time a page is read from disk into memory it is decrypted. A reusable index tends to stay in memory more, reducing the number of times the CPU has to decrypt it. See, I TOLD you this belonged in an indexing strategy post!

Nothing’s free, so here’s what you’re giving up:

  • The index isn’t going to be the prefect index for most queries. Test the performance of your critical queries, but also keep in mind that these indexes are more likely to be in cache which could eliminate physical reads from the execution of those queries.
  • These indexes will tend to be wider than the query needs, which is basically restating that this isn’t going to be the perfect, most efficient index for a query. It also means that physical reads on these indexes will tend to be more expensive as there are fewer rows per page.  Again, keep in mind they’re more likely to be in memory because you’re going with fewer indexes that are shared by more queries.

Once you decide on an index or two to add, there are a couple things to consider.

  • What indexes don’t you want anymore? If a query could use another index slightly more efficiently, it will.  However, if it’s close enough then you want to get rid of that other index because of all those benefits of reusability I just mentioned (weren’t you paying attention?). It’s not a question of if a query would use the other index, it’s a question of if you want it to use it.
  • Some queries “should” use this index based on the key columns, but instead of it showing up as a seek predicate it shows up as a predicate. In these cases either your chain of key columns was broken (if column 2 wasn’t an equality column, column 3 will not be a seek predicate) or this column is not being referenced in a SARGable way.
  • Test in non-prod, not prod. Then test it again.
  • Know you’re accepting risk and understand everything involved the best you can. Have a healthy fear of that risk while also understanding that you took a risk just driving to work this morning.

Once these changes go through keep an eye on how they’re being used over the next couple weeks. If you’re in a rush to make a big impact, start a second table as the first change or two are in progress on the first table. Just don’t get too many changes in motion for a single table at once as that’s typically adding more risk and hiding which changes had positive and negative impacts. This is a process, and the longer it takes you do go through it the better the chance is that you’re doing it right.

If I’m doing this process for someone else who wants consistent improvement without taking on too much time or risk at once, then I like to get on their servers once or twice a month, find one or two changes to suggest, then have those go through testing and implementation.  Afterwards review the results and come up with the next suggestion.  It’s hard to be that patient as a full-time employee, but try.

Cluster It

All of that was talking about nonclustered indexes, but you get to pick a clustered index for your table as well.  Keep in mind this is a bigger change and involves more risk, but it’s also a bigger reward.

So, what do I care about that’s special for a clustered index?

  • Uniqueness
  • Key width
  • Width of columns being queried
  • Column types being returned (some can’t be in nonclustered indexes)
  • Number of rows being returned

The size of your key columns on your clustered index is the MINIMUM size of the key columns on a nonunique nonclustered index, and it’s also the MINIMUM width of the page level of any nonclustered index.  You need to keep that in mind.

However, just because your table has an identity column in it doesn’t mean that’s the best clustered index.  What is the best clustered index is going to vary wildly from table to table; there’s not always going to be a clear answer, and the answer will partially depend on how the table is queried.  I get into that a lot more in my last post, Picking a Clustered Index.  Yes, I wrote that post specifically to keep this one shorter…with mixed results.

If a table is often queried by a relatively small column that’s not unique, but the queries tend to pull back most of the columns in the table and a large number of rows then it’s worth considering using this as part of the clustered index key.

If you don’t then you’re faced with two solutions; you can make a really wide nonclustered index to cover these queries, or you can let the queries decide if they want to do a ton of key lookups or just scan the clustered index.  These don’t sound like fun to me.

You still have to worry about the integrity of your data, so if you’re dropping the unique clustered index with a single column to do this then you almost definitely want to add a unique nonclustered index with that single key column to maintain your data integrity.

Compress It

Index compression is an Enterprise-ONLY feature.

Compression is a very big point to hit on here, even if I’m only giving you the compressed version.  It makes your data smaller on disk (less I/O), smaller in memory (less need for I/O), and actually tends to lower your CPU usage instead of raising it.  I get into a lot more detail in my Data Compression post because I didn’t want to have too much space dedicated to a feature not everyone can use here.

Don’t Forget the Outliers

Go back to that list you made of all the queries hitting a specific table. Were some of the queries different than the rest? There are usually a couple, and that doesn’t necessarily mean there’s an issue. However, I look at these to determine if they are using the table properly.

Are they are joining on all the fields they should be. Sometimes you can get the correct results by joining on 3 of the 4 fields you technically should, so why join on the 4th? Well, index reusability is one of those reasons, because it may not be able to use the proper index because someone skipped a column that happens to be the first key field of the perfect index for this query.

Is the query SARGable? Sometimes you’re joining or filtering on the right fields, but something is written in a way that SQL couldn’t do a direct comparison.

Are you returning too many columns? I’ve seen queries returning 20 columns (or using *, which is a move obvious version of the same thing) to populate a screen that uses 3 of them, and on the SQL side you have a DBA trying to figure out if they should add included columns to an index to make that run more efficiently. The most efficient for this and many other examples is refactoring, not reindexing.

Remember, your goal is to make your server run more efficiently, and tweaking indexes is simply one of your tools. While you’re going through this process keep your eyes open towards how other tools can be used.  SSMS is never going to come up with a warning telling you that you should read a book or two by Itzik Ben-Gan (b|t) or Kalen Delaney (b|t), but I would welcome that change.

Does this negate my previous advice?

If you follow my blog at all, which is suggested in my very biased opinion, you may have seen me talk about Unused and Duplicate Indexes, but I make no mention of them here. Did I forget about them?

No, I did not. This is designing every index you want to have on your table in a reusable way. If that index was not on the list then you’ll want to consider getting rid of it. It’s two ways of looking at the same thing. A complete understanding of both of these methods will help you make intelligent indexing decisions and go as far as you need to for the situation you’re in.

Talk to me

This isn’t a short or easy process, and perhaps I could have worded some of it better.  I enjoy what I do, both writing this post and playing with indexes, and having someone think enough of me to ask me questions on this makes it all the more enjoyable.

I may be rewriting parts of this post as I find ways to reword concepts better, especially as I finalize and tweak my presentation with the same name for which this post is my guide. That presentation will make its debut at SQL Saturday Cleveland on February 6, 2016.

If you feel you can help me improve, please don’t hold back.  I’d rather feel that I’m improving than falsely believe I’m infallible.