Xperton Blog

Minimal knowledge requirements for SQL Server DBAs (Part 2)

previous blog entry | next blog entry

By: Eugene Khazin

Continuing with the list of things that a DBA should know.

T-SQL

joins (inner, outer), order by, distinct, group by, having, union
insert into
cursor
temp tables, temp table variables
stored procedures vs. inline sql
executing dynamic sql
user defined function
views
triggers
if..else statements
case statements
try/catch

SQL Server waits

CXPACKET
EXCHANGE
LATCH_x
LCK_x
NETWORKIO
PAGEIOLATCH_x
PAGELATCH_x
WRITELOG
IO_COMPLETION

Locking

types of locks
lock compatibility
lock escalation
transaction isolation levels
deadlocks

Linked servers

configuration
query writing for linked servers

Backups

recovery model (full, simple, bulk-logged)
full, transaction log, differential
checkpoint

Memory

memory architecture (buffer pool, memtoleave)
data cache and procedure cache
64 bit, 32 bit specifics
AWE, PAE, 3GB
min, max server memory
lock pages in memory

Memory

Memory\Available MBytes
Memory\Pages/sec
Paging File\% Usage
Paging File\% Usage Peak
PhysicalDisk(*)\% Idle Time
PhysicalDisk(*)\Avg. Disk sec/Read
PhysicalDisk(*)\Avg. Disk sec/Write
Processor(_Total)\% Processor Time
SQLServer:Access Methods\Full Scans/sec
SQLServer:Access Methods\Index Searches/sec
SQLServer:Access Methods\Workfiles Created/sec
SQLServer:Access Methods\Worktables Created/sec
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Lazy writes/sec
SQLServer:Buffer Manager\Page life expectancy
SQLServer:Databases(_Total)\Active Transactions
SQLServer:General Statistics\Active Temp Tables
SQLServer:General Statistics\Processes blocked
SQLServer:Locks(_Total)\Lock Requests/sec
SQLServer:Locks(_Total)\Lock Timeouts/sec
SQLServer:Locks(_Total)\Number of Deadlocks/sec
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
SQLServer:Wait Statistics(Average wait time (ms))\Lock waits
SQLServer:Wait Statistics(Average wait time (ms))\Log buffer waits
SQLServer:Wait Statistics(Average wait time (ms))\Log write waits
SQLServer:Wait Statistics(Average wait time (ms))\Memory grant queue waits
SQLServer:Wait Statistics(Average wait time (ms))\Network IO waits
SQLServer:Wait Statistics(Average wait time (ms))\Non-Page latch waits
SQLServer:Wait Statistics(Average wait time (ms))\Page IO latch waits
SQLServer:Wait Statistics(Average wait time (ms))\Page latch waits
SQLServer:Wait Statistics(Average wait time (ms))\Wait for the worker
System\Processor Queue Length