QUESTION NO: 1
You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Correct Answer:
QUESTION NO: 2
You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
Correct Answer:
Explanation:
Box 1: sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats shows you how many times the index was used for user queries. It returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.
Box 2: sys.dm_db_missing_index_details
sys.dm_db_missing_index_details returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql
https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx
QUESTION NO: 3
You need to implement a solution that meets the data recovery requirements.
You update each stored procedure to accept a parameter named @transactionID.
What should you add next to the beginning of each stored procedure?
You need to implement a solution that meets the data recovery requirements.
You update each stored procedure to accept a parameter named @transactionID.
What should you add next to the beginning of each stored procedure?
Correct Answer: D
Vote an answer
QUESTION NO: 4
You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?
You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?
Correct Answer: D
Vote an answer
QUESTION NO: 5
You have an application that queries a database.
Users report that the application runs more slowly than expected.
You need to identify which queries take the most time to execute. The solution must minimize the use of processor resources on the server.
What should you use?
You have an application that queries a database.
Users report that the application runs more slowly than expected.
You need to identify which queries take the most time to execute. The solution must minimize the use of processor resources on the server.
What should you use?
Correct Answer: A
Vote an answer
QUESTION NO: 6
You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a slow local disk.
You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores.
The stored procedure will use temporary storage defined as follows:
The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data.
For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a slow local disk.
You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores.
The stored procedure will use temporary storage defined as follows:
The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data.
For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: C
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 7
You have a Microsoft SQL Azure database.
You have the following stored procedure:
You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?
You have a Microsoft SQL Azure database.
You have the following stored procedure:
You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?
Correct Answer: B
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 8
You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql.
Which code segment should you use to complete the function?
You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql.
Which code segment should you use to complete the function?
Correct Answer: C
Vote an answer
QUESTION NO: 9
You use SQL Server 2012 to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?
You use SQL Server 2012 to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?
Correct Answer: C
Vote an answer
QUESTION NO: 10
You need to create a function that will use a SELECT statement in ProductsByProductType.sql.
Which code segment should you use to complete the function?
You need to create a function that will use a SELECT statement in ProductsByProductType.sql.
Which code segment should you use to complete the function?
Correct Answer: B
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 11
You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
* Permit insertions into a table variable.
* Support structured exception handling.
* Prevent changes to the definition of referenced objects.
* Support the use of the APPLY operator on the output of the object.
Which type of object should you use?
You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
* Permit insertions into a table variable.
* Support structured exception handling.
* Prevent changes to the definition of referenced objects.
* Support the use of the APPLY operator on the output of the object.
Which type of object should you use?
Correct Answer: B
Vote an answer
QUESTION NO: 12
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
* Both processes must never use more than 50 percent of the CPU at any one time.
* The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
* The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
* Both processes must never use more than 50 percent of the CPU at any one time.
* The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
* The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Correct Answer:
Explanation:
Note:
CREATE WORKLOAD RESOURCE POOL
* Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.
* Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool.
* CAP_CPU_PERCENT =value
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to be the same as the specified value. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
* MIN_IOPS_PER_VOLUME =value
Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the resource pool.
* GROUP_MAX_REQUESTS =value
Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. value must be a 0 or a positive integer.
QUESTION NO: 13
You need to prepare the database to use the .NET Framework ProcessProducts component.
Which code segments should you execute? (Each correct answer presents part of the solution. Choose all that apply.)
You need to prepare the database to use the .NET Framework ProcessProducts component.
Which code segments should you execute? (Each correct answer presents part of the solution. Choose all that apply.)
Correct Answer: C,E,F,G
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 14
You are designing two stored procedures named Procedure1 and Procedure2.
You identify the following requirements:
* Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure.
* Procedure2 must use business logic that resides in a Microsoft .NET Framework assembly.
You need to identify the appropriate technology for each stored procedure.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)
You are designing two stored procedures named Procedure1 and Procedure2.
You identify the following requirements:
* Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure.
* Procedure2 must use business logic that resides in a Microsoft .NET Framework assembly.
You need to identify the appropriate technology for each stored procedure.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)
Correct Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms131102.aspx
http://msdn.microsoft.com/en-us/library/bb522446.aspx
http://msdn.microsoft.com/en-us/library/bb510489.aspx