Da Notes - 70-029 - P

Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender.

Passing Score: 693, Questions: 49,  Time: 150 Min.

Da Notes Index 

[ 70-029 - A ] [ 70-029 - B ] [ 70-029 - C ] [ 70-029 - D ] [ 70-029 - E ] [ 70-029 - F ] [ 70-029 - H ] [ 70-029 - I ] [ 70-029 - J ] [ 70-029 - K ] [ 70-029 - L ] [ 70-029 - M ] [ 70-029 - N ] [ 70-029 - O ] [ 70-029 - P ] [ 70-029 - Q ] [ 70-029 - R ] [ 70-029 - S ] [ 70-029 - T ] [ 70-029 - U ] [ 70-029 - V ] [ 70-029 - W ]

Partitioning

Horizontal should be used when you have a narrow table with many rows

Vertical should be used when you have a table with a lot of columns, but only a few are query on a regular basis

Permissions 

Review books on-line (will need for the administration test in detail)

PRIMARY KEY 

Try picking a single column for the primary key to uniquely identify the row.

Enforces entity integrity by uniquely identifying each row in a table. Used to relate the table to foreign keys in other tables. All columns in a primary key must contain defined values other than NULL.

Profiler 

Wizard reports by importance

Trace transact SQL by application - event classes of the TSQL event category should be monitored.

SQL: Batch starting and Batch ending show info on SQL batches.

ROC: starting and ending shows info on remote procedure calls.

SP: cache hit and recompile indicates the execution plan of a stored proc.

Object: opened and closed determine the use of database objects.

Lock: deadlock and deadlock chain are used to determine deadlocks.

Find the worst performing queries

Identify scans of large tables - solve specific problems

Identify cause of a deadlock- solve specific problems

Profile the performance of a stored procedure - solve one specific problem

Trace transact SQL by user - audit user security