Overview
Today was the fifth and final day of the Internals & Performance immersion event. I have really enjoyed learning from not only Paul and Kimberly, but my classmates as well. There were many smart people in that room and hearing each others’ perspectives is a part of the class that I’m glad is there. I want to thank Paul and Kimberly for doing these events. I also want to thank my employer for sending me.
We spent the morning finishing up the Statistics module. More interesting stuff about helping the query optimizer pick the best plan. Indexing strategies was even more info about how to provide the optimizer the best info to get a good plan. It’s amazing how much difference this can make.
The last part of the day was Paul rushing through Index Fragmentation. Sadly, the Indexing for Performance modules took up a lot of time, thus this module was shorten. Fortunately, I had seen the videos and what was skipped is in the practice virtual machine image that we got on the first day.
To finish up, Kimberly did a quick presentation on the affects GUIDs have on clustered indexes. If you haven’t seen this presentation or read the blog post, do so. You’ll thank yourself if you are able to make the changes or prevent implementation in the first place.
Tonight was another night at the hotel bar, but it was my first time staying for it and socializing. It’s great when instructors will stick around and socialize. You learn more about their experiences that way and you get to tell your own stories. Now I regret not sticking around the previous nights.
There’s another event in Dallas in March which I probably won’t get to go to. I really want to go to their HA & DR class in Bellvue in August. Hopefully that will work out.
Indexing for Performance (Statistics)
- As of 2005, stat updates are based on column change counter. This means that a column has to change 20% before stats are auto updated.
- (col = @v1 OR @v1 IS NULL) is bad. WITH RECOMPILE won’t help
- Stored-Procedure-Parameters-giving-you-grief-in-a-multi-purpose-procedure.aspx
- sp_executesql = force statement caching
- Inaccurate stats can cause problems, filtered stats over ranges can help
- Have jobs to run scheduled maintenance on filtered stats
- Steve Kass has some interesting math background info for stats
- Beware uneven data distributions
- Don’t trust the query estimate cost comparisons
Indexing for Performance (Indexing Strategies)
- Don’t index to the query plan, index to the query
- OR is similar to UNION… you may be able to get better performance with UNION, UNION ALL even better if you can ensure no dups
- Hash => tempdb
- sp_updatestats will update stats if even 1 row has changed
- ola.hallengren.com
- Relational data warehouse, heavily consider indexed views
Index Fragmentation
- Clustered / Non-clustered fragmentation is the same
- Binary search used for singleton lookup on a page
- Readaheads need logically ordered pages
- 4MB largest readahead possible in 2008
- http://support.microsoft.com/kb/329526
- Narrow index keys = shallow index tree = faster lookups
- Intra query parallelism deadlock
