Morning
I got up at 6am PT this morning, got ready, got some coffee from Starbucks, and made it to the convention center by 7AM. I had no problem following the signs to registration and there was no wait in getting registered. I wish I had gotten in early enough yesterday to make registration, but no luck there. The good news is that the convention center and hotel are so close I had more then enough time to run back and drop off the laptop bag and goodies from registration in my room. I met and chatted with a lot of folks and sat down for breakfast with Adam Saxton, Allen White, Wil Sisney and others before I dropped everything off.
Pre-Con
I was lucky enough to attend a pre-con. I chose to attend execution plans by Grant Fritchey and Gail Shaw. I’m not going to blog the whole thing, but here are a subset of my notes to give you an idea of what was covered and the best tips I picked up. Please remember these points are just notes and not well formed.
- Using Optimize for ad-hoc workloads has no downside, so it was suggested to turn it on regardless. I’m not a fan of turning things on that you don’t need, but if there is no overhead and it could save you issues in the future then it’s worth it.
- Always use SPs if you can. They are stored in the engine and execution plans are cached. I’ve been preaching this one myself. Ad-hoc queries are more difficult to track down, especially if you don’t have optimize for ad-hoc workloads turned on.
- Every SP has it’s own plan, so if an SP calls another SP they each have their own plan.
- ANSI settings on the connection from SSMS and .NET are different, so you’ll get different plans and makes troubleshooting the query more difficult.
- Rebuilding your indexes can cause statistics to be out of date, which means you could end up with an inefficient plan in your plan cache. Reorganizing an index does not update statistics. <<<<ask and clarify this.
- Every insert into a temp table causes a recompile of the plan. Another perfomance hit of using temp tables. Although table valued parameters NEVER cause a recompile.
- Plan cache hit ratio. There are some general numbers out there, but you can’t use this. As usual you need a baseline for your system. If your normally at a consistent 95% and you get a SUSTAINED drop to 92% then that is a problem for you even though that 92% is above the suggested value.
- The cache_miss event is not all that helpful for two reasons. If you execute an SP from SSMS then the call to the SP (The EXEC statement) is causes a miss because the call itself never gets cached. You’ll see a second one come in for the procedure itself, and that is the one you are interested in. The other reason is if the optimizer has to insert a plan into the cache the miss is assumed, and not recorded in the miss counter. It is only counted in the cache_insert counter, so that counter is a better place to look.
- If you use optimize for ad-hoc workloads and a run a query from SSMS with the ESTIMATED plan it is seen by the optimizer and will create a stub. When you later run the query fo real it will see the stub and cache the plan.
- A nested loop join is actually a cursor! Read the tooltip in the plan for the description and think about what it is saying.
- Nested loop joins can be efficient if the outer table has a small amount of rows.
- Scan count does not mean how many time SQL read the table or even how many time it accessed it. Ignore this value and concentrate on Logical Reads instead.
- Merge joins are extremely efficient. You usually see them when the join columns are indexed.
- If you see worktable in your IO statistics output it is temp table created by SQL generally for a hash match or sort.
- Sum and count are good to put in an include, but min and max are better in the predicate because it will already know the range.
Evening
I met up with Allen Kinsel, Dave Stein, Jim Murphy, and John Clark and headed down to Lowell’s for the networking dinner hosted by Andy Warren and Steve Jones. When we got there the line was crazy long and we were all hungry so we decided to head over to the Pike Place Grill. We all had a great time networking and went to the Tap House. We met up with Tim Radney, Bill Graziano, and several others but the Tap House was packed with staff complaining of too many people. We all headed over to the Sherton lobby and hung out for the rest of the evening.
It was an awesome first day and start to the conference with plenty of SQL goodness yet to come.