none
Query using temp objects are slow

    Domanda

  • Inserts and updates into temp tables are really slow. There are two datafiles for tempdb, same size on the same disk.

    Is there anything I should do to increase the performance of the queries that involve temptables?

    mercoledì 29 febbraio 2012 16:19

Risposte

  • Hi,

    the usage of the temp DB should be transparant for the Query writer. You should write well-tuned SQL-queries.

    However, if your temp db is a bottleneck, you could move it to a Solid State disk in your server!! Strange idea?!
    No:

    • it is temporary storage
    • Temp DB's are most of the time relatively small.
    • their read:write speeds exceed those of classical HD's

    You could buy the smallest SSD available on the market, which is more than enough for temp db's.

    Of course, the quality of you queries should be checked first; maybe you could give an example here of a query with a problem...

    Regards
    Wim


    mercoledì 29 febbraio 2012 21:46
  • What kind of load are you doing into a temporary table? Do you have indexes on the table?

    http://technet.microsoft.com/en-us/library/cc966545.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    giovedì 1 marzo 2012 07:32

Tutte le risposte

  • Hi,

    the usage of the temp DB should be transparant for the Query writer. You should write well-tuned SQL-queries.

    However, if your temp db is a bottleneck, you could move it to a Solid State disk in your server!! Strange idea?!
    No:

    • it is temporary storage
    • Temp DB's are most of the time relatively small.
    • their read:write speeds exceed those of classical HD's

    You could buy the smallest SSD available on the market, which is more than enough for temp db's.

    Of course, the quality of you queries should be checked first; maybe you could give an example here of a query with a problem...

    Regards
    Wim


    mercoledì 29 febbraio 2012 21:46
  • It's a bit early to be talking about hardware modifications when the OP has not provided any details on the tables or queries being used.

    OP, please provide DDL for your tables and queries, and sample data to test your queries.


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    giovedì 1 marzo 2012 02:00
  • What kind of load are you doing into a temporary table? Do you have indexes on the table?

    http://technet.microsoft.com/en-us/library/cc966545.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    giovedì 1 marzo 2012 07:32
  • Hi Wharty,

    That's the reason why I stated: "Of course, the quality of you queries should be checked first; maybe you could give an example here of a query with a problem"

    However, If you read his question, it is really a question about the physical aspects of the Temp db: slow read and slow writes.
    I know that tuning doesn't start at the point of changing hardware, but in this case I just answered the question.

    And honestly, if I was a DBA I would always suggest to add a local SSD for Temp DB! Especially when working with external storage systems.

    Regards
    Wim





    giovedì 1 marzo 2012 09:10
  • However, If you read his question, it is really a question about the physical aspects of the Temp db: slow read and slow writes.

    Don't think so.  The performance issues could be with the temp tables they've created i.e. no or inappropriate indexes, wrong datatypes.  If it was TempDB then inserts and updates would be slow for all tables, not just temp tables.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    giovedì 1 marzo 2012 09:15
  • And honestly, if I was a DBA I would always suggest to add a local SSD for Temp DB! Especially when working with external storage systems.

    This would also be a bad suggestion as SSD is not required for high performance.  If you beleive it is, can you please provide some statistics or proof that shows SSD should be used for TempDB.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    giovedì 1 marzo 2012 09:19
  • Maybe we should wait for his first reaction ;)

    I think the question above can go in many directions...

    giovedì 1 marzo 2012 09:20
  • Yes.  It all depends on whether the OP means TempDB when they talk about Temp Tables as they are two different things i.e, a temp table is not TempDB.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    giovedì 1 marzo 2012 09:21
  • What would you consider as a proof?

    (maybe we should continue this via another medium?)

    giovedì 1 marzo 2012 09:26
  • Setting up a new discussion topic would be better.

    Proof would be statistical comparisons of tests conducted on SSD and other drives.  These tests would also need to include a comparison of cost, performance, life expectancy etc

    It's very easy to come out with a statement saying that SSD should always be used for TempDB however the cost to performance ratio may not be worth it.


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    giovedì 1 marzo 2012 09:33
  • Hi Mr. Wharty,

    I've sent a message on your facebook....

    Regards
    Wim


    giovedì 1 marzo 2012 11:00