tempdb data and log file

Question

Post reply

tempdb data and log file

sqlguy80

SSCrazy

Points: 2763
More actions
April 1, 2017 at 1:33 pm

#320063

Hi,
We have some reporting queries which use tempdb very heavily and I get alerts from monitoring tool, that temp db contention, tempdb autogrowth occurred etc.
We recently moved to SQL Server 2016 . The box has 16 virtual processors, so I created 8 data files .
There is only 1 log file on the same drive as the tempdb data drive.
Should I move the tempdb logfile to another drive along with other user database log files?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Yes move the log.

Are all of your data files the same size? Are they large enough to support the reporting requirements?

If you still have contention with the 8 files, you should look at adding more data files.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 2

I guess I'll have to disagree if TempDB is on a SAN. Unless you can guarantee that the MDF and LDF files are guaranteed to be on separate spindles, there's no point in standing up another drive because the same read/write heads will be used and that's the bottle neck... R/W head movement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 3

sqlguy80 - Saturday, April 1, 2017 1:33 PM
Hi,
We have some reporting queries which use tempdb very heavily and I get alerts from monitoring tool, that temp db contention, tempdb autogrowth occurred etc.
We recently moved to SQL Server 2016 . The box has 16 virtual processors, so I created 8 data files .
There is only 1 log file on the same drive as the tempdb data drive.
Should I move the tempdb logfile to another drive along with other user database log files?
Thanks

You're problem isn't with TempDB. The problem is with the queries. They need to be fixed. You can trick around the issue 'til the cows come home but it won't change anything, The reporting queries need to be fixed. You might get lucky with the addition of a proper index or two but, if the queries are using that much TempDB as to become a concern, then any added indexes might not actually be used because of things like SARGability and a lack of pre-aggregation, etc, etc. Rewrite the queries because that's where the performance is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff Moden - Saturday, April 1, 2017 8:41 PM
I guess I'll have to disagree if TempDB is on a SAN. Unless you can guarantee that the MDF and LDF files are guaranteed to be on separate spindles, there's no point in standing up another drive because the same read/write heads will be used and that's the bottle neck... R/W head movement.

He asked if he should move it to the log volume. We aren't talking about standing up a new volume.

If the machine is a VM it is more unlikely that the volumes are accessible via different aggregates and paths. That said, a good san admin and working in concert with the sysadmins should hopefully have the LUNS adequately separated.

If it is a flash array, not really necessary to separate but there is some benefit still because the volumes should realistically be allocated and formatted differently according to the different access uses.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 5

SQLRNNR - Saturday, April 1, 2017 8:48 PM
Jeff Moden - Saturday, April 1, 2017 8:41 PM
I guess I'll have to disagree if TempDB is on a SAN. Unless you can guarantee that the MDF and LDF files are guaranteed to be on separate spindles, there's no point in standing up another drive because the same read/write heads will be used and that's the bottle neck... R/W head movement.
He asked if he should move it to the log volume. We aren't talking about standing up a new volume.
If the machine is a VM it is more unlikely that the volumes are accessible via different aggregates and paths. That said, a good san admin and working in concert with the sysadmins should hopefully have the LUNS adequately separated.
If it is a flash array, not really necessary to separate but there is some benefit still because the volumes should realistically be allocated and formatted differently according to the different access uses.

That would definitely work but haven't met many SAN admins that think that way. Most of the ones I've talked to have actually rebelled against that idea. Definitely not saying they right or wrong. Just repeating what they've said. In most cases, it doesn't matter to me one way or the other. Even though we do some heavy lifting in the batch world, our 8 TempDB MDF/NDF files and the LDF have each been set to just 2GB and haven't grown in 5 years even on the 2TB system. It didn't used to be that way when I first started. We fixed a fair bit of inefficient code left by our predecessors.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Jeff Moden - Saturday, April 1, 2017 9:33 PM

SQLRNNR - Saturday, April 1, 2017 8:48 PM

Jeff Moden - Saturday, April 1, 2017 8:41 PM
We fixed a fair bit of inefficient code left by our predecessors.

That's the real key there.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 734744 More actions · Answer 7

SQLRNNR - Saturday, April 1, 2017 9:52 PM
That's the real key there.

+1

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 8

Steve Jones - SSC Editor - Sunday, April 2, 2017 3:01 AM
SQLRNNR - Saturday, April 1, 2017 9:52 PM
That's the real key there.
+1

Heh... +1000... Like I said earlier...

Jeff Moden - Saturday, April 1, 2017 8:46 PM
You're problem isn't with TempDB. The problem is with the queries. They need to be fixed. You can trick around the issue 'til the cows come home but it won't change anything, The reporting queries need to be fixed. You might get lucky with the addition of a proper index or two but, if the queries are using that much TempDB as to become a concern, then any added indexes might not actually be used because of things like SARGability and a lack of pre-aggregation, etc, etc. Rewrite the queries because that's where the performance is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233958 More actions · Answer 9

sqlguy80 - Saturday, April 1, 2017 1:33 PM
Hi,
We have some reporting queries which use tempdb very heavily and I get alerts from monitoring tool, that temp db contention, tempdb autogrowth occurred etc.
We recently moved to SQL Server 2016 . The box has 16 virtual processors, so I created 8 data files .
There is only 1 log file on the same drive as the tempdb data drive.
Should I move the tempdb logfile to another drive along with other user database log files?
Thanks

No, do not move the tempdb t-log to the same volume as the user logs.
If you can separate it to a separate volume or leave with tempdb data files.
As stated already the real problem is the queries the server is handling

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉