We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have more rows and more partitions. Business would like to have some stats/data thanks to which will be able to decide If we can create index on production environment. So we prepare:

  • Logical reads before and after index for select queries and insert batches
  • CPU time (and elapsed time to check if we need parallelism) before and after index for select queries and insert batches
  • Duration of select queries and insert batches before and after index
  • Storage size of new indexes

Do you have any advice ? Is there any list or process for that ?


Here is my check list for when I give the DBA the orders to CREATE INDEX

  1. Proof that it actually solves a very specific business problem

Now, there are some transactions that could be negatively impacted, proof of minor impact is included (where needed)


Additionally you can compare the before and after actual execution plans of the queries you're trying to tune by creating these nonclustered indexes:

Actual execution plans are generated after the Transact-SQL queries or batches execute. Because of this, an actual execution plan contains runtime information, such as actual resource usage metrics and runtime warnings (if any). The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine used to execute the queries.

If the runtimes of the queries improved, and you see your nonclustered indexes used in the new execution plans, especially if you see them used with an index seek operation (when applicable) then that is proof they are effectively being used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.