(SQL) Tip of the Day: A handful of simple steps

  • Article

Today’s (SQL) Tip…

Did you know that most of our Windows Azure SQL Database cases have historically been how-to and performance related?  Of those, a significant percentage of the performance problems are resolved by doing a handful of simple steps.  So, if you’re encountering performance issues, start with the following:

  • Rebuild existing indexes.  (This will also automatically update any statistics on those indexes.)
  • Drop any “auto-generated” statistics.  (Don’t worry.  These statistics were automatically generated once; if they’re needed again, they will be auto-generated again.)
  • Update any remaining statistics that were not already touched by index rebuilds.
  • Check the “Missing Indexes” DMV for an index suggestion related to the table or tables being queried.  Here’s the command I use to query the DMV as this query also shows the potential “impact” adding this index could have:

select a.database_id,a.equality_columns,a.included_columns,a.inequality_columns,
a.statement, c.avg_user_impact
from SYS.dm_db_missing_index_details a
join sys.dm_db_missing_index_groups b
on a.index_handle = b.index_handle
join sys.dm_db_missing_index_group_stats c
on b.index_group_handle = c.group_handle
order by c.avg_user_impact desc