look at my reply above , thats a five day window!
the scripts i use for indexing are just the standard ola hallengren scripts
And as i said, normal erp sql activity is pretty good
im probably looking at something thats irrelevant, but its just something i thought was peculiar?
Not the one I was originally looking at, which was this one... I posted without refreshing my screen and so didn't see your additional post.
On that subject (TempDB usage) and also looking at the 5 day graph you're talking about, you don't have the indications of a problem there no does it indicate the you don't have problems.
On the Subject of Index Maintenance
Shifting gears to the indexing... Ola's code is fantastic. The way people use it is not. You're doing the same wrong thing that 99% of the world does. Even the guy that "invented" those settings denounced them way back in 2009. Read the last 2 paragraphs of the following article... especially the very last sentence of the article.
As many before me have said, "Index maintenance is a really expensive way to rebuild your statistics", which is usually the real savior when it comes to performance, especially if most of your Clustered Indexes are based on an "Ever Increasing" key such as an IDENTITY/SEQUENCE or some form of DATETIME column.
REORGANIZE is a serious problem. People use it without knowing what it actually does and how little it actually does. I know you don't know what it does because you wouldn't be using it if you did. It will NOT, for example, set your pages to the FILL FACTOR because it will NOT create pages to reduce page density to the FILL FACTOR like REBUILD will. In fact, REORGANIZE will decrease page density UP to the FILL FACTOR, which is the worst thing you can do when most of your page densities have filled to above the FILL FACTOR.
Also, take a month of Sundays like I have and find any form of BBFAATT (pronounced as "bee-bee-fat" and can be bleated like a goat when sarcasm is intended and stands for Books, Blogs, Forums, AI, Articles, 'Tubes, and Talks) where it say that logical fragmentation CAN cause performance issues and then find even one where they demonstrate that is true. No one has proven it and I'm working on an article (it's taking a while to simplify it to keep it from being a hefty tome) with real, live, repeatable demonstrations that prove that it's usually not true and is so small that it doesn't matter in most cases. I also prove where it can actually make performance worse. I also prove that it's better to do NO index maintenance than to do it wrong and that, if you're using REORGANIZE in a generic fashion, then your doing it wrong.
I DO have a 'Tube out there that proves that GUID Fragmentation is mostly because of people using REORGANIZE And, it's not just about GUID fragmentation. A lot of indexes fall into the very similar "even random distribution" pattern and REORGANIZE puts the screws to those, as well. Here's the link to the 'Tube. Like it says in the 'tube, it's not just about GUIDs. We're just using those because they're the "poster child" for index fragmentation.
In other words, the 5/30 method is actually a WORST practice. On April 2oth of 2021, even Microsoft changed the page where the 5/30 myth was published on. What they replaced it with is just as bad because no one has the time to manually evaluate every one of the indexes. I've pretty much got that beat, as well... I'm just not ready to publish it yet.
I'll also tell you that defaulting to a 90% FILL FACTOR is nearly a complete waste. You're just wasting space for most of your "ever increasing" indexes that fragment in the hot-spot and for the rest because you're using REORGANIZE.
In the meantime, stop killing yourself with index maintenance every night and just rebuild stats that need it. You may see an improvement right away because you won't actually be perpetuating and making your page splits worse every day. If you want to do index maintenance, then only do it for space recovery or to selectively prevent fragmentation on evenly distributed fragmenting indexes until you know a lot more about the individual indexes.
I did zero index maintenance on my production box for 4 years without performance issue. In fact, performance got a little better each day for the first 3 months. I don't recommend you go that long without some "space recovery" of low page density indexes but I wanted to let you know that I do practice what I preach. I also NEVER use REORGANIZE on rowstore indexes because I DO know what is does and does not do.
As for your occasionally slow code...
It's not TempDB. It sounds like it could be a form of parameter sniffing. You're going to have to bite the bullet and ad some code that reports how long and how many reads each section took unless you can somehow catch an "actual" execution plan for it.