Harmful, Pervasive SQL Server Performance Myths - SQLPerformance.com
SentryOne - SQL Sentry
Mar 092017
 

Between my travels, presentations, and Q & A moderation, I talk to a lot of people about a wide variety of SQL Server performance issues. Recently, I've had a few interactions where people believe things that are either altogether incorrect, or are only correct in a very narrow set of use cases. Yet their adamance that these things are universally true is unsettling.

Fake News

So, I thought I would start a new series to help quell some of these myths. Not to point at people and prove that they're wrong, but to stop the spread. When they make these blanket statements in their workplace, or on twitter, or in forums, if they go unchecked, they can "teach" impressionable or less experienced users.

Note that I don't intend to prove that these things are never true, because some can certainly be true in isolated or contrived scenarios. My aim is simply to demonstrate at least one case where it is not true; hopefully, this can start to change these stubborn mindsets.

Here are some of the "facts" I've been told recently, in no particular order:

As I write each post, I'll update this page by linking the corresponding item in the above list.

Do you have any performance myths that are passed around as absolute fact, but you suspect (or maybe even know) that they aren't always true? Let me know in the comments below, on twitter, or at abertrand@sentryone.com.

  23 Responses to “Harmful, Pervasive SQL Server Performance Myths”

  1. * TRUNCATE TABLE is not logged (and can't be rolled back)
    * A seek is always better than a scan

  2. Great series, very much looking forward to reading more!!

    One that I often come across is that "scans are bad" and that's the end of it. Yes, in most situations a scan operator might indicate that there's potential for some tuning, I'm happy with that, but at the same time there are times when scans are ideal; small tables, low selectivity etc (that's why the optimiser chooses them).

  3. Having seen many of these discussions unfold on dba.se recently, I'm looking forward to the drubbing.

  4. Add to myths: Joining tables hurts performance (argument for denormalization).

    • Upvote! I remember seeing this thrown about by people who should have known better going back to the days before they used this to advocate NoSQL solutions even.

  5. A few that I come across:-

    A table scan always reads the whole table.
    Key lookups are always bad.
    Partitioning tables will increase performance.

    And, bizarrely, read uncommitted isolation level is better than NOLOCK (no idea who dreamt that one up….)

  6. Eagerly waiting your response on these points..

  7. Table variables are more efficient than temp tables.

  8. – Cursors are always "bad" decision.
    – The primary key must be clustered index.
    – Any data colmn independent from the data type will not request space as far as we don't store values, i.e. null

  9. Views should NEVER be used they kill ALL queries performance

  10. Query optimizer will arrive at same plan for query regardless of specified join order.

  11. Linked Servers are the fastes and best way for what ever

  12. Heap tables are always bad.

  13. My favorite howler, told to me by a developer with a query that ran for hours.

    The order of your indexed columns should always follow the order of the columns in the WHERE clause. This was on an accounting detail table with 1 billion rows and growing.

  14. In SQL Server, the INFORMATION_SCHEMA views are better to use than the sys catalog views because they require fewer joins to get the metadata you want.

  15. A page life expectancy of 300 sec is fine, no need to worry at all with that 500GB maxmem instance.

  16. Yes the defragmentation argument and the confusion between rebuilding and reorg snd thr staristics. Also the amazing use of frofiling and the dta ….someone please simplify this and make it less of a dark art…

  17. At SQL Saturday in Chicago last weekend, I asked Adam Machanic which idea about query tuning people found most surprising. His answer: that IN and EXISTS are logically equivalent and that replacing one with the other does nothing to improve performance. "IT'S A MYTH!!!" he shouted.

    I was surprised because I'd always believed EXISTS would execute quicker. Apparently not.

  18. I don't know how pervasive this is, but:

    "It is understood that rewriting the query with temporary tables can lead to some slowdown due to additional expenses: creation of temporary tables. If the database will not be mistaken with the choice of the query plan, it will perform the old query faster than a new one. However, this slowdown will always be negligible. Typically the creation of a temporary table takes a few milliseconds. That is, the delay can not have a significant impact on system performance, and usually can be ignored."

    http://stackoverflow.com/a/16770129/2333499

  19. Set MAXDOP to one in order to sort out your parallelism/CXPACKET problems. (alternatively just set it to one for no reason at all)
    Use DISTINCT at the start of all your queries to sort out duplicate result problems.
    Use NOLOCK on your queries to sort your performance[concurrency] problems.
    Use OPTION(RECOMPILE) in all your stored procedures because parameter sniffing is bad.
    Don't use transactions because they slow down your queries.
    Don't use foreign keys because they slow down your queries as well.
    I should create this specific index because missing indexes/dta suggested it.
    My backups are not failing therefore my data is safe.

 Leave a Reply

(required)

(required)