patternsqlModerate
What is hot spotting in the context of adding files to tempdb?
Viewed 0 times
spottingthetempdbwhataddingfileshotcontext
Problem
I'm trying to find out whether it is possible to add tempdb files to a SQL Server without having to restart the SQL Server service. I saw this answer here on Database Administrators:
And one answer states:
ADD - no outage required. Although as Sean from Microsoft pointed out,
SQL will prefer to use the lower filled files. If you are going from 1
data file and adding more, then SQL will use the new ones for a while,
but your performance won't be worse than only having one file.
However, if you have 2+ already and add one more, then it will hotspot
on the new one and decrease performance.
However, a comment cautions the following:
I would put an addendum on the "Add" part: "Add: No, but you'll most
likely be imbalanced so you'll be hot spotting which could make things
much worse."
I have the following questions about that comment but was instructed to ask those questions in a new question of my own (this one) rather than asking the commenter via comment in that question's answers.
Specifically:
what happens with hotspotting on tempdb after adding files)
- Tempdb Add files require Restart
And one answer states:
ADD - no outage required. Although as Sean from Microsoft pointed out,
SQL will prefer to use the lower filled files. If you are going from 1
data file and adding more, then SQL will use the new ones for a while,
but your performance won't be worse than only having one file.
However, if you have 2+ already and add one more, then it will hotspot
on the new one and decrease performance.
However, a comment cautions the following:
I would put an addendum on the "Add" part: "Add: No, but you'll most
likely be imbalanced so you'll be hot spotting which could make things
much worse."
I have the following questions about that comment but was instructed to ask those questions in a new question of my own (this one) rather than asking the commenter via comment in that question's answers.
Specifically:
- What is hot spotting? (I got some info via Google but not in detail
what happens with hotspotting on tempdb after adding files)
- What about hot spotting makes things much worse in tempdb?
- What specific things in the DB would get much worse?
Solution
-
What is hot spotting?
"Hot spotting" in this context means that, even though tempdb has multiple files, all the I/O work is being done in a single file. If tempdb is busy enough to justify adding files, the imbalance that leads to hot-spotting (due to proportional fill) will be short-lived, so I think the warnings may be a little Chicken Little. In my experience, anyway.
-
What about hot spotting makes things much worse in tempdb?
I think it is deemed to be worse in tempdb because it takes a brunt of the write activity in most workloads. You can certainly suffer similar problems in user databases, but since you're already trying to solve a problem in tempdb...
-
What specific things in the DB would get much worse?
Write times, mostly. Imagine everyone trying to use the same ATM, even when there are 7 other ATMs nearby. Only so much can be written at any point in time; everything else has to wait. With more files (and enough cores to schedule the work), the I/O can be spread more evenly.
Just make sure:
What is hot spotting?
"Hot spotting" in this context means that, even though tempdb has multiple files, all the I/O work is being done in a single file. If tempdb is busy enough to justify adding files, the imbalance that leads to hot-spotting (due to proportional fill) will be short-lived, so I think the warnings may be a little Chicken Little. In my experience, anyway.
-
What about hot spotting makes things much worse in tempdb?
I think it is deemed to be worse in tempdb because it takes a brunt of the write activity in most workloads. You can certainly suffer similar problems in user databases, but since you're already trying to solve a problem in tempdb...
-
What specific things in the DB would get much worse?
Write times, mostly. Imagine everyone trying to use the same ATM, even when there are 7 other ATMs nearby. Only so much can be written at any point in time; everything else has to wait. With more files (and enough cores to schedule the work), the I/O can be spread more evenly.
Just make sure:
- you have an even number of files
- all the files are the same size
- all the files have the same autogrow settings
- the number of files is somehow driven by number of cores
- depending on version (these are enabled by default in more modern versions of SQL Server), you are using trace flags 1117 (grow all data files at once) and 1118 (don't use mixed allocation extents).
Context
StackExchange Database Administrators Q#246889, answer score: 16
Revisions (0)
No revisions yet.