MS SQL 数据库迁移文件-统计信息对执行计划的影响(...-关于存储过程的。。。

当前位置: 数据库>sqlserver

本页文章导读:

▪MS SQL 数据库迁移文件 MS SQL 数据库迁移文件，这里说的不是将数据库迁移到另外一台服务器，只是在服务器不同磁盘目录内做迁移。移动数据库文件的情况大致有下面一些：　　1: 事先没有规划好，数据库文件或.........

▪统计信息对执行计划的影响(二) 上一篇文章统计信息对执行计划的影响(一) 演示了统计信息对连接方式的影响，这一篇将给大家演示统计信息对单表数据获取方式的影响在上次的测试DB上执行以下代码创建测试表及.........

▪关于存储过程的。。。 USE [jcCmsDb]GO/****** 对象: StoredProcedure [dbo].[up_DataPageRowNumber] 脚本日期: 04/25/2013 20:06:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE[dbo].[up_DataPageRowNumber]@SQL Nvarchar(2000),@Order Nvarchar(40),@Pa.........

[1]MS SQL 数据库迁移文件

来源: 发布时间: 2013-10-29

MS SQL 数据库迁移文件，这里说的不是将数据库迁移到另外一台服务器，只是在服务器不同磁盘目录内做迁移。移动数据库文件的情况大致有下面一些：

　　1: 事先没有规划好，数据库文件或日志文件增长过快，导致某个盘或整个磁盘空间不足，需要移动数据文件或日志文件

　　2: 纯粹由于业务需求，数据增长过快。

　　3: 为了更好的IO的性能，需要将数据文件、日志文件分布到不同磁盘，减轻IO压力，提供IO性能。

　　4：故障恢复。例如，数据库处于可疑模式或因硬件故障而关闭。

案例：现在我在数据库实例中有数据库MyAssistant,(假设)由于事先没有规划好，导致数据文件位于E:\DataBase目录下, 我们需要将数据文件移动到D:\DataBase_Data目录下，

将日志文件移动到F:\DataBase_Log目录下。

步骤1：对数据库中每个要移动的文件（数据文件/日志文件），通过下面命令指定到新的目录

USE master

GO

ALTER DATABASE MyAssistant

MODIFY FILE(NAME='MyAssistant', FILENAME='D:\DataBase_Data\MyAssistant.mdf');

GO

ALTER DATABASE MyAssistant

MODIFY FILE(NAME='MyAssistant_log', FILENAME='F:\DataBase_Log\MyAssistant_log.ldf');

GO

如果有多个数据库的数据文件/日志文件需要移动，可以通过一系列上述命令执行

ALTER DATABASE DATABASE_ID1

MODIFY FILE(NAME='DATABASE_NAME', FILENAME='....mdf');

ALTER DATABASE DATABASE_ID2

MODIFY FILE(NAME='DATABASE_NAME', FILENAME=.....mdf');

.......

步骤2：停止SQL Server实例，你可以在SQL Server Management Studio的配置工具Sql Server Configuration Manager下停止。也可用NET STOP MSSQLSERVER命令实现。

步骤3：将那些数据文件或日志文件手工移动到对应的目录（也就是上面命令中FILENAME对应的目录）

步骤4：重启SQL Server实例，验证数据文件迁移是否成功。

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('MyAssistant');

对于临时数据库（它比较特殊），由于每次启动 MSSQLSERVER 服务时都会重新创建 tempdb，因此不需要从物理意义上移动数据和日志文件。将在步骤 4 中重新启动服务时创建这些文件。重新启动服务后，tempdb 才继续在当前位置发挥作用。

二：由于硬件故障而需要移动系统数据库

对于由于硬件故障而需要移动系统数据库，移动方案稍微有些不同

步骤1：如果SQL Server实例已经启动，那么停止该实例;

步骤2：在命令提示符下输入 <SQLPath>\binn\sqlservr -c -f -T3608 ，其中 <SQLPath> 是 SQL Server 实例的路径。例如，C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL。这将启动 SQL Server 实例，以进行仅 master 恢复（SQL Server实例启动到master-only恢复模式）

步骤3：和上面的步骤一样了，在此不累赘的叙述了。

本文链接

[2]统计信息对执行计划的影响(二)

来源: 发布时间: 2013-10-29

上一篇文章统计信息对执行计划的影响(一) 演示了统计信息对连接方式的影响，这一篇将给大家演示统计信息对单表数据获取方式的影响

在上次的测试DB上执行以下代码创建测试表及插入数据

--创建测试表
CREATE TABLE CHECK2_T3
(
    ID INT,
    COL1  CHAR(800),
    COL2  INT,
    COL3  CHAR(5000)
)


---向表中插入数据
BEGIN TRAN
DECLARE @I INT,@COL1 CHAR(1)=''
SET @I=1
WHILE @I<=10000
 BEGIN
    IF @I%4=0 
    BEGIN SET @COL1='B' END
    ELSE IF @I%250=0
    BEGIN SET @COL1='C' END
    ELSE
    BEGIN SET @COL1='A' END
    
    INSERT INTO CHECK2_T3 SELECT @I,@COL1,cast(rand()*1000 AS INT),'X'
    SET @I=@I+1
 END
COMMIT TRAN

然后执行下面代码在COL1上创建非聚集索引，我们知道创建索引的时候自动创建与之相关的统计信息，WITH(STATISTICS_NORECOMPUTE = ON)是禁用其统计信息自动更新

--在COL1列上创建非聚集索引，并禁用自动更新统计信息
CREATE NONCLUSTERED INDEX NCIX_COL1 ON CHECK2_T3(COL1) WITH(STATISTICS_NORECOMPUTE = ON)

--查看刚建立的索引的统计信息
DBCC SHOW_STATISTICS(CHECK2_T3,NCIX_COL1)

然后再执行以下sql，看到执行计划是用的索引查找

--COL1为'C'的只有20行，执行以下语句查询采用索引查找
SELECT AVG(COL2) FROM CHECK2_T3 
WHERE COL1='C'

再往表内插入30W条C记录，因为已经禁用自动更新统计信息，所以统计信息没有变化

BEGIN TRAN
DECLARE @I INT
SET @I=1
WHILE @I<=300000
 BEGIN    
    INSERT INTO CHECK2_T3 SELECT 100001,'C',cast(rand()*1000 AS INT),'XD'
    SET @I=@I+1
 END
COMMIT TRAN

这时C的记录数变成30W+,接着执行以下两条sql，②的sql使用了表提示，目的是让查询走全表扫描，①的执行计划和之前的一样，这时RID运算符的开销就相当高，以下两句对比，表扫描体现性能优势

以下两句在我本机测试的速度②比①快了一倍，多次执行注意清空缓存

① SELECT AVG(COL2) FROM CHECK2_T3 WHERE COL1='C'

② SELECT AVG(COL2) FROM CHECK2_T3 WITH(INDEX(0)) WHERE COL1='

[3]关于存储过程的。。。

来源: 发布时间: 2013-10-29

USE [jcCmsDb]
GO
/****** 对象: StoredProcedure [dbo].[up_DataPageRowNumber] 脚本日期: 04/25/2013 20:06:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE[dbo].[up_DataPageRowNumber]
@SQL Nvarchar(2000),
@Order Nvarchar(40),
@PageIndex int,
@PageSize int,
@TotalRecorder int output
AS
BEGIN
SET NOCOUNT ON;
declare @ExceSQL nvarchar(4000)
declare @start_row_num AS int
SET @start_row_num=(@PageIndex-1)*@PageSize+1
declare @end_row_num int
set @end_row_num=@PageIndex*@PageSize
declare @RowNumber nvarchar(100)
set @RowNumber=',ROW_NUMBER() OVER(ORDER BY '+@Order+')as RowNumber from'
set @SQL=Replace(@SQL,'from',@RowNumber)
set @ExceSQL='select @TotalRecorder=max(RowNumber)from(' +@SQL+ ')as tmp'
execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output
set @ExceSQL = 'select*from(' + @SQL + ') as tmp where RowNumber between' +Convert( nvarchar,@start_row_num)
+ 'And' +Convert( nvarchar,@end_row_num)
print @ExceSQL
execute(@ExceSQL)
END

declare @a int
exec up_DataPageRowNumber "select * from jc_Article","Id",2,2,@a output
print @a

为什么会一直抱这个错？？抓狂。。。。

select*from(select * ,ROW_NUMBER() OVER(ORDER BY Id)as RowNumber from jc_Article) as tmp where RowNumber between3And4
消息 4145，级别 15，状态 1，第 1 行
在应使用条件的上下文(在 'between3And4' 附近)中指定了非布尔类型的表达式。

本文链接