Does anyone have a good link to documentation regarding how the syshobtcolumns and syshobt system tables are populated. The reason for asking is that we are receiving pagelatch_sh and pageliolatch_ex blocking on tempdb.sys.syshobtcolumns.clust (lead blocker). I realise that the tables are undocumented.
This is caused by a very poor sharepoint query which does a lot of work in the tempdb. The query is generated by the sharepoint API's. I understand that our developers can make alterations to the query to reduce the amount of work which it performs. Lazy coding means that hundreds of irrelevant columns and rows are being returned.
I'm interested to find out how this system table is being blocked. The query generates a table variable, inserts a bunch of rows and then performs a god awefull query against the data involving spooling etc. I would attach this but the query is over 60000 chars. I know that this is causing the blocking but understanding the syshobtcolumns system table will enable me to determine if it is these sharepoint queries causing issues or if we have a more serious issue with our configuration(s).
I have identified 6 sharepoint queries for the developers to look into reducing the number of rows and columns returned. All are called throughout the day and all have similar workloads in the tempdb. The blocking and subsequent slow down of all queries on the sharepoint SQL instance occur when the CheckDB weekly job executes. We see an increase in disk waits during this period once a week. We are yet to determine if we have exhibiting a tipping point on the SAN and we are effectively hitting a bottle neck when our maintenance tasks execute at the same time of these I/O intensive queries.
We most certainly need to improve these sharepoint queries to reduce contention, however the issues occur at regular times when the maintenance task (checkdb full) executes.
Our setup is on a DL580 a 16 core server, 4 quads, 32Gb ram, 3par san, tempdb has 8 data files, sql server 2005 SP3 cu8 x64 bit enterprise, windows server 2003 R2 x64 bit enterprise, separate luns for data, logs, tempdb etc. Performance monitoring through SQL Sentry (lead blocker found here along with intensive queries and disk waits).
Best Answer
DBCC CHECKDB uses tempdb and can stress the I/O subsystem heavily when it runs. It uses tempdb to store facts during the checks it is running. Paul explains some of the details of this in his blog post (http://sqlskills.com/BLOGS/PAUL/post/How-does-DBCC-CHECKDB-WITH-ESTIMATEONLY-work.aspx) If you are getting latch contention on the system tables in tempdb, the only thing you can really do is reduce your usage of tempdb. Adding additional files only reduces latch contention on the PFS, GAM and SGAM pages.
Have you configured the SQL instance following Microsofts recommendations for Sharepoint, which includes setting 'max degree of parallelism' to 1 for the instance? (http://technet.microsoft.com/en-us/library/hh292622.aspx)