This is a great little video that helps reinforce a few things.

1. Make sure your backups are running.
2. Test your recovery strategy.
3. Working from home can solve everything.

You have no idea that what you are not doing is causing you pain.

Then again, how could you know this? I mean, if you are not doing it, why would you ever think that your inaction could be the very thing that is causing you heartache every time your phone rings in the middle of the night?

I see DBAs make these three mistakes repeatedly. But they are not a mistake as you might think of a mistake: a misstep along a specified process. No, these mistakes are brought about because the person simply didn’t know any better.

But they should.

I’m hoping by listing them here, it will help.

1. You Don’t Have A Recovery Plan

The number one reason for a DBA to exist is this: recovery. It should be your primary focus each and every day. You need to be able to recover from any event, no matter how big or small. Lose an entire data center? You had better be able to recover that data. Lose a handful of LUNs? You had better be able to recover that data. Someone deletes a table? You had better be able to recover that data.

So why then do I see many DBAs spend time focusing on a good backup strategy but never spend an equal amount of time on a recovery strategy? Of course we know that every good recovery strategy must start with a good backup strategy, but so many DBAs (especially novice ones) never bother to test their restore process. Are your backups written to disk, and then saved off to tape? OK, that’s fine, now…quick…how long will it take to recover that tape? If your end user calls and says they need to recover a database from last week, how long before that request can be completed. Here is a better question: how long before your end user become very annoyed that they have to wait any longer?

There is only one way to be certain that the backups you have can even be used for recovery: you need to actually recover them without error to be 100% certain. That’s why I wrote this article for Simple-Talk two years ago on how to use statistical sampling in order to be 95% confident that all your backups are valid.

Taking backups is not even half of what you need to do as a DBA. You need to recover, from a variety of failures, and you need to be able to do it as quickly as possible. Having a plan is good, but practicing that plan is even better.

2. You Pretend That Virtualization Doesn’t Matter

I know that VMWare will tell you that running in a virtual environment can be up to 99% of the performance you have in a physical environment. I also know that when people see that 99% number they decide that they can shove as many guests onto a VMWare host as space will allow for and they still believe they will have 99% performance for each and every instance. This is especially true if those instances are believed to be “lightly used”.

The beauty of running your servers in a virtual environment is that you get to share resources and easily migrate guests between hosts. The ugly truth about running your servers in a virtual environment is that you get to share resources and easily migrate guests between hosts.

As a DBA you are now managing a piece of software that runs inside of a guest O/S that is running on a host, that has a hypervisor, and that likely shares its disks with other hosts as well. What that means is that when someone runs a query, that query has to travel down that stack and back again in order to return a result. Now, if someone comes to you and says that they are having a performance issue what would you do first? Would you look at the query? Or the O/S metrics for that guest? Or something else?

Or would you prefer to spend five minutes examining the virtual layer to see if any bottlenecks exist. For example, if the host is seeing a spike in disk latency, then it is likely the guests are seeing disk latency as well. So, how much time would you want to spend trying to make a query faster when the issue has to do with an overloaded disk subsystem? I know what my answer is: none. What if the bottleneck is host CPU saturation? How long will you want to spend trying to tune a query when you already know that the issue is at the host layer?

Pretending that virtualization doesn’t matter for your database servers is a mistake I see being made by both DBAs and server admins. Those counters hold the key to being efficient at performance tuning in a virtual world. But they are only useful if someone knows to be looking at them.

3. You Don’t Configure Memory Settings Properly

This one drives me crazy.

I still see database servers with the default memory settings. When a customer comes to me and complains about having memory issues one of my first questions is: what are your default settings?  At this point I am shocked when someone doesn’t say “we left those alone”. Configuring your default memory setting is one of the easiest thing any DBA can do and should be part of a standard checklist when rolling out a new instance. Yet it still is not happening.

Things get even trickier with memory in a virtual world, as you are competing with other guests for host memory. You simply must know what your memory requirements are for your database server and then work on securing that memory. The easiest way to do this is to configure those memory settings. Even I can’t remember everything, so that’s why I just reference this post when I need general guidelines regarding the configuration of the min and max memory settings.

There you go, three things that you are likely not doing today and are also equally likely to be causing you and your end users lots of pain.

It all started with a garbage can.

Not just any old garbage can, mind you. It was this one from SimpleHuman. We’ve had it for years, and it was wonderful except for one small detail.

It was broken.

The lid would not stay open any longer. The springs underneath had broken away, most likely due to the fact that SimpleHuman didn’t do any QA testing that involved very small children abusing the lid in many, many ways. Considering the cost  of the can we decided to just deal with the broken lid…for the past five years.

Because we could not find a replacement lid, earlier this year we considered buying a new can. Before opening my wallet (again) I decided to drop a quick note to SimpleHuman on their website and inquire about getting a replacement part for a can they no longer were offering for sale. Within a few days I got back an email that seemed rather amazing (and I am paraphrasing here):

“We’re sending you a replacement.”

I didn’t really believe that they were going to send me the part because (1) I don’t recall giving them my address and (2) there was no way I was covered under any type of warranty. Two weeks later a new lid arrived. I was shocked. And then it hit me: I am now a customer of SimpleHuman for life.

Why am I telling you this? Great question.

I often hear people ask “what is the ROI for this”? I think ROI is an awful way to measure something. It is shortsighted at best, typically focused on monetary gains. And ROI is often hard to calculate with any degree of certainty as well. I mean, you know how much something will cost, that part is easy. What you can never predict is how much return you will get. That’s why the SEC makes companies tell you how “past performance is no indication of future results”.

When I hear of someone asking about the ROI on something I often laugh to myself and think “what’s the ROI on the water cooler”? How many times has there been a conversation around the water cooler that has led to something amazing being done? And what is the ROI on that coffee table in the reception area? Can’t we measure that? What about the ROI on this blog?

I think a much better metric for everyone to digest is this: ROE, or Return On Experience.

Back to SimpleHuman. My experience with them as a customer has resulted in my loyalty. Even if I never buy something from them again the experience has led to me singing their praises in this blog post. If they had said to me “hey Tom, here’s $100 to write something nice about us” I would have refused. But that is essentially what happened, right? They gave me something of monetary value, sure, but they also gave me an experience that has more than just money behind it.

Now, stop and think for a minute. Think about your customers. Think about the end users for the systems you build and maintain.

What is your ROE with them?

Now, what can you do today to make that ROE even better than yesterday?

Tomorrow is the first Monday of the month, which means it is time for the latest installment of Meme Monday.

The rules of Meme Monday are simple: I give you a topic. You write about the topic. No need to link back, or tag anyone. It is a writing exercise only.

The topic for tomorrow is this: If you could go back in time and meet yourself on Day One of your IT career, what advice would you give?

In my case, I would go back, slap myself in the back of the head, and tell me to NOT send “those” emails.

You know the type of emails I am talking about. It is often much better to pick up a phone.

I look forward to reading your entries! Just post to twitter with the #mememonday hashtag and I can read them all.

Which one of these orders more bacon?

No matter what our profession, we all have our favorite set of tools to help us get the job done.

Database Administrators will have a set of scripts that we use in order to gain insight into the server instances under our care. I often see beginner DBAs look to the counters provided by Perfmon (AKA, the Resource and Performance Monitor). The trouble here is the counters themselves don’t necessarily give you enough details to make an informed decision about what actions you should take next. You need to look at a handful of the counters at any given time (or over a period of time) in order to better ascertain where the bottlenecks exist.

Here is a list of five metrics that I find myself frequently using when troubleshooting performance issues. They help me get insight quickly into some of the deep recesses of SQL Server so that I can easily corroborate with standard Perfmon counters in order to troubleshoot issues for customers. Ideally these are all being collected over a period of time so that you can do some proper trend analysis.

These are the metrics, in no particular order, that you should be using during the first few minutes of analysis. They will help you save time.

1. Signal Waits Percentage

This metric has provided the most value personally to me over the years. I started using it four years ago when troubleshooting CPU issues. The instance itself was showing signs of “slowness”, but the overall CPU utilization numbers were nominal. I came across this article by Tom Davidson and found out that I could calculate the percentage of signal waits to the overall waits in order to understand internal CPU pressure exists. [A "signal" wait means that a session is in the "runnable" queue, waiting for a signal from an available processor.]

I filter out some background tasks for this query as well. The value you want returned here is for no more than 20-25% of your total waits to be signal waits. If you are consistently seeing numbers greater than 20% then you are having internal CPU pressure. You can remedy the situation by reducing the number of sessions (not always likely), increasing the number of available CPUs (also not likely), or reducing the amount of time the queries need to execute (often very likely, and sometimes easily done).

SELECT (100.0 * SUM(signal_wait_time_ms)/SUM (wait_time_ms)) AS [SignalWaitPct]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
AND wait_time_ms <> 0

2. SQL Compilation Percentage

Another sneaky performance issue is having a high number of statements being compiled. There happens to be a performance counter for compiles but the counter itself does not have much meaning unless you also know the overall number of batch requests. Rather than look at either of those counters separately what you want to do is look at their ratio.

The recommended percentage for compilations is roughly 10% of the total number of batch requests. The following code will show you the current percentage of compilations to the number of batch requests:

SELECT 1.0*cntr_value /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec')
AS [SQLCompilationPct]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec'

At this point someone usually asks “what about re-compilations”? Well, re-compilations should only be roughly 1% of the total number of batch requests, so that code would be like this:

SELECT 1.0*cntr_value /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec')
AS [SQLReCompilationPct]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Re-Compilations/sec'

3. Page Lookups Percentage

What I like most about this metric is that it often gives me a hint regarding plan cache bloating without me having to dig through the plan cache itself. I also get insight on the queries hitting the instance, to see if they could use some tuning/indexing as there are likely some inefficient query plans being utilized.

What this metric really shows me is if I have one of two extremes. Either I have a few rather large queries hitting my server, or I have a lot of little queries (probably ad-hoc) hitting my instance and likely leading to some plan cache bloating.

What I want to see here is a value that is less than 100 on average:

SELECT 1.0*cntr_value /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec') 
AS [PageLookupPct]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec'

4. Average Task Counts

I got this metric from Glenn Berry (blog | @GlennAlanBerry), and I use this one to determine what my system has been busy doing. I like how Glenn always has a brief description for what you want (or don’t want) to see. In this case it is as follows:

• Sustained values above 10 suggest further investigation in that area
• High Avg Task Counts are often caused by blocking or other resource contention
• High Avg Runnable Task Counts are a good sign of CPU pressure
• High Avg Pending DiskIO Counts are a sign of disk pressure

I can use this one in conjunction with others to get some corroborating evidence as to what is happening under the covers:

SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE)

5. Buffer Pool I/O Rate

The most misunderstood counter I see is Page Life Expectancy. I know I used to pay attention as if it meant something. Over time I have come to realize that it is a throwback to days of yore when having 4GB of memory was a really big deal. In other words, its intended purpose no longer has the same value as it once did. It is not totally worthless, however. You just need to know what to do with it.

What you really want to see is the rate at which your pages are being cycled through the buffer pool. The following code returns the “Buffer Pool Rate” in MB/sec. I usually look for rates around 20MB/sec as a baseline. Why that number? Because if I have a “typical” server with 56GB of RAM available for the buffer pool (thank you, locked pages in memory) and I want to keep my pages around for an hour or so (3600 seconds), then I come up with 56000MB/3600sec, or about 15.5 MB/sec. That’s why I look for a sustained rate of about 20 on average, and if I have a spike upward from there then I know I am having memory pressure (pressure that might otherwise fail to be seen if I only examine the PLE counter).

Here is the code I use:

SELECT (1.0*cntr_value/128) /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name like '%Buffer Manager%'
AND lower(counter_name) = 'Page life expectancy')
AS [BufferPoolRate]
FROM sys.dm_os_performance_counters
WHERE object_name like '%Buffer Manager%'
AND counter_name = 'total pages'

You should also be aware that if your server is NUMA aware then you will want to take that into consideration whenever you try to use PLE as a performance metric.

There you go, the five metrics I utilize for customers whenever they need my help in solving performance problems. I’m always looking to add to my toolbox, so if you have a favorite metric you want to share just leave it in the comments below.

As a system admin, DBA, or a developer it is our job to root out performance problems quickly. The trouble is that there are so many things that can cause performance issues it can be difficult to know every possible root cause. This is especially true when the root cause lies somewhere outside your area of expertise or control.

It is also true when the root cause is right under our noses the whole time.

Here are six things that I have seen cause performance issues for end users. None of them are complex by nature. Each is something that is simple and easily addressed when they are known.

So that’s why I wrote this post, in an effort to get these into the mainstream. If you are not taking steps to to mitigate these possible performance issues, start now. If you find yourself one day sitting at your screen and scratching your head wondering “what’s wrong”, then take a chance that one of these six things could be the culprit.

1. Configure your memory settings

I see this scenario all the time: Customer notices they are low on memory. They call me. I ask if they have configure the max memory setting to a non-default value. They say no.

Lather, rinse, repeat. I used to count the number of times this would happen to me but I stopped after the first hundred or so. It is one of the reasons I put together a talk on SQL Server memory management last year, because it just seemed to be the easiest thing for people to be doing and yet they weren’t doing it.

I wrote a post a while back to help explain this fact as well. And then there is this post by Glenn Berry (blog | @GlennAlanBerry) and one done by Jonathan Kehayias (blog | @SQLPoolboy), and yet still people don’t know to configure these settings. Take the few minutes you need to adjust your settings and you will reduce the chances of having performance problems due to memory settings.

2. Optimize tempdb

This one is often overlooked as most folks wait around for a problem with tempdb before thinking about how best to optimize it for performance right from the start. This SQLCAT link mentions this as well, and there is info over at MSDN that you should review before making changes. And yes, you should consider moving tempdb to it’s own dedicated set of drives.

In fact, I would tell you that the placing of data files, log files, backup files, and tempdb onto separate disks should be a part of any standard database server build. I emphasize the ‘should’ because for some odd reason I still see shops where this is not true. If that is the case for you, and you need to boost performance, then roll up your sleeves and get started on the work that should have been done from the onset.

3. Increase transaction log throughput

Another area that will affect your database performance has to do with the configuration of your transaction logs. Kimberly Tripp (blog | @KimberlyLTripp) has a great post on the 8 Steps to Better Transaction Log Throughput. Many folks don’t think about how something as simple as managing log file growth properly can help with overall performance.

One of the hidden parts of transaction logs has to do with virtual log files (VLFs), the “chunks” that are strung together to form the transaction log. I wrote about VLFs a while back when I witnessed firsthand the havoc they could cause. Until I knew about VLFs I had no idea why my log files were “misbehaving”. After I found out about them I decided to come up with a way to identify databases that had too many VLFs and were causing us issues (both for performance and administrative activities) and took the necessary corrective actions. Those actions also led to a review of overall transaction log throughput, as I wanted to make certain we were deploying servers to our end users that were configured for performance right from the start because it is a real pain to go back after the fact and say things like “Oh, sorry, I didn’t realize you wanted that server to perform well, so we just gave you all the default stuff which is usually crappy, we can go back and fix all of that for you and have things ready in about two months.”

Save yourself that headache. Take care of your transaction logs from the start. You can thank me later.

4. Build a proper reporting database

I see a lot of “mixed-use” databases in every type of industry. At some point someone gets the idea that they need a database in order to store information, usually customer information at first, then things like products, inventory, or sales. You know, standard business stuff. Most of those systems are built along guidelines for typical transactional based processing. After some time, perhaps months or years, other people in the office think to themselves “hey, I need some info, and don’t we have it already in that database”? So they go about building some reports.

And before you can say “writers block readers” you soon have yourself an OLTP system that is being used more often for OLAP purposes. I used to have a job that would alert me whenever a spid was blocked for 300 seconds. That’s five minutes. Of doing nothing but waiting for someone else. When I would call the person and tell them something like “hey, you stuff is blocked and just sitting there, and it is Joe doing the blocking” I would get responses that would range from “OK, his stuff needs to run, so I can wait” to “OMG WTF IS JOE DOING IN MY DATABASE RIGHT NOW WHILE I AM TRYING TO FINISH MY WORK!”

Building a proper reporting solution can help you improve performance for everyone, and it is often never talked about. Usually people focus on trying to coexist when they should really talk about signing the divorce papers.

5. Choosing the correct datatypes

More often than not, database performance (good and bad) can be traced all the way back to the design phase. During that phase there is (or, there should) be a discussion around the choice of proper data types for the underlying data. Unfortunately I see that such discussions typically don’t happen, thanks to tools like Visual Studio that will create tables with columns that are nvarchar(50) by default.

Most of the time I try to talk to customer about the selection of data types and the impact on performance I get back one of two responses: “I didn’t know that” or “It’s vendor code, we can’t touch it”.  Clearly we need to raise awareness in either case. Also, we need to stop blaming the database for the problems that have been created at design time. The database is only doing what you have asked, and you have likely asked it to work inefficiently and slowly. Check out this article by Greg Larsen regarding performance and data types. And here is a list of bad habits to kick by Aaron Bertrand (blog | @aaronbertrand).

6. Configure Your Power Settings

I know that “being green” is all in vogue these days. As a result we have things like power savings options for servers that allow for the CPU to be throttled when not in use. The net result of this is that you get the chance to explain to your end users that while database performance is not very good they can feel better knowing that they are helping to keep polar bears colder at night. Then again, you can only make such a comment to them if you are aware about these power savings options. Since most people have no idea about these power settings they are likely to spend many fruitless hours trying to track down the root cause of performance issues. In an incredible twist of irony, the extra power they consume while trying to research the issue brought about by power saving results in those polar bears needing more and more suntan lotion with each passing year.

The above six items are all ways that you could be suffering from poor database performance. The problem I see with many DBAs is that they try to fix the one thing that is on their plate at the moment, without seeing a bigger picture. Similar to saying “you can’t see the forest through the trees”, many DBAs are so focused on getting one query at a time tuned that they never bother to look up and see things like memory settings, or transaction log configurations, or optimizing tempdb for performance as ways to improve performance. Rarely will I ever see a discussion about changing data types or building a proper report solution and yet those are also ways your performance could be slowly draining away.

These are all items that can be addressed early on in any project, or even a rollout of a new instance of SQL Server. It doesn’t take much time and the benefits are worth every minute.

I’ve been writing (and blogging) for about ten years now. I’ve written lots of things over the years and I have learned how much better my writing becomes when I remove words upon editing. So much improvement can be found that I often feel it might have been best to have not written anything at all in the first place.

Over the weekend I came across a quote that gave me pause. It is attributed to Antoine de Saint-Exupéry, and has to do with writing as well:

“Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.”

After reading that quote I started to think about data. I often see people trying to cram more data “just in case” in might be needed later. I will also find databases that are so badly abused that entire teams of people agree that “a re-write is needed”. They will discuss at great length what to do about sunsetting one system as they build another system with EVEN MORE crap than what the first one had. It can be surreal to experience, too. Some people are so attached to the old system you would think it was a family member or pet. They fight to keep someone from pulling the plug while others talk about what snacks they need to order for the wake.

Before you go about trying to shove more things into a perfectly good database do yourself a favor: stop and think. Are you shoving those extra columns onto that table so you can run a new report? Consider the benefits if you have your own reporting database, maybe even your own cube, before you consider just blindly shoving more and more stuff into the current system. Many times I see this as a result of “scope creep”, which is like the herpes of any good database design project…you know it’s there, you put some ointment on it, and the blemish goes away for a while but it never really goes away, the bad stuff is still with you, just lurking under the surface.

Think more about what you can take away from your current systems.

Think how much better things would be if you could separate out the transaction parts of your system from the reporting components.

Think about how much better things might be with less, and not with more.

“Il semble que la perfection soit atteinte non quand il n’y a plus rien à ajouter, mais quand il n’y a plus rien à retrancher.”

Yesterday I showed you some real world SQL joins and their associated diagrams. I also left as an exercise for the reader this question regarding a standard three table join:

“explain why I could need 127 different diagrams”

I had a few people guess the answer on Twitter and G+ as well as some emails. So I decided to post the answer here today in case you wanted to understand it a bit more.

Let’s consider the seven regions that we have in the following diagram: 

Now, consider the fact that no matter what type of join we want to execute (INNER, CROSS, LEFT, RIGHT, FULL OUTER) the end result is that we are either going to return data from one of those seven regions or not return data. In my diagram the green shaded regions represent areas for which we will return data and white is for regions where no data is returned.

In a nutshell: they are bits. On or off.

Most people still stumble at what needs to be done next, so I offer them a more familiar example. Consider you have a coin to flip, and also consider that after each flip we will either have a heads or a tails (yes, we will leave out the other possibilities such as landing on an edge, or disappearing into a wormhole, or having it swallowed by a marmot). If I flip that coin three times then this becomes the entire list of possible outcomes (H = result of flip is heads, T = results of flip is tails):

HHH
HHT
HTH
HTT
THH
THT
TTH
TTT

Eight possible outcomes, right? Most people see this example and say “yeah, I know that, but how does that help me with the above diagram?”

It is because each region in the diagram is just like a coin flip. Either we are getting data or not. Having seven regions is like having seven coin flips.

When I flip a coin three times I end up with 2^3 = 8 possible outcomes.

When I flip a coin seven times I end up with 2^7 = 128 possible outcomes. But one of those outcomes would be for when all regions are not selected, which is a fairly trivial example that we wouldn’t put on a diagram, so we are left with 127 total diagrams that we would need to draw.

That being said, many of the diagrams are repeats of each other. The join syntax for returning one table and the intersection of two other tables is the same; we just swap A with B with C and we can consolidate the need for a lot of the 127 diagrams.

But it was still far too many diagrams for me to think about wanting to draw and show the syntax for. Also: it isn’t practical, because the examples I gave yesterday are what we see in reality. Who wants to see textbook examples of joins anyway?

Just old math geeks like myself, I suppose.

I found a diagram recently that helped to explain the different types of JOIN syntax. Being the type person that enjoys the visualization of abstract concepts such as set theory this diagram spoke to me. Despite the diagram being well done something seemed slightly wrong about it. I thought about it for a while and then it hit me: the diagram does not accurately reflect the complexity of joins for queries I see every day. The diagram seems better suited for a classroom, not the real world. So I decided to put together my own diagrams for you to show you the things I see frequently. You’re welcome.

1. The Three Table Join

This first diagram that I thought about was a simple three table join. In fact, I was going to put together a new diagram for all the possible three table joins but decided that I really did not have the desire to put together 127 different diagrams (I am leaving it as an exercise for the reader to explain why I could need 127 different diagrams, leave your answer in the comments below). So, here is the first type of join I thought about:

What I find worth mentioning here is that you can change your result set by altering that second join. Instead of A.c1 = C.c1, change it to B.c1 = C.c1 and you will find that you may not return the expected data in your select list (another exercise for the reader there, go ahead and try that for yourself or you can just download this quick sample script).

Another fascinating point here is that it occurred to me that this same T-SQL would also apply to the following:

This diagram seems slightly more realistic to me, as I am likely to start with one table and need to go out to two (or more) other tables with joins.

2. The Table Join For Disjointed Tables

That idea got me thinking even more (always a dangerous thing) and I then thought about other types of joins I see frequently:

Yeah, that’s right…multiple tables with no relation between them. The end user just selects everything possible and then filters the rows based upon the WHERE clause or uses features in Excel or PowerPivot to do the filtering. In a way this is what I visualize “Big Data” to be, the grabbing of as much data as possible, mashing it together, and filtering the results until you find something that helps you get one step closer to wherever it is you wanted to go today.

3. The One Big Table Join

After that graph it suddenly occurred to me that there are a lot of people who create OLTP databases with really wide tables. I mean, REALLY wide tables, hundreds of columns. So, their syntax and graph looks like this: 

Everything in one table, what a perfect database design, right? What could be simpler than that? In fact most data warehouses are built this way, where they have a few tables that are really wide and you may not need to join to any other tables. All the more reason to point out how the original diagram was not very indicative of the queries I see. In fact, the last picture I have for you is the EXACT type of query I see all the time.

4. The “What Were They Thinking” Table Join

How many times have you seen code similar to the following? Ever try to draw the picture? Here is my visualization: 

Yes, that’s right, I did include the infamous =’NULL’, don’t pretend like you haven’t seen that one before! I also included column names of ‘pid’ and ‘p_id’ to give you an indication about how we often see column names that aren’t very descriptive and leave you wondering if the columns are truly representing the same value type.

I think the above five diagrams are a lot closer in reality to the types of queries we see every day. And the types of queries we are expected to tune, often immediately.

I often find it easier to help with performance problems once I am able to visualize what the joins are representing. I bet your end users would appreciate seeing similar diagrams as well, it may help them to understand what they are asking for, and it would likely help lead to a healthy discussion about design considerations. If nothing else, it should help to level set the expectations for performance.

This video is an hour-long but well worth sharing with you today. One of the main takeaways from this video I want you to have is the fact that troubleshooting performance is not always rocket surgery. When you watch this video and listen to Conor Cunningham talk about some of the real world examples he has faced you will see what I mean.

I also liked how they help you to understand the concept of how to “bucketize” performance issues. It goes something like this:

Are all queries affected, or just a subset of queries affected?

If all queries are having performance issues then you will want to examine settings that affect the entire instance, such as memory settings, or high CPU utilization. You will want to do this first before trying to examine any one particular query.

If it is only a subset of queries (or users, or a particular application) then you will want to focus your efforts on those queries first. Otherwise you will be wasting time trying to fix one query without addressing the root cause of the performance issue affecting all queries. [A great example of this for my customers has to do with virtualization: why waste time trying to tune one query when the reason for the slowness is because your memory settings have been dynamically changed? Better to spend five minutes talking to your server admins about the memory for your guest than to waste time trying to get a query to suddenly run faster with less memory available.]

Just being able to diagnose “all versus some” in the first five minutes of triage in a production down situation can save you a lot of time as you begin to form your action plan to correct problem and bring performance back to within acceptable limits. Set aside some time this week to watch this video and learn more.