If all worked, you should now see that we have contention on the
sysschobjstable. Earlier we discussed using
sp_helpto get index details on system tables, if we do that now and look at index 2, we will see the lead column is
nsclasswhich is a tinyint field. Using a tinyint as a lead column is typically a terrible idea since there is little selectivity on such a narrow field, and this is no exception.
This isn’t the only case of contention you might see with system objects related to temporary tables. We ran into a few different contention scenarios with tempdb:
sysschobjsagain, but on index 3. This index leads with the name of the temporary table and is fairly narrow so you can fit a lot of records on a single index page. Because of this, if you are running lots of concurrent procedures that create temporary tables with the same or similar names, it creates a hot spot on a single page, leading to more contention.
Temporary table auto-stats. Statistics objects for all tables (including temporary tables) are stored in the
sys.sysobjvaluestable. If you get enough auto-stats generations on temporary tables you can see contention here.
Mark’s post reads like a book chapter and he does a great job of summing up the problem and the solution.