September 3, 2019 at 3:31 pm
Hello experts,
I have inherited a SQL Server 2008 R2 server. Somehow even though it is in production, its tempdb is on the same drive as the data and the tempdb is set to its defaults.
tempdev Initial Size (MB) 8 / Autogrowth By 10%, unrestricted growth
templog Initial Size (MB) 1 / Autogrowth By 10%, unrestricted growth
The server has a database that is several hundred GB in size, so it's amazing that the server is even running at all.
I am compiling my list of issues to present to management.
But for the immediate issue (understandably slow SQL behavior given the tempdb issue) is it possible for me to add tempdb files on a separate drive (1) without needing to restart SQL services and (2) that the SQL processes on the server can start using immediately to help alleviate the slowness?
My hope is that I can recommend something that won't require downtime now, even though surely some downtime will be required later for a comprehensive reconfiguration of the tempdb and other items.
Thanks for any help.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 3, 2019 at 5:08 pm
I believe so. Just use the standard SQL command to add a file(s):
ALTER DATABASE tempdb ADD FILE
( NAME = ..., FILENAME = '...' , SIZE = nnnMB, ... ),
( NAME = ..., FILENAME = '...' , SIZE = nnnMB, ... )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2019 at 8:47 am
Yes, you can add files. moving/removing, IIRC, takes a restart
September 4, 2019 at 2:48 pm
The server has a database that is several hundred GB in size, so it's amazing that the server is even running at all.
Heh... while the world insists that you absolutely need more than 1 or 2 TempDB files and MS drank that Kool-Aid in their installation process, as well, the real fact of the matter is that it's not necessarily true and can actually cause you some serious grief when other things occur like when you're working on a large table and you want to use SET IDENTITY INSERT ON (if you're working with the whole table, the whole table will be sorted in TempDB and ALL files will grow even if only 1 is being used to the size of the table because MS made the mistake of enforcing Trace Flag 1117 with no option to opt out in TempDB as of (IIRC), 2016). And, yeah, I do realize that you're using 2008 but thought it all worth mentioning for the future.
Yes, there is some good and strong merit to having multiple TempDB files BUT... if you have a server that's been running with fewer files or even just one and you can't prove that TempDB is the actual point of contention (it's usually crap code that causes the contention), then you might want to consider leaving it alone.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2019 at 6:15 pm
webrunner wrote:The server has a database that is several hundred GB in size, so it's amazing that the server is even running at all.
Heh... while the world insists that you absolutely need more than 1 or 2 TempDB files and MS drank that Kool-Aid in their installation process, as well, the real fact of the matter is that it's not necessarily true and can actually cause you some serious grief when other things occur like when you're working on a large table and you want to use SET IDENTITY INSERT ON (if you're working with the whole table, the whole table will be sorted in TempDB and ALL files will grow even if only 1 is being used to the size of the table because MS made the mistake of enforcing Trace Flag 1117 with no option to opt out in TempDB as of (IIRC), 2016). And, yeah, I do realize that you're using 2008 but thought it all worth mentioning for the future.
Yes, there is some good and strong merit to having multiple TempDB files BUT... if you have a server that's been running with fewer files or even just one and you can't prove that TempDB is the actual point of contention (it's usually crap code that causes the contention), then you might want to consider leaving it alone.
I don't understand. Why would SET IDENTITY_INSERT ON force a sort of the whole table?
Tempdb should be sized so that it does not need to grow, including even for rebuilds of large tables. If you have to, add another drive to contain only tempdb file(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2019 at 9:59 pm
Jeff Moden wrote:webrunner wrote:The server has a database that is several hundred GB in size, so it's amazing that the server is even running at all.
Heh... while the world insists that you absolutely need more than 1 or 2 TempDB files and MS drank that Kool-Aid in their installation process, as well, the real fact of the matter is that it's not necessarily true and can actually cause you some serious grief when other things occur like when you're working on a large table and you want to use SET IDENTITY INSERT ON (if you're working with the whole table, the whole table will be sorted in TempDB and ALL files will grow even if only 1 is being used to the size of the table because MS made the mistake of enforcing Trace Flag 1117 with no option to opt out in TempDB as of (IIRC), 2016). And, yeah, I do realize that you're using 2008 but thought it all worth mentioning for the future.
Yes, there is some good and strong merit to having multiple TempDB files BUT... if you have a server that's been running with fewer files or even just one and you can't prove that TempDB is the actual point of contention (it's usually crap code that causes the contention), then you might want to consider leaving it alone.
I don't understand. Why would SET IDENTITY_INSERT ON force a sort of the whole table?
Tempdb should be sized so that it does not need to grow, including even for rebuilds of large tables. If you have to, add another drive to contain only tempdb file(s).
Most of us that have run into the problem have asked the same question. It's also a "bug" item on the Azure Feedback site (formerly, the CONNECT site).
And, I agree... I had TempDB properly sized but when 8 files try to grow to 146GB each because one file did (thanks to the other damned MS-Builtin problem of TF 1117 not being able to be overridden in TempDB), it would have broken my estimate by a factor of 7 (we have 8 TempDB files) anyway. There's just no justification for needing a Terabtye of TempDB to work on a 146GB table.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply