There’s beauty everywhere – even in SQL Server.  I was just staring at this plan when I was struck by its beauty:

Stored Procedure Part 1

Oh, sure, it’s ugly, and I recoiled in horror at first, but when you get over that reaction, there’s an amazing amount of stuff there.  Someone – probably many someones – poured every waking moment of their life into building a query that does a staggering amount of stuff.  Then under that, SQL Server had to churn away to interpret all that text, look at the database, and decide how to execute this work.  All of that isn’t just man-hour – it’s man-decades, all distilled into one giant array of elbow lines.  One screen doesn’t even begin to capture it.  Let’s look at another view of that same plan:

Stored Procedure Part 2

It’s like a Mark Bradford piece, one of his giant collage mashups of aerial maps – you just can’t appreciate it until you see the plan in life size, scrolling around in all its sprawl. Once I saw execution plans as art, I went looking for pleasing patterns, and they were everywhere.

Parallel Parallelism

Repeated rows of parallel parallelism become little armies marching in formation.  Who cares if the work looks redundant?  Why tune the query?  Praise the developer for their artful design.

Plumbing the Depths

Lines of loops become fishermen casting their lines into the bottom of the execution plan, dragging up data from the deep.  The execution plan turns into a statement about religion: these fishers of data work diligently, raising up information to the highest power – the end user.

The Darkness Begins

Familiar enemies come to life as villains, bringing conflict to the art.  Curses, cursors!

Wiping the Slate Clean

Everyone wants a fresh start.  The artist tells of wiping the slate clean before starting anew, but thanks to the foreshadowing lines to the left, we all know how this will end – badly.  The strife begins again with each call of the query, struggling against itself.

Printing Pinstripes

Artists love to make prints, don’t they?  Some queries fancy themselves as artists, printing their own works of mysterious art.  What do they produce?  Why are the lines so long?  Seeing the plan through this small frozen window leaves the viewer wanting to know more.  Sometimes, though, you take one look at a plan and you don’t want to know any more whatsoever:

X Marks the Spot

Even SSMS can’t handle the zoom-out feature on this particular work of art.  It cries out for mercy with a blood-red X, signifying that it’s been wounded.  Everything is mortal, even software.  Ain’t art grand?

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

Hi!  I’m your SQL Server.  I know you don’t usually listen to me, so I’ve decided to take a drastic step and find you where you spend all your time – Brent’s blog.  Seriously, you need to spend less time on the intertubes and more time on the error logs.  You’re lucky this post is about me, not about you, or else I’d also have to divulge the fact that you installed Adobe Flash on my desktop.  Whoops, I just did.

Resolution 1: Keep in Touch with Loved Ones

Bad things have been happening to me all year long, but I’ve been keeping it secret from you.  It’s time you and I were more honest with each other.  To do this, I want you to write your name in my heart – and by that I mean, set yourself up as my operator.  The below script sets you up as an operator and sends you alert emails whenever bad things happen.  Before you run it, make these two changes:

• Change YourEmailAddress@Hotmail.com to your actual email
• Change 8005551212@cingularme.com to your actual phone’s email address (look up your provider)

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name = N'The Database Administrator',
    @enabled = 1, @weekday_pager_start_time = 0,
    @weekday_pager_end_time = 235959, @saturday_pager_start_time = 0,
    @saturday_pager_end_time = 235959, @sunday_pager_start_time = 0,
    @sunday_pager_end_time = 235959, @pager_days = 127,
    @email_address = N'YourEmailAddress@Hotmail.com',
    @pager_address = N'8005551212@cingularme.com',
    @category_name = N'[Uncategorized]'
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 016', @message_id = 0,
    @severity = 16, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 016',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 017', @message_id = 0,
    @severity = 17, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 017',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 018', @message_id = 0,
    @severity = 18, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 018',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 019', @message_id = 0,
    @severity = 19, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 019',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 020', @message_id = 0,
    @severity = 20, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 020',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 021', @message_id = 0,
    @severity = 21, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 021',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 022', @message_id = 0,
    @severity = 22, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 022',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 023', @message_id = 0,
    @severity = 23, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 023',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 024', @message_id = 0,
    @severity = 24, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 024',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name = N'Severity 025', @message_id = 0,
    @severity = 25, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1,
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 025',
    @operator_name = N'The Database Administrator', @notification_method = 7
GO

Resolution 2: Do a Data Detox Diet

Over the years, I’ve eaten a lot of bad stuff.  It’s time to do a little cleansing ritual to purge myself.  People have dumped in heaps of temporary backup tables that never got queried again.  Here’s a modification of Jason Strate’s query to find heaps – this one just looks for heaps (tables without clustered indexes) that haven’t been accessed since the last server restart (or database restore/attach).  Run it in my largest or most important databases:

SELECT SCHEMA_NAME(o.schema_id) AS [schema]
,OBJECT_NAME(i.object_id) AS [table]
,p.rows
,'EXEC sp_rename ''[' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(i.object_id) + ']'', ''[' + SCHEMA_NAME(o.schema_id) + '].[_ToBeDropped_' + OBJECT_NAME(i.object_id) + ']''' AS ScriptToRename
,'DROP TABLE ''[' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(i.object_id) + ']''' AS ScriptToDrop
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc = 'HEAP'
AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) IS NULL
ORDER BY rows desc

These heaps are slowing down my backup times, my DBCC times, and my 100 meter sprint times.  We can make some quick judgment calls based on table names – if they’re names like Sales_Backup2009BeforeDeployment, it’s probably a table someone made once as a backup – and then forgot about it.  We could either rename them temporarily and then drop ‘em in a few days, or just drop ‘em outright.  Standard warning about deleting data, blah blah blah.  (Hey, my resolution wasn’t to be more cautious.)

Resolution 3: Lose Waits

Users love me, and they show it to me by feeding me huge dinners of data. I love my life, don’t get me wrong, but sometimes I see those new guys with their solid state drives and their 512GB of memory, and I think, wow, they must be able to handle anything. I’ll never be that well-endowed, but there’s still some easy things I can do to get in better shape. Right now, I’m in heart attack territory, and it’s time to fix that.

I’ll start by working out with Brent Ozar and Buck Woody, watching this video where Brent is dressed up like Richard Simmons. It’s an oldie but a goodie, and it’s better than being seen in public with a ShakeWeight.

Now, weren’t those resolutions easy?  What, we’re not done yet?  You haven’t run the scripts?  What the hell, you lazy meatbag?  I can’t pull this off by myself – I need you to do ‘em.  What do you think this is, the cloud?  And wasn’t one of your resolutions this year to make sure the boss doesn’t think the cloud is better than us?  Hop to it before we lose our jobs.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

A few weeks ago, Microsoft announced that SQL Server 2012 will no longer be licensed by the CPU socket, and will instead be licensed by the core.  You can read more at Microsoft’s recap or Denny Cherry’s analysis.

After contemplation and discussing the issues with companies, I think of this as a few related announcements.

SQL Server is getting more expensive overall.

This shouldn’t come as a surprise to anybody: good things get more expensive over time, and crappy things get cheaper.  This change simply doesn’t affect me because I’ve never paid a single penny for SQL Server myself anyway, and I don’t plan to start now.  If I had to pay my own money for a database platform, I’d have switched to PostgreSQL long ago.

When I talked to my clients about this change, they all had one of two reactions:

• “OMG, THIS IS UNBELIEVABLE!”  These people were more worried about their individual careers than their budgets: DBA managers wanted to know if SQL Server would start waning in popularity, thereby reducing the value of their skills.  (If the high salaries of Oracle DBAs are anything to go by, bring on the licensing price increases.)
• “No big deal, but no new 2012 projects next year.”  These people understood that they were already licensed for their existing servers anyway, and only their ongoing maintenance fees would be impacted.  However, they immediately crossed Availability Groups off their list of 2012 projects, and that really sucks for me.  I’d been so excited about the ability to scale out with multiple read-only replicas, but the pricing just makes this a no-go for most of my clients.  Between the licensing changes and the traditional hesitation to deploy before Service Pack 1, 2012 is dead in the water for them.

Nobody decided to throw SQL Server out of the shop altogether, but some of them did start asking tough questions about their future projects.  It’s really hard to hire a good production SQL Server DBA right now (email us your resume if you’re looking in the Chicago, Los Angeles, or Portland areas, but no remote workers) so many of our clients are running understaffed.  One client said to me, “As long as I’m relying on outsiders for my database administration, what difference does it make whether it’s MSSQL, Oracle, DB2, Postgres, or the cloud?”

SQL Server used to be seen as the middle ground between expensive-but-awesome Oracle and free-but-limited open source.  Those days are gone – SQL’s pricing is higher, and open source platforms have gotten pretty darned good.

Limits kick in quickly on Standard Edition.

Standard Edition’s limits haven’t really changed significantly – but today’s hardware has, and Standard Edition isn’t keeping up.  If you’re struggling with application performance on Standard and you survive by throwing hardware at the problem, your options run out once you hit 16 cores and 64GB of memory.  At that point, you have to throw hardware and licensing at the problem by upgrading to Enterprise Edition.

I don’t think 16 CPU cores is really all that limiting.  CPU-intensive SQL Server queries tend to be the easiest ones for me to tune, and I’ve always argued that database servers aren’t app servers anyway.  The core-based licensing change just gives me more ammo to tell developers not to do string processing in the database server.

While 10-core Xeon CPUs are already available, they’re not typically deployed in 2-socket configurations.  You can technically buy a 4-socket box like the HP DL580 and populate it with 2 10-core CPUs, but that config just doesn’t make sense for Standard Edition due to the high cost.  With Intel’s upcoming tick/tock roadmap, the next couple of Xeon families are still slated to be in the 6-10 core range, so I don’t think the 16-core limitation is going to have a dramatic impact in 2012/2013.

The 64GB memory limitation, on the other hand, is frustratingly small given today’s memory prices.  32GB of server memory runs around $1,000, and memory can hide a lot of sins.  Standard Edition just doesn’t let you hide sins – you’re forced to spend manpower to keep tuning applications, and unfortunately, that’s not an option with third party applications.  I do a lot of work with independent software vendors (ISVs), and they’re frustrated that their customers can’t just buy $1,000 worth of memory to get awesome performance quickly.

There’s an opening for DBAs who love performance tuning.

It’s just you and me here, so let’s be honest: I make money when companies are in pain, and SQL Server’s licensing changes will inflict some pain.  Companies can’t just throw more CPU or memory at the problem anymore without writing a big check to Microsoft for additional licensing.  As a consultant, I can say, “I’ll fix that problem for less money than Enterprise Edition costs, let alone the cost of a server with more CPU sockets.”

If Microsoft had raised the 64GB memory limit, then companies could afford to mask problems longer by throwing memory at the problem.  They can’t, so I win.

The bummer for me is that I just can’t make the math work for scaling out with Availability Groups now.  I was really, really hoping for a lower-tier license cost for the read-only replicas (after all, they only support a subset of the work) but the cost to run a 5-node Availability Group is staggering.  Even if we use just 2-socket, 6-core servers across the board, that’s $412,440 of licensing – simply unthinkable, even if we throw in discounts.  It kills me, because I’m a huge fan of this feature, but right now companies have to be under tremendous pain in order to write a check that large to scale one application.  It’s just easier to tune SQL Server, and that’s where I come in.

It’s hard to be upset about that.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

My new sp_Blitz stored procedure helps you take over SQL Servers, identify risks, and build an inventory of what needs to be fixed.  Here’s the video from our Tech Tuesday Triage webcast series this week when I explained how to use it:

In the sp_Blitz output, I include a link to learn more about each problem on your server.  Thanks to the magic of web site analytics, I can see the most popular issues.

1. Slow Storage Reads or Writes – so often, our biggest bottleneck is storage.
2. Untrusted Foreign Keys or Check Constraints – I just recently started checking for this problem myself, and it’s popping up everywhere.
3. Single-Use Plans in the Procedure Cache – just one isn’t a problem, of course, and the severity of the problem is based on the number you have and the size of memory they take up.  Those numbers are listed in the sp_Blitz output.
4. Databases Owned by Users <> SA – and I love that this one hit the top 5 because it was our first user-contributed query in sp_Blitz, sent in by Ali Razeghi.
5. Triggers Found on Tables – no surprise there because this was one of the reasons I first started writing my Blitz scripts.  My developers were troubleshooting problems with insert/update statements and had no idea someone else had written a trigger to modify their data.

Those are just five of the checks – and there’s already over fifty.  If you’d like to add a check, email me with your script and the permission to use it, and I’ll add it into sp_Blitz too.

Check out sp_Blitz and see what it finds on your own servers!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts