1

Current, we have a tracking system that uses SQL Server 2012 database. None of the tables in the database are indexed. I believe if index is applied for those tables, the performance should improve.

In order to impress my boss with this idea, I would like to be able to generate a BEFORE and AFTER indexing performance report.

Is it a must that I have to select one or a few specific queries from our tracking system program and use them as the benchmark? Or I can just use some kind of data collector that collect certain performance data for say 1 week and then compare BEFORE index and AFTER index?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rick Dee
  • 51
  • 1
  • 4
  • `"None of the tables in the database are indexed."` does it mean that all your tables are **HEAPs** (no clustered index)? – Lukasz Szozda Sep 11 '15 at 08:25
  • Hopefully they have primary keys ??? – iDevlop Sep 11 '15 at 12:04
  • Right click the database name in SSMS and open the Report context menu. There's plenty to try and explore there. Some will tell you which views are the most expensive. – iDevlop Sep 11 '15 at 12:06

1 Answers1

0

If you haven't done much SQL Server optimizations before I would suggest to try Database Engine Tuning Advisror - it will give you missing indexes hints.

As for the benchmark queries - you can identify the most expensive/popular queries on your SQL Server instance using dm_exec_query_stats management view. An example can be found in this question

Community
  • 1
  • 1
Sergey Rybalkin
  • 3,004
  • 22
  • 26