One of the main problem that we have when we are working with multiple databases in an Elastic Databases Pool or Azure SQL Managed Instace (that are running on same SQL Instance) is how to consolidate all the information in a single place.
For example, if we use Query Data Store we need to review one by one the database finding the performance problem. Trying to resolve these insights we would like to suggest to use PerfCollector and PerfCollector Analyzer to allows you to capture and query the information collected for all the databases that you have in an Elastic Database Pool and SQL Managed Instance and even, all the databases associated in an Azure SQL Server or On-Premises.
How to collect the data?
Videos how to use?
- Lesson Learned #220:Hands-On-Labs: Activity Monitor in my Elastic Database Pool - Microsoft Tech Community
- Lesson Learned #221:Hands-On-Labs: Activity Monitor in my Azure SQL Managed Instance - Microsoft Tec...Community
Where download the PerfCollector Analyzer
Advantages to use PerfCollector Analyzer:
- Aggregate Query Data Store collected information for all databases that you have in Elastic Database Pool, Managed Instance, Azure SQL or On-Premises
- Review best practices in performance:
- Missing Indexes
- Out-Of-Date Statistics
- Other topics.
Some Example queries to obtain the information from Query Data Store for all the databases:
- Highest wait durations at instance level
SELECT TOP 10
sum(total_query_wait_time_ms) AS sum_total_wait_ms,
ws.[wait_category_desc]
FROM [_xTotalxAcummulatedx_xQDSx_query_store_wait_stats] ws
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] p ON ws.plan_id = p.plan_id and ws.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] q ON p.query_id = q.query_id and p.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query_text] qt ON q.query_text_id = qt.query_text_id and q.dbname = qt.dbname
GROUP BY ws.[wait_category_desc]
ORDER BY sum_total_wait_ms DESC;
- Highest wait durations per database at instance level/elastic database pool level
SELECT TOP 100
sum(total_query_wait_time_ms) AS sum_total_wait_ms,
ws.[wait_category_desc],
q.dbname
FROM [_xTotalxAcummulatedx_xQDSx_query_store_wait_stats] ws
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] p ON ws.plan_id = p.plan_id and ws.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] q ON p.query_id = q.query_id and p.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query_text] qt ON q.query_text_id = qt.query_text_id and q.dbname = qt.dbname
GROUP BY q.dbname , ws.[wait_category_desc]
ORDER BY q.dbname, sum_total_wait_ms DESC;
- Highest wait durations per instance level/elastic database pool level and query
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time, p.dbname
FROM [_xTotalxAcummulatedx_xQDSx_query_store_query_text] AS qt
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] AS q
ON qt.query_text_id = q.query_text_id and qt.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] AS p
ON q.query_id = p.query_id and q.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_runtime_stats] AS rs
ON p.plan_id = rs.plan_id and p.dbname = rs.dbname
WHERE rs.last_execution_time > DATEADD(DAY, -10, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
Video how to use it
Enjoy!
