Welcome to the Prodata open source library for dedicated SQL Pool maintenance and monitoring. The code contained here is open source/free with no warranty or support implied. Feel free to ping me bob@prodata.ie if you found it useful.
- Install Maintenance Solution from MaintenanceSolution.sql
- View Maintenance Source Code
- View Monitoring Source Code
Click on one of the above linkes ot get started or you can read more details, syntax and example from the below section
Goto Maintenance for details on the Synapse SQL Pool maintenance solutiuon for Columnstore and Statistics maintenance. https://github.com/ProdataSQL/SynapseTools/blob/main/SqlPools/Maintenance
This solution will help automate daily/weekly maintenance in a simialr syntax to the ubiquitous Ola Hallogren SQL Maintenance Solution, but for SQL dedicated Pools.
Note that maintenance on Synapse SQL Pools is very different than the traditional DB Engine due to scale out archietcture and less DMVs exposed to track usage.
Selection of sample stored procedures to help with SQL Pool monitoring based on the SQL Pool DMVs and the awesome sp_WhoIsActive from Adam machanic. Albeit a lot more limited due to restrictions in SQL Pool DMVs.
https://github.com/ProdataSQL/SynapseTools/tree/main/SqlPools/Monitoring
Views and SProcs include
- Requests View. Showing all requests and an analysis of step timing. use this to spot shuffles, movement and other performance issues
- sp_WhoIsActive. A wrapper for the Requests View showing currently running requests and timing
- sp_WhoWasActive. A wrapper for the Requests View showing historical requests and timing.