Find Missing Indexes

Once again the dynamic management views to rescue. Found this from brent ozar's blog havent really used it as i was working on production environment when i came across this but it would be worth a try to carefully deploy these one by one and check for the usage.


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 COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') +
CASE WHEN mid.inequality_columns IS NULL THEN
''
ELSE
CASE WHEN mid.equality_columns IS NULL THEN
''
ELSE
','
END + mid.inequality_columns
END + ' ) '
+
CASE WHEN mid.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


click here to view brents video

Identifying indexes which can be deleted

Dynamic management views contain a lot of important information which can be used to manage the data more efficiently. The first instinct when asked to performance tune a database is to add new indexes and improve the not so well performing queries. However a more logical view is to beign with removing the unused indexes first before adding any new ones as adding an index also has an overhead.

When working on indentifying the unsed indexes the following query which is basec on the dynamic management view sys.dm_db_index_usage_stats can be used :

SELECT o.name
, indexname=i.name
, i.index_id
, reads = user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE WHEN s.user_updates < 1 THEN
100
ELSE
1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o
on s.object_id = o.object_id
INNER JOIN sys.schemas c
on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads


The column reads_per_write is used to identify the indexes which are the least used. The indexes which are most often written to and the least read from are the ones which should be gotten rid of. The most useful index is the one which is the most used.

Navision Application Roles

Navision secuirty works on application roles which are created in the SQL Server. For each user that is created in Navision an application role is created and the effective permissions for the user are applied to the appplication role by the navision client.

During a performance tuning excercise I modified some of the SIFT tables to increase the performance. The problem set in when due to these changes the navision security got disturbed and the users were automatically revoked permissions on the SIFT tables. I was required to constantly make changes to the SIFT tables and it was painful to synchronize all the user again and again so i figured out that we could grant the access permissions to these tables for all the application roles which would solve the problem and not require the login synchronization each time.

To get a list of all the application roles i used the following query :
select name 'rolename', uid 'roleid', isapprole from sysusers where isapprole = 1

The grant query was also easy to generate as shown below
select 'grant select, insert, update on [TableName] to ' + name
from sysusers where isapprole = 1