When a query is executed on a database, indexes are used to physically locate the data on disk. Due to frequent insert, update and delete statements this data can become fragmented. This is a serious performance issue. To mitigate this schedeule this script in a SQL Agent Job to frequently defragment you indexes. Create stored procedure DefragmentIndexes in master database. This stored procedure defragments the indexes in your database
PRTG SQL Server Monitoring a powerful tool to monitor SQL servers. SQL servers are widely used for data storage and lots of applications and their performance depend on their constant availability and performance
Run this query on a Microsoft SQL Server 2005 or later and it will list indexes that you could create to improve the query performance of your database based on the workload of the server since the SQL service last restarted
SELECT ‚CREATE INDEX IX_Auto_‘ +
CONVERT(varchar(max), MID.index_handle) +
‚ ON ‚ +
[statement] +
‚ (‚ +
COALESCE(equality_columns + ‚, ‚ + inequality_columns, equality_columns, inequality_columns) +
‚)‘ +
ISNULL(‚ INCLUDE (‚ + included_columns + ‚)‘, “) AS create_statement,
CONVERT(int, avg_total_user_cost * user_seeks * avg_user_impact) AS potential_saving,
[statement] AS table_name,
equality_columns,
inequality_columns,
included_columns,
last_user_seek,
avg_total_user_cost,
user_seeks,
avg_user_impact
FROM sys.dm_db_missing_index_details MID
JOIN sys.dm_db_missing_index_groups MIG ON MIG.index_handle = MID.index_handle
JOIN sys.dm_db_missing_index_group_stats MIGS on MIGS.group_handle = MIG.index_group_handle
ORDER BY avg_total_user_cost * user_seeks * avg_user_impact DESC