The most important performance-tuning technique available is having the needed indexes created on the tables to ensure that the SQL Server database engine is able to get the data that it needs to as quickly as possible. Having proper indexes is even more important than having the fastest possible storage because even with several Fusion ioDrive cards in a SQL Server, having the proper indexes on the tables are still required. The reason for this is because without the indexes there will simply be too much data for the database engine to scroll through for each query (which is run for the database engine to get through the data quickly). Not only would these queries begin running slowly, but other queries would be blocking due to locking within the database engine.
The first way that we know that we may need to add indexes to a SQL Server database is because new queries are being run against the database engine. Often when an application is upgraded and new queries are being run against the database engine they don’t have the indexes needed to perform well. Every time there are major software upgrades performed, the system should be reviewed and index tuning should be performed.
CXPACKET Wait Type
Another great way to know that it is time to add indexes to a SQL Server database is by looking at the specific wait types for the queries which are running longer than expected. Using only the native functionally this can be done by using the sys.dm_os_waiting_tasks dynamic management view. By looking at the wait_type column finding rows which are waiting for the CXPACKET wait type will give you the sessions which are running queries which could need to have indexes added to improve performance. This will require joining the sys.dm_os_waiting_tasks dynamic management view to the sys.dm_exec_requests dynamic management view and cross apply against the sys.dm_exec_sql_text dynamic management function as shown below: