Mcts 70-432 Part 3

Front 1

What are the items that you can capture data for with System Monitor?

Back 1

objects, counters and counter instances.

Front 2

What types of data can system monitor capture?

Back 2

numeric data for performance counters that are defined for hardware or software components.

Front 3

What are the three counters that, by themselves, indicate a system problem?

Back 3

System:Processor Queue Length, Network Interface:Output Queue Length, and Physical Disk:Avg. Disk Queue Length.

Front 4

What is the purpose of the Blank Profiler Trace Template?

Back 4

An empty trace; allows you to create an entire trace from scratch.

Front 5

What is the purpose of the SP_Counts Profiler Trace Template?

Back 5

Captures each stored procedure executed so that you can determine
how many of each procedure is being executed.

Front 6

What is the purpose of the Standard Profiler Trace Template?

Back 6

The most common template to start with; captures stored procedure and ad hoc SQL being executed along with performance statistics for each procedure and batch. Every login and logout is also captured.

Front 7

What is the purpose of the TSQL Profiler Trace Template?

Back 7

Captures a list of all the stored procedures and ad hoc SQL batches that are executed, but does not include any performance statistics.

Front 8

What is the purpose of the TSQL_Duration Profiler Trace Template?

Back 8

Captures the duration of every stored procedure and ad hoc SQL batch that is executed.

Front 9

What is the purpose of the TSQL_Grouped Profiler Trace Template?

Back 9

Captures every login and logout along with the stored procedures and ad hoc SQL batches that are executed. Includes information to identify the application and user executing the request, but does not include any performance data.

Front 10

What is the purpose of the TSQL_Locks Profiler Trace Template?

Back 10

Captures blocking and deadlock information such as blocked processes, deadlock chains, deadlock graphs, lock escalation, and lock timeouts. This template also captures every stored procedure, each command within a stored procedure, and every ad hoc SQL request.

Front 11

What is the purpose of the TSQL_Replay Profiler Trace Template?

Back 11

Captures the stored procedures and ad hoc SQL batches executed against the instance in a format that allows you to replay the trace
against a test system. This template is commonly used to perform load and regression tests.

Front 12

What is the purpose of the TSQL_SPs Profiler Trace Template?

Back 12

Captures performance data for all ad hoc SQL batches, stored procedures, and each statement inside a stored procedure. Every login and logout is also captured.

Front 13

What is the purpose of the Tuning Profiler Trace Template?

Back 13

Captures basic performance information for ad hoc SQL batches, stored procedures, and each statement inside a stored procedure.

Front 14

What is the cause of enabling file rollover for a trace file?

Back 14

After the file reaches the upper size limit specified, the file closes and a new file is opened.

Front 15

Why should you never log trace events to the same instance for which you are capturing events?

Back 15

You can possibly overwhelm the server; the best solution is to log trace events to a file and then later import the file into the server for analysis.

Front 16

What is the purpose of the Broker SQL Trace Event Group?

Back 16

13 events for Service Broker messages, queues, and conversations

Front 17

What is the purpose of the CLR SQL Trace Event Group?

Back 17

1 event for the loading of a Common Language Runtime (CLR) assembly

Front 18

What is the purpose of the Cursors SQL Trace Event Group?

Back 18

7 events for the creation, access, and disposal of cursors

Front 19

What is the purpose of the Database SQL Trace Event Group?

Back 19

6 events for data/log fi le grow/shrink as well as Database Mirroring state changes

Front 20

What is the purpose of the Database SQL Trace Event Group?

Back 20

2 events to notify when a deprecated feature is used within the instance

Front 21

What is the purpose of the Errors and Warnings SQL Trace Event Group?

Back 21

16 events for errors, warnings, and information messages being logged. Events to detect suspect pages, blocked processes, and missing column statistics.

Front 22

What is the purpose of the Full Text SQL Trace Event Group?

Back 22

3 events to track the progress of a full text index crawl

Front 23

What is the purpose of the Locks SQL Trace Event Group?

Back 23

9 events for lock acquisition, escalation, release, and deadlocks

Front 24

What is the purpose of the OLEDB SQL Trace Event Group?

Back 24

5 events for distributed queries and remote stored procedure calls

Front 25

What is the purpose of the Objects SQL Trace Event Group?

Back 25

3 events that track when an object is created, altered, or dropped

Front 26

What is the purpose of the Performance SQL Trace Event Group?

Back 26

14 events that allow you to capture show plans, use of plan guides, and parallelism. This event group also allows you to capture full text queries

Front 27

What is the purpose of the Progress Report SQL Trace Event Group?

Back 27

1 event for online index creation progress

Front 28

What is the purpose of the Query Notifications SQL Trace Event Group?

Back 28

4 events to track the parameters, subscriptions, and templates for query notifications

Front 29

What is the purpose of the Scans SQL Trace Event Group?

Back 29

2 events to track when a table or index is scanned

Front 30

What is the purpose of the Security Audit SQL Trace Event Group?

Back 30

44 events to track the use of permissions, impersonation, changes to security objects, management actions are taken on objects, start/stop of an instance, and backup/restore of a database.

Front 31

What is the purpose of the Server SQL Trace Event Group?

Back 31

3 events for mounting a tape, change to the server memory, and closing a trace file

Front 32

What is the purpose of the Sessions SQL Trace Event Group?

Back 32

3 events for existing connections when the trace starts as well as tracking the execution of logon triggers and resource governor classifier functions

Front 33

What is the purpose of the Stored Procedures SQL Trace Event Group?

Back 33

12 events for the execution of a stored procedure, cache usage,
recompilation, and statements within a stored procedure

Front 34

What is the purpose of the Transactions SQL Trace Event Group?

Back 34

13 events for the begin, save, commit, and rollback of transactions

Front 35

What is the purpose of the TSQL SQL Trace Event Group?

Back 35

9 events for the execution of ad hoc T-SQL or XQuery calls. Events for an entire SQL batch as well as each statement within a batch

Front 36

What is the purpose of the User Configurable SQL Trace Event Group?

Back 36

10 events that you can confi gure with SQL Trace

Front 37

What event groups are commonly captured to baseline and troubleshoot query performance?

Back 37

Stored Procedures, Performance, and TSQL event groups.

Front 38

What event group is used to define auditing quickly across a variety of security events?

Back 38

The Security Audit event group.

Front 39

What event group is commonly used to troubleshoot concurrency issues?

Back 39

The Locks event group.

Front 40

What events return a significant amount of data and should be used with caution on a very busy instance?

Front 41

What is the purpose of the sp_trace_create trace module?

Back 41

A stored procedure that creates a new trace object. Equivalent to the definition on the General tab of the New Trace dialog in Profiler.

Front 42

What is the purpose of the sp_trace_generateevent trace module?

Back 42

A stored procedure that allows you to defi ne your own trace event.

Front 43

What is the purpose of the sp_trace_setevent trace module?

Back 43

A stored procedure that adds a data column for an event to be captured by a trace. You need to call sp_trace_setevent once for each data column being captured for an event. Equivalent to the event and data column selection grid in the Events Selection tab of the New Trace dialog in Profiler.

Front 44

What is the purpose of the sp_trace_setfilter trace module?

Back 44

A stored procedure that adds a fi lter to a trace. Equivalent to the Edit Filter dialog box in Profi ler.

Front 45

What is the purpose of the sp_trace_setstatus trace module?

Back 45

A stored procedure that starts, stops, and closes a trace. A status of 0 stops a trace. A status of 1 starts a trace. A status of 2 closes a trace and removes the trace defi nition from the instance.

Front 46

What is the purpose of the sp_trace_geteventinfo trace module?

Back 46

A function that returns the events and data columns being captured by a trace.

Front 47

What is the purpose of the sp_trace_getfilterinfo trace module?

Back 47

A function that returns the filters applied to a specified trace.

Front 48

What is the purpose of the sp_trace_getinfo trace module?

Back 48

A function that returns status and property information about all traces defined in the instance.

Front 49

What is the purpose of the sp_trace_gettable trace module?

Back 49

A function that reads one or more trace files and returns the contents as a result set. Commonly used to import trace files into a table.

Front 50

What is required to correlate a counter log trace with a trace file?

Back 50

You need to have captured the StartTime data column in the trace.

Front 51

Which events are commonly used to establish a performance baseline?

Back 51

The RPC:Completed and SQL:BatchCompleted events.

Front 52

What are the four places that error and information messages related to your server running SQL Server can be found?

Back 52

Windows Event Log
SQL Server error log
SQL Server Agent log (sqlagent.out)
Database mail log

Front 53

The current SQL Server error log is called errorlog (no extension) and located where?

Back 53

MSSQL10.<instance>\MSSQL\LOG

Front 54

What stored procedure can you run to extract the contents of the current error log?

Back 54

sys.xp_readerrorlog

Front 55

When the transaction log fills up and any write action attempted rolls back, what is the error logged to the SQL Server error log and Windows Application Event log?

Back 55

9002

Front 56

If a transaction log fills up, what 5 actions can you perform?

Front 57

A transaction log backup might not free up enough space within the log to be reused if any of the following occurs: ?

Front 58

If a db runs out of disk space, what errors are logged? What action can you take?

Back 58

1101 and 1105. You could add a new file to the appropriate filegroup on a disk volume that has space.

Front 59

Before adding files to a filegroup to resolve errors 1101 and 1105, what should you do?

Back 59

Check to see if the data files have auto-grow disabled. If the disk still has space, you could increase the size of the data files and resolve the issue.

Front 60

If tempdb runds out of space, what error codes are generated?

Back 60

3958, 3959, 3966 and 3967

Front 61

What does a service with a startup mode of Disabled appear as in the Start Mode column in the SQL Server Configuration Manager main window?

Back 61

Other

Front 62

The start mode for all SQL Server services in a clustered installation should be set to what?

Back 62

Manual - they are controlled by the cluster service.

Front 63

What permissions do SQL Server service accounts need?

Back 63

Read and Write access to the folder(s) where the data/log files for system databases are stored

Front 64

What sequence of events appear in the Windows Application Event viewer log indicate that SQL Server service account does not have sufficient permissions to access the data or log file(s) for either the master or tempdb databases?

Front 65

Errors in what three databases can prevent an instance from starting?

Back 65

Errors in the master, tempdb, and mssqlsystemresource databases can prevent an instance from starting. Errors in all other databases just make the problem database inaccessible until you can fix the problem.

Front 66

When changing the startup parameters for the SQL Server service, what happens if a semicolon, which separates parameters, is followed by a space.

Back 66

It interprets everything after the space as part of the previous parameter. Do not introduce spaces following semicolons.

Front 67

What is the first indication you normally have for memory or processor issues?

Back 67

A stack dump is generated.

Front 68

What are 3 of SQL Server's 7 locking modes?

Back 68

Shared
Exclusive
Update

Front 69

What are the three lock types?

Back 69

Row
Page
Table

Front 70

What type of lock is acquired for reads?

Back 70

A shared lock; you can have multiple shared locks on the same resource.

Front 71

What type of lock is acquired for a resource being modified?

Back 71

An exclusive lock; it is held until the modification is complete.

Front 72

What is an update lock?

Back 72

A hybrid of the shared and exclusive locks. An update lock starts out by acquiring a shared locks on resources until it finds the piece of data that needs to be modified, the shared lock is then changed to an exclusive lock while the data is changed.

Front 73

What determines what type of lock to acquire?

Back 73

The Lock Manager; uses an aggressive resource threshold, commonly called 2% rule, designed to minimize the number of locks needed--as each lock consumes memory. If determined that more than 2% of the rows on a page will need to be accessed, a page lock will be acquired. Likewise, if more than 2% of the pages in a table will be needed, a table lock is acquired.

Front 74

What is lock escalation?

Back 74

Because distribution statistics are not always accurate or don't exist, Lock manager uses lock escalation to allow a lock to be promoted to another lock type. A row lock can be escalated to a table lock and a page lock can be escalated to a table lock. Row locks are NEVER escalated to page locks.

Front 75

What are the five transaction isolation levels?

Back 75

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
READ SERIALIZED
SNAPSHOT

Front 76

What is the READ UNCOMMITTED transaction isolation level?

Back 76

Data can be read that has not been committed. Although an exclusive lock still blocks another exclusive lock, any read
operations ignore an exclusive lock.

Front 77

What is the READ COMMITTED transaction isolation level?

Back 77

This is the default isolation level for SQL Server. An exclusive lock blocks both shared as well as exclusive locks. A shared lock blocks an exclusive lock. Shared locks are released as soon as the data has been read.

Front 78

What is the REPEATABLE READ transaction isolation level?

Back 78

Exclusive locks block both shared and exclusive locks. Shared locks block exclusive locks. Shared locks are held for the duration of the transaction.

Front 79

What is the READ SERIALIZED transaction isolation level?

Back 79

All the restrictions as the REPEATABLE READ isolation level. In addition, you cannot insert a new row within the keyset range
currently locked by the transaction. Locks are held for the duration of the transaction.

Front 80

What is the SNAPSHOT transaction isolation level?

Back 80

Uses the row versioning feature to keep shared and exclusive locks from blocking each other while maintaining data consistency. A read operation retrieves data from the version of the row prior to the start of a data modification operation.

Front 81

How can you determine whether a process is blocked?

Back 81

Use the sys.dm_exec_requests view. A process that is blocked will show a nonzero number in the blocking_session_id column. sp_who2 system stored procedure displays the SPID that is blocking a given process.

Front 82

If you determine that a process is causing contention within your database, what command do you execute?

Back 82

KILL <spid> where SPID is the system process ID of the blocking session.

Front 83

What happens when a process is killed?

Front 84

What sequence described a how a deadlock occurs?

Back 84

1. SPID1 acquires an exclusive lock on RowA.
2. SPID2 acquires an exclusive lock on RowC.
3. SPID1 attempts to acquire a shared lock on RowC and is blocked by the exclusive lock.
4. SPID2 attempts to acquire a shared lock on RowA and is also blocked by the exclusive lock.
Because both processes have to wait on the other process to release an exclusive lock before they can complete, the Lock Manager has an impossible situation. Lock Manager will kill one of the processes automatically (the one that has the least amount of accumulated time).

Front 85

What error is returned when a deadlock is detected?

Back 85

1205

Front 86

How can you troubleshoot a deadlock?

Back 86

Use SQL Server Profiler to capture a deadlock trace, which allows you to inspect the cause of the gridlock graphically.