Monthly Archives: December 2012

2012 Blog Statistics in Review

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

About 55,000 tourists visit Liechtenstein every year. This blog was viewed about 400,000 times in 2012. If it were Liechtenstein, it would take about 7 years for that many people to see it. Your blog had more visits than a small country in Europe!

Click here to see the complete report.

Filed under: SQL Server 2008 R2

SQL Server 2012 Standard Edition Licensing Limits

As you might be aware, SQL Server 2012 Standard Edition has some hardware-related licensing limits that I think should be adjusted in light of the capabilities of modern, commodity server hardware.

As a DBA and consultant, I would like to see everyone running SQL Server 2012 Enterprise Edition. It has many, many truly compelling features that make it absolutely worth the extra licensing cost compared to SQL Server 2012 Standard Edition. Despite this, I recognize that some organizations simply cannot afford these extra licensing costs (even though it is still far more affordable than Oracle licensing).

The first SQL Server 2012 Standard Edition licensing limit is that you are restricted to 64GB of RAM for the Database Engine and 64GB of RAM for SSAS and SSRS. Even if your database server has a much higher amount of RAM than 64GB, it will only be able to use 64GB of RAM for each of these services.

I think that is is a ridiculously low RAM limit, given the fact that DDR3 ECC RAM for servers is currently priced at around $10-$15/GB for 16GB DIMMs. Microsoft is essentially limiting you to using about $1000.00 of RAM if you are using SQL Server 2012 Standard Edition.

Modern, two-socket servers have 24 memory slots, so they can hold 384GB of RAM with these affordable 16GB DIMMs.  Even though you are limited to 64GB of RAM per service per instance, you should strongly consider getting a slightly larger amount, such as 72GB or 96GB, so that you can set your Max Server Memory setting to 65536 (which would be 64GB), and still leave plenty of RAM for the operating system.

This 64GB RAM limit did not exist for SQL Server 2008 Standard Edition, which could use up to the OS limit for RAM. It was first introduced in SQL Server 2008 R2 Standard Edition, and was not changed for SQL Server 2012 Standard Edition. That was a mistake, in my opinion, designed to drive more Enterprise Edition sales.

Windows Server 2012 Standard Edition can use up to 4TB of RAM (unlike Windows Server 2008 R2 Standard Edition, which was limited to 32GB of RAM), so there is even more precedent for not placing artificially low RAM limits on the Standard Editions of Microsoft Server products.

The second hardware-related licensing limit for SQL Server 2012 Standard Edition is that you are limited to the lesser of 16 physical processor cores or four processor sockets, whichever is less. I don’t really have a problem with the four processor socket limit, but I do think that a 16 physical processor core limit is simply too low. A two-socket, AMD Opteron 6200 or 6300 series based system could have 32 physical cores, while a two-socket Intel Xeon E7-2800 series based system could have 20 physical cores.

Of course, you really should not be using any of those processor families for SQL Server 2012 usage, since the Intel Xeon E5-2600 series is far superior for single-threaded performance and would also have a lower core-based licensing cost. It is also likely that the upcoming Intel Xeon E5-2600 v2 series (Ivy Bridge-EP) could have up to ten physical cores per processor, which would put a two-socket server over the 16 physical core limit.

I would really like to see Microsoft eliminate both the RAM limit and the physical core count limit for SQL Server 2012 Standard Edition. The features and advantages of SQL Server 2012 Enterprise Edition are valuable enough to convince people to buy Enterprise Edition when it is appropriate without these artificially low license limits. Microsoft could support this notion by doing a better job of explaining and selling the benefits of Enterprise Edition, with some blog posts and whitepapers.

I would love to hear your thoughts and opinions on this!

Filed under: Computer Hardware, Microsoft, SQL Server 2012 Tagged: SQL Server 2012, Standard Edition

SQL Server 2012 Diagnostic Information Queries (Dec 2012)

Here is the December 2012 version of my SQL Server 2012 Diagnostic Information Queries, with some minor tweaks and improvements to a couple of the existing queries. There is also one new query at the very end, adapted from my colleague, Erin Stellato.

To go with it is an updated, blank 2012 results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.

Many of these queries only work on SQL Server 2012, although some will work on older versions of SQL Server. If you are using an older version of SQL Server, you should use the version of my diagnostic queries for that version of SQL Server.

You should be aware that both Query 51 and Query 54 could take some time to run, depending on the size of your database and the speed of your hardware and storage subsystem.

As always, I welcome any feedback you may have about these queries.

Filed under: Microsoft, SQL Server 2012 Tagged: DMV, DMV Queries

The Complete SQL Server Holiday Guide

SQL_Holiday_GuideTitleIdeally the holidays are a time to catch up with friends, family and food. But end-of-year deadlines and work emergencies can also make it a stressful time. Between the festivities, you may face some tough choices. Do I go to John’s holiday beer bash, or do I finish up the playhouse I’m building for Suzy? I’m on call and I know an emergency may come in while I’m at the party? Should I really go? I’ll likely end up checking my email every thirty seconds.

We know these are tough calls to make, so the SQL Team wanted to offer some help.

We compiled a Holiday Gift Guide and a Holiday Etiquette Guide to assist. The Holiday Gift Guide is just that –a list of gifts a DBA would enjoy giving and getting this holiday season. We know you don’t have time to build everyone an original by hand, and while it’s tough to admit, many of your gifts may end up last minute purchases. Before jetting out, consult the list of gifts we’ve provided, so you make a choice out of inspiration not desperation.

And you’ll probably need a card to go with that. We got that covered too. The set of cards includes zombies and cats for the joker in the next cube and traditional warm greetings for your aunt. If your aunt is the joker in the next cube, then we have you really covered. (Share, or download and tweet or email the cards from Facebook.)

The Etiquette Guide lists some pointers for how to tactfully decline a holiday invitation due to a work emergency or any other work-related conflict. Be honest. Send a gift. Don’t over-explain yourself. Check out the guide for more.

We know balancing work and holiday duties is tricky. We hope to make it easier.  You can find all of these nifty reads in the complete SQL Server Holiday Guide.  Share it on Facebook or download the PDF.

The SQL Team wishes everyone a very happy holidays!

Unique Holiday Gift Ideas for the DBA

SQL_Holiday_GiftGuide (2)Ring in the cheer, the New Year is near! As the holidays approach and the shopping days grow short, we think we’ve got a one-stop shopping list to help you find the perfect gift for the extra special DBA in your life! The SQL Server Holiday Gift Giving Guide includes unique gift ideas to use on the job or on evenings out on the town.

For example, with our guide, you can dress your guy up in a timeless classic! That’s right, wood is the new black, and this wood bowtie is sure to be the talk of the party, along with HotSox for your leading lady. Of course, let’s not forget that the perfect gift always has a personal touch to it, and we believe that it can’t get much more personal than a pillow made from your face or an action figure made in your likeness. (Really, can it get much more personal than that?)

So, check out this quirky gift list and you’ll be sure to find great ideas for the holidays up ahead, the next birthday, or for any occasion. Then, tell us which item on the list is your favorite find, or let us know if you have other nifty gift ideas to share!

What are you waiting for? Get a head start on your shopping and download the SQL Server Holiday Gift Giving Guide now!

Disk and File Layout for SQL Server

Guest blog post by Paul Galjan, Microsoft Applications specialist, EMC Corporation. To learn more from Paul and other EMC experts, please visit – www.windowtotheprivatecloud.com and join our Everything Microsoft Community.

The RAID group is dead – long live the storage pool!   Pools fulfill the real promise of centralized storage – the elimination of storage silos.  Prior to pool technology, when you deployed centralized storage you simply moved the storage silo from the host to within the array.  You gained some efficiencies, but it wasn’t complete.  Pools are now common across the storage industry, and you can even create them within Windows 2012, where they are called “Storage Spaces.”  This post is about how you allocate logical disks (LUNs) from pools so that you can maintain the visibility into performance.  The methods described can be used with any underlying pool technology.

To give some context, here’s how storage arrays were typically laid out 10 years ago.

Layout of storage arrays ten years ago

A single array could host multiple workloads (in this case, Exchange, SQL, and Oracle), but usually it stopped there – spindles (disks) would be dedicated to a workload.  There were all sorts of goodies like virtual LUN migration that allowed you to seamlessly move workloads between the silos (RAID groups) within the array, but those silos were still there.  If you ran out of resources for Exchange, and had some spare resources assigned to SQL Server, then you’d have to go through gyrations to move those resources.  For contrast, this is how pool technology works:

Pool Technology

All the workloads are sharing the same physical resources.  When you run out of resources (either performance or capacity) you just add more. The method is really enabled by automatic tiering and extended cache techniques.  So the popularity of pool technology is understandable. Increasingly I see VNX and VMAX customers happily running with just one or two pools per array.

The question here is this: if you’re not segregating the workload at the physical resource level, is there any need to segregate the workloads at the logical level?  For example, if tempdb and my user databases are in a single pool of disk on the array, should I bother having them on multiple LUNs (Logical Disks) on the host?

If the database is performance sensitive, then the reason is “Yes.” If you don’t, you may have a difficult time troubleshooting problems down the road.  Take an example of a query that’s resulting in an extraordinarily large number of IOs.  If your tempdb is on the same LUN as your user databases, then you really don’t know where those IOs are destined for.  It also reduces your ability to potentially deal with problems.  Pools may be the default storage option, but they’re not perfect, and not all workloads are appropriate for pools.  Segregating workloads into separate LUNs allows me to move them between pools, in and out of RGs without interrupting the database.

So here’s my default starting layout for any performance sensitive SQL Server environment:

  • Disk 1: OS/SQL Binaries
  • Disk 2: System databases (aside from tempdb)
  • Disk 3: tempdb
  • Disk 4: User databases
  • Disk 5: User DB transaction logs

This allows me to get a good view of things just from perfmon.  I can tell generally where the IO is going (user DBs, master, tempdb, logs etc), and if I need to move things around, I can do so pretty easily.

SQL Server 2008 Diagnostic Information Queries (Dec 2012)

Here is the December 2012 version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements to a couple of the existing queries. There is also one new query at the very end, adapted from Erin Stellato.

To go with it is an updated, blank 2008 results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.

These queries will work on both SQL Server 2008 and SQL Server 2008 R2 (although some of them will only work on SQL Server 2008 R2 SP1 or later). This is indicated in the instructions for each query.

You should be aware that both Query 46 and Query 49 could take some time to run, depending on the size of your database and the speed of your hardware and storage subsystem.

Filed under: Microsoft, SQL Server 2008, SQL Server 2008 R2 Tagged: DMV, DMV Queries

SQL Server 2012 Cumulative Update 5

Microsoft has released SQL Server 2012 Cumulative Update 5, which is Build 11.0.2395. I count 28 fixes in the public fix list. Some of these fixes look fairly important, so you should take a look at the fix list.

Here are a couple of the more important hotfixes:

SQL Server 2012 experiences out-of-memory errors

FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008

This Cumulative Update is only for SQL Server 2012 Cumulative Update 5. It will not work with any other major version of SQL Server or with any other Service Pack Level.

Ideally you should be on the Service Pack 1 branch of SQL Server 2012.

Filed under: Microsoft, SQL Server 2012 Tagged: Cumulative Updates, SQL Server 2012 Cumulative Update 5

SQL Server 2008 R2 SP2 Cumulative Update 4

Microsoft has released SQL Server 2008 R2 Service Pack 2 Cumulative Update 4, which is Build 10.50.4270. I count 34 fixes in the public fix list. Some of these fixes look fairly important, so you should take a look at the fix list.

This Cumulative Update is only for SQL Server 2008 R2 Service Pack 2. It will not work with any other major version of SQL Server or with any other Service Pack Level.

If you are running SQL Server 2008 R2 Service Pack 2, you are on the latest branch of the SQL Server Server R2 “release tree”, which is a good place to be. This means you have a much longer time remaining before your Service Pack is retired.

When a SQL Server Service Pack is “retired” that means that it is considered an “unsupported service pack” by Microsoft CSS. This means that you will only get limited troubleshooting support until you upgrade to a supported Service Pack. It also means that there will be no more Cumulative Updates for that Service Pack.

Filed under: Microsoft, SQL Server 2008 R2 Tagged: Cumulative Updates, SQL Server 2008 R2 SP2 CU4

SQL Server 2008 R2 SP1 Cumulative Update 10

Microsoft has released SQL Server 2008 R2 Service Pack 1 Cumulative Update 10, which is Build 10.50.2868. I count 15 fixes in the public fix list. Some of these fixes look fairly important, so you should take a look at the fix list.

This Cumulative Update is only for SQL Server 2008 R2 Service Pack 1. It will not work with any other major version of SQL Server or with any other Service Pack Level. Speaking of SP levels, I think you should be planning on moving to SQL Server 2008 R2 SP2 if you have not already done so, since SQL Server 2008 R2 SP1 is due to be retired in about six months.

When a SQL Server Service Pack is “retired” that means that it is considered an “unsupported service pack” by Microsoft CSS. This means that you will only get limited troubleshooting support until you upgrade to a supported Service Pack. It also means that there will be no more Cumulative Updates for that Service Pack.

Filed under: Microsoft, SQL Server 2008 R2 Tagged: Cumulative Updates, SQL Server 2008 R2 SP1 CU10