June 14, 2017 at 4:43 am
hi all,
i have a complex question how can we do performance tuning in a SQL AZURE when your scenario is that you get a daily backup restore in Test DB and
we can only access to Test and if there is any performance issue in prod like below we need to provided recommendation to the Prod DBA by checking in Test DB.
1-fragmentation issue
2-missing index
one more question does backup of DB contain metadata of user scan and etc... that we required for fragmentation check and execution plan.
Thanks
Ivan
June 14, 2017 at 5:38 am
Backup?
Are you using a VM on Azure, or Azure SQLDB (the PaaS offering)?
The DMVs are not included in backups, they're dynamic views, views into the server's active state.
If you're using Azure SQLDB or have a SQL 2016 instance in a VM, you can use Query Store for the analysis.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2017 at 8:22 am
GilaMonster - Wednesday, June 14, 2017 5:38 AMBackup?Are you using a VM on Azure, or Azure SQLDB (the PaaS offering)?
The DMVs are not included in backups, they're dynamic views, views into the server's active state.
If you're using Azure SQLDB or have a SQL 2016 instance in a VM, you can use Query Store for the analysis.
Although, a DACPAC export of the database (the only "backup" option currently available in PaaS) wouldn't include the QueryStore information unfortunately. You could export that info to flat files and import it into tables later for querying I suppose. Otherwise, if you expect query metrics, I'd use Extended Events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply