I must make a confession, I am not a DBA. I think I know quite a bit about SQL, but when it comes down to it, there is just too much to know without specializing in SQL. I will probably sound like a SQL rookie with this post, but that's the price to pay for getting better, acknowledging weaknesses, and working to improve on them.

A query in one of our batch processes was timing out, and it was up to me to fix it. You pretty much have two options, the easy way, just increase the timeout, or the harder, tune the query. I personally feel the second option is more rewarding, but ended up spending more time on it then I expected, and exposed myself as the DBA imposter that I am.

I have tried to reproduce the general form of the query. It basically consists of several derived tables joined together and returned as an XML document.

SELECT table1.column1, table1.column2, table2.column1, table2.column2,
       table3.column1, table3.column2, table4.column1, table4.column2
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table1
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table2
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      Where Clause) table3
FROM (SELECT sub1.column1 as column1, sub2.column2 as column2
      FROM sub1 INNER JOIN sub2 ON sub1.id = sub2.id
      WHERE Clause) table4
ORDER BY table3.column1, table3.column2
FOR XML AUTO, ELEMENTS

I ran the query thru the DB Engine tuning advisor, and it came back with no improvements, as there were existing indexes which it probably thought was adequate. I also looked at the estimated execution plan, and noticed 2 sorts, each with a 25% weight. According to the comments in the stored procedure, one of the last things to be added was the ORDER BY table3.column1, table3.column2, so I commented that out, and the query ran in 1 second (original execution time was ~50 seconds). The first thing I thought was a missing index, or an index that needed to be rebuilt. There was an index already on table3.column1, so I tried adding column2 and that didn't help at all.

Let me take a step back and offer an opinion on how to classify the level of knowledge a person has with SQL.

• Level 0: Just starting out. Doesn't understand even the basics of optimization and performance.
• Level 1: Some experience in writing good queries. Understands concepts such as normalization, not doing much with indexes at this point.
• Level 2: Starting to place an emphasis on writing good queries, and creating good schemas. Starting to create indexes
• Level 3: Can start to spot performance issues in other peoples queries. Knows how to use the DB engine tuning tool and estimated execution plan
• Level 4: Adds indexes, hints, etc without needed the DB engine tuning tool for most queries
• Level 5+: I haven't gotten there yet, but it probably involves, among other things, intelligently using the skills from level 0-4, as just adding indexes isn't always the solution, and can cause issues themselves.

I was basically stuck at level 3 witch my problem, and needed to move to level 4. I would suspect that allot of people who list SQL experience are at the same level. The SQL engine does a pretty good job of optimizing queries, and throwing something into a GUI tool isn't that hard to do once you know about it. For smaller projects, the database isn't going to be too complex, and on really big, enterprise level projects, you'll probably have a dedicated DBA.

I knew of Index hints, it just wasn't clicking at this point that, that is exactly what I needed to do. I turned to everyone's favorite helper, Google and started searching for ways to increase performance on sorts. The first hits were the obvious things, add an index, but then I started to see glimpses of things like, index hints. I redid my search for index hints, and started to get more specific hits, and realized this was what I needed, and it finally clicked with other things I have read in the past. When you never really have to do this level of DBA, you don't think of everything you should, you get rusty compared to say, someone who has just read a book on SQL.

Armed with what "tool" I needed to do, I set out to add an WITH INDEX (ix_Table1_Column1_Column2) statement to my query, but even this was a humbling experience. I just couldn't figure out where to stick it, I kept getting syntax errors. I finally looked up the syntax for a select statement in books online, and very carefully examined the syntax and compared it with my query. I finally realized that the statement has to go between the "FROM sub1" and the "INNER JOIN" statement in the table3 sub query. After adding my index hint, the query completed in ~2 seconds, or a 48 second improvement.

This was a learning experience on several levels for me. First, I fixed my query, and realized that when the DB tuning tool fails to provide an answer, you got to switch to "manual" mode. I was lucky enough to have know to try removing the ORDER BY statement, and with out the comment in the stored procedure, I'm not sure if I would have been able to figure out exactly where to look. I'm sure the information provided in the estimated execution plan, would have pointed a more experience DBA in the right direction.