Home » Microsoft » 70-765 » Which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30 stored procedures that are regularly used by the application.
HOTSPOT
Background
You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile parts.
The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application has approximately 30,000 concurrent users and relies heavily on the use of temporary tables.
The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is protected with Transparent Data Encryption (TDE).
You plan to migrate this database to a new server. You detach the database and copy it to the new server.
You are performing tuning on a SQL Server database instance. The application which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30 stored procedures that are regularly used by the application.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button).
Exhibit:
You need to resolve the identified issues.
Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.
Hot Area:
Correct Answer:
Explanation/Reference:
Explanation:
From exhibit we see:
Cost Threshold of Parallelism: 5
Optimize for Ad Hoc Workloads: false
Max Degree of Parallelism: 0 (This is the default setting, which enables the server to determine the maximum degree of parallelism. It is fine.) Locks: 0
Query Wait: -1
Box 1: Optimize for Ad Hoc Workload
Change the Optimize for Ad Hoc Workload setting from false to 1/True.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
Incorrect Answers:
Not Query Wait: Use the query wait option to specify the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out.
Box 2: Cost Threshold for Parallelism to 50
Increase the Cost Threshold for Parallelism from 5 to 50.
Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism.
Note: Longer queries usually benefit from parallel plans; the performance advantage negates the additional time required to initialize, synchronize, and terminate parallel plans. The cost threshold for parallelism option is actively used when a mix of short and longer queries is run. The short queries run serial plans, whereas the longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans.
References: https://technet.microsoft.com/en-us/library/ms188603(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/cc645587.aspx