在sql server 2005里,根据数据库性能动态构建索引。
来源:互联网 发布:网络电视频道大表 编辑:程序博客网 时间:2024/05/17 06:01
在sql server 2005里,根据数据库性能动态构建索引。
数据库设计好后,系统上线运动一个周期后,数据库性能瓶颈突现出来,这个时间,需要一种根据性能,来动态构建索引,提高查询效率。
以下为SQL 原码。
-- 添加 IX_AutoIndex_ 系列索引
/*
--过程优化SQL
SELECT [RowNumber] = row_number() over (order by (avg_user_impact ) ) , [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [RowNumber] ;
--查字段类型
select
ColumnsName = sc.name ,
DataType = st.name,
DataLength = sc.Length
from lzmisplat..syscolumns sc
inner join lzmisplat..sysobjects so on sc.id=so.id
inner join lzmisplat..SysTypes st on sc.XType=st.XType
where
so.name='S_FlowInstance' and sc.name='State'
*/
declare @TableName varchar(1000)
declare @EqualityUsage varchar(1000)
declare @InequalityUsage varchar(1000)
declare @IncludeCloumns varchar(1000)
declare @IndexTemplete nvarchar(2000)
declare @IndexIncludeTemplete nvarchar(2000)
declare @Templete nvarchar(2000)
declare @ExecuteSql nvarchar(4000)
declare @NowTime varchar(500)
declare @IndexName varchar(500)
declare @RowNumber int
-----------------
select @NowTime = replace( replace( replace( getdate() ,'-','') ,' ','') ,':',''), @ExecuteSql =''
select @IndexTemplete = '
CREATE NONCLUSTERED
INDEX [IndexName]
ON [TableName]
(
[EqualityUsage] [InequalityUsage]
)
', @IndexIncludeTemplete = '
include(
[IncludeCloumns]
)
',@Templete =
'
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)
ON [PRIMARY]
'
--建立临时表,存放错误信息
create table #tmp_ErrorInfo
(
sqlContent varchar(4000) ,
ErrorDes varchar(max)
)
declare cursor_index cursor for
SELECT
[RowNumber] = row_number() over (order by (avg_user_impact ) )
, [TableName] = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [IncludeCloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
where ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) > 1
ORDER BY [RowNumber];
open cursor_index
fetch next from cursor_index into @RowNumber,@TableName,@EqualityUsage , @InequalityUsage,@IncludeCloumns
while @@fetch_status = 0
begin
if ( @EqualityUsage is null )
set @EqualityUsage = ''
else
if ( @InequalityUsage is not null )
set @InequalityUsage = ',' + @InequalityUsage
else
set @InequalityUsage = ''
select @IndexName = 'IX_AutoIndex_' + replace( replace( replace( replace( @TableName , '[','') , ']','') , ']',''), '.dbo.','') + '_' + @NowTime + '_' + ltrim( str( @RowNumber ) )
--拼SQL
select @ExecuteSql = replace ( replace ( replace ( replace ( @IndexTemplete , '[IndexName]' , @IndexName ) , '[TableName]' , @TableName ) , '[EqualityUsage]' , @EqualityUsage ) , '[InequalityUsage]' , @InequalityUsage )
+ isnull( replace ( @IndexIncludeTemplete , '[IncludeCloumns]' , @IncludeCloumns ) , '' ) + @Templete
--print @ExecuteSql
begin try
Execute ( @ExecuteSql )
end try
begin catch
insert into #tmp_ErrorInfo select @ExecuteSql ,ERROR_MESSAGE()
end catch
fetch next from cursor_index into @RowNumber,@TableName,@EqualityUsage , @InequalityUsage,@IncludeCloumns
end
close cursor_index --关闭游标并且销毁
deallocate cursor_index
select * from #tmp_ErrorInfo
truncate table #tmp_ErrorInfo
drop table #tmp_ErrorInfo
- 在sql server 2005里,根据数据库性能动态构建索引。
- SQL Server 数据库索引
- SQL Server数据库性能优化之索引篇
- 如何获得数据库里有多少个全文索引。删除全文索引的方法 SQL server 2005
- 删除全文索引的方法 SQL server 2005 如何获得数据库里有多少个全文索引。
- 在SQL Server中用索引视图查看性能状况
- 在SQL Server中用索引视图查看性能状况
- 在SQL Server 2000里设置和使用数据库复制
- 在SQL Server 2000里设置和使用数据库复制
- 在ORACLE里设置访问多个SQL Server数据库
- 在SQL Server 2000里设置和使用数据库复制
- 在SQL Server 2000里设置和使用数据库复制
- 在SQL Server 2000里设置和使用数据库复制
- 在SQL Server 2000里设置和使用数据库复制
- SQL SERVER数据库中的索引
- SQL SERVER数据库全文索引
- 详解SQL Server数据库索引
- sql server 2008 数据库 索引
- 批处理教程6——精彩实例放送
- 批处理教程7——致谢&一些废话
- Spring框架:项目名称起源
- c# hashtable 遍历
- AT91SAM9263 WINCE 6.0 R2驱动开发-扩展4个串口芯片SC16C554驱动
- 在sql server 2005里,根据数据库性能动态构建索引。
- 学英语也能学成哲学家!
- tar命令详解
- java 调用命令 备份mysql数据库
- 友元函数
- no_data_found
- java 调用mysql客户端导入,导出数据
- svn授权问题(authorization failed)
- 动态生成树状菜单