question

Fatherjack avatar image
Fatherjack asked

Should a Sharepoint procedure cause massive IO

I have been asked to look at the performance of a SharePoint (SP) SQL Server that periodically goes slow. Some configurations have been changed (MAXDOP and AUTO STATISTICS) to meet SP best practice and the UI performance affecting IO has been reduced from ~2hours down to 10-15 minutes but I am still uncertain if the procedure should still cause such massive IO every hour. The process in question is proc_GetChanges and it seems to be triggered by a timer event in SP. Looking at a query plan from last night it is joining 3 sets of 1,700M rows to eventually output a very small result set. Any DBAs supporting SP, or SharePoint admins, that have experience of this circumstance please?
sql-server-2008-r2performance-tuningsharepoint-2010
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Sacred Jewel avatar image
Sacred Jewel answered
Although I am also a newbie on SharePoint....1700M rows for EventCache seems too big for me....Are you sure the timer job to clear the events is running successfully and purging the expired data....Also, you may need to look at the retention period value....if this is the real issue and gets fixed then I guess the performance would be fixed automatically...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply - EventCache actually has 118M records. I have asked the SP admins to review their data retention settings and let me know the outcome.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.