Microsoft-Developing SQL Databases (2) Set 1

You have a database that is experiencing deadlock issues when users run queries. You need to ensure that all deadlocks are recorded in XML format. What should you do?

You are developing an application that connects to a database. The application runs the following jobs: The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined. If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete. You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA. Which trace event class in the Locks event category should you use?

You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues: At each level of the query plan, a low total number of rows are processed. The query uses many operations. This results in a high overall cost for the query. You need to identify the information that will be useful for the optimizer. What should you do?

You are experiencing performance issues with the database server. You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?

You have a view that includes an aggregate. You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses. You need to ensure that you can update the view. What should you create?

You are experiencing performance issues with the database server. You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems. What should you create?

You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1. The environment is configured with the following properties: The database is configured with the default isolation setting. The application and process use the default transaction handling. You observe the application cannot access any rows that are in use by the process. You have the following requirements: Ensure the application is not blocked by the process. Ensure the application has a consistent view of the data Ensure the application does not read dirty data. You need to resolve the issue and meet the requirements with the least amount of administrative effort. What should you do?

You run the following Transact-SQL following statement: Customer records may be inserted individually or in bulk from an application. You observe that the application attempts to insert duplicate records. You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications. Which Transact-SQL statement should you run?

You suspect deadlocks on a database. Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

You have the following stored procedure that is called by other stored procedures and applications: You need to modify the stored procedure to meet the following requirements: Always return a value to the caller. Return 0 if @Status is NULL. Callers must be able to use @Status as a variable. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.