SQL Performance Tuning – 3 Using MDW to T-shoot Performance Degradation
Management Data Warehouse is a great tool to create SQL Server performance baseline. Below are the steps I recommend my customer to use Management Data Warehouse to find out possible root causes for performance degradation scenario, which means the application response time was in acceptable level previously but now end users are complaining it's slower recently.
| Step | What to Check | What does it mean | What to Do Next |
| 1 | Top N queries list | If the application usage pattern change? | If new query appears, then tune the new query.If the query pattern is same, go to step 2. |
| 2 | CPU time of top N query | Where SQL Server spend more time? | If CPU time is same level, it indicates wait time increase, go to step 3.If CPU time is larger than baseline, it indicates SQL Server spend more time. Go to step 4. |
| 3 | Check Top N wait stats | What kind of Wait type waiting time increase? | Find them out and tune it case by case. |
| 4 | Check total logical read | Does the data volume increase? | If yes, it indicates SQL Server handles more IO, go to step 5.If no, it indicates memory, CPU or disk bottleneck, go to step 6. |
| 5 | Check execution number | Does the number of execution increase? | If yes, it indicates the workload increase, this is normal that performance degrade when concurrency increasing.If no, it indicates the avg IO increase per execution, go to step 7. |
| 6 | Check total physical read | Does physical read increase? | If yes, it indicates SQL Server doesn't have enough memory at that moment.If no, it indicates CPU or disk bottleneck, go to step 8. |
| 7 | Check table/database size | Does table/database size grow? | If yes, it indicates the query performance is related to table size, review the index design or reduce table size.If no, it indicates SQL Server consumes more IO for same query, go to step 9. |
| 8 | Check Disk Sec for read/write | Does disk sec increase? | If yes, then disk is the bottleneck. Check the performance from storage level.If no, the CPU is the bottleneck. Make sure Power Plan is High and check CPU configuration if SQL running on VM. |
| 9 | Check execution plan | Is execution plan the same? | If yes, it indicates index fragment, go to step 10.If no, it indicates parameter sniffing, go to step 11. |
| 10 | Check index fragmentation | Is fragmentation rate high? | If yes, rebuild index.If no, call Microsoft Support for further investigation. |
| 11 | Check SQL execution plan with different parameter | Does the execution plan change for different parameter? | If yes, we are hitting parameter sniffing, we could consider to use index hint or freeze plan.If no, call Microsoft Support for further investigation. |
HTH,
--Shiyang Qiu, July 17, 2016