Configure Database Solutions for Optimal Performance DP-300 Practice Questions (Page 3)

Questions 11–15

1. 11expert · medium

   A database on Azure SQL Database has automatic tuning enabled with FORCE LAST GOOD PLAN. The DBA notices that a query is running slower than before, but the automatic tuning has not forced a plan. The Query Store shows that the last good plan is not being used. What should the DBA do first?

   • AManually force the last good plan from Query Store.
   • BVerify that the query is eligible for automatic tuning.
   • CUpdate statistics on the tables involved.
   • DDisable and re-enable automatic tuning.
   Reveal Solution Discussion Select an answer first
2. 12expert · medium

   A database on Azure SQL Database has a query that uses a table-valued function (TVF) that returns a large number of rows. The query is slow because the optimizer estimates that the TVF returns only one row. The DBA wants to improve the query performance. Which intelligent query processing feature should be enabled?

   • AApproximate query processing
   • BBatch mode on rowstore
   • CInterleaved execution for multi-statement TVFs
   • DTable variable deferred compilation
   Reveal Solution Discussion Select an answer first
4. 13expert · hard

   You are the database administrator for a SQL Server 2022 instance on an Azure VM. The server hosts two databases: a mission-critical OLTP database that must always respond quickly, and a reporting database that runs heavy analytical queries. The reporting queries sometimes consume all CPU and cause the OLTP workload to slow down. You need to ensure that the OLTP workload always gets at least 70% of the CPU, while allowing the reporting workload to use the remaining capacity. You also want to avoid manual intervention. What should you do?

   • AScale up the VM to add more vCPUs
   • BUse Query Store to force a good plan for reporting queries
   • CConfigure Resource Governor with a resource pool for the reporting workload and set MAX_CPU_PERCENT to 30
   • DSet MAXDOP to 1 for the reporting database
   Reveal Solution Discussion Select an answer first
5. 14expert · medium

   A database on Azure SQL Database has a table with a large number of rows and a column that is frequently used in WHERE clauses. The statistics on this column are updated automatically, but the DBA notices that the query optimizer sometimes uses a poor cardinality estimate, leading to slow queries. The DBA wants to improve the accuracy of the statistics without causing excessive overhead. What should the DBA do?

   • AEnable automatic tuning with FORCE LAST GOOD PLAN.
   • BCreate a filtered statistic on the column for the most common value.
   • CUse the AUTO_UPDATE_STATISTICS_ASYNC setting.
   • DDisable AUTO_UPDATE_STATISTICS and manually update with FULLSCAN daily.
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   What is the primary purpose of SQL Server Resource Governor?

   • ATo limit resource consumption by different workloads
   • BTo encrypt data at rest
   • CTo automatically tune query plans
   • DTo manage user permissions
   Reveal Solution Discussion Select an answer first