Maintenance Plan Running forever -Sharepoint SQL Server 2016

Copper Contributor

Hello experts,

 

we have maintenance plan which shrinks top 10 DATABASES based on the SIZE and free space available. It then checks database integrity, reorganizes the index, rebuilds the index and updates the statistics. The plan was running fine for sometime. However, I ran it an issue last couple of times where it gets hung forever.

 

As a part of a troubleshooting, I was hoping to find the queries causing the High CPU usage for a day before and the current day. I did not get that piece of information from "sys dm_exec_query_stats" DMV and it shows the last-execution time for the current day only.

 

Could anyone please assist how would I troubleshoot this issue? 

Looking forward to get educated on this task.

 

Thanks,

Jinal

1 Reply

 


reorganizes the index, rebuilds the index and updates the statistics.

@Jinal_P , index first reorg then rebuild; that's redundant and makes no sense; same with stats update after an index rebuild.

Throw away that maintenance plan and use SQL Server Index and Statistics Maintenance (hallengren.com)