The data is present in random order, but the logical ordering is specified by the index. We have a lot of data in our database and we want various types of reports from different-2 tables using joins, indexing etc. Most Important part is our store procedure and sql queries. There are two main ways to determine costly queries. One is to execute the store procedure including an Actual Execution Plan. The other is to get a time difference of a before and after for each statement.
The most commonly used indexes in a SQL Server database are clustered and non-clustered indexes. You can create these types of indexes on most columns in a table or a view, except those columns configured with large object data types, such as text and varchar(max). Although this limitation is not a problem in many cases, there will be times when you'll want to query such column types. However, without indexes defined on the columns, the query engine must perform a full table scan to locate the necessary data.
Create the clustered index before creating any non clustered indexes. Existing non clustered indexes on tables are rebuilt when a clustered index is created. If CLUSTERED is not specified, a non clustered index is created.
We can find non-clustered index using following SQL Query. If we have a large amount of data and number of Store procedure then we can not find where we can implement non-clustered index. So, this SQL Query is very useful to implement non-cluster index and speed up query execution time.
use test
SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE
NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATEDATABASE_DEFAULT + '
( ' + IsNull(mid.equality_columns, ''
+ CASE WHENmid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + '
) ' + CASE WHENmid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE
(' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans)DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC
use test
SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE
NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATEDATABASE_DEFAULT + '
( ' + IsNull(mid.equality_columns, ''
+ CASE WHENmid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + '
) ' + CASE WHENmid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE
(' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans)DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC