Backup compression<\/span><\/p>\nAnswer: 1) Plan forcing<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Query Store plan forcing allows an administrator to instruct the query optimizer to use a selected execution plan for a particular query. This can be useful when a query has experienced a performance regression after a plan change. Before forcing a plan, administrators should investigate the workload and understand why the optimizer selected the newer plan. Plan forcing is a performance-management capability and does not permanently prevent future plan changes in every circumstance. Data masking, row-level security, and backup compression address data protection or storage rather than execution-plan management.<\/span><\/p>\nQuestion 382. Which stored procedure can be used to configure a Query Store hint for a query?<\/b><\/h3>\n
1)<\/b> sp_query_store_set_hints<\/span>
\n<\/span>2)<\/b> sp_addrolemember<\/span>
\n<\/span>3)<\/b> sp_configure_backup<\/span>
\n<\/span>4)<\/b> sp_database_firewall<\/span><\/p>\nAnswer: 1) sp_query_store_set_hints<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The sp_query_store_set_hints stored procedure is used to apply a Query Store hint to a specific query. Query Store hints allow administrators to influence optimizer behavior without modifying the application code or directly changing the query text. Depending on the supported hint, administrators can address certain performance scenarios by controlling selected query-optimization behaviors. Query Store hints should be applied carefully because inappropriate hints can negatively affect performance. The other procedures listed are not valid mechanisms for configuring Query Store hints. Query Store provides the historical information needed to identify queries that may require investigation.<\/span><\/p>\nQuestion 383. What is a common symptom of parameter sniffing in SQL Server?<\/b><\/h3>\n
1)<\/b> A query performs differently depending on the parameter values used<\/span>
\n<\/span>2)<\/b> A database cannot be backed up<\/span>
\n<\/span>3)<\/b> A firewall rule disappears<\/span>
\n<\/span>4)<\/b> Encryption keys are automatically deleted<\/span><\/p>\nAnswer: 1) A query performs differently depending on the parameter values used<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Parameter sniffing occurs when SQL Server creates an execution plan based on parameter values encountered during compilation and later reuses that plan for different parameter values. If data distribution is uneven, a plan that works efficiently for one parameter value may perform poorly for another. This can result in inconsistent query performance. Administrators can investigate execution plans, Query Store history, statistics, and workload characteristics when diagnosing the issue. Parameter sniffing is a query-optimization behavior rather than a backup, networking, or encryption problem. Possible remedies depend on the specific workload and should be tested carefully.<\/span><\/p>\nQuestion 384. Which SQL Server option can reduce compilation overhead for workloads that execute many single-use ad hoc queries?<\/b><\/h3>\n
1)<\/b> Optimize for ad hoc workloads<\/span>
\n<\/span>2)<\/b> Transparent Data Encryption<\/span>
\n<\/span>3)<\/b> Database Mail<\/span>
\n<\/span>4)<\/b> Row-Level Security<\/span><\/p>\nAnswer: 1) Optimize for ad hoc workloads<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The optimize for ad hoc workloads configuration option can help reduce the memory used by cached plans for workloads containing many single-use ad hoc queries. When enabled, SQL Server can initially store a smaller compiled-plan stub instead of the complete plan for certain ad hoc queries. If the query is executed again, SQL Server can then store the full compiled plan. This can be useful in environments where plan cache memory is being consumed by large numbers of single-use statements. The option does not control encryption, email notifications, or row-level access restrictions.<\/span><\/p>\nQuestion 385. What is the primary purpose of a plan guide in SQL Server?<\/b><\/h3>\n
1)<\/b> To influence query optimization without changing the query text<\/span>
\n<\/span>2)<\/b> To create database backups<\/span>
\n<\/span>3)<\/b> To configure Azure firewall rules<\/span>
\n<\/span>4)<\/b> To encrypt transaction logs<\/span><\/p>\nAnswer: 1) To influence query optimization without changing the query text<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A plan guide allows administrators to influence how SQL Server optimizes a query without modifying the application’s query text. It can associate hints or other optimization behavior with matching queries, which can be useful when changing application code is difficult or impractical. Plan guides should be used carefully because they can affect execution-plan selection and may become unnecessary after application or database changes. Administrators should monitor their effectiveness over time. Plan guides are unrelated to backup creation, Azure firewall configuration, or transaction-log encryption.<\/span><\/p>\nQuestion 386. Which technique can help prevent excessive recompilation of a stored procedure when appropriate?<\/b><\/h3>\n
1)<\/b> Appropriate plan and procedure design<\/span>
\n<\/span>2)<\/b> Disabling all indexes<\/span>
\n<\/span>3)<\/b> Removing all statistics<\/span>
\n<\/span>4)<\/b> Disabling transaction logging<\/span><\/p>\nAnswer: 1) Appropriate plan and procedure design<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Stored procedure recompilation can occur for several legitimate reasons, but unnecessary recompilation may increase CPU usage because SQL Server must repeatedly compile execution plans. Administrators can investigate recompilation causes through performance monitoring and execution-plan analysis. Appropriate procedure design, stable parameter usage, suitable indexing, and carefully selected recompilation options can help manage this behavior. Recompilation is not inherently harmful because a new plan can sometimes be beneficial when data or workload characteristics change. Disabling indexes, removing statistics, or disabling transaction logging are not appropriate general solutions and can create other performance or recovery problems.<\/span><\/p>\nQuestion 387. What is ownership chaining in SQL Server?<\/b><\/h3>\n
1)<\/b> A mechanism that can allow access through related objects without separate permissions on every underlying object<\/span>
\n<\/span>2)<\/b> A backup retention policy<\/span>
\n<\/span>3)<\/b> A database encryption method<\/span>
\n<\/span>4)<\/b> A network routing mechanism<\/span><\/p>\nAnswer: 1) A mechanism that can allow access through related objects without separate permissions on every underlying object<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Ownership chaining occurs when SQL Server objects such as stored procedures and tables have the same owner and a permission check can be avoided on the referenced object as part of the chain. This can simplify application security because users may receive permission to execute a stored procedure without receiving direct permissions on every table accessed by that procedure. Ownership chaining must be designed carefully because it affects how permissions flow between objects. It is not a backup, encryption, or network-routing mechanism. Understanding ownership relationships is important when implementing least-privilege database security.<\/span><\/p>\nQuestion 388. What is the purpose of an application role in SQL Server?<\/b><\/h3>\n
1)<\/b> To provide permissions based on an application-defined security context<\/span>
\n<\/span>2)<\/b> To automatically rebuild indexes<\/span>
\n<\/span>3)<\/b> To create database backups<\/span>
\n<\/span>4)<\/b> To configure Azure regions<\/span><\/p>\nAnswer: 1) To provide permissions based on an application-defined security context<\/b><\/p>\n
Explanation:<\/b><\/p>\n
An application role allows a database application to activate a specific database security context and use the permissions assigned to that role. This can help applications enforce a defined set of database permissions independently of the permissions associated with the individual user’s normal database access. Application roles should be designed carefully because activating one changes the security context used by subsequent database operations within the connection. They are useful when applications need controlled access to database resources. Application roles do not perform backup, index maintenance, or Azure-region configuration tasks.<\/span><\/p>\nQuestion 389. Which statement correctly describes a database role?<\/b><\/h3>\n
1)<\/b> It groups database principals so permissions can be managed collectively<\/span>
\n<\/span>2)<\/b> It automatically creates database backups<\/span>
\n<\/span>3)<\/b> It represents a physical database server<\/span>
\n<\/span>4)<\/b> It stores transaction log files<\/span><\/p>\nAnswer: 1) It groups database principals so permissions can be managed collectively<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A database role is a security principal that can contain users or other database principals and receive permissions as a group. Instead of granting the same permissions individually to many users, administrators can assign permissions to a role and then add appropriate users to that role. This simplifies permission management and supports the principle of least privilege when roles are carefully designed. Database roles are logical security objects and do not represent physical servers or storage structures. They also do not automatically create backups or store transaction logs.<\/span><\/p>\nQuestion 390. What is a securable in SQL Server?<\/b><\/h3>\n
1)<\/b> A resource to which permissions can be granted or denied<\/span>
\n<\/span>2)<\/b> A backup compression algorithm<\/span>
\n<\/span>3)<\/b> A database monitoring alert<\/span>
\n<\/span>4)<\/b> A physical network cable<\/span><\/p>\nAnswer: 1) A resource to which permissions can be granted or denied<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A securable is a SQL Server resource that can be protected through the permission system. Examples include databases, schemas, tables, views, stored procedures, and other objects. Administrators grant, deny, or revoke permissions on securables to control which principals can perform specific actions. Understanding securables is essential when designing database security because permissions can be assigned at different levels of the object hierarchy. Securables are unrelated to backup compression or physical networking. Effective security administration requires identifying the appropriate securable and granting only the permissions necessary for the user’s or application’s responsibilities.<\/span><\/p>\nQuestion 391. Which permission statement grants users SELECT access to all current and future tables within a schema?<\/b><\/h3>\n
1)<\/b> GRANT SELECT ON SCHEMA::Sales TO User1<\/span>
\n<\/span>2)<\/b> GRANT BACKUP ON DATABASE::Sales TO User1<\/span>
\n<\/span>3)<\/b> GRANT SELECT ON SERVER::Sales TO User1<\/span>
\n<\/span>4)<\/b> GRANT TABLE ON DATABASE::Sales TO User1<\/span><\/p>\nAnswer: 1) GRANT SELECT ON SCHEMA::Sales TO User1<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Granting SELECT permission on a schema provides access to the appropriate securables within that schema, including tables and views to which the permission applies. Schema-level permissions can simplify administration when many objects require consistent access controls. They can also apply to objects added to the schema later, reducing the need to grant permissions individually each time a new object is created. Administrators should still ensure that the schema contains only objects appropriate for the intended users. The other statements use invalid or inappropriate permission syntax and do not provide the required schema-level access.<\/span><\/p>\nQuestion 392. What is cross-database ownership chaining used for?<\/b><\/h3>\n
1)<\/b> It can allow permissions to flow between objects in different databases under supported ownership conditions<\/span>
\n<\/span>2)<\/b> It automatically copies databases to another region<\/span>
\n<\/span>3)<\/b> It encrypts every database backup<\/span>
\n<\/span>4)<\/b> It controls Azure DNS records<\/span><\/p>\nAnswer: 1) It can allow permissions to flow between objects in different databases under supported ownership conditions<\/b><\/p>\n
Explanation:<\/b><\/p>\n
Cross-database ownership chaining extends ownership-chaining behavior across database boundaries under supported conditions. When ownership and security requirements are satisfied, a user executing an object in one database may access referenced objects in another database without receiving direct permissions on those underlying objects. Because this behavior can affect security boundaries, administrators should evaluate whether it is appropriate for the environment. It should not be enabled or relied upon without understanding the security implications. Cross-database ownership chaining does not replicate databases, encrypt backups, or manage Azure DNS.<\/span><\/p>\nQuestion 393. Which authentication method uses a SQL Server-defined username and password?<\/b><\/h3>\n
1)<\/b> SQL authentication<\/span>
\n<\/span>2)<\/b> Microsoft Entra authentication<\/span>
\n<\/span>3)<\/b> Certificate-only authentication<\/span>
\n<\/span>4)<\/b> Azure Storage authentication<\/span><\/p>\nAnswer: 1) SQL authentication<\/b><\/p>\n
Explanation:<\/b><\/p>\n
SQL authentication uses a SQL Server login configured with a username and password managed by SQL Server. It differs from Microsoft Entra authentication, where identity management is handled through Microsoft Entra ID. SQL authentication can be useful for applications or environments where SQL logins are required, but administrators should protect credentials carefully and follow organizational security policies. Strong passwords, appropriate permissions, secure connection settings, and monitoring remain important. SQL authentication is an identity mechanism and does not determine database backup behavior, encryption settings, or network routing.<\/span><\/p>\nQuestion 394. What is the role of the Microsoft Entra administrator for an Azure SQL logical server?<\/b><\/h3>\n
1)<\/b> It establishes an initial Microsoft Entra administrative identity for the server<\/span>
\n<\/span>2)<\/b> It automatically creates all database indexes<\/span>
\n<\/span>3)<\/b> It controls backup compression<\/span>
\n<\/span>4)<\/b> It configures storage redundancy for every database<\/span><\/p>\nAnswer: 1) It establishes an initial Microsoft Entra administrative identity for the server<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The Microsoft Entra administrator provides an identity that can administer Azure SQL resources using Microsoft Entra authentication. Configuring this administrator is an important step when organizations want to use Microsoft Entra-based access rather than relying only on SQL authentication. After authentication is established, database administrators can create appropriate Microsoft Entra users or principals and assign permissions according to organizational requirements. The Microsoft Entra administrator setting does not automatically manage indexes, backup compression, or storage redundancy. Those functions are handled through separate database-management and Azure configuration capabilities.<\/span><\/p>\nQuestion 395. Which identity is commonly used by an Azure-hosted application when passwordless access to Azure SQL is required?<\/b><\/h3>\n
1)<\/b> Managed identity<\/span>
\n<\/span>2)<\/b> FTP account<\/span>
\n<\/span>3)<\/b> Local printer account<\/span>
\n<\/span>4)<\/b> Anonymous user<\/span><\/p>\nAnswer: 1) Managed identity<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A managed identity provides an Azure-managed identity that applications can use to authenticate to supported Azure services without storing credentials such as passwords or client secrets in application configuration. For Azure SQL scenarios, a managed identity can be granted appropriate database permissions and used by an application to obtain an authentication token. This approach can reduce credential-management overhead and improve security when configured correctly. Administrators still need to create the appropriate database principal and permissions. Managed identities are different from FTP accounts, local system accounts, and anonymous access.<\/span><\/p>\nQuestion 396. Which Azure SQL networking feature provides a private IP address for accessing a database through an Azure virtual network?<\/b><\/h3>\n
1)<\/b> Private endpoint<\/span>
\n<\/span>2)<\/b> Public DNS record only<\/span>
\n<\/span>3)<\/b> Database role<\/span>
\n<\/span>4)<\/b> Query Store<\/span><\/p>\nAnswer: 1) Private endpoint<\/b><\/p>\n
Explanation:<\/b><\/p>\n
An Azure Private Endpoint provides a private network interface with a private IP address for accessing supported Azure services through an Azure virtual network. For Azure SQL Database, this can allow clients in connected networks to communicate with the database service without relying on a public endpoint for the database connection path. Private endpoints are commonly used with private DNS configuration so that the service hostname resolves appropriately within the private network. A database role controls permissions, Query Store manages query-performance history, and public DNS alone does not create a private network connection.<\/span><\/p>\nQuestion 397. What is the purpose of a private DNS zone when using an Azure SQL private endpoint?<\/b><\/h3>\n
1)<\/b> To resolve the database service name to the private endpoint address<\/span>
\n<\/span>2)<\/b> To create SQL Server logins<\/span>
\n<\/span>3)<\/b> To encrypt transaction logs<\/span>
\n<\/span>4)<\/b> To rebuild database indexes<\/span><\/p>\nAnswer: 1) To resolve the database service name to the private endpoint address<\/b><\/p>\n
Explanation:<\/b><\/p>\n
A private DNS zone can provide name resolution for resources accessed through private endpoints. When configured correctly, the database service hostname can resolve to the private IP address associated with the private endpoint rather than directing clients toward a public endpoint. This helps applications continue using the expected service hostname while network traffic follows the private connectivity path. DNS configuration is therefore an important part of private-endpoint deployments. Private DNS does not create database users, encrypt transaction logs, or perform index maintenance; those functions belong to separate database services and security mechanisms.<\/span><\/p>\nQuestion 398. What is a key difference between an Azure SQL private endpoint and a service endpoint?<\/b><\/h3>\n
1)<\/b> A private endpoint provides a private IP address for the Azure service connection<\/span>
\n<\/span>2)<\/b> A service endpoint creates a database user automatically<\/span>
\n<\/span>3)<\/b> A private endpoint disables authentication<\/span>
\n<\/span>4)<\/b> A service endpoint encrypts all database columns<\/span><\/p>\n