Performance in SQL Server

1
(No Transcript)
2
Performance in SQL Server

• Bulgarian SQL BI User Group meeting
• 18 April 2007, Interpred

3
Our community

• www.bgsqlgroup.com
• Registration, discussions.. Using the site
• MS Days preferential invitation for all members
  of SQL BI User Group! To register for the
  event
• SQL Topics on MS Days
• SQL Roadmap
• Microsoft BI FrontEnd
• LINQ for developers
• HA for developers

4
Topics to discuss

• Monitoring Performance problems
• Indexes, fragmentation, partitioning and
  optimizations deep inside
• Dynamic SQL stuff

5
Monitoring Performance Problems in SQL Server 2005

• How to monitor and see what and where is the
  problem
• How to react

6
3 symptoms for slowing down

• Resource bottleneck
• CPU
• Memory
• IO Subsystem
• Tempdb bottleneck
• Queries (workload)
• Database design (physical)

7
Monitoring and troubleshootingGeneral strategy

• Know baseline
• Drill down to particular resource bottleneck
• Try to find out the mechanics of particular
  resource usage
• Watch for changes that may have cause the problem
• Possible steps for resoulution

8
CPU bottleneck

• SYSMon
• DMVs
• sys.dm_os_schedulers
• sys.dm_exec_query_stats
• Potential issues
• Excessive compilation/recompilation
• Inefficient query plans
• Intra-query parallelism
• Poor cursor usage

9
demo
10
Excessive compl/recompl

• Monitor set option changes, avoid them in stored
  procedures
• Consider temp tables vs. table variables, KEEP
  PLAN hint
• KEEPFIXED PLAN hint
• Consider automatic statistics update OFF vs. ON
• Use qualified object names (dbo.TableA vs.
  TableA)
• Do not mix DDL/DML statements
• Use DTA create missing indexes to improve
  compile time
• Consider RECOMPILE hint inside stored procedure

11
Inefficient query plans

• Tune with DTA
• Check cardinality
• Use hints for this query only if you cannot
  rewrite it
• Optimize for
• Use plan

12
Intra-query parallelism

• Use DTA to opt
• Cardinality

13
Memory botlenecks

• Memory pressures
• Internal/external
• Physical/virtual
• Tools
• Memory related DMVs
• DBCC MEMORYSTATUS command
• Performance counters performance monitor or DMV
  for SQL Server specific object
• Task Manager
• Event viewer application log, system log

14
Memory errors

• 701 - There is insufficient system memory to run
  this query
• 802 - There is insufficient memory available in
  the buffer pool
• 8628 - A time out occurred while waiting to
  optimize the query. Rerun the query
• 8645 - A time out occurred while waiting for
  memory resources to execute the query. Rerun the
  query
• 8651 - Could not perform the requested operation
  because the minimum query memory is not
  available. Decrease the configured value for the
  'min memory per query' server configuration
  option.

15
IO Subsystem bottlenecks

• SM Counters - PhysicalDisk Object Avg. Disk
  Queue Length, Avg. Disk Reads/Sec, Avg. Disk
  Writes/Sec Physical Disk Disk Time
• Compare with memory counters
• Find waits and corresponding databases
• Find IO Intensive execution plans
• Run DTA to find out missing indexes

16
Tempdb

• TempDB use
• Explicitly created user objects
• SQL Server created internal objects
• Features using version store MARS, online index,
  triggers and snapshot based isolation levels
• Problems
• Running out of TempDB space
• Bottleneck in system tables due to excessive DDL
  operations
• Allocation contention

17
Tempdb tips

• TempDB capacity planning
• Account for new features that use TempDB
• Preallocate space for TempDB
• Many TempDB files of equal size ( CPUs) to
  reduce contention
• User objects identify and eliminate offenders
  if possible
• Version store
• Eliminate longest transactions
• Account for size 2 version store data
  generated per min longest runtime of the
  transaction
• Excessive DDL
• Consider where temp tables are created
• Consider query plans that create many internal
  temp objects and verify if they are efficiently
  written, rewrite as needed

18
Start from WAITS

• Analyze WAITS at instance level
• Correlate with queues
• Determine a course of action
• Drill down to database/file level
• Drill down to process level
• Tune indexes/queries

19
Demo scenario
20
Blocking

• Sys.dm_tran_locks
• Monitor
• Execute Sp_configure blocked process threshold,
  200  
• Reconfigure with override
• If using SQL Trace, use sp_trace_setevent and
  event_id137
• In SQL Server Profiler - Blocked Process Report
  event class
• sys.dm_db_index_operational_stats blocking per
  object

21
Index usage and optimizations

• Index fragmentation
• Index reorganize/rebuild
• Partitioning for optimization of large tables
• Monitoring
• Sys.dm_index_usage_stats

22
(No Transcript)