May 12, 2020 at 11:37 pm
OK, before everyone jumps on me and tells me this is settled... 🙂
I have a client where the application vendor and SQL System Admin are insisting that having more than 8 TempDB files is recommended. In the DBAs words "Microsoft recommend up to 8 unless you make heavy use of TempDB".
Well they have a 32 core server, and you guessed it in one, they have 32 TempDB Data files. So the question is: Is this recommended anywhere, and if it is can someone send me a reference? Alternately, can I get definitive proof that it is recommended not to do this.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 13, 2020 at 1:53 pm
BrainDonor beat me to the link he provided. I'll also add that if you're having serious problems with TempDB, there's usually some code in desperate need of a fix.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2020 at 1:56 am
Further to this post, I have another post regarding CMEMTHREAD Waits, which is the same client (https://www.sqlservercentral.com/forums/topic/cmemthread-waits-2)
The database is 3.6TB, and their indexes are seriously fragmented, even the clustered indexes/PKs, even those that are identity (1,1). My understanding regarding index fragmentation is there is debate around optimizing based on traditional parameters (>15% Rebuild, 5% - 15% Reorg) but there is no real debate around optimization in general, and when there are large tables (100 Million+) at 90% fragmentation (over 60% for some clustered indexes), I would expect these indexes to be optimized. This is especially true when I look at the space wasted in memory. They have a PK taking 51GB of RAM, of which 17GB is unused space! To me this shouts REBUILD!
Along with this, the statistics are old, or poorly sampled. For example a 480 million row table, with the clustered index/PK as above being sampled at 3% and not updated recently despite a 14% change in data since the last update over a month ago. Trace Flag 2371 has not been enabled. The research I've done and my own experience indicates that low sampling rates on indexes like this generate skewed statistics which then produce bad performance.
Unfortunately, the application vendor has done no work to adapt the database for its size, so there's no table partitioning which would allow for index rebuilds at the partition level and incremental update stats (SQL 2014 feature).
Also, they make massive use of TempDB with # tables and with the poor maintenance, and given what I've seen so far, there are significant code fixes required.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 15, 2020 at 9:54 pm
Rebuild the stats first. Let's talk about the indexes after that.
Believe it or not and depending on the type(s) of workloads they have, logical fragmentation may not actually matter for performance here (and I'll confirm that BOL is correct about that but most people never read that). Page Density (which some people call physical fragmentation) can certainly be a problem but if you try to solve that in a typical fashion, you could end up with some really nasty blocking (speaking from some serious experience here). A little research (which I've built some tools to do) will go a really long way towards understanding what the indexes are actually doing. The averages from sys.dm_db_index_physical_stats just don't tell a complete story.
Whatever you do, don't used REORGANIZE (it doesn't work the way most people imagine even after reading BOL, which correctly states exactly how it works albeit in a very poor manner) unless you can actually confirm that you have LOBs that need to be compressed (again, voice of some serious experience there).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply