If you have a question or saved method that needs performance tuning, what are the first stuff you try?

This is actually the handy-dandy listing of a few things i always share with someone asking me about optimisation.
We mainly use Sybase, but the majority of the advice will apply overall.

99% of problems I've come across are triggered by putting a lot of tables inside a join. The treatment for this really is to complete one join with less tables and cache the produces a temporary table. Then perform the relaxation from the query joining on that table.

Query Optimisation Record

  • Run UPDATE STATISTICS around the underlying tables
  • Remove records from underlying tables (possibly archive the erased records)
  • Rebuild Indexes
  • Rebuild Tables (bcp data out/in)
  • Dump / Reload the database (drastic, but might fix corruption)
  • Build new, appropriate index
  • Run DBCC to ascertain if there's possible corruption within the database
  • Locks / Deadlocks
    • Ensure not one other processes running in database
      • Especially DBCC
    • Are you currently using row or page level securing?
    • Lock the tables solely before beginning the query
    • Make sure that all processes are being able to access tables within the same order
  • Are indeces getting used properly?
    • Joins is only going to use index if both expressions are the identical data type
    • Index are only used when the first area(s) around the index are matched up within the query
    • Are clustered indeces used where appropriate?
      • range data
      • WHERE area between value1 and value2
  • Small Joins are Nice Joins
    • Automatically the optimiser is only going to think about the tables 4 at any given time.
    • Which means that in joins using more than 4 tables, it features a pretty good possibility of selecting a non-optimal query plan
  • Split up the Join
    • Are you able to split up the join?
    • Pre-choose foreign secrets right into a temporary table
    • Do half the join and set produces a temporary table
  • Maintain Summary Tables
    • Build with triggers around the underlying tables
    • Build daily / hourly / etc.
    • Build ad-hoc
    • Build incrementally or teardown / rebuild
  • See exactly what the query plan's with SET SHOWPLAN ON
  • See what’s really happenning with SET STATS IO ON
  • Pressure a catalog while using pragma: (index: myindex)
  • Pressure the table order using SET FORCEPLAN ON
  • Break Saved Procedure into 2
    • call proc2 from proc1
    • enables optimiser to select index in proc2 if @parameter continues to be transformed by proc1
  • Are you able to enhance your hardware?
  • What time are you currently running? It is possible to quieter time?
  • Is Replication Server (or any other non-stop process) running? Are you able to suspend it? Run it eg. hourly?

Slightly off subject but when you've treatments for these problems...
Higher level and Impact.

  • For top IO conditions make certain your disks are for either RAID 10 or RAID +1 or some nested implementation of raid 1 and raid .
  • Avoid using drives under 1500K.
  • Make certain your disks are just employed for your Database. IE no logging no OS.
  • Switch off auto grow or similar feature. Allow the database make use of all storage that's anticipated. Not always what's presently getting used.
  • design your schema and indexes for that type queries.
  • whether it's a log type table (place only) and should maintain the DB don't index it.
  • in case your doing allot of confirming (complex chooses with lots of joins) then you should think about developing a data warehouse having a star or snowflake schema.
  • You shouldn't be scared of replicating data in return for performance!