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 ..
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:
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/
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
