SQL Server Questions and Answers – Query Plan Reuse

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Query Plan Reuse”.

1. The use of parameters, including parameter markers in _______ applications, can increase the reuse of execution plans.
a) OLE DB
b) ADO
c) ODBC
d) All of the mentioned
View Answer

Answer: d
Explanation: Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string.

2. Point out the correct statement.
a) JDBC use parameter markers
b) Parameter markers are question marks (?) that replace a constant in an SQL statement
c) When forced parameterization is tried but fails, simple parameterization is still subsequently tried
d) All of the mentioned
View Answer

Answer: b
Explanation: Parameter markers are bound to a variable

3. Use to bind an integer variable to the first parameter marker in an SQL statement.
a) SQLParameter
b) SQLBind
c) SQLBindParameter
d) All of the mentioned
View Answer

Answer: c
Explanation: SQLBindParameter is used in ODBC applications

4. Which of the following query cannot be parameterized according to the rules of forced parameterization?
a)

advertisement
advertisement
SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

b)

Note: Join free Sanfoundry classes at Telegram or Youtube
SELECT * FROM Person.Address
WHERE AddressID = 1;

c)

advertisement
SELECT * FROM Person.Address
WHERE AddressID = 2;

d) All of the mentioned
View Answer

Answer: a
Explanation: When Forced parameterization is enabled, Simple parameterization can still occur.

advertisement

5. Point out the wrong statement.
a) MisGuided Plan Executions/sec represents the number of plan executions per second in which the query plan has been generated by using a plan guide
b) Execution plans remain in the procedure cache as long as there is enough memory to store them
c) When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost
d) All of the mentioned
View Answer

Answer: a
Explanation: Guided Plan Executions/sec represents number of plan executions per second in which the query plan has been generated by using a plan guide.

6. Which of the following SQL Server SQL Statistics counter represents number of auto-parameterization attempts per second?
a) Batch Requests/sec
b) Failed Auto-Params/sec
c) Forced parameterizations/sec
d) Auto-Param Attempts/sec
View Answer

Answer: d
Explanation: Auto-parameterization occurs when an instance of SQL Server tries to parametrize a Transact-SQL request by replacing some literals with parameters so that reuse of the resulting cached execution plan across multiple similar-looking requests is possible.

7. The maximum size for all caches is a function of the ________ size.
a) RAM
b) Buffer pool
c) Hard disk
d) None of the mentioned
View Answer

Answer: b
Explanation: The resource monitor and user threads can examine plans run concurrently to decrease the current cost for each unused execution plan.

8. The conditions that invalidate a plan include which of the following?
a) An explicit call to sp_compile
b) Executing a stored procedure using the WITH COMPILE option
c) Changes made to a table or view referenced by the query
d) All of the mentioned
View Answer

Answer: c
Explanation: ALTER TABLE and ALTER VIEW are used to made changes to the table.

9. What is the syntax for DBCC FREEPROCCACHE with all the optional parameters in T-SQL?
a) DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ]
b) DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]
c) DBCC FREEPROCCACHE [ ( { sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]
d) None of the mentioned
View Answer

Answer: b
Explanation: DBCC FREEPROCCACHE removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

10. _________ uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.
a) pool_name
b) plan_handle
c) sql_handle
d) none of the mentioned
View Answer

Answer: b
Explanation: plan_handle is varbinary(64) and can be obtained from the various dynamic management objects.

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.