Can adding basic new SQL Server index create more problems?

Question

I'm completely new to Indexing and would like to speed up some of my customers systems. My organisation made a mistake and upgraded a few of our customers from SQL Server 2000 to SQL Server 2008 before testing it fully in-house. The (new) servers are running slower than ever before as result. This is baffling the customers as you can imagine. Each customer has a two SQL Server user databases set up both about 1GB in size and each customer has about 30 users each.

At my organisation, there are not any currently resources to bring in a Developer or DBA to recommend what to do to speed up the new systems in place.

If I were to spend say four weeks learning as much as about SQL Server databases and indexes, do you think it might be a good idea to add some indexes to see if this makes any difference? Also could I easily delete the Indexes to put the system back the way it was so no harm done? Apart from it being a learning experience?

Answer 1

Playing with indexes is often a two-edge sword. Too many is not good and too few is not good either.

However you can start by adding those that most contribute to your queries.

Run this script

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

(script is from Burt Duncan)

and then analyse the columns like user_scans, user_seeks, avg_user_impact, last_user_seek, last_user_scan and etc. The query returns the index creation statement. You can change the index name to be more user friendly. I recommend to use it carefully, not just run all indexes, but one by one and see how the matters are improving.