Select * From Table Where A='条件1' and D>'时间1' and D<'时间2'有区间查询,D列建立聚集索引吧 其他列上建立非聚集索引,建议在A上建立一个非聚集索引,B,C列上建立符合索引至于索引的重建与否,看碎片吧,碎片达到一定程度,超过30%是rebuild,大于5%且不超过30%是REORGANIZE参考微软给出的建议吧http://msdn.microsoft.com/zh-cn/library/ms189858.aspx
create clustered/nonclustered index 索引名 on 表名 (列名,......) 具体语法联机丛书大把。怎么查看索引使用情况要看sqlserver的版本。
select * from sys.dm_db_index_physical_stats(DB_ID('数据库名字'),object_id('表名字'),null,null,'detailed') 碎片参考这个列avg_fragmentation_in_percent 好多个参数,你自己先慢慢查查sys.dm_db_index_physical_stats联机丛书吧重建索引 alter index index_name on tableNamerebuild 重新组织索引 alter index index_name on tableNamerebuild reorganize置于怎么建立索引,这个东西吧,没有标准答案,主要看实际情况来的,我觉得版主分析的很好了,他叫你的是内功
这是查碎片的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]
其他列上建立非聚集索引,建议在A上建立一个非聚集索引,B,C列上建立符合索引至于索引的重建与否,看碎片吧,碎片达到一定程度,超过30%是rebuild,大于5%且不超过30%是REORGANIZE参考微软给出的建议吧http://msdn.microsoft.com/zh-cn/library/ms189858.aspx
也是,
不过话说回来,重建或者重组索引是因为发现了它上面的性能问题,才去做这个的,
假如是小表,占用空间不大,不太可能在它上面发生性能问题
如果只在D列上使用聚集索引,那A列需要经常分类汇总,所以是不是应该使用聚集索引的组合?顺序为A,D?
什么语句查看索引碎片使用情况,再请教一下rebuild和reorganize的使用方法,谢谢!
碎片参考这个列avg_fragmentation_in_percent
好多个参数,你自己先慢慢查查sys.dm_db_index_physical_stats联机丛书吧重建索引
alter index index_name on tableNamerebuild
重新组织索引
alter index index_name on tableNamerebuild reorganize置于怎么建立索引,这个东西吧,没有标准答案,主要看实际情况来的,我觉得版主分析的很好了,他叫你的是内功
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]