gs gs116 Performance Tuning and Optimization - Query Plan Reuse - Quiz No.1
gs gs116 Microsoft SQL Server Quiz
This quiz belongs to book/course code gs gs116 Microsoft SQL Server of gs organization. We have 100 quizzes available related to the book/course Microsoft SQL Server. This quiz has a total of 10 multiple choice questions (MCQs) to prepare and belongs to topic Performance Tuning and Optimization. NVAEducation wants its users to help them learn in an easy way. For that purpose, you are free to prepare online MCQs and quizzes.
NVAEducation also facilitates users to contribute in online competitions with other students to make a challenging situation to learn in a creative way. You can create one to one, and group competition on an topic of a book/course code. Also on NVAEducation you can get certifications by passing the online quiz test.
Question 1: The use of parameters, including parameter markers in _______ applications, can increase the reuse of execution plans.
OLE DB
ADO
ODBC
All of the mentioned
Question 2: Point out the correct statement.
JDBC use parameter markers
Parameter markers are question marks (?) that replace a constant in an SQL statement
When forced parameterization is tried but fails, simple parameterization is still subsequently tried
All of the mentioned
Question 3: Use to bind an integer variable to the first parameter marker in an SQL statement.
SQLParameter
SQLBind
SQLBindParameter
All of the mentioned
Question 4: Which of the following query cannot be parameterized according to the rules of forced parameterization?
SELECT * FROM Person.Address WHERE AddressID = 1 + 2;SELECT * FROM Person.Address WHERE AddressID = 1;SELECT * FROM Person.Address WHERE AddressID = 2;
All of the mentioned
Question 5: Point out the wrong statement.
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
Execution plans remain in the procedure cache as long as there is enough memory to store them
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
All of the mentioned
Question 6: Which of the following SQL Server SQL Statistics counter represents number of auto-parameterization attempts per second?
Batch Requests/sec
Failed Auto-Params/sec
Forced parameterizations/sec
Auto-Param Attempts/sec
Question 7: The maximum size for all caches is a function of the ________ size.
RAM
Buffer pool
Hard disk
None of the mentioned
Question 8: The conditions that invalidate a plan include which of the following?
An explicit call to sp_compile
Executing a stored procedure using the WITH COMPILE option
Changes made to a table or view referenced by the query
All of the mentioned
Question 9: What is the syntax for DBCC FREEPROCCACHE with all the optional parameters in T-SQL?
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ]
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]
DBCC FREEPROCCACHE [ ( { sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]
None of the mentioned
Question 10: _________ uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.
pool_name
plan_handle
sql_handle
none of the mentioned
Online Quizzes of gs116 Microsoft SQL Server
Developing with SQL Server - Error Handling - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
Laying the Foundation - Aggregation of Data - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
Laying the Foundation - Joins in SQL Server - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
Laying the Foundation - Views in SQL Server - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
