|
Greg Linwood's talk on indexing and the webcast
Posted: 15 Nov 2005 05:06 PM |
So what did you all think of what Greg had to say about indexing in SQL2005? I thought it was pretty interesting, but I didn't get to hear much of it! I was too busy trying to work out the hassles we were having with the webcast.
In case anyone's interested, we had about 42 people in person, and about 15 on the webcast. The audio dropped out every 10 minutes for most of it, but hopefully we'll have that sorted by the next one. I'm really sorry for the interruptions.
RobF
|
|
|
 |
|
|
Re: Greg Linwood's talk on indexing and the webcast
Posted: 15 Nov 2005 06:21 PM |
I really enjoyed this trip - I spent another four days in Adelaide after the User Group meeting which was my longest stint since moving back to Melbourne in '91 after living in Adelaide for a few very enjoyable years. I really love Adelaide & always feel relaxed over there, as it offers a great escape from the regular Melbourne madness..
The presentation was very enjoyable & it was great to receive a nice turn out on the day -I'm sure this group is going to flourish with Rob's energy behind it. It was also great to meet some of the other UG members, especially David Gardiner who spent a couple of hours with Rob & I after the meeting, discussing how we can continue to improve the UG. Some solid questions came from the audience during the session & the webcast was fun too, despite the audio glitches.
Given that a few people have asked about the missing audio from the live meeting recording, I summarised it for a mailing list post today. I've re-posted it here for future reference & welcome critical comment or questions!
Regards,
Greg Linwood
Summary: Adelaide AUSSUG meeting Nov 10th
"Indexing: best practices & new SQL 2005 features"
I covered two major topics in the session:
(a) What makes a good clustered index & how the relationship between clustered / non-clustered indexes drive some decisions about best practises with clustered indexes
and
(b) New indexing features in SQL 2005.
In brief, my main points were:
(a) Clustered indexes:
* Implementation details haven't changed much between 2000 / 2005
* The name "clustered" is mis-leading - I actually prefer Oracle's "Index Organised Table" terminology, which I feel more accurately describes the function & behaviour of a SQL Server "Clustered Index".
* Clustered Indexes are not the performance tuning "ace" they're often claimed to be. Because all columns are stored in the leaf node of clustered indexes, unless your query actually USES all columns, you're accessing data needlessly when a query uses a clustered index (never a good thing). Because the columns in non-clustered indexes are sub-sets of those in the clustered index, they store more rows per 8kb page, can be tailored to the specific needs of a query & are therefore always more efficient than clustered indexes.
* The fact that clustered indexes are "pre-sorted" is often claimed to provide performance benefits, but non-clustered indexes are ALSO sorted - a point which is often over-looked.
* The ideal criteria for clustered indexes are:
1. Clustered index columns should be NARROW
Because the keys of a clustered index are stored in the other non-clustered indexes on a table, the wider they clustered index keys, the less efficient non-clustered indexes are because fewer rows fit on the non-clustered index, requiring more i/o to service any given query.
2. Clustered index columns should be UNIQUE
SQL Server "uniquefies" clustered index columns by adding extra 4 bytes to duplicated values (not all values), which means that the non-unique cluster keys are (a) wider than unique keys and (b) non-uniform width (adding overhead to scan operators)
3. Clustered index columns should be STABLE
Again, because the keys of a clustered index are stored in the other non-clustered indexes on a table, updates to the key have to be propagated through the non-clustered indexes, adding significant additional processing overhead to updates.
4. Clustered index columns should be INCREMENTAL
Because (like all indexes), clustered indexes are stored IN ORDER, insertion of new rows (or updates) can cause SQL Server to "shuffle" rows around to make room for the new rows between existing rows. The term for this is "Page Splitting" and the overhead to performance can be significant. Incremental keys, on the other hand, require no splitting as new rows are simply appended to the end of the index.
* Given the above "ideal" criteria listed above, columns based on IDENTITY() are IDEAL in every way for clustered index keys because they're narrow (can be anywhere from 1 to 8 bytes), generally unique (apart from a few obscure scenarios but notably replication), stable (who ever updates an indentity value?) and certainly incremental (serial isn't important to this argument).
* I discussed an obscure scenario where GUIDs can actually be better, the cruz of which is that they can help avoid some mainteance window scenarios due to their randomness, allowing even distribution accross pre-defined fill-factor space.
* My "mantra in a bottle": Non-clustered indexes are the real performance tuning aces - clustered indexes are essentially a table organisation technology and are generally over-rated as a performance tuning tool. They should not be considered of prime importance to performance tuning except in some relatively obscure scenarios which are usually DSS / OLAP oriented (where the columns being queried vary substantially, eliminating the option of tailoring indexes to specific queries). This is generally in the DSS / OLAP / Reporting / Searching domain.
(b) New SQL 2005 Indexing features:
* Online index re-builds. Awesome, Awesome, Awesome, Awesome!! This will save me lots of sleep personally & eliminates one of the big advantages Oracle had over SQL Server in the maintenance arena. Some caveats - degrades performance somewhat, so this feature should be considered for use in periods of low activity & never used during peak processing. Requires double the space of existing index. Short term lock synchronisation is required at beginning & end of preparation & final phases (not *quite* a free lunch). Both rebuilds & defrags supported online. Not every index operation is online but most of the important rebuilds are.
* Included columns - allow indexes to be wider than 900bytes (an old historial limitation). Provides extra capability to "cover" queries with indexes without having the extra columns in the index b-tree.
* sys.dm_db_index_usage_stats view - hugely valuable to performance tuning as it lets you know whether an index has been accessed, so you know if you can drop it (impossible to do with SQL 2000 presently & hence a huge limitation to SQL 2000). Also lets you know how often an index is being either seeked or scanned. This is a big benefit over Oracle which didn't have this feature afaik.
* Other indexing functions - useful for further drill-down into things such as page splitting in indexes etc
* Disabled indexes - drops body of indexes but leaves schema meta information there so they can be re-activated (built) later without having to issue the full DDL statement (great for storing long term backups).
|
|
|
|
|
|
Re: Greg Linwood's talk on indexing and the webcast
Posted: 15 Nov 2005 06:28 PM |
Chuck Sterling posted something a few hours after the webcast was done, saying that he needed to upload the audio. So I'm hoping that the problems with the audio there is temporary.
The link to the webcast download is http://msevents-as.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=120465372&EventCategory=3&culture=en-AU&CountryCode=AU
|
|
|
|
|
|
Re: Greg Linwood's talk on indexing and the webcast
Posted: 16 Nov 2005 08:18 AM |
Just thought i'd put in my 2c worth that I thought this was probably one of the best talks I've seen this year, if not possibly in the history of the Adelaide group.
I also really enjoyed catching up with Greg and Rob afterwards.
Now to find some time to work on some of those enhancements to the site!
-dave |
|
|
|
|
|
Re: Greg Linwood's talk on indexing and the webcast
Posted: 16 Nov 2005 11:32 AM |
| I'm hoping to be able to have that standard of talk become the norm for the group. We'll probably need some help in making sure that the content is consistently of a high standard, but I do want people to come along expecting to hear a good talk. |
|
|
|
|