Query Performance


Execution Plans


Partitioning: partitioned column and queries directly & indirectly based on it. Partitioned indexes & partitioned heaps.

Create Partition Function C1(date) As Range Right For Values (‘2014-01-01’, ‘2014-02-01’…)


Covering indexes

Filtered indexes: create index .. Where c1 > 1000

Indexes views

Summary tables.


Performance metrics: Query Duration, Database I/O, CPU time


Monitoring options:

  • SQL profiler
  • sptracecreate and related system SPs
  • SQL utility
  • Activity monitor
  • SSMS reports

Management DW & Data Collection Components:

  • SSMS > Server> Collection Data > Configure MDW
  • Open SQL profiler, select all ‘Complete’ events, then export as a Data Collection Trace.
  • From SSMS > Data collection > Your trace > start.
  • set the frequency and the retain period of the trace run and outcome.
  • This creates a list of MDW tables and jobs populating them.
  • Data Collection > Reports >
  • 1. Query statistics History.
  • 2. Disk Usage Summary: List of DBs and their growh, and when clicking on it: Log & Data Files Spaces.
  • 3. Server Activity History: CPU%, Memory, Disk I/O, network usage.

To check manually in MDW: check Reads, Duration, CPU columns in snapshots.trace_data joined with cores.snapshots, grouped by left(text_data,80)

Performance Tuning:

Check Disk I/O – CPU time – Run times (duration) in SQL monitor or MDW.

Set Statistics IO ON;

Set statistics Time ON;

Check actual execution plan, then:

  • modify a index
  •  Create a new index
  • created an indexed view
  • create a summary table with periodic updates.

Tuning based on Disk I/O

Execution Plans

Join Operators:

  • Loop: t1 Inner Loop Join t2 – scans t1 x t2 times
  • Merge: orders each table according to join column then scans t1+ t2 times (each table only once)
  • Hash match: builds a hash value for each record then scans each table only once.

Isolation Level:

  • Read uncommitted.
  • Read committed
  • Repeatable read
  • Serializable

Best practices:

  • Master execution plans.
  • Denormalize to reduce joins
  • Partition fact tables
  • Use heap instead of clustered index for partitioned tables
  • Use filtered index if queries use (recent) subset of records
  • Use indexed views instead of denormalized table or to materialize an aggregated dataset
  • Use summary tables instead of indexed views.
  • Use MDW, data collections and I/O, memory & duration measures


Leave a Reply

Your email address will not be published. Required fields are marked *