- (Exam Topic 7)
You need to modify the stored procedure usp_LookupConcurrentUsers. What should you do?
Correct Answer:
A
- (Exam Topic 8)
You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary. You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?
Correct Answer:
C
http://msdn.microsoft.com/en-us/library/ms177459.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx
- (Exam Topic 7)
You need to redesign the system to meet the scalability requirements of the application. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Solution:
Box 1:
< ><>>< ><>>< ><>>< ><>>< ><>>< ><>><>>* >>* >>* >-- >-- >>>>)
Does this meet the goal?
Correct Answer:
A
- (Exam Topic 8)
You have a SQL Server 2012 database named database1.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to
execute.
You need to view the server resource consumption when the queries are executed. What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Solution:
Box 1: Start a SQL Server Profiler trace.
Box 2: Start a data collection by using Performance monitor. Box 3: Save the SQL Profiler trace.
Box 4: Save the Performance Monitor data.
Box 5: Import the performance data into SQL Server Profiler. Note:
* (step1, step 2) Both the Profiler trace and the Performance Monitor logs should be started and stopped at
about the same time.
* (step 3, step 4) Once you have completed capturing the data for both tools, you are ready to perform the correlation analysis.
* (step 5) How to Correlate SQL Server Profiler Data with Performance Monitor Data
Correlating Performance Monitor and Profiler data is a straightforward process that simply involves importing both sets of data into Profiler. Start Profiler and load the trace file you want to correlate.
From the main menu of Profiler, select File | Import Performance Data,
* With SQL Server Profiler, we have the tools to identify the causes of such spikes. We can import Performance Monitor log data and compare it directly with Profiler activity. If we see a spike in CPU utilization, we can identify which statement or statements were running at the same time, and diagnose potential problems.
Does this meet the goal?
Correct Answer:
A
- (Exam Topic 8)
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:
< ><>>>>< >
Correct Answer:
A
* You must specify a value for the BUCKET_COUNT parameter when you create the memory-optimized table. In most cases the bucket count should be between 1 and 2 times the number of distinct values in the index key. If the index key contains a lot of duplicate values, on average there are more than 10 rows for each index key value, use a nonclustered index instead
You may not always be able to predict how many values a particular index key may have or will have. Performance should be acceptable if the BUCKET_COUNT value is within 5 times of the actual number of
key values.
