70-764 Administering a SQL Database Infrastructure Practice Questions

Your company’s environment includes on-premises Microsoft SQL Server as well as Microsoft Azure SQL Database instances. A number of client databases are hosted in this environment.

A client has complained that query completion in their on-premises instance takes longer than necessary.

You are required to make sure that the query optimizer is able to use the optimal query execution plans available by reconfiguring table statistics.

Solution: You make use of the sys.index_columns Transact-SQL segment.

Does the solution meet the goal?

Your company has a Microsoft SQL Server 2016 database hosted on a server running Windows Server 2012.

You have been tasked with troubleshooting database query wait statistics. To achieve this you need to gather information over a cycle. Your solution should make sure that the effect on the server is as little as possible.

Solution: You create a SQL Profiler Trace.

Does the solution meet the goal?

Databases in your company’s Microsoft Azure SQL Server environment are directly stored in Azure blob storage. Currently, the backup processes in use are complicated.

You have ben tasked with making sure that databases can be restored to a particular point between backups. The process should keep the necessary amount of Azure storage containers to a minimum.

Solution: You make use of the backup compression option.

Does the solution meet the goal?

Your company’s environment includes on-premises Microsoft SQL Server as well as Microsoft Azure SQL Database instances. A number of client databases are hosted in this environment.

A client has complained that query completion in their on-premises instance takes longer than necessary.

You are required to make sure that the query optimizer is able to use the optimal query execution plans available by reconfiguring table statistics.

Solution: You make use of the UPDATE STATISTICS Transact-SQL segment.

Does the solution meet the goal?