Query Store Hints Preview en Azure SQL

A principios de junio de este año Microsoft lanzo Query Store Hints como Public Preview en Azure SQL (Azure SQL Database y Managed Instance) 

Esta nueva funcionalidad agrega una nueva característica super interesante a Query Store. 

Ya conocemos que Query Store se encarga de almacenar los planes de ejecución así como las estadísticas de runtime (tiempo de ejecución, uso de CPU, lectura , escritura , etc) para todos los procesos en una base de datos de usuario.  

Sabemos que muchas veces SQL Server no elige el mejor plan de ejecución, por lo que en algunas ocasiones vemos alguna regresión en la ejecución de nuestros procesos.

En estos casos, si tenemos acceso al código podemos cambiar el comportamiento de dicho proceso modificando el código y agregando una QUERY HINT . En muchos casos no tenemos la posibilidad de modificar el código por lo que no podríamos cambiar dicho comportamiento. 

Con Query Store Hints ahora podemos identificar el query en el que detectamos la regresión para luego asignar en forma externa unos o varios hints, por lo que podremos cambiar el comportamiento de los procesos problemáticos sin tener que modificar el código. 

Los invito a ver este video donde explico todos los detalles y muestro algunos ejemplos de esta nueva funcionalidad.

Exam DP-300 Administering Relational Databases on Microsoft Azure – Study Guide

AZURE DP-300 STUDY GUIDE

ADMINISTERING RELATIONAL DATABASES ON MICROSOFT AZURE

If you are interested in getting the new Microsoft role-based certification “Azure Database Administrator Associate” you only have to take the exam (DP-300. Administering Relational Databases on Microsoft Azure) so I’ve prepared a study guide that includes all the areas and topics that will be evaluated as well as links to the official documentation and learning portal.

Plan and Implement Data Platform Resources (15-20%)

Deploy resources by using manual methods
• Deploy database offerings on selected platforms
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas
• Configure customized deployment templates
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-resource-manager-samples?tabs=single-database
• Apply patches and updates for hybrid and IaaS deployment
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-automated-patching

Recommend an appropriate database offering based on specific requirements

• Evaluate requirements for the deployment
• Evaluate the functional benefits/impact of possible database offerings
• Evaluate the scalability of the possible database offering
• Evaluate the HA/DR of the possible database offering
• Evaluate the security aspects of the possible database offering

Configure resources for scale and performance

• Configure Azure SQL database/elastic pools for scale and performance
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool-manage
• Configure Azure SQL managed instances for scale and performance
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance
• Configure SQL Server in Azure VMs for scale and performance
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance
• Calculate resource requirements
https://docs.microsoft.com/en-us/sharepoint/administration/storage-and-sql-server-capacity-planning-and-configuration
• Evaluate database partitioning techniques, such as database sharding
https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning

Evaluate a strategy for moving to Azure

• Evaluate requirements for the migration
https://docs.microsoft.com/en-us/sql/dma/dma-assesssqlonprem?view=sql-server-ver15
• Evaluate offline or online migration strategies
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql
• Evaluate requirements for the upgrade
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-version-15?view=sql-server-ver15

• Evaluate offline or online upgrade strategies
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade

Implement a migration or upgrade strategy for moving to Azure

• Implement an online migration strategy
https://datamigration.microsoft.com/scenario/sql-to-azuresqldb?step=1
• Implement an offline migration strategy
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql
• implement an online upgrade strategy
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade
• Implement an offline upgrade strategy
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-ver15

Implement a Secure Environment (15-20%)

Configure database authentication by using platform and database tools

• Configure Azure AD authentication
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure?tabs=azure-powershell
• Create users from Azure AD identities
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication
• Configure security principals
https://docs.microsoft.com/en-us/sql/relational-databases/security/securing-sql-server?view=sql-server-ver15

Configure database authorization by using platform and database tools

• Configure database and object-level permissions using graphical tools
https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15
• Apply principle of least privilege for all securables
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authorization-and-permissions-in-sql-server

Implement security for data at rest

• Implement Transparent Data Encryption (TDE)
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
• Implement object-level encryption
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15
• Implement Dynamic Data Masking
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15
• Implement Azure Key Vault and disk encryption for Azure VMs
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disk-encryption-key-vault

 

Implement security for data in transit

• Configure SQL DB and database-level firewall rules
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure
• Implement Always Encrypted
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15
• Configure Azure Data Gateway
https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway

Implement compliance controls for sensitive data

• Apply a data classification strategy
https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-data-discovery-and-classification?view=sql-server-ver15&tabs=t-sql
• Configure server and database audits
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15
• Implement data change tracking
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver15
• Perform vulnerability assessment
https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-vulnerability-assessment?view=sql-server-ver15

Monitor and Optimize Operational Resources (15-20%)

Monitor activity and performance

• Prepare an operational performance baseline
https://docs.microsoft.com/en-us/sql/relational-databases/performance/establish-a-performance-baseline?view=sql-server-ver15
• Determine sources for performance metrics
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
• Interpret performance metrics
https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-ver15
• Assess database performance by using Azure SQL Database Intelligent
Performance
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance
• Configure and monitor activity and performance at the infrastructure, server, service, and database levels
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure?view=sql-server-ver15

 

Implement performance-related maintenance tasks

• Implement index maintenance tasks
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/rebuild-index-task-maintenance-plan?view=sql-server-ver15
• Implement statistics maintenance tasks
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/update-statistics-task-maintenance-plan?view=sql-server-ver15
• Configure database auto-tuning
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning-enable
• Automate database maintenance tasks
– Azure SQL agent jobs, Azure automation, SQL server agent jobs
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-ver15
• Manage storage capacity
https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15

Identify performance-related issues

• Configure Query Store to collect performance data
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
• Identify sessions that cause blocking
https://support.microsoft.com/en-gb/help/224453/inf-understanding-and-resolving-sql-server-blocking-problems
• Sssess growth/fragmentation of databases and logs
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
• Assess performance-related database configuration parameters
– including AutoClose, AutoShrink, AutoGrowth
https://support.microsoft.com/en-gb/help/315512/considerations-for-the-autogrow-and-autoshrink-settings-in-sql-server

Configure resources for optimal performance

• Configure storage and infrastructure resources
– Optimize IOPS, throughput, and latency
– Optimize Tempdb performance
– Optimize data and log files for performance
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-server-storage-configuration
• Configure server and service account settings for performance
https://support.microsoft.com/en-gb/help/319942/how-to-determine-proper-sql-server-configuration-settings
• Configure Resource Governor for performance
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver15

Configure a user database for optimal performance
• Implement database-scoped configuration
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15
• Configure compute resources for scaling
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-scale
• Configure Intelligent Query Processing (IQP)
https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15

 

 

Optimize Query Performance (5-10%)

Review query plans
• Determine the appropriate type of execution plan
– Live Query Statistics, Actual Execution Plan, Estimated Execution Plan, Showplan
https://docs.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver15
• Identify problem areas in execution plans
https://docs.microsoft.com/en-us/sql/relational-databases/performance/analyze-an-actual-execution-plan?view=sql-server-ver15
• Extract query plans from the Query Store
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

Evaluate performance improvements
• Determine the appropriate Dynamic Management Views (DMVs) to gather query performance information
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs
• Identify performance issues using DMVs
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs
• Identify and implement index changes for queries
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15
• Recommend query construct modifications based on resource usage
https://docs.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-ver15
• Assess the use of hints for query performance
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15

 

Review database table and index design
• Identify data quality issues with duplication of data
https://docs.microsoft.com/en-us/sql/data-quality-services/introduction-to-data-quality-services?view=sql-server-ver15
• Identify normal form of database
https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
• Assess index design for performance
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15
• Validate data types defined for columns
https://www.mssqltips.com/sqlservertip/4799/validate-integer-and-decimal-values-in-sql-server/
• Recommend table and index storage including filegroups
• Evaluate table partitioning strategy
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15
• Evaluate the use of compression for tables and indexes
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table-or-index?view=sql-server-ver15

 

Perform Automation of Tasks (10-15%)

Create scheduled tasks
• Manage schedules for regular maintenance jobs
https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-ver15
• Configure multi-server automation
https://docs.microsoft.com/en-us/sql/ssms/agent/automated-administration-across-an-enterprise?view=sql-server-ver15
• Configure notifications for task success/failure/non-completion
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-ver15
Evaluate and implement an alert and notification strategy
• Create event notifications based on metrics
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-insights-alerts-portal

• Create event notifications for Azure resources
https://docs.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications?view=sql-server-ver15

• Create alerts for server configuration changes
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql?view=sql-server-ver15
• Create tasks that respond to event notifications
https://docs.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications?view=sql-server-ver15

Manage and automate tasks in Azure
• Perform automated deployment methods for resources
https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml
• Automate Backups
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups?tabs=single-database
• Automate performance tuning and patching
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning
• Implement policies by using automated evaluation modes
https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/administer-servers-by-using-policy-based-management?view=sql-server-ver15

 

 

 

Plan and Implement a High Availability and Disaster Recovery (HADR)
Environment (15-20%)

Recommend an HADR strategy for a data platform solution
• Recommend HADR strategy based on RPO/RTO requirements
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-business-continuity
• Evaluate HADR for hybrid deployments
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr
• Evaluate Azure-specific HADR solutions
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-high-availability
• Identify resources for HADR solutions
https://www.sqlskills.com/blogs/erin/sql-server-hadr-features/

Test an HADR strategy by using platform, OS and database tools
• Test HA by using failover
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15
• Test DR by using failover or restore
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery

Perform backup and restore a database by using database tools
• Perform a database backup with options
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15#TsqlProcedure
• Perform a database restore with options
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15
• Perform a database restore to a point in time
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model?view=sql-server-ver15
• Configure long-term backup retention
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-retention

 

Configure DR by using platform and database tools
• Configure replication
https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-preparing-the-server-for-replication?view=sql-server-ver15
• Configure Azure Site Recovery for a database offering
https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-sql

Configure HA using platform, OS and database tools
• Create an Availability Group
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-availability-group-transact-sql?view=sql-server-ver15
• Integrate a database into an Availability Group
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database?view=sql-server-ver15
• Configure quorum options for a Windows Server Failover Cluster
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server?view=sql-server-ver15
• Configure an Availability Group listener
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15

 

Perform Administration by Using T-SQL (10-15%)

Examine system health
• Evaluate database health using DMVs
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs#monitoring-query-performance
• Evaluate server health using DMVs
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs
• Perform database consistency checks by using DBCC
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

Monitor database configuration by using T-SQL
• Assess proper database autogrowth configuration
https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/
• Report on database free space
https://docs.microsoft.com/en-us/sql/relational-databases/databases/display-data-and-log-space-information-for-a-database?view=sql-server-ver15#TsqlProcedure
• Review database configuration options
https://docs.microsoft.com/en-us/sql/relational-databases/databases/change-the-configuration-settings-for-a-database?view=sql-server-ver15#TsqlProcedure

Perform backup and restore a database by using T-SQL
• Prepare databases for AlwaysOn Availability Groups
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-ver15
• Perform transaction log backup
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-ver15#using-transact-sql
• Perform restore of user databases
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15
• Perform database backups with options
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

Manage authentication by using T-SQL
• Manage certificates
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-certificate-transact-sql?view=sql-server-ver15
• Manage security principals
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-ver15


Manage authorization by using T-SQL
• Configure permissions for users to access database objects
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/grant-a-permission-to-a-principal?view=sql-server-ver15
• Configure permissions by using custom roles
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-an-application-role?view=sql-server-ver15#TsqlProcedure

 

SQL Server Workshops check out https://microsoft.github.io/sqlworkshops/