Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,1054,1064,1074,1084,109»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 3:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488332
Posted Monday, August 26, 2013 6:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:44 PM
Points: 2,762, Visits: 7,235
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
Post #1488351
Posted Monday, August 26, 2013 6:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 15,661, Visits: 28,047
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1488354
Posted Monday, August 26, 2013 6:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488356
Posted Monday, August 26, 2013 6:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488359
Posted Monday, August 26, 2013 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1488368
Posted Monday, August 26, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 PM
Points: 6,594, Visits: 8,877
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?


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1488394
Posted Monday, August 26, 2013 8:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488400
Posted Monday, August 26, 2013 8:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 2,607, Visits: 17,926
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
Post #1488401
Posted Monday, August 26, 2013 9:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 21,639, Visits: 15,309
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1488429
« Prev Topic | Next Topic »

Add to briefcase «««4,1054,1064,1074,1084,109»»»

Permissions Expand / Collapse