While SQL queries don't reference indexes explicitly, we all know how important indexes are to database performance. They constitute the backbone of database operations. Indexing currently is a difficult
undertaking usually mastered by senior DBAs/developers. What happens if a SQL Server site does not have an indexing expert? Performance will range from less than optimal to unacceptable.
The SQL Server Database Engine has access to all the technical information, albeit not all recorded currently, to enable automating indexing.
The DBA would have to supply query priority information, especially indicate which are the
Business Critical queries. It is safe to assume that very frequent queries are Business Critical. However, there are less frequent queries which maybe Business Critical as well.
In a nutshell current indexing wisdom: Primary Key columns (this is already automatic), Foreign Key columns and columns in WHERE clause predicates should be indexed.
View Complete Post