temp_db slow

  • (This issue was also posted in the Replication thread by a colleague, but I don't believe it is related to Replication. I apologize for the cross post)

    We're having issues with #tables running excessively slow. We traced the execution of a Select count(*) from #record to 734 ms. This table is a one or none temp table. Other SQLs on #tables ran comparatively slow, also. As I alluded to before, Replication was enabled, but then turned off completely, including stopping stats collection. Still having the performance problems. The only other factor we came up with is that the drive that temp_db resides on was extremely fragmented (>90%). It's on the same drive as the data files, but the regular tables don't seem to be affected.

    Does anyone have any other ideas other than defragging the disk?

  • Is Tempdb allowed to automatically grow? What is the file growth increment setting? Set the original size of the tempdb database files to a reasonable size to avoid the files from automatically expanding as more space is needed. Set the file growth increment percentage to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small compared to the amount of data being written to the tempdb database, then tempdb may need to constantly expand, thereby affecting performance.

  • Sorry, I forgot to add that info to the original message.

    temp_db is 604mb with 580 mb free. The autoincrement factor is 10%. The disk that it resides on is 120 GB, w/ 70GB free.

  • Hi

    Query the tempdb over a period of time in which performance is suffering to better gauge what is happening. Also, now are they being created, static cursors? have you traced via profiler looking for IO/CPU intensive SQL?

    Cheers

    Ck


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Use Performance monitor over some period to see waht is the resource choking the performance of tempDB? Is tempDb on the same drive as OS files? Also is there any other application running on the system? As you have mentioned about fragmentation, go offline for few hours and defragment the drive. Its a good practive to have defragmented drive for data files.

    HTH.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply