Post-it notes: Chapter 10 – Monitoring and Troubleshooting

April 8, 2013

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

 start SQL Server in min config


Monitoring & Troubleshooting Tools

  • SQL 2000
    • Profiler
    • Trace flags 1205 & 3605
    • DBCC commands
  • SQL 2005
    • DMV’s
    • Profiler
      • Deadlock graph
      • Show XML Events
      • View Perfmon in Profiler
  • SQL 2008
    • Extended Events

Error Log

  • SQL Server writes errors and info to the error log
  • If it can’t write to the error log it still writes to the application log (SQL won’t start)
  • [C:\programfiles\ms sql server\mssql10.instance\mssql\log\]
  • SQL – ErrorLog
  • Agent – SQLAgent.out
  • Access the log in SSMS or using text
  • Startup parameter (-e)
  • Every SQL restart recycles the logs. Keeps 6 by default. Configurable, right-click in SSMS
  • Also recycles agent log (sqlagent.out)
  • Sp_cycle_errorlog
  • Sp_cycle_agentlog


Start SQL in minimal configuration

  • Allows database instance to start without tempdb
  • [c:\prog file\ms sql server\mssql\bin] – CMD – errorlog writes to console
  • Sqlservr.exe –c –f –m
    • –c : Start from command line
    • –f : Start in minimal configuration
    • –m : Start in single user mode (only sysadmin can connect)
  • After it’s running, connect using SQLCMD
  • Only sysadmins can connect

SQL Server Browser Service

  • Provide instance + Port info
  • UDP: 1434
  • SQL: 1433
  • If Browser service is stopped, must provide instance + port to access
  • If SQL configured with dynamic ports – must use browser service
  • If stopped, instance names not published
  • 1 browser service, works for all instances on the server



Set Transaction isolation level

  • Read Uncommitted: Read rows from other trans that are not committed, least restrictive, which creates ‘dirty reads’. NOLOCK
  • Read Committed: Cannot read data that is not committed. No dirty reads. It’s the SQL Server default – Shared log.
  • Repeatable read: shared lock
  • Snapshot: Read any data, transnationally consistent to the start of the query
  • Serealiazable: Range locks. Least restrictive.


  • Sqlagent.out
  • SQL Agent service, account that it’s running under, should be a sysadmin
  • If Agent running under system or local account, use proxy accounts to access network locations
  • Domain account -> Credential -> Proxy -> Agent Job
  • If domain account password is changed- then must update credential
  • T-SQL Agent step cannot use a proxy
  • Msdb.dbo.sysjobhistory


  • Sp_counts – used to count number of calls to SP
  • Standard – Instance overview, t-sql + sp start  + completion
  • Tsql – t-sql batch start, troubleshoot client apps, performance
  • Tsqlduration – tsql statements and completion times
  • Tsqlgrouped – capture activity for user or app, tsql grouped by user/app
  • Tsql_locks – tsql submitted by apps + exceptional locks, deadlock troubleshooting
  • Tsql_replay – trace to be replayed. Recreated workload another server
  • Tsql_sps – each step within a SP. Performance within SP’s.
  • Tuning – pass to sql database tuning advisor


Useful Perfmon counters. These are listed OBJECT first, then COUNTER

  • MEMORY – Available Bytes – amount of memory available to processes on server
  • Paging File – % used – amount used
  • Physical Disk – Avg. Disk sec/Read – avg time taken to read data
  • Physical Disk – Avg. Disk sec/Write – avg time taken to write data
  • Physical Disk – Disk reads/sec – rate of read operations per sec
  • Physical Disk – Disk writes/sec – rate of write operations per sec
  • Processor – % Processor time – Time that CPU is busy
  • SQLServer: Buffer Manager – Buffer cache hit ratio – % of data found in memory without reading from disk
  • SQLServer: Buffer Manager – Page life expectancy – number  of seconds page stays in buffer pool without references
  • SQLServer:General Statistics – User Connections – Number of users connected
  • SQL Server: Memory Manager – Memory Grants Pending – # of processors waiting on memory grant
  • SQL Server:SQL Statistics – Batch Requests/Sec – # of sql batch requests received by server
  • SQL Server:SQL Statistics – Compilations/Sec – # of sql compilations/sec  (should be low)
  • SQL Server:SQL Statistics – Re-Compilations/Sec – # of sql re-compilations/sec
  • System – Processor Queue Length – # of threads in processor queue. Shows ready threads, not threads that are running.


Thanks for reading.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: