Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 2

Question - 15 Maximum mark : - 27

Home
All Exams
Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Online Mock Tests
Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 2

You need to optimize the indexing strategy for the Parts database. You must ensure that index maintenance is minimized. What should you do?

Create clustered indexes on all tables that are queried by SELECT statements that use the ORDER BY clause. Create the indexes on all columns that are included in any ORDER BY clauses. ✘ Create indexes on all primary key constraint fields that are used in the common queries. ✘ Create indexes on all foreign key constraint fields that are used in the common queries. ✘ Add nonclustered indexes to cover all queries that are issued against the database. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to find out what is causing the slow response times to the Contractor database. Because the hardware on SQL3 should be sufficient for the Contractor database and because regular database maintenance is performed, you want to investigate what else could be causing the problem. Which tool should you use?

SQL Server Profiler ✘ System Monitor ✘ Network Monitor ✘ Database Engine Tuning Advisor ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You notice that some of the performance problems on the Parts database are due to a high number of pages being read for many of the most common queries. You need to find out the optimal sizes for the part number and code columns in the tables that are used in the most common queries. What should you do?

For each table and part number or code column in the Parts database, run the following SQL statement: SELECT MIN(ColumnName) FROM TableName WHERE TableName is the actual table name and ColumnName is the actual column name. ✘ For each table and part number or code column in the Parts database, run the following SQL statement:SELECT COUNT(ColumnName) FROM TableName WHERE TableName is the actual table name and ColumnName is the actual column name. ✘ For each table and part number or code column in the Parts database, run the following SQL statement:SELECT MAX(ColumnName) FROM TableName WHERE TableName is the actual table name and ColumnName is the actual column name. ✘ For each table and part number or code column in the Parts database, run the following SQL statement:SELECT MAX(LEN(ColumnName)) FROM TableName WHERE TableName is the actual table name and ColumnName is the actual column name. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to develop a strategy that will add all required data to the EngLogging table. You must ensure that only required data is added to the EngLogging table. What should you do?

Use the DDL_DATABASE_LEVEL_EVENTS event group, specifying ALL SERVER as the scope. ✘ Use the DDL_DATABASE_LEVEL_EVENTS event group, specifying DATABASE as the scope. ✘ Set up event notifications for the DDL_TABLE_VIEW_EVENTS event group, specifying DATABASE as thescope. ✘ Use the DDL_AUTHORIZATION_SERVER_EVENTS event group, specifying ALL SERVER as the scope. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are investigating the performance problems on the Parts database. You need to find out whether inefficient queries are part of these problems. You need to troubleshoot the longest-running queries first. What should you do to find these queries?

Use the Windows System Monitor to examine the SQLServer:Transactions object. ✘ Use the Windows System Monitor to examine the SQLServer:Wait Statistics object. ✘ Use SQL Server Profiler. Use the most recent existing trace for SQL2 and change the trace properties togroup by application. ✘ Use SQL Server Profiler. Use the most recent existing trace for SQL2 and change the trace properties togroup by duration. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to resolve the problems with the routine named Routine112. What should you do?

Use a separate connection for each update. ✘ Open a multiple active result sets (MARS) connection, use it for both the reader and the updates, and issuethe updates on the same thread as the loop. ✘ Disable connection pooling. ✘ Implement connection pooling. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to optimize the query against the ReservationRequests table to fulfill the technical requirements. What should you do?

Add the READPAST locking hint to the query. ✘ Add the READUNCOMMITTED locking hint to the query. ✘ Add the UPDLOCK locking hint to the query. ✘ Set the transaction isolation level to SNAPSHOT. ✔ Set the transaction isolation level to REPEATABLE READ. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to implement a solution to the transactions that add and modify rows based on the CustomerID column to fulfill the technical requirements. What should you do?

Implement the repeatable read transaction isolation level. ✘ Implement the snapshot transaction isolation level. ✘ Implement the serializable transaction isolation level. ✘ Implement the read committed transaction isolation level. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to allow the new version of the application to run from the remote offices. All SQL Server 2005 features must also be available through this connection. What should you do?

Use the System.Data.OleDb namespace to connect to SQL Server. ✘ Use the System.Data.SqlClient namespace to connect to SQL Server. ✘ Create an HTTP endpoint on SQL Server and configure the client computers to connect to the endpoint. ✘ Create a Web service on another computer, and configure this computer to connect to SQL Server.Configure the client computers to connect to the Web service. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

You need to design the retrieval of the airfares for the new version of the application to fulfill the business requirements. What should you do?

Design a routine for the client computers that refreshes the information every five minutes. ✘ Design a routine for the client computers that refreshes the information every 10 seconds. ✘ Use the SqlDependency.OnChanged event on the client computers to notify them when the informationchanges. Then, if the most recent refresh was more than five minutes ago, automatically refresh the data. ✘ Use the SqlDependency.OnChanged event on the client computers to notify them when the informationchanges. Then, automatically refresh the data. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 2

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner