Support_38
asked on
Tempdb Contention - SQL SERVER
Good evening,
I would like you to help me understand an issue.
According to the attached image, the tempdb base is with contention.
My question is whether this containment is from PFS, SGAM GAM or a normal data page.
If it is not a control page, GAM, SGAM PFS, adding more datafiles would help minimize the problem?
Thank you.
tempdb.png
I would like you to help me understand an issue.
According to the attached image, the tempdb base is with contention.
My question is whether this containment is from PFS, SGAM GAM or a normal data page.
If it is not a control page, GAM, SGAM PFS, adding more datafiles would help minimize the problem?
Thank you.
tempdb.png
Yes. Let me find the corresponding article and post the link here. The logic is number of data files = number of cores on your box.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What's the configuration of the server? And the SQL Server instance?
How big is the tempdb and how it's configured (data and log files)?
How big is the tempdb and how it's configured (data and log files)?
ASKER
Good Morning
I have a 128-core server.
I can not create 128 datafiles.
I have 16 datafiles.
thank you
I have a 128-core server.
I can not create 128 datafiles.
I have 16 datafiles.
thank you
8 tempdb data files should be enough but 16 won't make any harm.
How about the resources (CPU and RAM) of the server?
How about the resources (CPU and RAM) of the server?
ASKER
hi
256 GB memory
128 core cpu
256 GB memory
128 core cpu
It looks like you have a good machine there. Is it dedicated for this SQL Server instance only?
Also, how big is the tempdb database?
The contention happens always at the same time of day or it has a random behavior?
Also, how big is the tempdb database?
The contention happens always at the same time of day or it has a random behavior?
Alex, is this issue solved?
If not, please answer by comments from my previous reply.
Cheers
If not, please answer by comments from my previous reply.
Cheers
ASKER
good evening
Sorry for the delay, actually we ended up detecting some processes that consumed tempdb a lot.
The Development team optimized the procedures and the problem was solved.
Thank you.
Sorry for the delay, actually we ended up detecting some processes that consumed tempdb a lot.
The Development team optimized the procedures and the problem was solved.
Thank you.
ASKER
good evening
Sorry for the delay, actually we ended up detecting some processes that consumed tempdb a lot.
The Development team optimized the procedures and the problem was solved.
Thank you.
Sorry for the delay, actually we ended up detecting some processes that consumed tempdb a lot.
The Development team optimized the procedures and the problem was solved.
Thank you.