October 3, 2007 at 2:43 pm
Hi all,
The Tempdb on my server has an initial space allocated for the datafile to be 24011MB and logfile as 10266MB.
now the TEMPDB database size is 34511 MB and the space available is decreasing greatly and the autogrows by 10%.
can this be a cause for the slowness/ poor performance of my server.
I have run profiler and found full scans as the bad part. I have several application users complaining about the saving data taking too long. there are application deadlocks occuring on the application end.
please help ........ any possible solutions?????
Thanks,
Sathya.
October 3, 2007 at 3:17 pm
An undersized TempDB can cause performance problems as SQL Server must grow the TempDB to handle the throughput. In your case, your TempDB has grown quite and bit. What I would suggest is to find out the high water mark for your TempDB and manually set the initial size to at or near the high water mark for a typical day/week or however often you recycle the SQL service. This will prevent SQL Server from having to grow the file so often. Growing the file causes additional I/O and sometimes boggs down performance. I would also suggest that with your file sizes being as large as they are, that you set your autogrow to grow by MB instead of %. At where you stand now, each time your TempDB mdf file needs to grow, it will grow by approx. 350 MB (10% of 3.4 GB). This can take time and equate to I/O slowdowns. Set your file size to grow by 50 MB or 100 MB instead.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply