sql2000升级sql2005
这次升级真是太折腾了。大大影响了平台的正常使用，把自己也搞的疲惫不堪。
sql2000升级2005有几种服务。
一种是在2000的基础上默认升级实例。另一种 是通过备份还原的形式。
我用的是备份还原。刚开始升级后，运行速度非常快，网站访问也很 顺畅。
但是过会之后，就会越来越卡，越来越慢，严重的超时现象。也不占cpu，就是
很卡，到最后不得已重启服务器。重启后又恢复正常。但是之后有开始了。
查阅了很多资料，最后 还是很可能在一个问题上 索引的重聚和索引碎片的整理。
我用了DBCC INDEXDEFRAG(‘dbname’,'tablename’),这样一个个执行表，很费力，
最后看了文章有用sp_updatestats 更新所有的。更新之后 至今还没有发现之前的
那种卡。说明数据库升级总算成功了。总算是平台正常了。
2005性能 还是有很大的提升的。其实主要还是用他的rownumber来提高分页查询的速度
下面用个自己的分页存储过程：
ALTER PROCEDURE [dbo].[P_PAGE] ( @CurrentPage int, @PageSize int, @TableName varchar(1000), @KeyField varchar(1000), @Fields varchar(1000), @Condition1 varchar(2000), @Condition2 varchar(1000), @PageCount int output, @RecordCount int output, @sql nvarchar(4000) output )AS declare @num numeric(8,2),@pz numeric(8,2),@rpz int set @sql = 'select @RecordCount=count(id) from '+@TableName+' '+@Condition1 exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output set @pz = @PageSize set @num = @RecordCount / @pz set @rpz = @num if @num > @rpz set @rpz = @rpz + 1 select @PageCount = @rpz if @CurrentPage < 1 begin set @CurrentPage = 1 end if @CurrentPage > @PageCount and @PageCount >0 begin set @CurrentPage = @PageCount end print @CurrentPage if(@CurrentPage=1) begin set @sql = 'select top '+convert(varchar,@PageSize)+' '+@Fields+' from '+@TableName+' '+@Condition1+' '+@Condition2 end else begin set @sql='SELECT '+@Fields+' FROM (SELECT '+@Fields+', ROW_NUMBER() OVER(ORDER BY id asc) AS rownum FROM '+@TableName+' ) as Tbl WHERE rownum BETWEEN '+convert(varchar,(@CurrentPage-1)*@PageSize+1)+' AND '+convert(varchar,@CurrentPage*@PageSize) end exec sp_executesql @sql
另附2005 索引重聚proc GO /****** 对象: StoredProcedure [dbo].[db2005_indexes] 脚本日期: 01/05/2012 09:32:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[db2005_indexes] AS SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @objectowner VARCHAR(255) DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @indexname CHAR(255) DECLARE @dbname sysname DECLARE @tableid INT DECLARE @tableidchar VARCHAR(255) SELECT @dbname = db_name() IF @dbname IN ('master', 'msdb', 'model', 'tempdb') BEGIN PRINT '不能运行系统数据库' RETURN END DECLARE tables CURSOR FOR SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type ='U' AND si.indid < 10 AND si.rows > 0 CREATE TABLE #templist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) OPEN tables FETCH NEXT FROM tables INTO @tableidchar WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #templist EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tableidchar END CLOSE tables DEALLOCATE tables SELECT * FROM #templist w--here indexname like '%destine%' DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity FROM #templist f JOIN sysobjects so ON f.ObjectId=so.id WHERE INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 SELECT '开始重新生成或者重组索引 ' + CONVERT(VARCHAR,GETDATE()) OPEN indexes FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag WHILE @@FETCH_STATUS = 0 BEGIN SET QUOTED_IDENTIFIER ON if(@frag>30) begin SELECT @execstr = 'DBCC DBREINDEX (' + '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS' SELECT '正在执行: ' --SELECT(@execstr) print @execstr EXEC (@execstr) end else if (@frag<30) begin SELECT @execstr = 'DBCC INDEXDEFRAG ('+db_name()+','+ '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS' SELECT '正在执行: ' --SELECT(@execstr) print @execstr EXEC (@execstr) end SET QUOTED_IDENTIFIER OFF FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag END CLOSE indexes DEALLOCATE indexes SELECT '完成结束索引' + CONVERT(VARCHAR,GETDATE()) DROP TABLE #templist