Wayne Sheffield

My blog about SQL Server

Browsing Posts published by Wayne Sheffield

This post is re-published from my original post on SQL Solutions Group. I hope that you enjoy it.

In my last article, I started off talking about checking various settings that make a performance difference prior to jumping into query tuning. This article continues the investigation into performance affecting settings, by jumping straight into the storage subsystem. How would you like to make a single change to your drives and have the IO operations improve by up to 40%?

Just reformat the drives that contain your database data and log files.

If you are running on a version of Windows prior to Windows Server 2008, or if the drives were migrated to a new system without reformatting them, then you really could improve IO performance simply by reformatting the drives. Older versions of Windows will create disk partitions that default to having the boundaries based upon the ancient Cylinder/Head/Sector addressing scheme that was common in disk controllers of that era. Once these boundaries are set, the only way to change them is to reformat the drive, so migrating this drive to a Windows Server 2008 or newer server alone does not fix the issue.

How reformatting the drive affects IO performance

File Allocation Size

On the hard drive, the minimum amount of data that can be read or written is called a sector, which historically was a mere 512 bytes (newer drives may have sector sizes up to 4KB). A group of sectors is a cluster (no not that type of cluster). A drive with a 64KB cluster size (at 512 bytes per cluster) will have 128 sectors, which is also known as the file allocation size. The first 63 sectors of a drive are reserved, or hidden, and include the master boot record (MBR). Simply multiplying the 63 sectors by the sector size of 512 bytes shows that this hidden area occupies 32,256 bytes, or 31.5KB. The drive dutifully starts storing data immediately after this.

SQL Server IO operations

Now, consider how SQL Server performs disk IO operations – an extent at a time (an extent is 8 pages, each of which are 8KB, for a total of 64KB). To store this information on disk, with the default settings, this 64KB will start immediately after the 31.5KB and continue for 64KB… and it will span two clusters. When the drive goes to read this data, it will have to read the first cluster, and then the second cluster, in order to get all of the data needed for the extent being read from disk. When the next extent is read, it will re-read the second cluster, and read the third cluster. The wasted IO operation should be obvious.

How to have SQL Server work better with disk

What we need to do is to offset the beginning of the data being stored on disk to a location more conducive to how the program is operating. This offset is known as the “Partition Alignment Offset”. To be in tune with SQL Server, this value should be an increment of 64KB. However, you also need to consider the entire storage subsystem – the disks, controllers and memory. Starting with Windows Server 2008, this offset is at 1024KB – a nice increment of 64KB that also works very nicely with most RAID disks/controllers. Prior to Windows Server 2008, partition alignment offset was not explicitly performed, so this will need to be performed.

Determining a drive’s partition alignment offset

To determine what the partition alignment offset is for a windows basic disk, there is a very simple wmic command to run:

The Index value is the same as the disk number reported in the Disk Management tool, and also from the wmic command wmic Volume. However, we tend to think in terms of drive letters, not drive indexes, so the following PowerShell script will return Partition Alignment Offset for all drives on the system that it is run on and perform the mapping from index to drive letter:

If you want to open the results in Notepad, pass in the parameter $True.

You may note that I said that using these wmic commands is for a Windows basic disk. If you are using a dynamic disk, you need to use alternative methods. With Windows Server 2008, you would use the tool diskdiag.exe (in earlier versions of Windows, this was called dmdiag). For either, you need to use the –V flag: dmdiag.exe –V or diskdiag.exe –V.

Changing the Partition Alignment Offset

To change the Partition Alignment Offset on Windows Server 2008 or higher, simply format the drive. On earlier systems, you will need to use the diskpart.exe tool to change the partition alignment offset, and then format the drive. In either case, this is a destructive operation – you will lose all data on the drive.

For more information on disk partition alignment, refer to this TechNet article: http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx.

These steps should yield a noticeable IO performance improvement. Test it for yourself and let me know what you experience.

TSQL TuesdayIt’s that time of the month… the time when all of the T-SQL bloggers have a party and blog about a specific topic. This month, the host is my friend Jason Brimhall (b/t). The topic that he has selected is “Sharpen Something”. Specifically, he wants us to:

I am asking you to not only write a post but to do a little homework – first. In other words, plan to do something, carry out that plan, and then write about the experience.

Immediately, I’m thinking of something that I could write on. Then I read on and see this other part of his post where he has some examples:

You know you are extremely deficient at a certain SQL Skill. Tell me what that skill is and develop a plan to get better at that skill. Report on the implementation of this skill and how you are doing at improving. Maybe that skill is about Extended Events, PoSH or availability groups.

And now I’m thinking that Jason’s picking on me. He knows that there is one particular skill that I’m deficient on, and I’ve been meaning to work on it. So Jason, thank you for prodding me along.

To wrap up, Jason says:

In recap, this is an invite to make a short term goal covering the next two weeks. Tell everybody what that goal is (in your tsql tuesday post of course) and how you went about creating a plan for that goal and how you have progressed during the two week interval.

“Tell me what the skill is”

So what is the skill that I’m deficient on? It’s just the most popular feature released in SQL 2012: Availability Groups (did you notice how Jason even mentioned that above? See, he’s picking on me!) It’s not that I’m completely ignorant about Availability Groups – it’s just that I’ve never had the opportunity to work with this feature. I’ve even tried to work with it before, however higher priority things keep popping up, delaying my getting around to this.

“Develop a plan to get better”

The short and simple plan is to create an environment where I have Availability Groups installed, and utilize it on some databases.

The longer plan goes like this: Since Availability Groups is built on top of Windows Server Failover Clustering, I need to:

  1. Build an environment where I have a domain controller and two servers that have clustering implemented.
  2. Install SQL Server on the two servers.
  3. Create a few databases – with data.
  4. Create an Availability Group with these databases.
  5. Test the secondary replicas being read-only.
  6. Repeat for the following environments to assess differences:
    1. Windows Server 2008R2 with SQL 2012
    2. Windows Server 2012R2 with SQL 2012
    3. Windows Server 2012R2 with SQL 2014
    4. Windows Server 2012R2 with SQL 2016
    5. Windows Server 2016 with SQL 2016

“Report on the implementation of this skill”

This is the section where I humble myself. To date, I have created three virtual machines running Windows Server 2008R2. They aren’t even in a domain yet. A setback that I faced was that I already had a domain set up for this… but since I’m using evaluation edition of Windows, they had expired. Everything had to be rebuilt, and now I’m running into domain setup issues. It seems to me that the Windows setup is harder than the SQL setup will be. But I will persist, and this will be done.

Because I am extremely deficient in this skill, and I need to be working with it.



Service Packs and Updates

Upgrades and Service Packs

Source: http://www.digipas.com/files/photos-firmware/software-btn1.png

One of the things that I always check on client servers is that they are running the latest service packs, for both SQL Server and for the Windows OS itself. Now, sometimes I know what the latest service pack is – but other times I don’t. Therefore, I end up spending more time than I would like looking up the latest service pack. Therefore, I’m making a list of service packs here, along with download links (as I find them – navigating Microsoft’s Download Center isn’t exactly a piece of cake). I tried to get the download links for both 32-bit and 64-bit architectures, where applicable. Hopefully, you can use this chart also.

If you have a link to any that I have missed, please leave me a comment so that I can update this! I’d appreciate your help.

Windows Update Rollups

Windows Version(s) Date Update Prerequisites
8.1, 2012R2 2016-07-21 July 2016 Update Rollup April 2014 Update Rollup
Service Stack Update
8, 2012 2012-12 Dec 2014 Update Rollup none
7, 2008R2 2016-07-21 August 2016 Update Rollup SP1
Servicing Stack Update

Back to Contents

Windows Server Versions

Operating System RTM SP1 SP2 SP3 SP4
Windows 2016 CTP5
Windows 2012 R2 6.3.9200
Windows 2012 6.2.9200
Windows 2008 R2 6.1.7600.16385 6.1.7601
Windows 2008 6.0.6000 6.0.6001
32-bit, 64-bit
32-bit, 64-bit
Windows 2003 R2 5.2.3790.1180 5.2.3790.?
32-bit, 64-bit
Windows 2003 5.2.3790 5.2.3790.1180
32-bit, 64-bit
32-bit, 64-bit
Windows 2000 5.00.2195 32-bit

Back to Contents

Windows (non-server) Versions

Operating System RTM SP1 SP2 SP3 SP4
Windows 10 10.0.10240
Windows 8.1 6.3.9200 6.3.9600
32-bit, 64-bit
Windows 8 6.2.9200
Windows 7 6.1.7600 6.1.7601
Windows Vista 6.0.6000 6.0.6001
32-bit, 64-bit
32-bit, 64-bit
Windows XP x64 5.2.2600 5.2.2600.1105 5.2.2600.2180
Windows XP 5.1.2600 5.1.2600.1105 5.1.2600.2180 5.1.2600.5512
Windows 2000 5.00.2195 32-bit

Back to Contents

SQL Server Versions

Version RTM SP1 SP2 SP3 SP4
SQL Server 2016 13.0.1601.5
SQL Server 2014 12.0.2000.8
SQL Server 2012 11.0.2100.60
SQL Server 2008 R2 10.50.1600.1
SQL Server 2008 10.0.1600.22
SQL Server 2005 9.0.1399.06 9.0.2047 9.0.3042
SQL Server 2000 8.0.194 8.0.384 8.0.532 8.0.760 8.0.2039
SQL Server 7 7.0.623 7.0.699 7.0.842 7.0.961 7.0.1063

Back to Contents

SQL Server Management Studio

Latest Release
August 2016 SSMS (16.3)

Back to Contents


Date Action
2016-08-05 SQL 2016 RTM, July update rollup for Win 7/2008R2
Added update rollups for Windows 8/2012 & 8.1/2012R2
Added SQL Server Cumulative Updates and SSMS
2016-08-22 Windows 7/2008R2, August update rollup
Fixed SQL 2005 CU links
2016-08-27 Added SQL 2014 CU1

I’m happy to announce that I’ll be going on a Central Texas SQL Tour in mid-August.

First up is the San Antonio SQL Saturday – I’ve been selected to be a speaker. This is really special because this is San Antonio’s first ever SQL Saturday!!! I’ll be presenting what is probably my favorite presentation – “Table Variables & Temp Tables – What You Need to Know!” This session usually runs about 75 minutes, so I’ve arranged to have the session immediately prior to lunch to help stagger the lunch flow. If you are anywhere in the area, come on out and see this presentation – I’ll guarantee that you will learn something, or double your registration fee back! You can register for this wonderful, free event at this link. From that link, you can also see the schedule and gaze in awe and wonder at all of the wonderful free sessions that you can attend all day. The SQL Saturday is on August 13th, 2016, and it is at Texas A&M University, One University Way, San Antonio, Texas, 78224 (map).

Image source: http://bit.ly/29R5yce

The following week, I’ll be at two user group meetings. The first is CACTUSS North on Tuesday, August 16th in Austin Texas. The meeting starts at 6pm, and I’ll be doing an encore presentation of “Table Variables & Temp Tables – What You Need to Know!”, so if you couldn’t see it at the SQL Saturday, be sure to come out to see it here. Sign up to attend this user group meeting here, and the meeting is at the Microsoft offices at 10900 Stonelake Blvd., Suite 225, Austin, TX, 78759 (map).

Image source: http://salssa.sqlpass.org/portals/404/salssa.gif

The next night, Wednesday, August 17th, I’ll be back in San Antonio, presenting at their SALSSA user group meeting. The meeting starts at 6pm, and I’ll be doing a different presentation – “SSMS Tips and Tricks – Using What You’ve Already Got”. They meet at the New Horizons Computer Learning Center, located at 2727 NW Loop 410, San Antonio, TX, 78230 (map).

Image source: http://www.thesanantonioriverwalk.com/images/site/home_night_banner.jpg

You can read the abstracts for both of these presentations here. And yes, I’m sure I’ll be at the Riverwalk in San Antonio sometime during my free time. I love the Riverwalk, and my favorite restaurant is nearby.



This post is re-published from my original post on SQL Solutions Group. I hope that you enjoy it.

When investigating a performance issue, the desired end result is already known… you need to make the queries run faster. It’s been my experience that most performance problems involve optimizing the query that is being run—sometimes the query needs a re-write to be more efficient, sometimes the tables being queried need a new (or modified) index, and sometimes even the underlying database schema might need modifying. Before starting down any of these routes though, the first thing that I do is to check the configuration settings that make a difference.

Enter SQL Server’s “Go Fast” button

turbobuttonIn a CPU universe far, far away, there existed a particular series known as the 80486 processor, commonly called just 486. One particular issue that this series of CPUs had was that they were too fast for many games of that era (games that were coded for running off of CPU ticks). If you can get hold of one of these games and try to run it on a modern system, I wish you luck in even being able to press a single key before the game ends! But I digress… in order to counteract this issue, the processors had a feature where they could be slowed down. There were different ways that the CPU could be slowed down, but to interact with the computer user, there was a button on the case, known as the Turbo button, that would cycle the system between the low and high speeds of the CPU. Accidentally leaving this button in the low speed would slow down everything else on that computer; merely putting the system into the high speed would fix your performance problem.

So what does this mean for SQL Server?

In a CPU universe very, very close to you, today CPUs have a similar feature known as CPU Throttling. This feature, otherwise known as Dynamic Frequency Scaling, dynamically adjusts the CPU frequency on the fly. There are several benefits from doing this: less heat is generated, less power is consumed, and it can even make the systems quieter by also reducing the fan speed necessary to cool the computer. In a large server farm, reducing the heat means that cooling costs are also reduced. By operating the systems in a reduced state, the lower cooling costs coupled with the lower power requirements of the computers themselves can mount up to quite a savings. The dynamic portion of this feature is based upon system monitoring of CPU usage, and when it is low the CPU frequency is reduced, typically by 50% or more.

For most systems, the computer’s BIOS will monitor certain communication from the operating system, so the operating system can also send instructions for setting the CPU Throttling. In the Windows operating system, this is performed through the “Power Plan” setting. Since Windows Server 2008, the default power plan is “Balanced”, which allows Windows to reduce the CPU speed in an effort to balance power and performance.

How does this affect SQL Server?

SQL Server is one application that doesn’t typically use CPU resources so much as to make the CPU Throttling disengage and the CPUs to run at full power. This manifests itself in SQL Server as queries just taking longer to run. You might even have a new server that the application runs slower on than the server it is replacing. The solution is to simply disengage the CPU Throttling feature so that the CPUs will run at full speed constantly.

Detecting if CPU Throttling is engaged

CPU Throttling is implemCPU-Z-CPU-Throttlingented at both the hardware and software level. The Windows operating system can control CPU Throttling, and this can also be controlled from the BIOS. On some systems, the BIOS will ignore signals sent from Windows, so we will cover how to test and set both methods.

To see if your CPUs are running at rated speed, there are two methods. The first is the third-party tool CPU-Z, available at http://www.cpuid.com/softwares/cpu-z.html. In this screen shot (from the above link), you can see that the CPU is rated to run at 3.2GHz, but is actually running at almost 1.2GHz. This system is having its CPU throttled.

Additionally, you can check out the WMI performance counters from the WIN32_Processor class. This DOS command returns the current and max clock speeds every second for 30 seconds; if the CurrentClockSpeed and the MaxClockSpeed differ significantly, then the CPU is being throttled.

In Windows, CPU Throttling is controlled from the Power Settings control panel applet. On a server running SQL Server, the desired setting is to be using the the “High Performance” power plan. You can do this from either the Power Settings control panel applet, or from the command line (starting with Windows Server 2008R2).

To start the Power Settings control panel applet, press the windows key + r key combination. In the run dialog box, enter powercfg.cpl. In the applet, you are looking for:


From a command prompt, you can simply run this DOS command:

Disabling CPU Throttling

To change the power plan, in the control panel applet simply select the High Performance power plan (as shown above). From the DOS command prompt:

Changing the power plan takes effect immediately, so you should immediately see your CPUs running at full speed. This means that you can change the power plan on your SQL Server without requiring an outage. If, however, this does not return your CPUs to full speed, then the BIOS is overriding the Windows setting, and you will need to reboot to go into the BIOS to disable it, and once changed reboot again. Different BIOS manufacturers call CPU Throttling / Dynamic Frequency Scaling by different names, so you will need to investigate your server manufacturer’s web site to determine what it is called. This will obviously cause an outage during this time, so this needs to be a scheduled maintenance action.

What kind of difference does this make?

This isn’t the fix for all queries, but it should help out pretty dramatically. For a system replacing another, it should return the application to pre-update performance, if not surpassing it.

Recently, I had the opportunity to test out the performance impact of changing the power plan from Balanced to High Performance. Prior to changing the power plan, a set of queries was running in 7 hours, 45 minutes. After changing just the power plan to High Performance, this set of queries is now running in 3 hours, 55 minutes. That’s almost ½ the time, from this simple setting change.

Just remember… SQL Server is not “green”. It needs full power.

And now you know about SQL Server’s hidden “Go Fast” button.

Recently (okay, it went live a month ago, and I’m super-late in my blog post!), I was the guest on the podcast that Carlos Chacon of SQLDataPartners publishes.

In this podcast, we talk about Table Variables and Temporary Tables (if you’ve been to one of my presentations on this topic, then you know what’s coming!). We also talk about super-hero powers and other general chit-chat. You can hear the podcast at at this link (MP3). The “Show Notes” are available here.

I hope that you go out and listen to it, and that you enjoy it as much as I enjoyed harassing Carlos during the show!

TSQL Tuesday
It’s been a while since I’ve written a T-SQL Tuesday post, mainly because I just wasn’t inspired by the topic. And truth be told, this one was turning out to be that way also. But then today, I was wishing that SQL Server would track something that it doesn’t do, and I realized that I had a potential post here. Since this is a couple of days past the deadline, I reached out to Chris Yates (the host for this month) (b/t), and he told me to just bring it on, so here goes…

The theme this month is SQL Gifts… at least that’s what I’m calling it. Chris says:

With that said let’s get this party started and have some fun with it this month shall we. Treat yourself to a birthday gift and come up with a present for yourself SQL related – no limitations:

  • Is there something you’ve been wanting to do for a long time in SQL, but haven’t done it yet?
  • Perhaps there is a feature you would like to see added into SQL that just isn’t there yet – what is it?
  • Maybe you are a consultant and know something many of us don’t as you parachute in and out of shops that a reader can benefit from?
  • Is there a co-worker struggling with something SQL related; here is your chance to share the present (knowledge).

Gift wrap your post for yourself and be innovative with what you write about.

Since I was wishing that SQL Server would do something that it doesn’t, it fits right in.

Determining Unused Objects

Have you ever been trying to clean up a database, and you can’t tell what is being used and what isn’t? My wish is for SQL Server to track the time that objects were last used.

Yes, we have sys.dm_exec_procedure_stats to tell us information about procedures that are in the plan cache. And we have sys.dm_db_index_usage_stats for index related information (and index 0/1 will tell us about the table). But both of these can have data disappear. Procedures when the plan is removed from the plan cache – and if SQL restarts. Index stats are removed if the database goes offline (including restarting SQL), when doing a CREATE with DROP_EXISTING and in SQL 2012 a bug was introduced where they were reset on a ALTER INDEX REBUILD. To handle both of these views, we need some kind of process to periodically query these views, and to update a physical table to track these.

These two views don’t take care of other types of objects – views, functions, triggers, etc. To track these, we need a custom process (for example, an Extended Event session or a server-side trace) that captures all database activity. We then need to periodically query this process and determine the objects being used, and again update a physical table to track these. If you’ve ever tried to get the objects being used from a server-side trace, then you know how much a pain-in-the-butt fun this can be.

It sure would be nice if SQL Server would track this all for us. It would make it so much easier to determine what is being used in the database, and what isn’t.

What I’d like to see is one or two columns added to sys.objects: either last_used_time_user / last_used_time_system, or just last_used_time (for both). Then for that object, when it is used, it updates these datetime columns. This will handle tables, views, procedures, functions, triggers, etc., and pretty much give me exactly what I’m looking for. This data needs to not be reset, just updated with the last time that it was used. And it needs to persist across restarts of the database / instance.

Doing this will add overhead to the system (probably less than if we were to create our own process to do this), so it makes sense to me to have this controlled by an alter database command – something like ALTER DATABASE TRACK_OBJECT_USAGE = ON.

Should this idea be extended?

Sometimes we want to know about whether a column is being used…. is it safe to drop a column? When running a query, the system verifies that all of the columns do exist, so this could potentially be an extension to that process. We could extend this to add these columns to sys.columns also. I imagine that this would create a bit of overhead, so this could also be controlled by another alter database command – something like ALTER DATABASE TRACK_COLUMN_USAGE = ON.

These columns can be added to sys.indexes also, where they will persist after a restart. This would be somewhat similar to the last_user(system)_scan/seek/update/lookup columns in the sys.dm_db_index_usage_stats view, but they won’t disappear unless the index is completely dropped. Since this data is already being tracked for sys.dm_db_index_usage_stats, I don’t know if it would really add that much of an overhead to also track it in sys.indexes – but if so, just toggle this tracking with another alter database command – say ALTER DATABASE TRACK_INDEX_USAGE = ON.

I’ve created a connect item for these suggestions. Go on out and up-vote it if you like this idea.


Whether this is implemented or not, however you determine objects that can be potentially removed, proceed cautiously. Ensure that you have considered end-of-period (month/quarter/year) processing where objects might be used just during these times. And always have a plan for bringing these objects back. In the case of tables / columns, you don’t want to lose any data. And in the case of T-SQL coded objects, ensure that you can re-create those if necessary. You don’t want this to be why your company let you go…

source: http://thistimeimeanit.com/wp-content/uploads/2011/06/officejuggler.jpg

One of the nightmare scenarios that a SQL Saturday organizer faces is selecting a speaker that is already going to another event on the same day. The speaker can only be at one event… and the site doesn’t really help us out with letting us know about these over-committed speakers.

So, I wrote this little PowerShell script to compare your selected speakers with the speakers selected at other SQL Saturday events that are on the same day. Now, by “selected speaker”, I mean those speakers that have been approved in the “Manage Sessions” section of the admin area of your event (those sessions don’t need to be on the schedule yet). Those sessions are then visible in the XML feed that is generated. This script will load the speakers for your event into an array, and then get the sessions for all of the other SQL Saturdays. If the other event is the same day as yours, it then compares the speakers. Finally, it will output those speakers that are selected in your event that are also selected in another event (aka the over-committed speakers) and the event that they are speaking at.

This script is based upon the script that Steve Jones’ wrote, however this script loads the information directly from the SQLSaturday.com web site each time that it runs. It could have used saved files (like Steve did), but this will be a bit easier to get changed speakers when either event changes the speakers that are presenting.

This script does not handle duplicate names (from a speaker presenting more than one session) at any of the events, so you may end up with duplicate output lines for a speaker.

Update: script updated on 2/9/2016 to handle > 1 duplicate speaker.

If anyone knows how to get a feed of the submitted sessions, I’ll create another script for comparing the sites for speakers that have submitted to multiple locations on the same day. I’d rather not even get to the selected phase if it can be avoided. If you know how to do this, just leave me a comment below please (or contact me directly).

source: http://cdn2.pcadvisor.co.uk/cmsdata/features/3380127/Infinity_Blade_II.jpg

In my previous post, I introduced you to the Advent of Code programming challenge, and explained how I’m going through the days, solving both parts of each puzzle. Continuing on, today we have day 21. In this challenge,you received a present – a role playing game. In this game, you (the player) and your enemy (the boss) take turns attacking each other. You each start off with 100 “Hit Points”, and you inflict damage based upon the weapons that you have. You are shielded by the armor that you have. You must purchase one weapon to use, and you may purchase one armor. You may purchase a ring for each hand. The store has just one of each item. The list of items in the store, along with their cost, how much damage they inflict, and how much protection they provide (as armor) is at the above link. The first person whose hit point score drops to zero (or less) loses. The damage inflicted is equal to the damage of all items of the attacker minus the armor total of the defender, and this damage is reduced from the hit points. Each attack will diminish the defender’s hit points by at least a value of one. The input file for this puzzle provides you with the boss’s stats. For Part 1 of this puzzle, you need to find out what is the least amount of gold that you can spend to win.

When the input file is examined, you see that it has three lines, one each for hit points, damage and armor. Each line lists the item, and then the amount of units for that item, with a colon between them. Therefore, we can use the string splitter routine to separate the lines, and then separate the items from their values by splitting again on the colon. The values returned will be stored into variables for future use. This code is:

The next section of code builds a common table expression (cte) for each weapon, armor and ring that is available. Note that for the armor and rings, I’ve added rows with 0 points to handle not purchasing those items. These ctes are (note that this will not run until all of the remaining sections are put together).

In the next cte, a list of all possible weapons with their costs, damage and armor scores are created. The actual weapon information is not necessary, just the values. This is accomplished by performing a CROSS JOIN between the three store items ctes. The rings cte is cross joined an additional time to handle a ring on each hand. Additionally, four CROSS APPLYs are performed. The first sums up the totals of the cost, damage and armor scores for the Player. The second calculates the damage to both the Player and Boss for that battle. The third adjusts the minimum damage to 1 in case it is less than 1. The fourth calculates the number of plays that the Boss and Player have for this battle by dividing the number of hit points (100) by the number of plays. Since this may return a fraction, and that fraction keeps the player alive for the next play, this is rounded up to the next integer value with the CEILING function. Additionally, the number of hits is handled as a numeric with a decimal position because in SQL Server, when an integer is divided by another integer, the result will be an integer with the fractional part truncated. A SELECT DISTINCT is performed because we only need to run the battle once for each condition:

Finally, the code returns the cost column from the first row where the number of Boss Plays is less than or equal to the number of Player Plays (since the player plays first, if they both would eliminate their opponent on that play, then the player eliminated the boss first):

And now we have the solution for Part 1. For Part 2, the challenge is to find the highest cost that the player can spend and still lose to the boss. All of the hard work has been done, and this requires only a minor change to the final query. Just replace the code starting at line 67 with this code:

Well, there we go, Day 21 has been solved. Come on back tomorrow to see how to solve Day 22.

source: http://www.myphillyalive.com/wp-content/uploads/2013/11/A-Elves-having-fun-Photo-by-Mark-Garvin.jpg

source: http://www.myphillyalive.com/wp-content/uploads/2013/11/A-Elves-having-fun-Photo-by-Mark-Garvin.jpg

In my previous post, I introduced you to the Advent of Code programming challenge, and explained how I’m going through the days, solving both parts of each puzzle. Continuing on, today we have day 20. In this challenge, it seems that Santa’s elves have too much time on their hands. To keep them busy, he is having them deliver presents. By hand. Door to door.

Santa sends the elves down a street. But this isn’t an ordinary street… this street is infinitely long. The houses on this street are sequentially numbered, starting with one. The elves (which is seems there is also an infinite supply of) are also numbered, starting with one. Each elf goes to every x house of the same number as the elf. In other words, elf #1 stops at every house. Elf #2 stops at every even numbered house. Elf #3 stops at every third house. And so on. At each stop, the elf leaves 10 times his number of presents. So elf 1 leave 10 presents, elf 2 leaves 20 presents, and so on.

For Part 1 of this puzzle, what is the lowest numbered house to receive at least a certain number of gifts? For me, I need to find out the lowest numbered house with 36 million gifts (and I’m left wondering just how long it would take to unwrap all of those presents).

To solve this problem, we need to determine what elves would be stopping at a particular house. We then need to sum up the elvf # times 10 for all of those elves, and see if it is at least the specified number. To determine if an elf would be stopping at a house, the elf’s number needs to divide evenly into the house number. In other words, we are looking for all of the factors of a number. In SQL Server, we can determine whether a number can be evenly divided into another with the modulo operator. This function returns the remainder, so we just need to look for results where the remainder is zero.

My first attempt to solve this is to use a set-based approach. In this approach, I use a virtual tally table, which is then cross-joined to itself (once for the sequential house numbers, and once for the elf numbers). The code checks that the elf number would go to the house number with the modulo operator, and it reduces the work by ensuring the elf number is less than or equal to the house number.

I did mention that this was my first attempt. This would be because this code locked up my laptop overnight for over 12 hours, and still didn’t complete (once I was able to determine the actual number, I came back to this and in running the code for just 1000 houses that included the correct house, this still took almost 90 seconds). Obviously, this is not a desired solution. So let’s look into an alternative.

In testing out this code, I found that this runs fine for an individual house. So I next decided to iterate through the houses, and to perform the determination of which elves go to that house in a set-based manner. The resulting code is:

In this newer incarnation of the code, the @HouseNbr variable is used to iterate through the house numbers. To keep track of the progress, I print out messages (with the RAISERROR function). At this point, patience is a virtue as you need to wait for the iteration to find the first house number that receives the specified number of presents. But at least while you wait, the laptop isn’t locked up and you can continue other work while waiting. And once the code returns the answer, you can enter it and move on to Part 2.

In Part 2, the elves will only stop at 50 houses each; however they will leave 11 presents at each house. With this new criteria, what is the new house number with this minimum number of presents?

Well, this is pretty straightforward change. Since each elf is now dropping of 11 presents instead of 10, change the 10 to 11 in line 13. And since each elf is only going to 50 houses, just add a new predicate where the Elf # * 50 is greater than or equal to the house number. Replace likes 13-16 of the above code with these 5 lines of code:

And there is my solution for day 20. I would love to find a better way to solve this – if you know of a way, please share it in the remarks.