December 27, 2017 at 6:38 pm
I have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
December 27, 2017 at 6:54 pm
Well are you doing full copies of production into your test/UAT systems? And are the configurations of all the other databases the same as production?
December 27, 2017 at 8:21 pm
They will be production backups and configurations will vary. E.g Simple recovery model will be in SYSTEST/UAT while PROD will have FULL.
Datavolume and transaction counts will obviously reduce in these env.
December 28, 2017 at 1:30 am
This was removed by the editor as SPAM
December 28, 2017 at 1:36 am
I think it's up to you, and it depends what you are using the UAT environment for?
If you are simply testing any changes or bespoke work, then maybe you aren't worried about how the work performs underneath.
However, if you want the testing to be representative of a production environment, then I would suggest making them as similar as possible. This way you have a chance of spotting issues before they become problems.
In my current setup, we have a test environment that mimics our live system. We also have a dev system, which obviously matches the live system in terms of the data, but the configuration of the vm is slightly different, as I'm personally not that bothered about performance here (or any developers 😛). This is what UAT is for.
December 28, 2017 at 6:27 am
DimPerson - Thursday, December 28, 2017 1:36 AMI think it's up to you, and it depends what you are using the UAT environment for?
If you are simply testing any changes or bespoke work, then maybe you aren't worried about how the work performs underneath.
However, if you want the testing to be representative of a production environment, then I would suggest making them as similar as possible. This way you have a chance of spotting issues before they become problems.
In my current setup, we have a test environment that mimics our live system. We also have a dev system, which obviously matches the live system in terms of the data, but the configuration of the vm is slightly different, as I'm personally not that bothered about performance here (or any developers 😛). This is what UAT is for.
Finding performance issues in UAT is finding it too late. It costs too much in rework or, worse yet, some bloody schedule dictates that the code will still be released to production even with the performance problems present.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2018 at 5:12 am
Tava - Wednesday, December 27, 2017 6:38 PMI have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 2, 2018 at 6:19 am
Perry Whittle - Tuesday, January 2, 2018 5:12 AMTava - Wednesday, December 27, 2017 6:38 PMI have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
But, just like TF1118, it doesn't actually hurt anything to have multiple TempDB files (correctly configured, of course) even if you don't have such contention. Right?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2018 at 6:59 am
Jeff Moden - Tuesday, January 2, 2018 6:19 AMPerry Whittle - Tuesday, January 2, 2018 5:12 AMTava - Wednesday, December 27, 2017 6:38 PMI have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
But, just like TF1118, it doesn't actually hurt anything to have multiple TempDB files (correctly configured, of course) even if you don't have such contention. Right?
Within reason. 4, fine. 8 probably fine. 64, not a good idea.
I typically recommend 4 files, no more without evidence of contention
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2018 at 7:12 am
GilaMonster - Tuesday, January 2, 2018 6:59 AMJeff Moden - Tuesday, January 2, 2018 6:19 AMPerry Whittle - Tuesday, January 2, 2018 5:12 AMTava - Wednesday, December 27, 2017 6:38 PMI have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
But, just like TF1118, it doesn't actually hurt anything to have multiple TempDB files (correctly configured, of course) even if you don't have such contention. Right?
Within reason. 4, fine. 8 probably fine. 64, not a good idea.
I typically recommend 4 files, no more without evidence of contention
Yep... absolutely agreed on the number of files as a part of "correctly configured". Thanks for the confirmation, Gail.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2018 at 8:53 am
Perry Whittle - Tuesday, January 2, 2018 5:12 AMTava - Wednesday, December 27, 2017 6:38 PMI have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
But, just like TF1118, it doesn't actually hurt anything to have multiple TempDB files (correctly configured, of course) even if you don't have such contention. Right?
/quote]
Right, correctly configured 😉
Like Gail says, don't go overboard
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 4, 2018 at 12:34 pm
Jeff Moden - Tuesday, January 2, 2018 6:19 AMPerry Whittle - Tuesday, January 2, 2018 5:12 AMTava - Wednesday, December 27, 2017 6:38 PMI have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
But, just like TF1118, it doesn't actually hurt anything to have multiple TempDB files (correctly configured, of course) even if you don't have such contention. Right?
/quote]
Right, correctly configured 😉
Like Gail says, don't go overboard
I learned it the hard way - too many tempdb files is definitely bad when there is intensive write to tempdb. The explanation I found at the time was that during long intensive writes, a sequential write was split into multiple IO streams to spread out the load over all tempdb data files, which in turn hurts performance.
One of our DW servers has a heavy nightly job involving intensive writes to tempdb. MAXDOP is set to 8 and there are 8 tempdb data files (high performance SSD). Once a week, the same job would fail to complete within the usual 2-hour windows. When this happened we usually kill the job for that night. I use WhoIsActive to take snapshots of waits every 5 minutes. When the job failsto complete, there is usually lots of CXPACKET and PageIOLatch waits on tempdb PFS pages. My first knee jerk reaction was to add more 4 more tempdb data files. That made things a lot worse. So on the same day I removed the 4 new files, rebooted and things went back to normal, although we expect the same job to run into issues on weekly basis.
I suspect that the SSD performance degrades after every few days of heavy writes, although HP denies that possibility. The general pattern I recorded so far is as follows.
After each Friday night reboot, the nightly job would run fine until the next Thursday. During this 7-day period, tempdb Avg.DiskWriteQueueLength would increase from 110 to 210, Avg.DiskSec/Write from 0.080 to 0.120 or even 0.240 sometimes, and DiskWrites/Sec would increase from 1200 to 1400.
The server has 24 processors, 256GB memory and main database is on a FusionIO card. Tempdb files are also on a high-performance SSD drive. This is pretty much a dedicated server. During the day it is used for standard non-ad hoc reporting. At night, this big job starts its number crunching for 2 hours.
I wonder if anyone has had similar experience. Unfortunately our security is tight and I cannot upload any files for discussion.
Thanks.
January 5, 2018 at 2:46 am
GilaMonster - Tuesday, January 2, 2018 6:59 AMJeff Moden - Tuesday, January 2, 2018 6:19 AMPerry Whittle - Tuesday, January 2, 2018 5:12 AMTava - Wednesday, December 27, 2017 6:38 PMI have a question regarding the splitting of TempDB in SQL Server, I know its a must in production environments as DBs Start growing and system becomes move complex.
In terms of a System Test / UAT environments, Should the exact same process be followed to replicate what's in PROD or is there no benefit in terms of having 1 TempDB file In these environments?
Assuming the answer would be what's in production should flow through all other environment, but is there anything debating against this?
Multiple files in tempdb are implemented to overcome allocation contention within the database, the first question is, are you facing allocation contention, have you monitored for this?
But, just like TF1118, it doesn't actually hurt anything to have multiple TempDB files (correctly configured, of course) even if you don't have such contention. Right?
Within reason. 4, fine. 8 probably fine. 64, not a good idea.
I typically recommend 4 files, no more without evidence of contention
Stupid question but how would you capture evidence of contention? Is it purely when you see lot of PageIO and CXpacket wait MS time
January 5, 2018 at 3:00 am
Tava - Friday, January 5, 2018 2:46 AMIs it purely when you see lot of PageIO and CXpacket wait MS time
Those have got nothing to do with TempDB allocation contention. PageIOLatch is an IO wait (pages loaded into memory) and CXPacket is related to parallelism.
TempDB contention = PageLatch waits (not PageIOLatch) on the TempDB allocation pages (most commonly 2:1:3)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2018 at 4:58 am
GilaMonster - Friday, January 5, 2018 3:00 AMTava - Friday, January 5, 2018 2:46 AMIs it purely when you see lot of PageIO and CXpacket wait MS timeThose have got nothing to do with TempDB allocation contention. PageIOLatch is an IO wait (pages loaded into memory) and CXPacket is related to parallelism.
TempDB contention = PageLatch waits (not PageIOLatch) on the TempDB allocation pages (most commonly 2:1:3)
Thanks for your help, spent the last few moments understanding this from multiple sources and reference (below) from Brents site had a very good explanation for dummies lol.
https://www.brentozar.com/archive/2014/05/tell-need-tempdb-files/amp/
going to start running some tests and snapshot the results.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply