Solved

The relationship between tempdb and replication slow response alert.

Posted on 2016-08-14
13
45 Views
Last Modified: 2016-08-17
hi,

we keep having replication unresponsive alert from SQL server as I recalled that the replication job alert show this as normal before but it said Tempdb contention, but I can't find the related record any more.

any resource can help to determine this or measure it?
0
Comment
Question by:marrowyung
  • 7
  • 6
13 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41756201
It's your complex Merge Replication with CDC?
Do you have the exact error message?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41756418
no.

it just merge replication slow and I am trying to see if enhancing tempdb can help.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41756429
I guess you should follow the usual recommendations for tempdb:
  • Isolate tempdb in a dedicated and fast disk
  • Have as many data files as the server has processors, till a maximum of 8
  • Having all files with the same size
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41757355
hi,

yes, I have all that.

but one thing:

"Have as many data files as the server has processors, till a maximum of 8"

 that one should be only for SQL 2016 and above.

we all talking about .mdf and .ndf but how about log ? all data writes to log first and then committed to the data file.

how about as much tempdb log file as tempdb data file? and all equal size ?

this might also solve the tempdb log contention problem.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41757455
that one should be only for SQL 2016 and above.
No. That's a rule that DBAs are using since at least SQL Server 2005.


we all talking about .mdf and .ndf but how about log ? all data writes to log first and then committed to the data file.

 how about as much tempdb log file as tempdb data file? and all equal size ?
The rule is only for data files. There's no sense to create more that a transaction log file since it's sequential writing (the engine can't write in more than a transaction log file at a time).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41757473
"No. That's a rule that DBAs are using since at least SQL Server 2005."

what I know there are only 3 x rules for tempdb:
1) number of tempdb file = number of CPU core.
2) number of tempdb file = 1/2 number of CPU core.
3) number of tempdb file = number of CPU socket,

I know in SQL 2016, default number of tempdb file is 8 and we no need to calculate any more, and just keep adding tempdb if tempdb contention is  found.

that's why I don't' understnad what you said.

"There's no sense to create more that a transaction log file since it's sequential writing (the engine can't write in more than a transaction log file at a time)."

tempdb data file is parallel write ?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41757638
what I know there are only 3 x rules for tempdb:
 1) number of tempdb file = number of CPU core.
 2) number of tempdb file = 1/2 number of CPU core.
 3) number of tempdb file = number of CPU socket,
So if you have for example a server with 4 cores in 2 sockets, how many files would you create?

I know in SQL 2016, default number of tempdb file is 8 and we no need to calculate any more, and just keep adding tempdb if tempdb contention is  found.
This is for facilitate the life on non-DBAs :) And I bet that you don't see there more than one transaction log file, do you?

tempdb data file is parallel write ?
Any SQL Server database is, since it has more than one data file.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41758967
"So if you have for example a server with 4 cores in 2 sockets, how many files would you create?"

it is a total of 8 cores, so 8 tempdb files, but I am more worry about the tempdb log files, as data write to that first.

"This is for facilitate the life on non-DBAs :) And I bet that you don't see there more than one transaction log file, do you?"

yes, it is for not that experts DBA.  

sorry you lost, I see a lot of situation where we have more than one log file and that's why I ask if it really helps.

you know now if I run who is active, you can see query from time to time write to tempdb log, this make me worry/think about one more tempdb log files to speed up the tempdb operation.

"Any SQL Server database is, since it has more than one data file."

just the log is not ...

do you know if it is in a round robin manner ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41758976
"This is for facilitate the life on non-DBAs :) And I bet that you don't see there more than one transaction log file, do you?"

can you tell me why people would like more than 1 tempdb log files ?
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41758980
I don't know why are you insisting on this one but there's no performance advantage AT ALL to have more than one transaction log file no matter what database is it.
Transaction log is being written sequentially so parallel writing is not possible. You're only losing time if trying to do something that's not possible.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41758981
can you tell me why people would like more than 1 tempdb log files ?
Besides you and don't know anybody that like to have more than 1 tempdb log file. The only situation that I can think that justifies a second transaction log file is when the disk is full and for emergency you'll need to create a 2nd transaction log file in another disk for an immediate workaround.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41760534
Vitor Montalvão,

"You're only losing time if trying to do something that's not possible."

ok, this is good answer then..

I just see TEmpdb contention message in replicaiton and I am not sure if I use create more log file instead of data files.

"The only situation that I can think that justifies a second transaction log file is when the disk is full and for emergency you'll need to create a 2nd transaction log file in another disk for an immediate workaround."

for emergency situation, like what we have today, we just kill spid and shrink log.

then tempdb fine.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41760535
tks victor.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now