Tempdb slowly after migrating on SQL Server 2008 R2
-
The tempdb database is located on a SSD disk. Before migration - a SQL Server 2005 SP3 - the avg_read_stall_ms value was 1.2 and avg_write_stall_ms value was 3.9
(These values provided by the DMV sys.dm_io_virtual_file_stats)
After migration on a SQL server 2008 R2, avg_read_stall_ms value value is 1.7 and avg_write_stall_ms 342.0.
I dont understand why write performance in Tempdb is deteriorated.
We tried copying a file to the SSD disk and the transfer rate before and after migration remains the same. It seems that only SQL instance have slowed when writing on tempdb.
Any ideas ?
Thanks.
Sorry for my bad english.
-
Phillippe can we review some basic setups first?
What is the default size of tempdb? at startup, by default, it's a piddling 8 meg, and would grow by 1 meg at a time when it needs to grow;
that's not what you want on a production system, and certainly not on a nice fast SSD...you want to change that to a larger startup size.
here's a default installation example:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Of course.
Initial size is 8,192 Mb (data and log), autogrowth 10%.
Recovery model : simple.
Others options are set with default values.
Change tracking is set to false.
The tempsdb is alone on this disk. Others databases are on a SAN.
-
Viens-tu du Québec / France?
-
Belgique. Je parle français.
-
Ok, je vais traduire si tu ne comprends pas.
-
Thanks.
-
my first recommendatino would be to change that to a larger Initial Size, which depends a lot on how big it is rig now. if it's 2Gig in size right now for example, that would be a good initial size;
that shows that the operations it's done so far made it grow to that size, so you don't want it having to grow a zillion times from 8 meg to 1 Gig when it doesn't need to; that impacts perforamance.
what is tempdb's current size?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Sorry, the initial size is 8 GB (8,192 Mb and not 8.192 Mb as i mentionned). My initial post was wrong, i've correct it.
The actual size is now 14.512 GB. The first time the tempdb reaches this size was during a full recalculation of statistics, next night the server was migrated.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply