-1)服务器名 --检查是否一致 use master go select @@servername select serverproperty('servername') --如果不一致，执行下面的语句 if serverproperty('servername') <> @@servername begin declare @server sysname set @server = @@servername exec sp_dropserver @server = @server set @server = cast(serverproperty('servername') as sysname) exec sp_addserver @server = @server , @local = 'LOCAL' end /*************************************** 说明：其实就是删除旧的服务器名servername，再添加新的服务器名 sp_dropserver '旧的服务器名' sp_addserver '新的服务器名' , 'LOCAL' 3、重启SQL SERVER 4、再运行以下脚本验证一下。 ***************************************/ use master go select @@servername select serverproperty('servername') --2)查找索引定义 DECLARE @tbl nvarchar(265) SELECT @tbl = 'u_store_c' --替换表名 SELECT o.name,i.index_id, i.name, i.type_desc, substring(ikey.cols, 3, len(ikey.cols))AS key_cols, substring(inc.cols, 3, len(inc.cols)) ASincluded_cols, stats_date(o.object_id, i.index_id) ASstats_date, i.filter_definition FROM sys.objects o JOIN sys.indexes i ON i.object_id = o.object_id CROSS APPLY (SELECT ', ' + c.name + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('')) AS ikey(cols) OUTER APPLY (SELECT ', ' + c.name FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.index_column_id FOR XML PATH('')) AS inc(cols) WHERE o.name = @tbl AND i.type IN (1, 2) ORDER BY o.name, i.index_id --3)每个表上索引的使用情况 SELECT OBJECT_NAME(ddius.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes], user_seeks+ user_scans + user_lookups AS [Total Reads] , user_updates-( user_seeks + user_scans + user_lookups ) AS [Difference] FROM sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id] AND i.index_id = ddius.index_id WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1 AND ddius.database_id = DB_ID() AND OBJECT_NAME(ddius.[object_id])='u_store_c' --替换表名 AND i.index_id > 1 --非聚集索引 ORDER BY [Difference] DESC , [Total Writes]DESC , [Total Reads]ASC; --4)获取某个索引被使用的情况 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT COALESCE(DB_NAME(p.dbid) ,p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)')) AS database_name ,DB_NAME(p.dbid) + '.' + OBJECT_SCHEMA_NAME(p.objectid, p.dbid) +'.' + OBJECT_NAME(p.objectid, p.dbid) AS OBJECT_NAME, cast ('索引名' as varchar(64)) AS IndexName ,cp.objtype ,p.query_plan ,cp.UseCounts AS use_counts ,cp.plan_handle ,CAST('<?query --' + CHAR(13) + q.text + CHAR(13) + '--?>' AS XML) AS sql_text INTO xxx.xxx.xxx表 FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q WHERE cp.cacheobjtype = 'Compiled Plan' AND p.query_plan.exist('//Object[@Index = "[索引名]"]') = 1 ORDER BY UseCounts DESC,COALESCE(DB_NAME(p.dbid), p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)')) --5)丢失索引 SELECT user_seeks * avg_total_user_cost *( avg_user_impact *0.01 ) AS [index_advantage] , dbmigs.last_user_seek , dbmid.[statement] AS [Database.Schema.Table], dbmid.equality_columns , dbmid.inequality_columns , dbmid.included_columns , dbmigs.unique_compiles , dbmigs.user_seeks , dbmigs.avg_total_user_cost , dbmigs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groupsAS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle INNER JOIN sys.dm_db_missing_index_detailsAS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle WHERE dbmid.[database_id] = DB_ID() ORDER BY index_advantage DESC; --6)索引上的碎片超过%并且索引体积较大（超过页）的索引。 SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id], DB_ID())+ '].[' + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] , i.[name] AS [index_name] , ddips.[index_type_desc] , ddips.[partition_number] , ddips.[alloc_unit_type_desc], ddips.[index_depth] , ddips.[index_level] , CAST(ddips.[avg_fragmentation_in_percent]AS SMALLINT) AS [avg_frag_%] , CAST(ddips.[avg_fragment_size_in_pages]AS SMALLINT) AS [avg_frag_size_in_pages] , ddips.[fragment_count] , ddips.[page_count] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id] AND ddips.[index_id] = i.[index_id] WHERE ddips.[avg_fragmentation_in_percent] > 15 AND ddips.[page_count] > 500 ORDER BY ddips.[avg_fragmentation_in_percent] , OBJECT_NAME(ddips.[object_id], DB_ID()) , i.[name]