Post-it notes: Chapter 13 –Performance Tuning

April 9, 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.

sql server partitioning 

Tools

  • System Monitor (Perfmon)
    • Performance monitoring provided by windows
    • Measure hardware performance, bottlenecks, process causing problem
    • Provides server wide overview
  • Tracing
    • Get info on t-sql or mdx executed on DB
    • Trace for deadlocks, lock escalation, warnings, data/log shrinks, blocks
  • SQL Profiler
    • Used to define/configure traces
  • Dynamic Management Objects

Trace Templates

Can modify all
Can export to text or database
Avoid tracing to system drives, data or log files stored 

  • Sp_counts – trace calls to start sp
  • Standard – instance overview, audit, t-sql/sp start and completion
  • T-SQL – troubleshoot client apps, t-sql start times
  • T-SQL_Duration – all t-sql stmts and duration to complete
  • T-SQL_Grouped – activity certain user/app
  • T-SQL_locks – all t-sql with exceptional locks
  • T-SQL_replay – recreate workload another server
  • T-SQL_sps – each step in a stored proc
  • Tuning – t-sql completion times, use in DTA for analysis

Trace Events

  • Decide what events to capture
  • Show all events/show all columns
  • Error and Warnings events – useful
  • Caution with lock events (many locks on busy sql server)
  • Trace as few events as possible – performance

Trace Filters

  • Restrict the data
  • Like or not Like
  • Filter by user, db, application etc

Server side tracing

  • Reduces impact of tracing
  • From server directly – no network involvement
  • Create trace in profiler – then export script trace definition – run in SSMS
  • Script out trace definition
    • Sp_trace_create – defines trace, output (@traceid)
    • Sp_trace_setevent – define events
    • Sp_trace_setfilter – define filters
    • Sp_trace_setstatus – controls trace, input(@traceid)
      • 0 – stop
      • 1 – start
      • 2 – close/remove definition
  • Select * from fn_trace_getinfo(null) – information on what traces are running
  • TraceID = 1 – always there, used for DMV’s

Load trace file into a table

  • Select * into abc_trace from ::fn_trace_gettable(‘c:\file.trc’,default)

Combining System Monitor & Profile traces

  • Introduced in SQL 2005
  • Combine perfmon and SQL trace (side-by-side)
  • Correlate high CPU with t-sql stmt

Replaying traces

  • Capture trace on prod, replay on dev
  • There are some minimum requirements
  • Easiest to use replay template
  • Cannot trace replay on server participating in transactional replication

Load trace file into a table

  • Select * into abc_trace from ::fn_trace_gettable(‘c:\file.trc’,default)

SQL DIAG

  • Command line tool
  • Data collection utility – since SQL 2005
  • Collects the following
    • Windows event logs
    • SQL Configuration details
    • System monitor performance logs
    • SQL profiler trace data
    • SQL server blocking info
  • All of the above can be captured individually – just easier with SQLDIAG – all in one go.

 Locks, Blocking and Deadlocks

  • Concurrency vital part of RDMS, Implemented by locking
  • Excessive locks cause blocks, which affects performance
  • Deadlocks occur – two tasks after same resource (endless loop)

Locking

  • Provides concurrency
  • Importance of locking (ACID)
  • Atomic & Consistent – transaction management
  • Isolation – achieved by locking
  • Durable – achieved by tran logs

Deadlocks

  • Deadlocks occur – two tasks after same resource (endless loop)
  • Least expensive tran to rollback is killed

Transaction Isolation Levels

  • 5 levels, provide balance concurrency and data integrity
  • Defines the type of lock, how long to hold lock, how to read the data
  • Read un-committed (dirty reads)
  • Read committed  (default)
  • Repeatable Reads
  • Snapshot
  • Serializable

Lock Escalation

  • SQL tries to lock fewer resources as possible
  • Increase single lock to higher level = lock escalation
  • Row locks escalated to table locks
  • Page locks escalated to table locks
  • Row never escalated to page
  • SQL 200 – row/page locks can escalate to partition

Lock Compatibility

  • About 22 locks in SQL Server
  • Intent (I) – used to establish lock hierarchy
    • IS Intent Shared
    • IX Intent Exclusive (IX)
    • Shared with Intent Exclusive (SIX)
  • Shared (S) – read only operations (select *)
  • Update (U) – on resources that can be updated
  • Exclusive (X) – Used for data modification

Detect and Resolve Locks

  • Activity Monitor
    • Session.state
    • Running, waiting, sleeping
  • Profiler
    • Blocked process report
    • Deadlock chain
    • Deadlock graph
  • DMV’s
    • Sys.dm_exec_requests (waiting for lock)
    • Sys.dm_tran_locks  (detailed locking info)
  • Trace Flag 1222
    • Output lock details to SQL error log (sp_readerrorlog)
    • –t1222 (add to sql service cmd line / requires restart)
    • Dbcc traceon (1222,-1), no restart required

DMV’s

  • Sys.dm_xxxx
  • Permissions: server scope (view server state permission) or database scope (view database state)
  • DMV – provides snapshot information or cumulative information since last restart
  • Remember, trace ID 1 – gathers infor for DMV’s
  • Instead of sp_who2
    • Sys.dm_exec_requests
    • Sys.dm_exec_sessions
  • Dbcc showcontig replaced by sys.dm_db_index_physical_stats
  • Sys_db_exec_sql_text = replaces fn_get_sql : text of the sql batch
  • Sys_dm_exec_query_stats = perf stats of queried cached plans
  • Sys_dm_os_wait_stats = waits encountered by threads
  • Sys_dm_db_index_usage_stats = number of index usages
  • Sys_dm_db_index_operational_stats = low level I/O locking and latching
  • Sys_dm_db_missing_index_stats = info on missing indexes

Partitioning

  • Around since SQL 7 in various formats
  • SQL 2008 enterprise introduces next level – round robin thread model
  • New level of lock escalation – row/page to partition
  • Horizontal partitioning
    • 1 Table split into multiple tables
    • E.g. Each table would contain year, e.g. 2011, 2012 records, Contains only subset of rows
    • All columns in all partitioned tables
  • Vertical partitioning
    • 1 Table split into multiple tables
    • E.g. Each table would contain a subset of columns
    • Each table contains all rows
    • Requires a join to get data back together
  • Partitions can exist in one filegroup. Best practise to have multiple groups though
  • 1 partition – 1 filegroup – different disk – better performance
  • Partition key – Logical division between partitions
  • Partition function
    • Creates partition key but no data placement
    • Specifies boundaries between each partition
    • CREATE PARTITION FUNCTION (name, column)
    • Use LEFT/RIGHT to determine partition boundry
    • LEFT is default (equal to or less than
  • Partition Schema
    • Map partition to filegroup
    • 1 partition – 1 filegroup – best for performance
    • CREATE PARTITION SCHEME
  • Moving data between partitions (ALTER PARTITION FUNCTION|ALTER TABLE)
  • ALTER PARTITION FUNCTION (Split – add new partition to end of existing range OR Merge – combine two partitions)
  • ALTER TABLE (Switch – move a complete partition OR Implement sliding partition window)

Performance Data Collector 

  • SQL 2008 only (server and target servers)
  • Disabled by default – requires config
  • Stores centralized performance data across all SQL servers
  • Provides reports
  • Considered a client consisting of
    • Data source
    • Executable (dcexe.exe)
    • Management Data warehouse db (mdw) – stores the data
    • Sql agent job – data collectors (dc)
    • Integration services
  • Sources
    • Perfmon counters
    • Dmv’s
    • Profiler trace
  • First create the MDW then the DC’s
  • MDW created in simple recovery mode – default
  • 2 types of data collectors (DC’s) – System and Custom
  • System
    • Disk usage
    • Server activity (mem, cpu)
    • Query stats (queries, exec plans)
    • Default reports available for above
  • Custom
    • T-sql query
    • Sql trace
    • Performance counter
    • Query activity
  • Data collectors run on each configured server (dcexec.exe)
  • Data collector caching
    • Cached mode – collect – store data temp – upload to mdw (separate agent job)
    • Non-Cached mode – collect and  upload to mdw at same time
  • MDW Security
    • Mdw_admin
    • Mdw_writer
    • Mdw_reader
  • DC Security
    • DC_admin
    • DC_operator
    • DC_proxy

Resource Governor Concepts 

  • SQL 2008 feature
  • Disabled by default
  • Ability to reserve minimum resources for users or tasks
  • However if resource is idle, it will be used for tasks that require it
  • 3 key concepts
    • Resource pool
    • Workload groups
    • Classification functions
  • Resource Pools
    • Manage min and max restrictions to resources
    • Internal pools (2) – created by install, used for internal sql processes
    • Default pools (18) – used for workloads assigned
    • Internal pools has highest priority
    • CREATE RESOURCE POOL xxx WITH (max_cpu_percent=100, min_cpu_percent = 50)
    • ALTER RESOURCE GOVERNOR RECONFIGURE – restart not required
  • Workload Groups
    • Used to assign user session to specific resource pool
  • Classification Function (UDF)
    • Identify incoming requests to SQL Serve
    • Map these requests to a workload group
    • Function can use any valid property to classify (username, hostname, ip)
    • Only one UDF can be a classifier at any time
    • Dedicated admin connection(DAC) is not exposed to classification
  • Resource Governor summary
    • Session begin
    • Classification function executes (UDF)
    • Workload group membership determined
    • Query executes within correct resource
  • Sys.dm_resource_governor_resource_pools
  • Sys.dm_resource_governor_workload_groups

Execution plans

  • Showplan xml statistics profile – actual execution plan
  • Showplan xml – estimated execution plan

Thanks for reading.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: