Are the posted questions getting worse?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/25/2013)


    What's your favourite index-related myth?

    That PK = Clustered Index

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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/

  • 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


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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