Term
| System Monitor: What is the most common cause of a counter log failing to start? |
|
Definition
| Failure to define a specific account. Then account expiration, locked out, or deactivated. p.311 |
|
|
Term
| System Monitor: A counter has how many instances? |
|
Definition
|
|
Term
| What are the items that you can capture data for with System Monitor? Hint: the 3 levels of hierarchy. |
|
Definition
| counter objects, counters, and counter instances. p. 313 |
|
|
Term
Which module is used to start/stop/close a trace?
sp_trace_setevent
sp_trace_setstatus
sp_trace_create
sp_trace_gettable |
|
Definition
|
|
Term
| What utility is used to view the SQL Trace API? |
|
Definition
| SQL Server Profiler p.330 |
|
|
Term
| Trace Events: Which event group is used to detect suspect pages, blocked processes, and missing column statistics? |
|
Definition
|
|
Term
Which module is used to define a new trace?
sp_trace_setevent
sp_trace_setstatus
sp_trace_create
sp_trace_gettable |
|
Definition
|
|
Term
| What is monitored in the trace event group Performance? |
|
Definition
| show plans, plan guides, parallelism, full text queries. p.320 |
|
|
Term
|
Definition
Transaction Log for database % is full.
To fix: Backup the Transaction Log, add another log to a disk with free space. p.335
{buses-hen} |
|
|
Term
| Performance Monitor: What 3 counters can indicate a system problem? |
|
Definition
System: Processor Queue Length
Network Interface: Output Queue Length
Physical Disk: Avg. Disk Queue Length
The counts represent processes waiting for resources to be freed up. p.312 |
|
|
Term
| What is monitored in the trace event group Scans? |
|
Definition
| When a table or index is scanned. p.320 |
|
|
Term
| What error code is generated for a deadlock? |
|
Definition
1205 p.359
{dead man locked on a tin sail boat} |
|
|
Term
| Locks have what 3 characteristics? |
|
Definition
mode: shared, exclusive, update
type: row, page, table
scope: session, transaction, cursor p.355 |
|
|
Term
| What utility captures statistical information for hardware, operating system, and other applications? |
|
Definition
| System Monitor / PerfMon p.309 |
|
|
Term
| What type of data can System Monitor capture? |
|
Definition
| Numeric data for performance counters that are defined for HW or SW components. |
|
|
Term
| Describe the READ COMMITTED isolation level. |
|
Definition
| Default for SS. Exclusive blocks both shared & exclusive locks. Shared blocks exclusive. Shared locks are released as soon as the data has been read. p.356 |
|
|
Term
| How does a deadlock occur? |
|
Definition
| At least 2 processes that are both modifying data. Each process acquires an exclusive lock on a resource, and then attempts to acquire a shared lock on the same resource exclusively locked by the other process. p. 359 |
|
|
Term
Name the logs for database engine messages,
login / logout,
hardware & operating system information. |
|
Definition
DB engine: Windows Application Event Log (WAEL) & SS error log
Login/out: Windows Security log & SS error log
HW & OS: Windows System Event log p.336 |
|
|
Term
| Trace Event Group: Server |
|
Definition
| Mounting a tape, change of server memory, closing a trace file. p. 320 |
|
|
Term
| Describe the REPEATABLE READ isolation level. |
|
Definition
Exclusive blocks exclusive & shared.
Shared blocks exclusive.
Shared blocks are held for the length of the transaction. |
|
|
Term
| Which trace events are commonly used to establish a performance baseline? |
|
Definition
Stored Procedures | RPC: Completed
TSQL | SQL: BatchCompleted p.325 |
|
|
Term
| Trace Event Group: Database |
|
Definition
data / log file growth / shrink
database mirroring state changes p.320 |
|
|
Term
| System Monitor: List the output options / media. |
|
Definition
| graphical display and logs p.310 |
|
|
Term
| Trace Event Group: Cursors |
|
Definition
| creation, access, & disposal of cursors p.320 |
|
|
Term
| Describe the SNAPSHOT isolation level. |
|
Definition
| Uses the row versioning feature to keep shared & exclusive locks from blocking each other. A read retrieves data from the previous version. |
|
|
Term
|
Definition
A transaction cannot find the required version record in tempdb. tempdb is full! p.335
{mop-leaf / mop=judge train submersion-music record} |
|
|
Term
| System Monitor: A counter object has how many counters. |
|
Definition
|
|
Term
| List the 5 trace events that can have a significant payload. |
|
Definition
Performance | Showplan *
Stored Procedures | SP: StmtCompleted / Started
TSQL | SQL: Stmt Completed / Started p.320 |
|
|
Term
|
Definition
| acquistion & release, escalation, & deadlocks p.320 |
|
|
Term
|
Definition
| Could not allocate space for object % in database % because filegroup is full p.334 |
|
|
Term
| What application combines all the log files into a single chronological list? |
|
Definition
|
|
Term
|
Definition
| The version store is forced to shrink because tempdb is full p.335 |
|
|
Term
| SQL Server startup parameters are separated by what? |
|
Definition
| semicolon only. No spaces! p.349 |
|
|
Term
| Where do disk drive errors show up? |
|
Definition
Windows System Event Log.
If whole array goes off-line, the SS Error log will also show device activation errors. p.351 |
|
|
Term
| To start SQL Server, the service account needs what 4 things? |
|
Definition
RW access to folders with data & log files.
RW access to SS registry keys.
LogOn as a Service Authority. (logon as a service in CP)
sysadmin on SS. p.343 |
|
|
Term
|
Definition
| Dump files are generated when SS encounters an error that aborts a transaction. Contains data from various DBCC functions. WWW |
|
|
Term
| Where are dump files stored? |
|
Definition
| SS installation directory \ LOG |
|
|
Term
|
Definition
| A query against multiple data sources. i.e. different instances or machines. WWW |
|
|
Term
|
Definition
| Any typed query that is not in a stored procedure. WWW |
|
|
Term
| What indicates memory or CPU failures? |
|
Definition
| stack dumps for intermittent errors. STOP & POST errors for hard failures. p.352 |
|
|
Term
|
Definition
| Acquired for read operations to prevent the data being read from changing. Multiple shared locks are allowed. p.355 |
|
|
Term
|
Definition
| Hybrid of shared & exclusive. Acquires a shared lock until SS can find the piece of data to be modified, then changed to exclusive while the data is being changed. p.356 |
|
|
Term
| What algorithm is used to determine the lock mode? |
|
Definition
| 2% rule. If > 2% of rows on a page will need to be accessed, a page lock will be acquired. If > 2% of the pages of a table need to be accessed, a table lock is acquired. p.356 |
|
|
Term
|
Definition
| The Lock Manager attempts to use distribution statistics. If not available, or not accurate, the LM can promote the lock ONLY to a table lock. p.345 |
|
|
Term
| READ UNCOMMITTED isolation level |
|
Definition
| Data can be read that has not been committed. Shared locks ignore exclusive. Exclusive still blocks exclusive. p.356 |
|
|
Term
|
Definition
| Acquired on a resource that is being modified and is held until the modification is complete. Can only have one exclusive lock on a resource at a time. p.356 |
|
|
Term
| A SQL Server service start mode might show Auto - Manual - Other. What does "Other" denote? |
|
Definition
| The start up mode is set to disabled. p.342 |
|
|
Term
| What is a device activation error? |
|
Definition
| SQL Server either cannot find or cannot access a data/log file. p.348 |
|
|
Term
| Query performance declines every 15 minutes. What tools can be used to diagnose the problem? |
|
Definition
| System Monitor & Profiler. p.331 |
|
|
Term
| Errors in what 3 databases prevent SQL Server from starting? |
|
Definition
| master, tempdb, mysqlsystemresource. p.348 |
|
|
Term
| What is mysqlsystemresource? |
|
Definition
| A hidden system database in SQL Server. Contains most of the stored procedures, functions, DMVs' & other code that ships with SQL Server. p.347 |
|
|
Term
|
Definition
| Could not allocate a new page for database % because of insufficient disk space. p.334 |
|
|
Term
| What actions can be taken when a database runs out of disk space? |
|
Definition
| drop unneeded objects, add files to filegroup on a drive with free space, ensure autogrowth is on. p.334 |
|
|
Term
| Startup Failure: Check what 1st if SQL Server starts, then immediately shuts down? |
|
Definition
| Check the SQL Server service account for deletion, locked out, disabled, or password expired. ~p.340 |
|
|
Term
| What command line utility can diagnose & fix local disk errors? |
|
Definition
|
|
Term
|
Definition
Storage Area Network
Network Attached Storage
Always use the special utilities that ship with the storage array to diagnose & repair any disk errors. p.351 |
|
|
Term
| What 4 events occur when a process is killed? |
|
Definition
Any open transaction is rolled back.
A message is return to the client.
An entry is placed in the SQL Server error log.
An entry is placed in the Windows Application Event log. p.358 |
|
|
Term
| What 2 components are used to encrypt the Service Master Key? |
|
Definition
| SQL Server service account & password. p.340 |
|
|
Term
| What data column in a trace must be captured to perform a correlation between a trace file and a counter log? |
|
Definition
|
|
Term
| What is the most common cause of service start up errors? |
|
Definition
|
|
Term
| What 3 items define a trace? |
|
Definition
| events, data columns, & filters. p. 325 |
|
|
Term
|
Definition
| The version store is full. tempdb is full! Usually appears after an 1101/1105 error in the log. p.335 |
|
|
Term
Which types of SQL Server events are logged to the Windows Application Event log?
A - stack dumps
B - start up configuration messages.
C - job failures
D - killed processes |
|
Definition
A & D.
B - only logged to SQL Server error log
C - only logged to SQL Server Agent log. p.339 |
|
|
Term
| Start up failure: SQL Server starts, then immediately shuts down. Service account & permissions check out. What next? |
|
Definition
| Check for existence & permissions of tempdb folder. ~p.340 |
|
|
