SQL Server Performance Forum – Threads Archive
inserts on tempdb is slow
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. Thanks,Pradeep
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
http://www.sqlbrad.com
Tempdb usage and performance is based on many factors as bradmcgehee mentioned… Read the following article… it may help you in troubleshooting tempdb issues… Working with tempdb in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx MohammedU.
Moderator
SQL-Server-Performance.com
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. Thanks,
Pradeep
you could run my storage performance scripts to see just how great your hw config actually is,
compare against some results already posted http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=16995
Thanks Joe, I’ll check it out.
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
http://www.sqlbrad.com
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. /Kenneth
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 thanks Olu Adedeji
Can you post your batch script? May the Almighty God bless us all!
www.empoweredinformation.com
Olu, even though you are new to this forum but your replies aren’t. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
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.
]]>