August 25, 2013 at 3:05 pm
Jeff Moden (8/25/2013)
GilaMonster (8/25/2013)
What's your favourite index-related myth?What's yours?
I have a few, but I need fodder for the PASS presentation (that's due today) π
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2013 at 3:19 pm
GilaMonster (8/25/2013)
What's your favourite index-related myth?
That PK = Clustered Index
August 25, 2013 at 6:04 pm
GilaMonster (8/25/2013)
What's your favourite index-related myth?
Tables don't need a clustered index
CI sorts the records in order on a page
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2013 at 8:46 pm
GilaMonster (8/25/2013)
Jeff Moden (8/25/2013)
GilaMonster (8/25/2013)
What's your favourite index-related myth?What's yours?
I have a few, but I need fodder for the PASS presentation (that's due today) π
Not necessarily my favorites but here's some fodder. I left out what the others have already suggested.
Clustered index scan is better than a non-clustered index scan (Perception is that the CI is the holy grail compared to NCI).
Index Seeks are always better than index scans (not if you do 12,000 of them and one of my favorites)
Along with the above... You must always write SARGable code.
It's good to make the CI as wide as necessary to cover your worst query especially in star schemas.
PK=CI (had to mention that one even though someone already mentioned it because its my favorite).
CI is always best if it's only one column.
CI is ok on a GUID column on large tables if the FILL FACTOR is large enough.
It doesn't matter if the CI is unique or not.
A PK is virtually useless unless it is the CI.
NCIs have the PK appended to them.
You should only have one unique index per table.
You never have to defrag the BTree of an index so no need to look for "Detailed" info from sys.dm_db_index_physical_stats
You can't do minimally logged processes against a table that has indexes.
Reorganize doesn't "repack" tables according to the FILLFACTOR. You need to rebuild for that.
You can rebuild any index in an online fashion. It just takes longer. (blobs)
Partitioning indexes should be done for code performance.
There's more but those are the only ones I can bring to the surface without more coffee. π
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2013 at 1:59 am
Thanks for the kitchen advise, all.
The missus has her mind set on granite. We're to take a trip to the warehouse in a couple of weeks to choose a slab. The advice the kitchen rep gave was that granite is essentially the same as "man made" but stronger. Oil it about once a year and keep acid off, but is the better choice.
A couple of questions from your comments:
- how do you get natural stone "permanently sealed"? Info from the reps over here is that it isn't possible
- how do you get a "warped" stone benchtop? Can stone actually warp?
Fal.
August 26, 2013 at 3:16 am
Jeff Moden (8/25/2013)
Clustered index scan is better than a non-clustered index scan (Perception is that the CI is the holy grail compared to NCI).Index Seeks are always better than index scans (not if you do 12,000 of them and one of my favorites)
Ah yes. Added.
It's good to make the CI as wide as necessary to cover your worst query especially in star schemas.
CI is always best if it's only one column.
CI is ok on a GUID column on large tables if the FILL FACTOR is large enough.
It doesn't matter if the CI is unique or not.
All covered in my 'what makes a good clustered index' section.
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2013 at 6:24 am
GilaMonster (8/26/2013)
Jeff Moden (8/25/2013)
Clustered index scan is better than a non-clustered index scan (Perception is that the CI is the holy grail compared to NCI).Index Seeks are always better than index scans (not if you do 12,000 of them and one of my favorites)
Ah yes. Added.
It's good to make the CI as wide as necessary to cover your worst query especially in star schemas.
CI is always best if it's only one column.
CI is ok on a GUID column on large tables if the FILL FACTOR is large enough.
It doesn't matter if the CI is unique or not.
All covered in my 'what makes a good clustered index' section.
Thanks
The more indexes, the better.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
Itβs unpleasantly like being drunk.
Whatβs so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 26, 2013 at 6:35 am
Don't forget the "one true index." All we need is a single, perfect, clustered index that will be all things to all people for all queries for all time. Any additional indexes are simply indications of failure to identify the PERFECT clustered index (and that you suck).
Lordy, I hate listening to those people.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2013 at 6:38 am
Grant Fritchey (8/26/2013)
Don't forget the "one true index." All we need is a single, perfect, clustered index that will be all things to all people for all queries for all time. Any additional indexes are simply indications of failure to identify the PERFECT clustered index (and that you suck).
Hmm, that sounds familiar. I'm sure I've been called stupid, idiot, shortsighted and a few other names because I don't hold to that philosophy...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2013 at 6:49 am
So the current list is:
No need to index small tables
No need to index tables that fit into memory
Indexes enforce physical order of data
Seeks are better than scans
Clustered index seek/scan is better than a nonclustered index seek/scan
Indexes are good. More indexes are better
You only need one (clustered) index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2013 at 7:16 am
wolfkillj (8/23/2013)
Sean Lange (8/23/2013)
The only read disadvantage of concrete countertops is that you have to keep resealing them. They do look pretty awesome though.
Resealing concrete counters is easy enough that I don't consider it a deterrent to installing them, though.
The actual act of resealing them isn't so bad, it is finding the counters underneath all the clutter that takes the time. π
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 β Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2013 at 8:08 am
Koen Verbeeck (8/25/2013)
GilaMonster (8/25/2013)
What's your favourite index-related myth?Clustered index sorts on physical level.
Koen - do you mean this one[/url]?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 26, 2013 at 8:28 am
Koen Verbeeck (8/25/2013)
GilaMonster (8/25/2013)
What's your favourite index-related myth?Clustered index sorts on physical level.
Definitely will address that one, along with the related 'nonclustered index sorts physically', since they are pet peeves of mine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2013 at 8:29 am
GilaMonster (8/25/2013)
What's your favourite index-related myth?
One to add to those already mentioned: Fill factor maintains the free space in the index at the specified level.
Love that one - usually takes a few minutes for them to realize they really DON'T want that.
Chad
August 26, 2013 at 9:23 am
Jeff Moden (8/25/2013)
GilaMonster (8/25/2013)
Jeff Moden (8/25/2013)
GilaMonster (8/25/2013)
What's your favourite index-related myth?What's yours?
I have a few, but I need fodder for the PASS presentation (that's due today) π
Not necessarily my favorites but here's some fodder. I left out what the others have already suggested.
You never have to defrag the BTree of an index so no need to look for "Detailed" info from sys.dm_db_index_physical_stats
You can't do minimally logged processes against a table that has indexes.
I like that list.
Here's another that came to mind...
All foreign keys and Primary Keys have an Index automatically created upon key creation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 41,056 through 41,070 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply