0

I want implement one of the data optimization techniques on my SQL database. I preferred to go for creating indexes first, for which i used Tuning Advisor. Tuning advisor recommended me many of the Non-Clustered Indexes.

Shall I create all these indexes to improve performance ? Or Can create those only having less in size?

Please suggest me..as I am very new to this area ..

sunshine
  • 125
  • 4
  • 15
  • Before creating indexes you need to know wich queries take long time to execute and are called frequent. Use profiler – Sergio Mar 13 '13 at 06:29
  • @Sergio - Thanks for quick reply.In my case, 1.I have data in terms of thousand for time being, but still application performance is not well. 2. In most of the pages I show data in Hierarchy of grids, which Takes input from one grid and fill in hierarchy.can you guide me in this scenario ? – sunshine Mar 13 '13 at 06:53

2 Answers2

4

As for your question in implementing indexes, you must know that the first step towards monitoring, and intelligently responding to index usage needs, is to collect and maintain a simple and easy-to-use data set of index usage metrics.

This data set should allow you to quickly search for common scenarios in which you might consider removing or altering an index:

  • Unused indexes
  • Minimally used indexes
  • Indexes that are written to significantly more than they are read
  • Indexes that are scanned often, but rarely the target of seeks
  • Indexes that are very similar and can be combined

SQL Server provides a dynamic management view that tracks all index usage: sys.dm_db_index_usage_stats.

This view is a cumulative total of operations against indexes and is reset when SQL Server services are restarted.

To learn more, please refer to this article https://www.sqlshack.com/sql-server-index-performance-tuning-using-built-in-index-utilization-metrics/

LeonG
  • 111
  • 1
  • 4
0

I’d suggest you do some more reading on this topic before trying to implement anything as this might actually cause negative effect if not applied properly.

Your question about implementing indexes lacks additional data. As others already pointed out – you need to see what queries are most commonly used before implementing indexes. Otherwise it might only cause degradation.

Here are some articles to get you started.

https://www.simple-talk.com/sql/performance/

http://www.codeproject.com/Articles/22853/SQL-Server-Performance-Tips-and-Guidelines

Bill Clayton
  • 21
  • 1