Xperton
  • home
  • services
    • overview
    • database services
    • reporting and business intelligence
  • solutions
  • blog
  • company
    • about xperton
    • contact us
    • site map
  • clients
    • about our clients
    • testimonials

home 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
contact us
  • services
  • solutions
  • blog
  • company
  • clients
  • database services
  • about xperton
  • about our clients
  • reporting and business intelligence
  • contact us
  • testimonials
© 2010 Xperton Enterprises, Inc. All rights reserved
terms of use | privacy | contact us