SQL Server Performance

Why is the tempdb much more faster ?

Discussion in 'Performance Tuning for DBAs' started by twister77, Apr 6, 2006.

  1. twister77 New Member

    Hi,

    Why is the tempdb much more faster than the other dbs (SQL2000 & SQL2005)?

    I've created some testdbs using the same physical locations for the ldf & mdf file like for the tempdb. And I've also set up the same db-settings.

    on sql2000 the tempdb is about 50% to 70% faster and on sql2005 about 1000%!!!

    ok...it's nice to have such a fast tempdb...but why???

    here's the testscript:
    CREATE TABLE Numbers(ID INT NOT NULL PRIMARY KEY)
    GO
    SET NOCOUNT ON
    DECLARE @Index INT
    SET @Index = 1
    WHILE @Index <= 8000
    BEGIN
    INSERT Numbers (ID) VALUES (@Index)
    SET @Index = @Index + 1
    END
    GO
    DROP TABLE Numbers
    GO

    Is this a usual behaviour?

    Regards,
    Dominic
  2. satya Moderator

    SQL Server 2005 makes greater use of tempdb than SQL Server 2000 did, so its better in locating tempdb on its own high-performance drive system.

    New features of tempdb scalability with SQL 2005 are:
    - Caching of IAM and first data page for temp table and table variables.
    - Improved allocation page latching protocol so that we use UP latch less frequently.
    - Reduced logging overhead for tempdb so that we consume less IO bandwidth in tempdb log file.
    - More efficient allocation algorithm for mixed pages in tempdb.

    I would recommend the following if you see lot of contention in TEMPDB:

    - Avoid auto grow
    - Allocate space for tempdb files
    - Make sure temp tables are cached

    Moreover the objects in tempdb are cleared once the session is closed or the connection is closed, so you do not have any contention there to refresh the optimization.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. twister77 New Member

    Thanks for your answer...Ok...I'll try to use the tempdb so much as I can...<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Hmm...so there is no chance to get running the usual db's so fast as well....? It looks like...:-(<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />SQL Server 2005 makes greater use of tempdb than SQL Server 2000 did, so its better in locating tempdb on its own high-performance drive system.<br /><br />New features of tempdb scalability with SQL 2005 are:<br />- Caching of IAM and first data page for temp table and table variables. <br />- Improved allocation page latching protocol so that we use UP latch less frequently. <br />- Reduced logging overhead for tempdb so that we consume less IO bandwidth in tempdb log file. <br />- More efficient allocation algorithm for mixed pages in tempdb. <br /><br />I would recommend the following if you see lot of contention in TEMPDB:<br /><br />- Avoid auto grow<br />- Allocate space for tempdb files<br />- Make sure temp tables are cached <br /><br />Moreover the objects in tempdb are cleared once the session is closed or the connection is closed, so you do not have any contention there to refresh the optimization.<br /><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  4. satya Moderator

    Hmm...so there is no chance to get running the usual db's so fast as well....? It looks like...:-(

    Well, provided the database is optimized to perform better ith valid design and maintenance tasks. You have many articles in this website talking about the same.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Ok...I'll try to use the tempdb so much as I can...<img src='/community/emoticons/emotion-1.gif' alt=':)' /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Well, there is a good reason why it is beneficial that tempdb has some degree of priority on the server. If you start using tempdb by yourself, you'll lose the benefits.<br /><br />You should be aware that tempdb is a SYSTEM database, which you should leave to SQL Server to work with.
  6. Luis Martin Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Ok...I'll try to use the tempdb so much as I can...<img src='/community/emoticons/emotion-1.gif' alt=':)' /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Well, there is a good reason why it is beneficial that tempdb has some degree of priority on the server. If you start using tempdb by yourself, you'll lose the benefits.<br /><br />You should be aware that tempdb is a SYSTEM database, which you should leave to SQL Server to work with.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Indeed.<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.<br />Leonardo Da Vinci<br /></font id="size1"><br /><font size="1"> Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />

Share This Page