최신 070-762 무료덤프 - Microsoft Developing SQL Databases
You have multiple stored procedures inside a transaction.
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?
정답: F
설명: (DumpTOP 회원만 볼 수 있음)
You have a data warehouse fact table that has a clustered columnstore index.
You have multiple CSV files that contain a total of 3 million rows of data.
You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.
Which solution will achieve the goal in the least amount of time?
You have multiple CSV files that contain a total of 3 million rows of data.
You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.
Which solution will achieve the goal in the least amount of time?
정답: B
설명: (DumpTOP 회원만 볼 수 있음)
Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:
You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size.
Which Transact-SQL statement should you run?
You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size.
Which Transact-SQL statement should you run?
정답: A
설명: (DumpTOP 회원만 볼 수 있음)
You are optimizing the performance of a batch update process. You have tables and indexes that were created by running the following Transact-SQL statements:
The following query runs nightly to update the isCreditValidated field:
You review the database and make the following observations:
* Most of the IsCreditValidated values in the Invoices table are set to a value of 1.
* There are many unique InvoiceDate values.
* The CreditValidation table does not have an index.
* Statistics for the index IX_invoices_CustomerID_Filter_IsCreditValidated indicate there are no individual seeks but multiple individual updates.
You need to ensure that any indexes added can be used by the update query. If the IX_invoices_CustomerId_Filter_IsCreditValidated index cannot be used by the query, it must be removed.
Otherwise, the query must be modified to use with the index.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
The following query runs nightly to update the isCreditValidated field:
You review the database and make the following observations:
* Most of the IsCreditValidated values in the Invoices table are set to a value of 1.
* There are many unique InvoiceDate values.
* The CreditValidation table does not have an index.
* Statistics for the index IX_invoices_CustomerID_Filter_IsCreditValidated indicate there are no individual seeks but multiple individual updates.
You need to ensure that any indexes added can be used by the update query. If the IX_invoices_CustomerId_Filter_IsCreditValidated index cannot be used by the query, it must be removed.
Otherwise, the query must be modified to use with the index.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
정답: A,B,C
설명: (DumpTOP 회원만 볼 수 있음)
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_operational_stats dynamic management view.
Does the solution meet the goal?
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_operational_stats dynamic management view.
Does the solution meet the goal?
정답: A
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a raid array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to examine delays in executed threads, including errors with specific queries and batches.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a raid array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to examine delays in executed threads, including errors with specific queries and batches.
정답: B
Background
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:
Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
- UspA: This stored procedure updates only the EmployeeStatus column.
- UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
- Allow user access to all columns in the tables that the view accesses.
- Restrict updates to only the rows that the view returns.
Exhibit
You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.
You need to generate the reports more quickly. The database must not use additional resources.
What should you do?
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:
Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
- UspA: This stored procedure updates only the EmployeeStatus column.
- UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
- Allow user access to all columns in the tables that the view accesses.
- Restrict updates to only the rows that the view returns.
Exhibit
You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.
You need to generate the reports more quickly. The database must not use additional resources.
What should you do?
정답: B
설명: (DumpTOP 회원만 볼 수 있음)
Note: this question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create an object that meets to following requirements:
* Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
* Run written a transaction and roll back if a failure occurs.
* Run when a table is created or modified.
What should you create?
You are developing an application to track customer sales.
You need to create an object that meets to following requirements:
* Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
* Run written a transaction and roll back if a failure occurs.
* Run when a table is created or modified.
What should you create?
정답: E
설명: (DumpTOP 회원만 볼 수 있음)
You have a Microsoft Azure SQL Database named MyDb that uses server version V12.
You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled.
You need to enable the database query store to meet the following requirements for the database:
- Statistics must be aggregated every 15 minutes.
- Query stores must use no more than 1,024 megabytes (MB) of storage.
- Query information must be retained for at least 15 days.
- Queries must be captured based on resource consumption.
You connect to the database by using SQL Server Managements Studio.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select. Each correct selection is worth one point.
You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled.
You need to enable the database query store to meet the following requirements for the database:
- Statistics must be aggregated every 15 minutes.
- Query stores must use no more than 1,024 megabytes (MB) of storage.
- Query information must be retained for at least 15 days.
- Queries must be captured based on resource consumption.
You connect to the database by using SQL Server Managements Studio.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select. Each correct selection is worth one point.
정답:
Explanation
or
Both answers are correct.
References: https://msdn.microsoft.com/en-us/library/mt604821.aspx
Background
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:
Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
* UspA: This stored procedure updates only the EmployeeStatus column.
* UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
* Allow user access to all columns in the tables that the view accesses.
* Restrict updates to only the rows that the view returns.
Exhibit
You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.
You need to generate the reports more quickly. The database must not use additional resources.
What should you do?
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:
Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
* UspA: This stored procedure updates only the EmployeeStatus column.
* UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
* Allow user access to all columns in the tables that the view accesses.
* Restrict updates to only the rows that the view returns.
Exhibit
You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.
You need to generate the reports more quickly. The database must not use additional resources.
What should you do?
정답: C
설명: (DumpTOP 회원만 볼 수 있음)