Indexing for Performance
So we started off with a lot of quality time discussing Heaps vs. Clustered tables. There was a clear bias for Clustered tables, but that's ok. One of the reasons I went to PASS was for Opinions not just raw facts.
Heaps
Heaps are nice because there are no page splits.
Heaps are not nice because they use a forwarding pointer when the row size changes.
This means that the RID may go to a leaf or require an extra jump to reach the leaf.
Why would they use this goofy forwaring pointer? Well the RID is not an incrementing value, but is tied to some internal structures. The indexes all point at the RID. If you update the RID to indicate the new page the data can be found on then any UPDATE statements have to wait for the index(es) to be updated as well. By using the pointer, the indexes can stay pointed at the RID. This was a case where the "The Answer is another layer of abstraction!". Unfortunately as you get more forwarding pointers it requires more reads to retrieve your data. Over time you will pay a price for this.
To check your forwarding pointer count look at dm_db_index_physical_stats
Clustered Indexes
Updates are faster but may cause page splits. This may not be an issue because the page split happens once and thenthere is room for a lot of additional data on the page.
The Page Splits can lead to fragmentation. The best way to reduce the page splits is to adjust your fillfactor down (~70%). Sure, your table will get a bit bigger at first but it is space you will likely need going forward regardless.
Ola Hallengren has some excellent maintenance scripts. Investigate this.
It's important for your clustered index to be minimal because the columns get added to each NonClustered index.
If your clustered index is not unique a "uniquifier" (I'm not kidding) is added to each set of values that exist more than once. This value is a 4 byte int. If you exclude 2.1 billion dupes things may break (but you likely have other problems by then).
Splits do not impact your NonClustered indexes and do not create separate entries in the transaction log because they are done as part of the UPDATE / INSERT transaction which created them.
Index lookups always reach down to the leaf level, this is required for consistency.
Trees!
SQL Server uses B+Trees for it's indexing method.
COUNT(*) works be doing a leaf-level scan of the smallest (minimal width) non-clustered index.
Trees live in the SQL Server cache and tend to be a large part of the logical reads.
Optimizer
The Optimizer's goal is not to find the best query plan, but to find an acceptable query plan swiftly.
The Optimizer expects the tree to be in the Cache and leafs to be on disk.
When does the optimizer decide to use a NonClustered Seek with bookmark lookup vs a Clustered index Scan?
It turns out this is tied to the page width.
When the # of rows returned <= 1/3 to 1/4 of the # of ALL pages (A table Scan) then the NonClustered index is used.
Otherwise the number of lookups of are cost prohibitive and it would be faster to use the Clustered index and filter.
Statistics
When performance drops off suddenly it is nearly always Statistic related.
When performance drops slowly over time, it is nearly always Fragmentation related.
Stats used to auto-update when 20% of the rows on the table had been touched. (SQL 2000). Can be found in sysindexes.rowmodctr.
Now Stats will change on a column in a table only when 20% of that COLUMN changes. This is irritating.
DBCC SHOW_STATISTICS has some good stuff, including:
*Last updated
*SampleSetSize
*Histogram
sp_autostats are interesting too.
They only record 200 entries in the histogram. They use Step Compression to show outliers. This means that similar values are lumped together so the outliers are more prominent.
_WA is used because MS is in WA.
sp_recompile works at the table level!
OPTION RECOMPILE is usefull to optimizing part of a query.
FULLSCAN will go parallel, SAMPLE will not. A sample of > 25% is slower than a full scan normally.
Filtered stats are pretty awesome.
TEMPDB
check out KB328551
Tips
AND is progressively limiting while OR is progressively inclusive.
To improve join performance
1. make sure the FKs are indexes
2. cover the join
3. cover the query
To improve aggregate performancehaving the group by in the index is important and you should INCLUDE any columns you want to aggregate.
DTA can useful for straight SQL but it is terrible with procs and views.
Notes
Kimberly makes heavy use of Solutions and SQLCMD. Should we consider these for the release foilders?
No comments:
Post a Comment