Microsoft Provisioning SQL Databases Set 3

You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders. Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmented. You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation. Which Transact-SQL command should you use?

You administer a Microsoft SQL Server 2012 database. The database is currently configured to log ship to a secondary server. You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup. You need to leave the primary database in a restoring state. Which option of the BACKUP LOG command should you use?

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server. What should you create?

You administer a Microsoft SQL Server 2012 database. You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges. You need to ensure that the job step can run using a different user account. What should you use?

You have Microsoft SQL Server on a Microsoft Azure virtual machine. 885CB989129A5F974833949052CFB2F2 You suspect that the current SQL Server indexes cause queries to execute slowly. You need to identify which indexes must be created to reduce the query execution time. Which three dynamic management views should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function. You need to ensure that the SQL Server does not limit the performance of App1. What configuration option should you set?

You plan to create an AlwaysOn availability group that will have two replicas in Microsoft Azure and two on-premises replicas. You need to configure the network to support the availability group listener. Which cmdlet should you run first?

You have a database named DB1. You discover that DB1 is corrupt. You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message. You need to repair the database corruption as quickly as possible. The solution must minimize data loss. What should you do?

Users report that a query takes a long time to execute. The query has the following wait statistics. Which resource causes the issue?

You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure. From SQL Server Management Studio (SSMS), a junior database administrator accidentally deletes several rows from the Azure SQL database and breaks the connection to Azure. You need to resume Stretch Database operations. Which two stored procedures should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.