woensdag 19 september 2012

Speeding Up Database Access

I found a complete serie on speeding up acces to your SQL Database.

So far, there are only 4 parts published of an 8 part series of articles on speeding up access to a SQL Server Database.
Here's an overview of the content of all 8 parts (including links of already published parts):

  • Part 1 Pinpointing missing indexes and expensive queries
    • Find expensive queries (with trace log)
    • Locate missing indexes with the query optimizer (or with Tuning Advisor)
    • Find unused indexes
  • Part 2 Pinpointing other bottlenecks
    • Find Locks and Latches (via counters in perfmon)
    • Execution plan reusability (using counters or dynamic management views (DMV))
    • Fragmentation of data and indexes
    • Memory check (using counters to verify if there’s a lack of memory)
    • Disk usage (locate possible disk bottlenecks using counters)
    • CPU (use counters to see if the CPU is stressed)
  • Part 3 Fixing missing indexes
    • Indexes explained (clustered and nonclustered indexes)
    • Index usage (when / when not)
    • Maintaining indexes
  • Part 4 Fixing expensive queries
    • Cache aggregation queries
    • Keep records short
    • Considering Denormalization
    • Be careful with triggers
    • Use table variables for small temporary result sets
    • Use Full Text Search instead of LIKE
    • Replacing cursors with set based code
    • Minimise traffic from SQL Server to Web Server
    • Object Naming
    • Use SET NOCOUNT ON
    • Use FILESTREAM for values over 1MB
    • Avoid functions on columns in WHERE clauses
    • Use UNION ALL instead of UNION
    • Use EXISTS instead of COUNT to find existence of records
    • Combine SELECT and UPDATE
  • Part 5 Fixing locking issues
  • Part 6 Fixing execution plan reuse
  • Part 7 Fixing fragmentation
  • Part 8 Fixing memory, disk and CPU issues

I'll update this post when new parts are published.

Geen opmerkingen:

Een reactie plaatsen