Matching tempdb files to physical cores
-
I heard one DBA talking about matching tempdb files to the physical core count of the VM. In essence, 4 tempdb files to 4 cores. Autogrowth being disabled it looks like, and matching the file sizes. When I asked about it, he simply said "because it performs better" but didn't have any explanation.
Anyone care to input while I'm searching online?
-
I've never heard of this and can't think of any reason why this would really make sense.
-
@scottalanmiller said in Matching tempdb files to physical cores:
I've never heard of this and can't think of any reason why this would really make sense.
I can only find things from SQL back in 2000.
-
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
I've never heard of this and can't think of any reason why this would really make sense.
I can only find things from SQL back in 2000.
Back to the era when crazy manual tunings on databases was a thing
If SQL Server doesn't handle this itself today, that's just sad. -
@scottalanmiller said in Matching tempdb files to physical cores:
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
I've never heard of this and can't think of any reason why this would really make sense.
I can only find things from SQL back in 2000.
Back to the era when crazy manual tunings on databases was a thing
If SQL Server doesn't handle this itself today, that's just sad.That's what I was thinking. Though, the client and a DBA both said that they had issues if it wasn't set that way. I had asked why they would need to be manually tuned but I may as well have been insulting them. Who knows I guess.
-
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
I've never heard of this and can't think of any reason why this would really make sense.
I can only find things from SQL back in 2000.
Back to the era when crazy manual tunings on databases was a thing
If SQL Server doesn't handle this itself today, that's just sad.That's what I was thinking. Though, the client and a DBA both said that they had issues if it wasn't set that way. I had asked why they would need to be manually tuned but I may as well have been insulting them. Who knows I guess.
They both have "issues"? I call BS. These two people are the only two to ever have this issue and both happen to just know the fix even though MS tells no one about this and DBAs don't know about it? No way. Why is no one else anywhere affected by this? Are these the biggest SQL Server users ever? Is this the biggest database ever managed by MS SQL Server?
-
Well, there is it, MS recommends one per thread (NOT cores) as a starting point but only as a guideline for sizing, not a performance thing.
So, like so many things like this, sounds like someone misunderstood a guideline, thought it meant something very different and just repeated it having thought that they were supposed to learn it by rote rather than understanding the info as it was supposed to be.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
-
@scottalanmiller said in Matching tempdb files to physical cores:
Well, there is it, MS recommends one per thread (NOT cores) as a starting point but only as a guideline for sizing, not a performance thing.
So, like so many things like this, sounds like someone misunderstood a guideline, thought it meant something very different and just repeated it having thought that they were supposed to learn it by rote rather than understanding the info as it was supposed to be.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
But you would call BS on someone who says "without doing this, you will see severe issues with this server"?
-
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
Well, there is it, MS recommends one per thread (NOT cores) as a starting point but only as a guideline for sizing, not a performance thing.
So, like so many things like this, sounds like someone misunderstood a guideline, thought it meant something very different and just repeated it having thought that they were supposed to learn it by rote rather than understanding the info as it was supposed to be.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
But you would call BS on someone who says "without doing this, you will see severe issues with this server"?
Yes, especially as they got the info wrong and it doesn't match what MS said. It sounds WAY too close to "I heard this but misunderstood it in common ways" and then claimed the performance thing after the fact to justify the mistakes that they had made. You can this constantly in the SMB, people can't test performance and just imagine things.
Had it today, someone claimed that remote NFS was faster than local disks. Even claimed to have tested it. Physically impossible, the claim was ludicrous, but he didn't know what was plausible so thought that it was okay to make such a claim.
-
The thing that makes it so certainly BS is that it is so obviously a "chinese telephone" of the original source information from MS.
-
Microsoft seems to agree, this isn't a normal thing. For these two to claim that this thing is always a problem and MS says that maybe 1% of the time it will help performance is a pretty big gap....
The recommendation isn't for general performance. It is for a very specific performance bottleneck that many people will never even experience. We generally refer to it as "tempdb contention" though we are actually talking about contention on the system allocation pages called GAM, SGAM, and PFS pages. Contention can occur on any page, but we are only concerned with these 3 page types in this discussion.
You can see a good description of the issue and what each of those allocation page types are in this blog post I wrote here: http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontention
99% of people will be perfectly fine with 1 tempdb data file for every 2 or 4 (logical) CPUs. Only on very busy systems do you need to go as far as 1 tempdb data file for every CPU. However, if you want to play it safe, you can do 1 data file per CPU and never have to worry.
You may also want to read the whitepaper I wrote on tempdb: https://www.idera.com/resourcecentral/whitepapers/demystify-tempdb-performance-and-management
-
@scottalanmiller said in Matching tempdb files to physical cores:
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
Well, there is it, MS recommends one per thread (NOT cores) as a starting point but only as a guideline for sizing, not a performance thing.
So, like so many things like this, sounds like someone misunderstood a guideline, thought it meant something very different and just repeated it having thought that they were supposed to learn it by rote rather than understanding the info as it was supposed to be.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
But you would call BS on someone who says "without doing this, you will see severe issues with this server"?
Yes, especially as they got the info wrong and it doesn't match what MS said. It sounds WAY too close to "I heard this but misunderstood it in common ways" and then claimed the performance thing after the fact to justify the mistakes that they had made. You can this constantly in the SMB, people can't test performance and just imagine things.
Had it today, someone claimed that remote NFS was faster than local disks. Even claimed to have tested it. Physically impossible, the claim was ludicrous, but he didn't know what was plausible so thought that it was okay to make such a claim.
But did they really test it?
-
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
@BBigford said in Matching tempdb files to physical cores:
@scottalanmiller said in Matching tempdb files to physical cores:
Well, there is it, MS recommends one per thread (NOT cores) as a starting point but only as a guideline for sizing, not a performance thing.
So, like so many things like this, sounds like someone misunderstood a guideline, thought it meant something very different and just repeated it having thought that they were supposed to learn it by rote rather than understanding the info as it was supposed to be.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
But you would call BS on someone who says "without doing this, you will see severe issues with this server"?
Yes, especially as they got the info wrong and it doesn't match what MS said. It sounds WAY too close to "I heard this but misunderstood it in common ways" and then claimed the performance thing after the fact to justify the mistakes that they had made. You can this constantly in the SMB, people can't test performance and just imagine things.
Had it today, someone claimed that remote NFS was faster than local disks. Even claimed to have tested it. Physically impossible, the claim was ludicrous, but he didn't know what was plausible so thought that it was okay to make such a claim.
But did they really test it?
I think that he probably did, but actually tested a big array with a big cache vs. a single disk with no cache. I think he contrived a fake test to test the disks, rather than testing the architecture. He refused to say anything about what he had tested, just made this ridiculous claim that was obviously untrue thinking we wouldn't call his bluff.
-
@BBigford said in Matching tempdb files to physical cores:
Though, the client and a DBA both said that they had issues if it wasn't set that way. I had asked why they would need to be manually tuned but I may as well have been insulting them. Who knows I guess.
As you should have been.
Let's put it this way. When I worked for the big red V, I had a physical SQL server for a car financial company. 64 processors, 512GB of RAM, running SQL Server 2008 R2. It ran one TempDB. Dedicated to a drive, standard procedure for us deploying. We never had performance issues, and I can guarantee you that there is no way anyone outside of another bank runs SQL Server that hard. If it is good enough for them, it's plenty good enough for the 200GB DB with 200MB of transactions a day.
People who claim bullshit need to be called out on their bullshit. This falls under the "never virtualize SQL/AD" bullshit.
-
That sums it up.
