SQL Server Performance

inserts on tempdb is slow

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by pseshan, Mar 28, 2007.

  1. pseshan New Member

    Inserts on tempdb is extremely slow. I'm running Sql Server 2005 and the hardware config is great(Quad processor, SAN for storage, etc) and I don't see any degrade in performance when I run other queries on the database. Happens only on inserts!!! What are the possibile issues that I should consider looking into??? Any help would be greatly appreciated.

  2. bradmcgehee New Member

    If I understand you correctly, you are inserting data into a temp table in the tempdb database? Can you tell us how many inserts you are doing, and how long is it taking? Does the temp table have an index on it? Is tempdb on a RAID 5, RAID 10, or other type of array? Is tempdb located on the same array as your user databases. Are there a lot of temp tables being used, or just this one?

    Brad M. McGehee, SQL Server MVP
  3. MohammedU New Member

  4. pseshan New Member

    Brad/Mohd Thanks for the response. The test case included 893 records broken into 5 batches (220 + 220 + 220 + 233). A comparison was made between the databases on Sql Server 2000 (QA environment 1) and Sql Server 2005 (QA environment 2). SQL2000 out performs 2005. We also setup a thrid environment, QA3 with Sql server 2005 and this out performs both QA1 and QA2. Listed below are the timings for the 3 environments for the same 893 inserts:

    QA1: 62 secs (Sql2005)
    QA2: 41 secs (Sql2000)
    QA3: 25 secs (Sql2005)

    An index is created on the temp table but its done only after all the inserts are made. QA1 is on RAID1 but QA2 and QA3 are on regular hard drives. The tempdb is located on the same array as the database files(I've already asked them to put them to put them on seperate drives). There is another temp table but is created only after the inserts on this table is done.

  5. joechang New Member

  6. pseshan New Member

    Thanks Joe, I'll check it out.
  7. bradmcgehee New Member

    The comparisons you are doing are hard to compare unless you are testing in the exact same physical box and software configurations. But even then, inserting 893 rows into a temp table should be much faster than the results you are showing. What does the code look like that you are using to create the temp table, and to insert the data into the temp table? Have you examined a query execution plan on the code to see if it is efficient? For example, may be the time problem is not when you insert the data, but when you select the data you are inserting. I am only guessing as I don't know your setup very well.

    Brad M. McGehee, SQL Server MVP
  8. Kewin New Member

    There is some debate whether a SAN would qualify as 'great' in the same sentence as SQL Server... =;o)

    At any rate, the timings for all three batches seems abysmal, to say the least... I'd expect some 800 rows inserts would be counted in milliseconds rather than seconds.

  9. Olu New Member

    Hi I am new to this Forum, can I suggest that you use sqlprofiler to determine the actual execution time at the server you can do this by selecting the SQLBatchStarting and SQLBatchCompleted events. plus the duration column to establish exactly how long it takes to execute.

    please confirm if 62s, 41s, 25s is referencing execution time (elapsed execution time on the server) or you are seeing the "total time" it takes from instruction from client --> sent across the network --> execution time on sql ---> result sets sent back to client

    Once you can conclusively prove that the above times are indeed execution times, then please post the results and

    then use SET STATISTICS TIME ON and STATISTICS IO ON as a starting point to determine where the most time and the most IO is

    incurred for each of the execution of the query..enough I think for now


    Olu Adedeji
  10. cmdr_skywalker New Member

    Can you post your batch script?

    May the Almighty God bless us all!
  11. satya Moderator

    Olu, even though you are new to this forum but your replies aren't.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page