Tim Ford’s Entry-Level Content Challenge

All bloggers started as beginners.  We picked things up, learned them, and got to the point where we wanted to start sharing our knowledge.  The problem is that most of us moved on to talking about advanced topics and left people to fend for themselves on the difficult entry-level learning curve.  My blog, Simple SQL Server, was actually created with me saying I was going to fill that gap, right up until the time I figured out the best way to learn advanced topics was to write things down as I learned.

Entry-Level Content Challenge

Tim Ford (b|t) noticed how we moved to advanced topics and asked us to remember where we came from in his Entry-Level Content Challenge.

It’s very simple.  Create one blog post a month that someone just learning your job could understand and benefit from, use the #EntryLevel tag on your blog and notification on twitter, and link to his original post.

This is for everyone, I don’t care what job you have.  I’m a SQL Server DBA, Tim’s a SQL Server DBA, but every profession has beginners that could use some help.

We’re Outnumbered

One of the things, if not THE biggest thing, you hope to do with a blog is to make a difference.  Then you talk about advanced topics to help other people near your level keep moving forward.  You also realize that teaching the details on these topics accelerates your learning and confidence faster than you thought possible.  It’s great; I recommend everyone does it.

However, there are a couple problems with this logic.  By the time people feel confident enough to blog, they aren’t talking about the fundamentals anymore.  Also, for every advanced person in any field, there are many, many more entry-level people who could benefit from learning the fundamentals properly from the start.

Bloggers aren’t talking to the largest portion of their potential audience, who just happen to be going through the most difficult part of the learning curve.

It’s the Right Thing To Do

In no specific order, here’s why it’s the right thing to do.  This list contains selfish reasons, moral reasons, and everything in-between.

  • You want to reach a larger audience, and there’s more people who need this content.
  • You were a beginner once and learned off of others.
  • You wish you learned the fundamentals right from the start, but you didn’t always.
  • You learn everything you teach better, even if you consider it simple.
  • Tim challenged you, and it’s hard to say no to a challenge.
  • You always wanted to make a difference.

New Bloggers

This is also a great opportunity for new bloggers.  Talk about subjects you know well, even if you’re not at the level you feel comfortable teaching advanced topics.

The content is the only difficult part of blogging.  Setting up a blog and creating a post is practically identical to setting up a new email account and sending an email.  If you want to get into blogging and need help, let me know.

If Einstein Can Do It…

Einstein said “If you can’t explain it simply, you don’t understand it well enough.”  I took this quote to heart enough to name my blog after it.  Try explaining the fundamentals simply, you’ll see how much your understanding of the topic increases to accomplish that feat.

 

 

Shrinking Database Data Files

Normal DBAs will sum up shrinking a database data file in one word: NO!

I’ll talk to you about this subject because I’m not normal.  Still, my goal is that you’ll go from “why can’t I shrink my files” to “I don’t want to shrink my files.”

Truth be told, even when I HAVE to shrink files, I don’t want to.

Basically, you needed the space at one point in time, so you’ll probably need it again.  Unless you changed a process or something extremely rare happened, you’ll use the space again and there’s no reason to take on the issues involved with shrinking just to have a file grow again.  While this advice is mostly the same for log files, it’s different enough where I’m making a separate post for shrinking database log files

When should you shrink data files

First, a process changed where you’ll never need that amount of space again, such as the database used to keep 10 years of history but now an automated process keeps it pruned to 6 months.  This change also left you with a very significant amount of free space that you need for another purpose.

Really, that’s about it.

What happens when you shrink data files

The most common way to shrink a file is to have it reorganize pages before releasing free space, so I’ll cover that.  It basically has two steps:

  1. Fragment the file as much as possible (killing performance)
  2. Truncate the end of the file

By “reorganize” it means take the tail end of the file after the size you said you’d like the file to be shrunk to, find every populated page of data after that point, then move those pages to the first available spot you find in the file.  When the end of the file is empty, truncate it.

It’s an expensive process that leaves a big mess behind, but there are times you’ll need to go through with it (kicking and screaming).  If you do, schedule an index maintenance job immediately after this to undo most of the damage you just did and make sure the server still loves you.

Think about this, you’re considering kicking off a process where you have to tell the server you’re sorry afterwards.  You better have a good reason for this.  A 100 GB database with only 2 GB used may be a good reason to shrink.  A 100 GB database with 75 GB used is normal and healthy.

If you are going to shrink, make sure the database won’t be using that space again.  If you have a 100 GB database with only 2 GB used, does it populate, process, and purge 90 GB in an overnight process?  Find out before you shrink.

Manual Shrinking

If I haven’t scared you off yet (and I hope I did) then here’s how you shrink.

Here’s the link how to shrink a file using T-SQL: DBCC ShrinkFile

Here’s the link how to shrink a file using the GUI: SSMS Shrink File

There are Shrink Database options as well.  I don’t use it and don’t recommend it.  You know what files you want to shrink, don’t shrink them all.  When this is used it’s typically because people want to shrink their data files, but they mess up their log files while their at it.  Be nice to yourself, don’t use shrink database.

What about the Auto-Shrink option

No.

Just no.

It’s not a feature, it’s a threat.

If you have a certain amount of free space it will automatically shrink your database causing all the problems I just discussed, but it will probably kick in during your busiest time of day.  Then it won’t follow that up with index maintenance, so it just left you hopping all over your disk to find data.  The best part is that it didn’t check to see if you would need that space again, so it’ll probably grow tomorrow to get that space back.

In the example above of a 100 GB database that only uses 2 GB during the day but populates 90 GB for overnight processing, those 2 GB will be horribly fragmented, you’ll spend the resources shrinking, then you’ll take the performance hit growing the database every night as it puts the growth anywhere it can which typically means physical file fragmentation as well.  In the mean time your monitoring software will show that you have tons of free space, right up until you get the disk full error.  You can auto-shrink that database and get all those issues, or you can just let it do its job as you do yours without a maintenance nightmare.

The Connect Item on this is marked as fixed, stating that they’ll consider removing it in future versions.  This wasn’t a joke opened by some wannabe DBA.  This was serious and opened by someone who went on to become the president of the Professional Association for SQL Server.

To be fair, we all word our feelings on this differently.  I say no, Tom LaRock says never, Paul Randal says Turn It Off!, and Brent Ozar is colorful enough to come up with the term Hamster Wheel of Death.

As of the time of this writing, no one has used the term “Magical Unicorn” to describe this feature.

Sum it up

  1. Don’t shrink data files.
  2. If you didn’t listen to #1, why?
  3. Follow it up with index maintenance.
  4. Every time you try to automate this process a unicorn dies.

Entry-Level Content Challenge

I’m taking Tim Ford’s (b|tEntry-Level Content Challenge, and this is my first post with the #EntryLevel tag.  The challenge is to have one post that earns that tag each month, but I hope to exceed that.

 

SQL Saturday Cleveland #SQLSat473

On February 6, 2016, Cleveland is hosting a free training event for SQL Server.  This has a lot of the great stuff from the big, paid events, and skips some of the negatives.

There’s a great team from the North Ohio SQL Server Users Group that took on a very difficult task to put all of this together.  They hand selected every presentation, being forced to turn away over half of the abstracts and many speakers.  What they came up with was a variety of topics that span the breadth of SQL Server and the experience levels of the professionals working with databases.

What is this SQL Saturday?

For those of you who have never been to a SQL Saturday, here’s how this one works.  Other SQL Saturdays will vary from this slightly, but not too much.

There are 6 training rooms with hour-long presentations running simultaneously in each.  You move from room to room between each session choosing the ones that apply to you the most.  The schedule for the sessions is posted in advance, and it’s impressive.

An optional $10 lunch, which is typically very obvious that no one is making a profit off of, is the closest thing the event has to an entrance fee. The stuff you’d expect to cost you is provided by volunteers and sponsors who pull together the venue, presenters, and community for you.

There’s time to network with vendors, attendees, and speakers before the event, during breaks and lunch, and often end up in conversations that last throughout the next session.  This differs from the big events in that it’s much smaller and personable with most attendees being relatively local.  The people you meet at a regional event like this are going to be more relevant to your life because they’re part of your local community.

The event at Hyland Software concludes with prizes ranging from $100 gift cards to new laptops being handed out to random attendees.  Yes, you can basically get paid to go to free training, which I still don’t quite understand.

Then there’s the after-party, also provided by the SQL Saturday volunteers and sponsors.  There’s food, fun, and more time to get to know some great people.

The venue can’t hold everyone who should come, and often can’t hold everyone who wants to come.  Register now to make sure there’s room for you, and make sure to cancel if your plans fall through.

What is SQL Saturday?

Not just this event, what is SQL Saturday as a whole?  It’s a program specifically set up to help everyone in the community to develop.

The obvious people benefiting are the attendees who get a chance to see all the sessions and meet the vendors.  You can see how they would benefit from here, but not quite how much until you’re at the event.

The less obvious are the speakers, some speaking publically for the first time.  As one of the speakers, I can personally say that I’ve grown more as a person and as a professional than I thought possible.  It’s a step I highly recommend, and one I’m very grateful to have with SQL Saturday.

The even less obvious are the vendors.  They’re speaking to the public, not just their established customers, getting candid feedback on what works, what doesn’t work, and how they can make their offerings better.  Even if they don’t make a single sale from attending the event, they can always come out ahead.

SQL Saturday didn’t just pop out of nowhere, and someone told me we should give thanks to the people who started it all.

Thank you:

Steve Jones (b|t)

Andy Warren (b|t)

Brian Knight (b|t)

These event are around the world with one or more occurring almost every weekend of the year as an annual event in most major cities.  See SQL Saturday’s website for a full list of what’s coming up.  If your city isn’t coming up soon, look in past events because most cities hold it about the same time every year.

Who’s speaking?

Where do I start?  Well, by listing EVERYONE on the current schedule, along with the blogs and twitter handles I’m aware of.

  • Deji Akomolafe (b|t)
  • Jim Arko (b|t)
  • Adam Belebczuk (b|t) <– Organizer
  • Chris Bell (b|t)
  • Delora Bradish (b|t)
  • Mindy Curnutt (b|t)
  • Aaron Cutshall (t)
  • Joey D’Antoni (b|t)
  • David Eldersveld (b|t)
  • Cassandra Faris (t)
  • Kevin Feasel (b|t)
  • Frank Gill (b|t)
  • Amy Herold (b|t)
  • Paul Hiles (t) <– Organizer
  • Steve Hood (b|t) <– What am I doing on a list this awesome?
  • Michael John
  • Jonathan Kehayias (b|t)
  • Dave Mattingly (t)
  • David Maxwell (b|t)
  • Evelyn Maxwell <– bright future ahead for her
  • Eugene Meidinger (b|t)
  • Kon Melamud (t)
  • Ben Miller (b|t)
  • Jeff Moden (b)
  • Colleen Morrow (b|t) <– Organizer
  • Matt Nelson (b|t)
  • Wendy Pastrick (b|t)
  • Pat Phelan (t)
  • Justin Randall (b|t)
  • Wayne Sheffield (b|t)
  • Peter Shore (b|t)
  • Warren Sifre (b|t)
  • Julie Smith (b|t)
  • Erin Stellato (b|t) <– Organizer
  • Michael J. Swart (b|t)
  • Allen White (b|t) <– Organizer
  • Andy Yun (b|t)

Note that the schedule may change some before the event, but it never changes too much.

What do we ask of you?

Show up.

Despite the amazing list of speakers, we’re all volunteers.  That means we do it because we want to do it, and truly enjoy it.  If you want to help us enjoy it even more, be involved.  Talk to us and the other attendees, ask questions, and give feedback.

If you want to absolutely blow us away, get in touch afterwards and let us know the difference it made for you.  Yes, we want this, it will absolutely make our day!  Let the organizers know what you think of the event.  Tell the presenters how you were able to use their advice at work, and even ask questions down the road.  Get in touch with the vendors to discuss their products, even if you aren’t planning a purchase, to give them real feedback and see what’s out there.  I used to think this was imposing, but now I realize this is the one of the most awesome compliments you can get.

The most important part….

Show up!

Hope to see you there!

UPDATE – 2016-01-13 – On the waiting list

If you registered before today, you’re in.  If you’re registering now, you’re on the waiting list.

If you can’t make it, please cancel your registration so someone on the waiting list can get in.  We’ll miss you, but at least there will be someone else we won’t be missing.

TempDB memory leak?

I found a bug where I’m seeing TempDB use more memory than it should on multiple versions of SQL Server, especially on servers set up with common best practices. There’s a workaround that has a profound affect on server performance, adding to my belief that this is a legitimate bug and leading me to open a Connect Item on the issue.

Querying the Buffer Pool

I have a query to show me what’s in the buffer pool for the entire server, showing the expected results as well as excessive space being used by TempDB.  The newest version showing these details is on my post Querying the Buffer Pool.

It shows number of pages in the buffer pool grouped by the database, table, and index.  The query makes use of left joins so it can see space in memory that’s not currently allocated to a specific object.

The results are surprising in many ways.

The good surprises are seeing what indexes are hogging up your buffer pool so you have an idea of where to start tuning.  I’m a huge fan of this and have blogged about it in Cleaning Up the Buffer Pool to Increase PLE, although the name of my older post is misleading because it does more than just help memory management in SQL Server.

The Bug

The bad surprise was a bug which has been harassing me for quite some time now.  As I mentioned, the query will return all the space in the buffer pool, specifically the contents of sys.dm_os_buffer_descriptors, and does a left join to the tables leading up to and including sys.indexes so space not currently allocated to a table will show up.  The problem is that the space that shows up as unallocated for TempDB is much larger than expected, in this case taking up 1/3 of my buffer pool.

QueryBufferPool_TempDB

On this post I’m talking about a single server, but the problem wasn’t limited to a single server.  It showed up at the same time, caused by the same change (implementing a common best practice), partially resolved by the same partial rollback (undoing the best practice) on SQL 2008 R2, SQL 2012, and SQL 2014.

Details About the Bug

So the query I have on yesterday’s post, Querying the Buffer Pool, showed I had unallocated space in TempDB in memory, and a lot of it.  However, it doesn’t show details.

To start looking at the details, what kind of pages are these that exist in sys.dm_os_buffer_descriptors, but not in sys.allocation_units?

SELECT bd.page_type
	, MB = count(1) / 128
FROM sys.dm_os_buffer_descriptors bd
	LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
WHERE bd.database_id = 2 --TempDB
	AND bd.is_modified = 0 --Let's not play dirty, only clean pages
	AND au.allocation_unit_id IS NULL --It's not even allocated
GROUP BY bd.page_type 
ORDER BY 2 DESC

TempDB_BufferPool_Unallocated

Ok, so we’re dealing with typical data in TempDB.  Well, other than it not being allocated, of course.

So I run another query to get more details.  This time I want to look inside the pages to see if they tell a different story.

SELECT TOP 100 bd.*
FROM sys.dm_os_buffer_descriptors bd
	LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
WHERE bd.database_id = 2 --TempDB
	AND bd.is_modified = 0 --Let's not play dirty, only clean pages
	AND au.allocation_unit_id IS NULL --It's not even allocated

TempDB_PageLevel_Unallocated

Then I follow that up with Paul Randal’s How to use DBCC PAGE, which comes with all the disclaimers about using an undocumented and unsupported trace flag and command.  This one isn’t horrible in my mind or Paul’s comments, but remember the undocumented and unsupported parts.

DBCC TRACEON (3604);

DBCC PAGE (2, 5, 502219	, 0)
DBCC PAGE (2, 5, 374929	, 0)
DBCC PAGE (2, 5, 69868	, 0)
DBCC PAGE (2, 5, 453687	, 0)
DBCC PAGE (2, 5, 214988	, 0)
DBCC PAGE (2, 5, 440966	, 0)

DBCC TRACEOFF (3604);

The results all looked about the same to me.

DBCC_Page_Results

There are several important parts to me.  The m_objId is a negative value I can’t find in TempDB.sys.objects, so it WAS a temporary object that no longer exists.  Across the board, these are “NOT ALLOCATED”, “NOT CHANGED”, “NOT MIN_LOGGED”, “0_PCT_FULL”, so there’s nothing there.

To me it looks like temp objects made it into memory and remained in memory after the temporary objects were dropped.  I have no idea what objects these were or how they were dropped, but I’m imagining these were temp tables automatically dropped when the session was either closed or reset.

A Recent Change (A CLUE)

I found this by noticing that PLE for several servers was lower now than it has been in the past, so I was peeking in the buffer pool to see who was playing nice.  Going off of “when did PLE start to be lower” I noticed that I implemented a change around that time to use a common best practice.

That change was presizing TempDB data files to take up a vast majority of the dedicated LUN instead of letting them grow as needed.  It avoids waiting for file growth, especially if you’re using TDE (I’m not) and can’t use IFI (I can), but for several other reasons as well, including file fragmentation and the slight pause even IFI causes.  So at the start of all these festivities, I took the 4 TempDB data files from 100 MB each to 12 GB each, using up 48 GB of the 50 GB available.

A Workaround

Seeing this, I wanted to partially roll back the change the next opportunity I had.  100 MB was too small and I was aware that it invoked file growths every month (we reboot monthly for OS updates).  48 GB wasn’t right though, we just have that much space on the drive due to server build standards and paranoia (I’m a DBA).  So I went through our Idera Diagnostic Manager monitoring software and found the most space TempDB used, which is captured once an hour.  I found that 4.8 GB was the peak usage with several incidents of usage going over 4.5 GB.

With that information available and still not wanting an autogrowth for all the reasons listed above, I decided that all 4 files should be 1.5 GB, so 6 GB total.  That means peak usage was about 75% full, leaving plenty of room for error, especially with my baseline only being captured once an hour.  Autogrowth is set to 256 MB, so it’d add 1 GB total each growth.  I can live with that.

I can’t say it eliminated the issue because I still have 2 GB of unallocated TempDB space in cache, but it’s better than 8 GB.  It can be considered more acceptable than other issues I need to tackle right now, but it still bugs me.

What’s the Best Practice?

It’s a best practice to have TempDB data files on their own LUN, drive, array, however you want to word it.  Then it just make sense to have the total size of your data files add up to 90% or more of the drive size.  I see this advice everywhere, with these two standing out:

  • Solar Winds – Configuration Best Practices for SQL Server Tempdb–Initial Sizing
    • “Next, if you can give tempdb its own disk, then configure it to almost fill the drive. If nothing else will ever be on the drive, then you’re better off setting it to be larger than you’ll ever need. There’s no performance penalty, and you’ll never have to worry about autogrow again.”
  • Brent Ozar – SQL Server 2005/2008/2012/2014 Setup Checklist
    • “Notice that I don’t have filegrowth enabled.  You want to proactively create the TempDB files at their full sizes to avoid drive fragmentation.”

Jonathan Kehayias does it a little bit differently in his post SQL Server Installation Checklist saying to add space to TempDB files in 4 GB increments.  Although he doesn’t fill the drive by default, this isn’t mentioned by him, either.

Now I need to be perfectly clear on this, I trust these three sources.  I trust Jonathan and Brent more than I trust myself with setting up SQL Server.  I also feel the same about the authors I know on the Solar Winds post.  This does not change that.

Sizing TempDB like that often means it’s much larger than you need.  The workaround I’m using is to right-size these files instead.  For me, for now, I’m going to stick with seeing how large TempDB gets and make it slightly larger than that until I have a solid answer to my problem.

What Was It?

I still don’t know.  The workaround managed to knock it off of my priority list enough where I’m not actively working on it.  However, my drive to understand SQL Server better won’t leave me alone.

This post is my solution.  I have some very intelligent people reading this who I hope will at least lead me further down the rabbit hole, even if they don’t have a conclusive answer.  There’s a good chance I’ll be asking for help on Twitter with #sqlhelp or opening a connect item on this, for which I have a very well documented description of the issue that I can link to.

Updates:

2016-01-06 – Caching of Temporary Objects

Due to a comment, I started looking into the caching of temporary objects to see if this was the root cause.  The comment specifically mentioned Paul White’s (b|t) post Temporary Object Caching Explained, and I also read over Itzik Ben-Gan’s (b|t) post Caching Temporary Objects.

Both of these left me with the impression that smaller amounts of data would be left in the cache linked to temporary objects linked to the proc cache.  What I’m seeing is large amounts of data in the buffer pool that did not drop when I ran DBCC FREEPROCCACHE (on a test server that wasn’t in active use) as I expected if this was the full explanation.

While it’s very likely this is related to the issue on hand, I’m not ready to accept it as a full explanation.  If the memory associated with TempDB dropped when clearing the proc cache (on a test server) then it would have been a great explanation with a poor side effect of going too far with the memory being used.

2016-01-07 – Opened a Connect Item

I mentioned this issue on the comments of Paul White’s blog post mentioned in the last update and comments below on this post.  His response concluded with this:

So, when memory pressure is detected, I would expect memory use like this to be freed up for reuse in pretty short order, by design. If it is not, and bad things happen because memory for unallocated tempdb is not released/reused, that would be a bug.

While I was already leaning that way, it pushed me over the edge to decided it was time to open up a connect item on this issue.  I feel it’s well worth the read going to Paul’s post and the connect item.  Also, if you’re seeing this as well, an upvote on connect is very appreciated.

https://connect.microsoft.com/SQLServer/feedback/details/2215297

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

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.