Should a Sharepoint procedure cause massive IO

Question

question

Fatherjack asked Apr 16, '14

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-r2 performance-tuning sharepoint-2010

10 |1200

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

Answer 1 · 2014-04-17T06:26:34Z

Sacred Jewel answered Apr 17, '14

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

10 |1200

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

Fatherjack ♦♦ commented · Apr 17, 2014 at 07:37 AM

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 ·

question