There’s a very old saying, “When you find yourself in a hole, stop digging.”

And my evidence today is:

That’s certainly not the hairiest execution plan I’ve seen. In some ways, it’s not all that horrible. But it sure is evidence that someone was down in a hole and they were working that shovel hard.

If you’re interested, most of the operators are scans against a table variable that’s 11 million rows deep. There are also table spools chugging away in there. And the select statement only returns 1500 rows.

Please, stop digging.

SQL Azure is still SQL Server at the end of the day. This means it is entirely possible to write queries against SQL Azure that really… what’s a good word… stink. So what do you do? It’s all in the cloud. You couldn’t possibly tune the queries, right? Wrong. Many of the same tools that you have available to you, such as execution plans and dynamic management objects, are still available in SQL Azure.

Let’s talk DMOs for a second. First off, don’t make the mistake I did of trying to run these outside the context of a specific database on SQL Azure. You’ll get extremely inconsistent results, trust me on this. Anyway, I did a quick run-down on some of the most used DMOs for performance tuning, the sys.dm_exec_* set. Here’s a complete listing of those procs and whether or not they’re available to you in SQL Azure:

SELECT  *
FROM    sys.dm_exec_requests AS der
--available

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_query_plan(der.plan_handle)
--available

SELECT  *
FROM    sys.dm_exec_requests der
        CROSS APPLY sys.dm_exec_sql_text(der.sql_handle)
--available

SELECT  *
FROM    sys.dm_exec_query_stats AS deqs
--available

SELECT  *
FROM    sys.dm_exec_cached_plans AS decp
--invalid object

SELECT  *
FROM    sys.dm_exec_connections AS dec
--available

SELECT  *
FROM    sys.dm_exec_cursors AS dec
--invalid object

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_plan_attributes(der.plan_handle) AS depa
--invalid object

SELECT  *
FROM    sys.dm_exec_procedure_stats AS deps
--invalid object

SELECT  *
FROM    sys.dm_exec_query_memory_grants AS deqmg
--invalid object

SELECT  *
FROM    sys.dm_exec_query_optimizer_info AS deqoi
--invalid object

SELECT  *
FROM    sys.dm_exec_query_resource_semaphores AS deqrs
--invalid object

SELECT  *
FROM    sys.dm_exec_sessions AS des
--available

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_text_query_plan(der.plan_handle, 0, -1) AS detqp
--available

SELECT  *
FROM    sys.dm_exec_trigger_stats AS dets
--invalid object

SELECT  *
FROM    sys.dm_exec_xml_handles(@@SPID)
--invalid object

The good news, most everything you need is available so you’re really going to be able to go to town on using DMOs as part of your query tuning. The bad news, “most everything” doesn’t include sys.dm_exec_query_optimizer_info is not on the list. This does take away a tool. It’s not a vital tool, but it’s one that allows you to understand some of what’s happening on the system. I’m not going to cry and rend my clothing because I don’t have it, but I will be somewhat disappointed.

This is great news! The tools you’re learning and using (and you are using DMOs, right?) will continue to be available in SQL Azure.

I believe I’ve been far too quiet about this event.

In May, in Orlando Florida, there will be a two day SQL Server conference called SQL Rally put on by PASS. This is a somewhat unique event since the sessions have all been chosen by the community. That’s right, a series of votes was held online (and if you missed it, you weren’t paying attention) so that all of you were able to decide which sessions you’d most like to see. I’m bringing this up because, now, it’s up to you to register and go.

Did I mention the pre-con? No? Well, there will be a one-day pre-conference seminar. There are multiple sessions of excellent material at the pre-con. These were also voted on by the community. One of them just happens to be a full day session on query tuning called “Query Performance Tuning: Start to Finish” (catch that little bit of racing tie-in, yeah!) that I’ll be putting on. I’m busting my nether regions to put together a great session. I just need you guys to show up to make it perfect.

This is a short, sharp, inexpensive, easy to get to (in the old days, all roads lead to Rome, now, all airlines lead to Orlando International) event with top speakers from around the country presenting sessions that you picked. Why haven’t you registered? Get on over to the web site. Get your registration in. Plus, if you register now, you save $100, so that’s nice.

Recently, a co-worker practically slammed me up against the wall, exclaiming “You have to check out this new tool, right now!” The piece of software he was so excited about was Idera’s SQL Doctor. Based on this assaultrecommendation, I decided to take a little time & look the software over.

SQL Doctor, as the name implies, is a diagnostic tool. It runs a set of best practice rules against your server, your databases and your code. As the rules are executed, your system’s compliance with these best practices is evaluated and an interactive report is generated. With the report you can drill down on various aspects of your system to see where you may have gone wrong.

All that sounds very clinical, just laid out like that. But the fact of the matter is, if you have a lot of inherited systems, or systems that have not been maintained, designed, or developed as well as they should have been, you know you have problems all over the place. How exciting would it be to get a pretty fast evaluation of the systems? Wait. Don’t answer that yet. What if, after the evaluation, a set of reports was available? Hang on. It gets better. What if the reports included a pretty decent set of suggestions on how to resolve the issue? Yeah, that’s right, suggested solutions.

I know some people reading this are tuning monsters who won’t benefit from a piece of software like this. But a lot of you are not. For those, here’s how it works.

First, you connect to the server you’re interested in. Once connected, SQL Doctor will ask what type of evaluation you’d like, a health check or some type of system slow-down, as shown below:

For this example, I’ll just pick the health check. SQL Doctor then asks if this is a production system or not. Then it asks what kind of system, OLTP or not, and then it runs the evaluation. The evaluation of the rules is somewhat time consuming. Once it is completed, a new window showing the health of the system is displayed:

And this is where things get interesting. You can see the server name, the state of health, etc., but the good stuff is down near the bottom, the recommendations. On this particular server, chosen because I knew it would list interesting results, you can see 509 total suggestions, 427 Query Optimization issues, 48 Index Optimization issues, 2 network issues and 2 memory issues. You can use the tabs to look directly at the recommendations or you can click on the recommendation categories. Clicking on the Query Optimization category the list of optimizations is shown:

The optimizations are listed by severity, meaning, the damage they can cause to your system. At the very top are two instances of functions being used on columns in the WHERE clause of queries. I can’t drill down and show you the details on this system, but what you get is a precise listing of the procedures and the specific location where the problem exists along with a recommendation for how to fix it.

This is momentous. Basically, what you’ve got, is the ability to capture the low hanging fruit. The easy, but painful, stuff that people who optimize queries all the time are fixing, can be found, fairly quickly, and you don’t have to be a tuning expert to make this happen. There’s quite a bit more to Idera SQL Doctor, but this is the main point. You can also flag certain optimizations, marking them for others so that they know what you consider to be a priority. You can block optimizations if you either know about them or don’t care about them. There’s quite a bit of control possible through the tool. But, I can’t emphasize the main point enough. You can get an automated basic health check on your system that will suggest the best ways to fix the problems that it finds.

There are recommendations I’m not crazy about. For example, it flags UNION operators as a potential performance problem. Yes, they might be, and if you could use UNION ALL instead, performance would improve, depending on the query we’re talking about, but having this one poking me in the eye over & over… well, at least around my shop, it’s a candidate for getting turned off. Further, the explanation of why it could be problematic is very short. It just doesn’t provide enough information for people to make up their mind based on a full set of data. I’m also a bit nervous (shocked and apalled are almost applicable) that they’ve included index usage stats in a tool for less experienced DBAs. Yes, that index may not have been used for the last six months, but tomorrow, the CIO is running his favorite report that he runs once every six months, and it had better be lightening fast… but you just dropped the index that the report depends on because some piece of software told you it’d be OK… oops. In short, there are areas within the software that could be improved.

Most of the TSQL recommendations I saw were excellent. I already mentioned the functions making arguments non-sargeable and there are more good ones, use of SELECT *, unfiltered deletes (I’ll be talking to those developers real soon), ANSI settings that affect performance, differences between estimated execution times and actual times (neat use of DMOs), and more. In short, I think there’s one heck of a lot more good here than bad.

Is SQL Doctor magic? Are all your performance problems going to disappear over night? No, absolutely not. If you’ve made poor choices in database design, if you’ve got stacks and stacks of bad code, this tool is not going to swoop in to clean up your mess. You’re still going to have to do a lot of hard work, depending on your situation. Further, don’t take the suggestions of any tool (or some yahoo on the web for that matter) as messages from the gods. Evaluate and test the suggestions, independently.

However, the workload for tuning your systems was just reduced. Because there are a lot of problems, possibly even the majority, that are very clearly defined, that can be found by running a search routine, that absolutely do hurt the performance of your systems, badly. Those problems will be identified by Idera SQL Doctor which will enable you to fix them, in a much faster and more efficient manner. That’s a win.

I get the call, you get the call, everyone gets the call. “Hey, my app/procedure/query/report is running slow.” Now what do you do? You go to my full day session at SQL Rally, that’s what. Assuming you vote for it.

I didn’t post the abstract I submitted for the SQL Rally before because I thought that it would be redudant. However, since it’s not right off the voting page (unless they updated it since I voted), if you’re interested, here’s what I thought I would do for a day. If it sounds good to you, please go here and vote for it.

One of the most common problems encountered in SQL Server is the slow running query. Once a query is identified as performing poorly, DBAs and developers frequently don’t understand how to diagnose the problem and often struggle to fix the problem. This one day seminar focuses exclusively on these two topics. Attendees will learn how to identify the queries that are performing badly and learn how to fix them. We will start by learning how to gather performance metrics, both server and query metrics, using tools available directly from Microsoft such as performance monitor, DMVs and Profiler. From there we’ll move into learning how the optimizer works and how it uses statistics to determine which indexes and other database objects can assist the performance of a query. The session takes considerable time to show exactly how to generate and read execution plans, the one best mechanism for observing how the optimizer works. We’ll then look at other DMVs that can assist you when performance tuning queries. With all this knowledge gathered, we’ll move into looking at common performance problems, how they evidence themselves in the metrics and execution plans, and how to address them. Finally, we’ll explore advanced methods for solving some of the more difficult query performance problems introducing such concepts as query hints, plan guides and plan forcing. Through all of this, best practices and common techniques will be reviewed. Attendees will go home with a working knowledge of query performance tuning, a set of methods for identifying poorly performing queries, scripts to assist in these processes and the knowledge of how fix performance problems in their own systems.

To see the other sessions go here:
BI
DBA
Developer
Misc

Although I would prefer that you voted for me, it’s more important that you vote at all (same thing as in real life). Please go here and select the sessions that you want to see.

I’ve blogged in the past about the nHibernate project that has been going on at my company for the last eighteen months. Prior to today, I have only seen the database generated by nHibernate. But today I finally started seeing some TSQL code. My first impressions… oy!

There are two levels of concern coming out of the gate. First, it appears that some of the programming decisions, completely independent of nHibernate, are going to cause problems. Second, it appears we’re going to be hitting issues with nHibernate.

First, the programming decision. I’ve been using Confio to monitor this server for a few days now (look for upcoming posts on my eval of Confio). Only one day has captured any real activity from the nHibernate team (and yes, I’m basically spying on these guys because they are absolutely insistent that my team stay out of their way). The longest running query was two calls to this (names have been changed to protect my job, there are no innocents):

SELECT BookID
FROM Books WITH (updlock,rowlock)
WHERE BookID = 42

 What they’re doing is locking the row so that no other user can get at it while it’s being edited. Total lock time for the first day was 38 seconds for two calls. I’ve posted about this previously, so I won’t go into it again, but this is potentially a disaster.

On to nHibernate. The next query was pretty benign:

SELECT OrgID
,OfficeID
,StartDate
,EndDate
,OtherID
FROM SpecialList
WHERE OfficeID = 42

Unfortunately this table, as created out of nHibernate, has no primary key, no clustered index or any other type of index, so this is just a table scan. But it’s only on 109 rows… in dev, not production, oh, and most of the 109 rows have a null value for OfficeID, but it’s all good I suppose… until I notice that this query also had 38 seconds of wait time, but it was called 394,652 times… oy (and no, I’m not even one of the chosen, but that’s a wonderful expression for this situation). Looking at the data in cache, this query has been called, since it was created in cache on the 2nd, 598351 times with a total elapsed time on the server of 5429689ms. The average then is 9ms, but the max was 29296 or 29 seconds.

The next query up looked like this:

SELECT col1
,col2
,col3
FROM Table1
INNER JOIN Table2
ON Table1.ChildID = Table2.OtherID
WHERE tabl2.ParentID IN (@p1,@p1,@p2…@p99)

Yep. 99 parameters passed and used against an IN statement. I can’t post the execution plans on this stuff without talking it over with the boss, but suffice to say, it’s two table scans and a merge join to get the data back. Oh, and this was called 1,138 times with a minimum elapsed time of 976ms. Yeah, minimum time to call this proc is 1 second and it’s been called over a thousand times.

It went down hill from there. I’m looking at other queries, one that hits seven tables, but instead of using JOINs uses a bunch of sub-selects in the WHERE clause. It has a minimum run time of 9 seconds. Luckily it’s only been called 64 times.

This is a horror show.

A lot of benefit can be reaped if we’re able to go in and create some indexes on these tables, but that’s going to be an issue anyway because I’m seeing lots of implicit data conversions on the parameters being passed in, as I noted a couple of years ago. At this point in time, I would say, at best, I’m very underwhelmed by what nHibernate is producing. To be perfectly honest, I’m concerned for the health of the project.

I don’t generally do lots of blog aggregation and cross post linking & stuff. It’s just not something I’m that into. However, this time is an exception. Gail Shaw, Gila Monster to those who hang on out SQL Server Central, has posted an excellent explanation of times when Estimated and Actual row counts vary for a reason. I’m one of those who emphasises that differences between estimated & actual is an indication of… something. It could be out of date or missing statistics or it could be caused by query language like multi-statement table valued functions, but it’s usually and indication of a problem. Except when it’s not. Read Gail’s explanation for more.

Since I just spent a bit more than half of my 24 Hours of PASS presentation on tuning queries talking about monitoring performance, you could probably tell that I think that the two are inextricably linked. I’m not alone. Tom LaRock has put a post on why it’s important to understand the extent of your problem prior to attempting to fix it. It’s absolutely spot-on and a must read.

Remember, if someone says performance on a system you manage is slow, you have to ask, as compared to what? You need to understand what “normal” performance is on your system in order to arrive at the ability to identify a particular process as performing in an abnormal manner. That’s not to say that you couldn’t tune a query in complete isolation to a baseline of performance. Of course you could. The question that Tom is answering is, are you tuning the right query for the right reasons?

Oh, and for those that are interested, some of the presentations made during 24 Hours of PASS are now available for download. The rest of the sessions will be available after the Summit. If you haven’t made plans to go, I’d get on over there now and register.

Recently I’ve been seeing a lot of people with bad execution plans, desperately trying to tune them, but they were unable to explain why they had such bad plans. More often than no these were larger queries, with a number of derived tables, CTE’s, CROSS APPLY, etc. In most cases the statistics appeared to be fine (this is usually checked by comparing estimated & actual rows within the operations in the execution plan) and the plans themselves didn’t look crazy, but the execution plans were flat out, not good.

If you’re looking at a plan and it doesn’t make much sense, one option that most people don’t check… SQL Server didn’t have enough time to complete optimization. The optimizer is a pretty amazing bit of code. The scary volume of work it does in frightenly short periods of time is quite awesome. However, it doesn’t always get done. To quickly check this situation you need to determine the “Reason For Early Termination Of Statement Optimization.” In the graphical execution plans this is found by going to the final operator in the plan, usually an INSERT, UPDATE, DELETE, or SELECT operator. Most people know to hover the mouse over the operator & get interesting little facts, but most people forget about checking the properties. Right click on this, or any other operator, and select Properties from the context menu. This will open the properties window, which looks something like this:

Right near the bottom you can see that this execution plan is showing an early termination reason of “Time Out.” That means that the optimizer didn’t finish optimizing. When the optimizer is running, it tries a whole series of different joins, join order, filters, what have you, all through some mathematic process that I’m not even remotely qualified to describe. But if it runs out of time, it goes with the last one it had. The last one it had might be the worst possible plan for this query, but because it hit a time out…

You can also find this right in the XML for the execution plan. In the Batch/Statements/StmtSimple property find the @StatementOptmEarlyAbortReason. It will show the same thing, “Time Out,” when the optimizer has run out of time.

You can get other early abort reasons, but the best one is “GoodEnoughPlanFound.” That means that the optimizer was able to complete it’s job. That doesn’t mean you have a good plan, but it means that you don’t have some interim pile of junk that is doing you more harm than good.

What can you do to fix this? Write simpler queries.

OK. Maybe that’s not helpful. Try to simplify the query. You may not be able to. If not, you might try rearranging the query, changing the join order. Yes, as everyone says, join order doesn’t matter, but the full statement is “Join order doesn’t normally matter.” Sometimes it can make a real difference. You may also have to supply some query hints, join hints, etc., to try to nudge the optimizer along.