This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Introduction:
Today, I worked on a service request that our customer has a Business Critical database with 4 vCores and Read-Scale Out enabled. Our customer noticed several performance issues using Read-Scale Out database and I would like to explain several lessons learned found during the troubleshooting steps.
Initial Script Execution and Synchronization:
The investigation began with the execution of a script on the primary database, which populated approximately 7,864,320 rows. Synchronization between the primary and secondary databases was successfully completed, ensuring consistency in data distribution.
Observations on Secondary Database:
- Running the following script (we never executed this query previously in the primary) it was observed that temporary automatic statistics were created (sys.stats (Transact-SQL) - SQL Server | Microsoft Learn ) , indicating potential areas for optimization and needed for this workload and will be deleted in case of any reconfiguration/failover. You could see them using the column is_temporary in sys.stats.
- Analysis of the execution plan highlighted a recommendation for index creation to improve query performance.
Identifying Performance Bottlenecks:
Further analysis revealed that the query execution was consuming a significant amount of CPU resources on the secondary database. To pinpoint the underlying issues, a query was executed to identify the top CPU-intensive operations.
Proposed Solutions and Implementation: To address the performance challenges, several actions were proposed:
- Removing temporary statistics generated on the secondary database.
- Creating the recommended index on the primary replica to enhance query execution.
- Executing necessary scripts on the primary replica to ensure optimal performance.
Actions done:
- If the indexes is needed, the first topic is to remove statistics using the command: DROP STATISTICS NOTES.[_WA_Sys_00000002_5AEE82B9_readonly_database_statistics]
- Create the index needed in primary replica CREATE NONCLUSTERED INDEX [NOTES_IX1] ON [dbo].[Notes] ([ID] ASC) include (name)
- Execute the script in the primary replica if any additional statistics is needed to create or any other topic:
- We could see that right now the primary has all the details and secundary will not needed to create additional statistics and will use the index to improve the query performance.