Explore topic-wise InterviewSolutions in .

This section includes InterviewSolutions, each offering curated multiple-choice questions to sharpen your knowledge and support exam preparation. Choose a topic below to get started.

51.

In _______ transaction we include the DML statements that need to be executed as a unit.(a) Explicit(b) Implicit(c) Distributed(d) All of the mentionedI got this question at a job interview.I need to ask this question from Transaction topic in section Performance Tuning and Optimization of SQL Server

Answer»

Right answer is (a) Explicit

To EXPLAIN I WOULD SAY: Generally we don’t include Select statement in a TRANSACTION.
Discussion
52.

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 mentionedThis question was posed to me by my school teacher while I was bunking the class.I want to ask this question from Query Plan Reuse topic in portion Performance Tuning and Optimization of SQL Server

Answer»

Right choice is (b) DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

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.
Discussion
53.

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 mentionedThis question was posed to me during a job interview.The doubt is from Query Plan Reuse topic in chapter Performance Tuning and Optimization of SQL Server

Answer» CORRECT ANSWER is (C) CHANGES made to a table or view REFERENCED by the query

The explanation is: ALTER TABLE and ALTER VIEW are used to made changes to the table.
Discussion
54.

The maximum size for all caches is a function of the ________ size.(a) RAM(b) Buffer pool(c) Hard disk(d) None of the mentionedThe question was asked during an internship interview.The above asked question is from Query Plan Reuse topic in division Performance Tuning and Optimization of SQL Server

Answer»

The correct option is (b) BUFFER pool

For explanation I WOULD say: The resource monitor and user threads can EXAMINE PLANS run concurrently to decrease the current cost for each UNUSED execution plan.
Discussion
55.

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/secThe question was asked in an interview for job.My question is based upon Query Plan Reuse topic in chapter Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT OPTION is (d) Auto-Param Attempts/sec

To explain: 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.
Discussion
56.

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 mentionedThe question was asked by my school principal while I was bunking the class.The doubt is from Query Plan Reuse topic in section Performance Tuning and Optimization of SQL Server

Answer»

Correct choice is (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

Best 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.
Discussion
57.

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 mentionedI got this question in an internship interview.I'd like to ask this question from Query Plan Reuse in section Performance Tuning and Optimization of SQL Server

Answer»

Right CHOICE is (d) All of the mentioned

For 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.
Discussion
58.

Which of the following index operations require no additional disk space?(a) ALTER INDEX(b) DROP INDEX(c) CREATE INDEX(d) All of the mentionedI had been asked this question by my college director while I was bunking the class.I'd like to ask this question from Indexing Strategies in chapter Performance Tuning and Optimization of SQL Server

Answer» RIGHT option is (d) All of the mentioned

The best I can explain: DROP INDEX does not require space when you are dropping a CLUSTERED index offline without SPECIFYING the MOVE TO CLAUSE and nonclustered INDEXES do not exist.
Discussion
59.

The SORT_IN_TEMPDB option cannot be set for ________ statements.(a) ALTER INDEX(b) DROP INDEX(c) CREATE INDEX(d) All of the mentionedThe question was posed to me in an online quiz.Enquiry is from Indexing Strategies topic in section Performance Tuning and Optimization of SQL Server

Answer»

The correct OPTION is (b) DROP INDEX

To explain I WOULD say: The temporary MAPPING index is always created in the same filegroup or PARTITION scheme as the target index.
Discussion
60.

Which of the following index operation require additional disk space?(a) UPDATE INDEX(b) DROP INDEX MOVE TO(c) ALTER INDEX ADD CONSTRAINT(d) All of the mentionedI got this question in semester exam.Enquiry is from Indexing Strategies in section Performance Tuning and Optimization of SQL Server

Answer»

Correct CHOICE is (b) DROP INDEX MOVE TO

Best explanation: DROP INDEX MOVE TO APPLIES only to clustered INDEXES.
Discussion
61.

Point out the wrong statement.(a) If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure(b) When you create a clustered index on a table that has nonclustered indexes, you must have available as free space(c) When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in master database(d) All of the mentionedI got this question by my college professor while I was bunking the class.Question is taken from Indexing Strategies topic in section Performance Tuning and Optimization of SQL Server

Answer» RIGHT choice is (a) If SORT_IN_TEMPDB is set to OFF, the free space in the DESTINATION filegroup must be large enough to store the final index STRUCTURE

The best I can explain: TEMPDB is used to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure.
Discussion
62.

Which of the following query can enable all indexes on Employee table?(a) DBCC DBREINDEX (“Employee”, ” “);(b) DBCC DBREINDEX (“EMP”, ” “);(c) DBCC DBREINDEX (“HumanResources.Employee”, “ALL”);(d) None of the mentionedI got this question by my college professor while I was bunking the class.The query is from Indexing Strategies in section Performance Tuning and Optimization of SQL Server

Answer»

The correct choice is (a) DBCC DBREINDEX (“EMPLOYEE”, ” “);

To ELABORATE: DBCC DBREINDEX can enable all INDEXES for a table in the SPECIFIED DATABASE.
Discussion
63.

Which of the following query enables the IX_Employee_OrganizationLevel_OrganizationNode index on the Employee table?(a) DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);(b) DBCC REINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);(c) DBCC DBINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);(d) All of the mentionedThis question was posed to me in examination.This is a very interesting question from Indexing Strategies topic in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct CHOICE is (a) DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);

The best I can EXPLAIN: DBCC DBREINDEX rebuilds one or more INDEXES for a table in the specified DATABASE.
Discussion
64.

By default, indexes are stored in the ______ filegroup as the base table on which the index is created.(a) same(b) different(c) may be same or different(d) none of the mentionedI got this question in class test.I need to ask this question from Indexing Strategies topic in chapter Performance Tuning and Optimization of SQL Server

Answer» RIGHT choice is (a) same

The explanation: A nonpartitioned CLUSTERED index and the BASE table ALWAYS RESIDE in the same filegroup.
Discussion
65.

Point out the wrong statement.(a) SQL Server supports only two index types: clustered and non-clustered(b) Appropriate indexes can make a world of difference in performance(c) SET option requires ALTER permission on the table or view(d) None of the mentionedThis question was posed to me in examination.My doubt stems from Indexing Strategies topic in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct OPTION is (a) SQL SERVER SUPPORTS only TWO index types: clustered and non-clustered

The explanation is: There are plenty of indexes in SQL Server such as full text, columnstore index in SQL Server 2012
Discussion
66.

Which of the following guideline is considered during column design?(a) Keep the length of the index key short for non clustered indexes(b) An xml data type can only be a key column only in an XML index(c) Examine column which have composite primary keys(d) None of the mentionedI got this question in an internship interview.My doubt stems from Indexing Strategies topic in division Performance Tuning and Optimization of SQL Server

Answer»

The correct answer is (B) An xml data type can only be a key column only in an XML index

The explanation is: QL Server 2012 SP1 INTRODUCES a new type of XML index known as a Selective XML Index. This new index can improve QUERYING performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads.
Discussion
67.

The way that a statement can be physically executed is called _____________(a) query plan(b) execution plan(c) query execution plan(d) all of the mentionedThe question was posed to me during an interview.The doubt is from Query Execution Plan in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct option is (d) all of the mentioned

To explain I would say: An execution plan is COMPOSED of primitive operations. EXAMPLES of primitive operations are: reading a table COMPLETELY, using an index, performing a nested LOOP or a hash join.
Discussion
68.

__________returns detailed information about how the statements are executed.(a) SET STATISTICS XML(b) SET SHOWPLAN_TEXT(c) SET STATISTICS PROFILE(d) All of the mentionedThe question was posed to me by my school principal while I was bunking the class.This interesting question is from Query Execution Plan topic in section Performance Tuning and Optimization of SQL Server

Answer»

The correct ANSWER is (B) SET SHOWPLAN_TEXT

For explanation: SET SHOWPLAN_TEXT CAUSES MICROSOFT SQL Server not to execute Transact-SQL STATEMENTS.
Discussion
69.

Which of the following is valid syntax for SET STATISTICS XML?(a) SET STATISTICS XML { OFF }(b) SET STATISTICS XML { ON | OFF }(c) SET STATISTICS XML { ON }(d) All of the mentionedI had been asked this question in an interview for internship.I'm obligated to ask this question of Query Execution Plan in section Performance Tuning and Optimization of SQL Server

Answer»

The CORRECT ANSWER is (d) All of the mentioned

The EXPLANATION is: SET STATISTICS XML causes MICROSOFT SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML DOCUMENT.
Discussion
70.

The document containing the XML schema for the XML output by SET STATISTICS XML is located at ______________(a) \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xml(b) \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsl(c) \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsd(d) None of the mentionedThe question was asked during an internship interview.My query is from Query Execution Plan in portion Performance Tuning and Optimization of SQL Server

Answer»

Correct answer is (c) \Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml. xsd

Explanation: SET STATISTICS XML produces XML OUTPUT in the form of SCHEMA.
Discussion
71.

Point out the wrong statement.(a) SET SHOWPLAN_XML can be specified inside a stored procedure(b) SET SHOWPLAN_XML returns information as a set of XML documents(c) SET SHOWPLAN_XML is intended to return output as nvarchar(max) for applications such as the sqlcmd utility(d) All of the mentionedThe question was asked in final exam.My question is based upon Query Execution Plan in section Performance Tuning and Optimization of SQL Server

Answer» CORRECT choice is (a) SET SHOWPLAN_XML can be specified inside a STORED procedure

For explanation I WOULD say: It MUST be the only statement in a BATCH.
Discussion
72.

To produce execution plan output by using most Showplan Transact-SQL SET options, users must have _________(a) SHOW SHOWPLAN(b) SHOW PLAN(c) SHOWPLAN(d) None of the mentionedThis question was addressed to me during an online interview.Query is from Query Execution Plan topic in chapter Performance Tuning and Optimization of SQL Server

Answer»

The correct CHOICE is (C) SHOWPLAN

Best explanation: The SHOWPLAN permission on the databases that contain OBJECTS REFERRED to in the Transact-SQL statement, such as views, stored procedures, or user-defined functions.
Discussion
73.

___________ displays the profile information for a statement.(a) SET STATISTICS(b) SET STATS PROFILE(c) SET STATISTICS PROFILE(d) All of the mentionedI have been asked this question in a national level competition.I would like to ask this question from Query Execution Plan topic in section Performance Tuning and Optimization of SQL Server

Answer»

Correct OPTION is (c) SET STATISTICS PROFILE

Easy explanation: STATISTICS PROFILE WORKS for ad hoc queries, VIEWS, and stored procedures.
Discussion
74.

Which of the following syntax is valid?(a) SET SHOWPLAN_XML { ON | OFF }(b) SET SHOWPLAN { ON | OFF }(c) SET SHOW_XML { ON | OFF }(d) None of the mentionedI got this question in unit test.This intriguing question comes from Query Execution Plan in section Performance Tuning and Optimization of SQL Server

Answer»

Right answer is (a) SET SHOWPLAN_XML { ON | OFF }

To ELABORATE: SET SHOWPLAN_XML causes SQL Server not to EXECUTE Transact-SQL statements.
Discussion
75.

Point out the correct statement.(a) SET SHOWPLAN_TEXT causes Microsoft SQL Server to execute Transact-SQL statements(b) The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time(c) The setting of SET SHOWPLAN_XML is set at execute or run time and not at parse time(d) None of the mentionedThe question was posed to me by my school principal while I was bunking the class.The origin of the question is Query Execution Plan in portion Performance Tuning and Optimization of SQL Server

Answer»

Correct option is (c) The setting of SET SHOWPLAN_XML is set at EXECUTE or run time and not at parse time

To explain: When SET SHOWPLAN_XML is ON, SQL SERVER returns execution plan INFORMATION for each STATEMENT WITHOUT executing it.
Discussion