How can you quickly determine if any of these in

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

How can you quickly determine if any of these indexes are really being used?

You are examining a SQL Server 2005 system as part of a consulting engagement. The client has complained the inserts and updates are slow to process on the system. You discover that there are over thirty indexes on some of the most heavily updated columns. How can you quickly determine if any of these indexes are really being used?

Answer: Query sys.dm_db_index_usage_stats

Explanation: SQL Server 2005 collects information on index usage since the last restart in sys.dm_db_index_usage_stats. You can query this after the system has run for a reasonable period of time to see if indexes are really being used.

(Continued on next question...)

Other Job Interview Questions

• Which of the following services is instance-aware?
• Where is varchar(max) data stored?
• True or false, Report Builder offers direct export to a Microsoft Access database as one of its supported export options?
• What security role should you assign them?
• Which of these options cannot be set in the SQL Server 2005 Integration Services Bulk Insert task?
• What is a primary key? What is a foreign key?
• What does this WHERE clause evaluate to?
• What does @@options return?
• What does the CEILING() function do?
• Which collation is used for Service Broker messages sent between the instances?
• More...