The Power of Indexes

Indexing is one of those things that a developer seems to forget about, because it seems to be a "DBA"-role for maintaining the database.  After a week of performance tuning of our cloud application, I am convinced that indexing should be a key part of the my process for any performance analysis.  There are a couple of simple ways to find appropriate indexes for your database to address the low-hanging fruit of poor database performance.  The process looks something like this:

1. Identify slow performing queries in your application.
2. Develop a SQL script containing test queries that are consistently low performance.
3. Execute the script in SSMS using the option to include the Execution Plan.
4. Review & apply suggested indexes, and retest again.

The first step can be done in multiple ways.  My favorites are to use timing code like I posted previously, or to use System DMV's (Dynamic Management Views) to identify the most expensive queries.  You can also use a tool like SQL Profiler to capture SQL traffic and timings, although this method can be challenging to apply practically because of excessive traffic "noise".
Here is one such example to find expensive queries:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

The result is a list of queries and the time (in microseconds) that the query typically takes to execute.  A rule of thumb I used is infrequent queries taking more than a couple seconds should be analyzed, as well as frequent queries taking more than about 100 ms for a high-performance, low-latency application.  These requirements will vary depending on the needs of your application.
Once you identify a query to review, use these highlighted options in SSMS to include the Execution Plan and Client Statistics for good data to analyze the query:

With luck, the Execution Plan will provide a clue as to the reason for the poor performance.  Reading execution plans is a whole other topic which is worth researching in and of itself.  If you have a clear need for an index, and you have a good test set, SSMS 2012 will suggest an index at the top of the window which you can click to generate the script.  Running this script will apply the index, and allow you to retest and see if the query performance is improved.  Using this method we found 7-8 indexes that improved the overall performance of our application by about 10-15%.

While indexing a database is a good way to quickly improve performance with large amounts of data, there are a few caveats to keep in mind:

• An index must be maintained, which means INSERT/UPDATE queries will have a slight performance impact for each index.  This trade-off must be weighed carefully with each index, so at least one performance test should include real-world traffic patterns.
• There is no replacement for good programming practices.  It is better to fix chattiness in your application or cache often-used data rather than just use indexing to make the queries faster.  For instance, using the wrong algorithm in your application can have a much more severe impact on performance than using a slow query.

Happy programming!