今天看书讲T-SQL,看到了UNBOUNDED
PRECEDING,就想比对下ROW_NUMBER()的运行速度。

SQL Server
2008中SQL应用系列–目录索引

SQLSERVER DBCC命令大全

DBCC
DROPCLEANBUFFERS:从缓冲池中删除所有缓存,清除缓冲区

在进行测试时,使用这个命令可以从SQLSERVER的数据缓存data
cache(buffer)清除所有的测试数据,以保证测试的公正性。

需要注意的是这个命令只移走干净的缓存,不移走脏缓存。由于这个原因,在执行这个命令前,应该先执行CheckPoint,将所有脏的缓存写入磁盘,

这样在运行DBCC RROPCLEANBUFFERS
时,可以保证所有的数据缓存被清理,而不是其中的一部分。

 

DBCC CacheStats:显示存在于当前buffer
Cache中的对象的信息,例如:hit rates,编译的对象和执行计划

 

DBCC ErrorLog
:如果很少重启mssqlserver服务,那么服务器的日志(不是数据库事务日志)会增长得很快,而且打开和查看日志的速度也会很慢

使用这个命令,可以截断当前的服务器日志,主要是生成一个新的日志。可以考虑设置一个调度任务,每周执行这个命令自动截断服务器日志。

使用存储过程sp_cycle_errorlog也可以达到同样的目的


 

一、DBCC 帮助类命令

DBCC HELP(‘?’) :查询所有的DBCC命令  

DBCC HELP(‘命令’) :查询指定的DBCC命令的语法说明 

DBCC USEROPTIONS :返回当前连接的活动(设置)的SET选项


 

二、DBCC 检查验证类命令 

DBCC CHECKALLOC(‘数据库名称’) :检查指定数据库的磁盘空间分配结构的一致性

DBCC CHECKCATALOG (‘数据库名称’)
:检查指定数据库的系统表内和系统表间的一致性

DBCC CHECKCONSTRAINTS (‘tablename’)
:检查指定表上的指定约束或所有约束的完整性

DBCC CHECKDB :检查数据库中的所有对象的分配和结构完整性 

DBCC CHECKFILEGROUP
:检查指定文件组中所有表在当前数据库中的分配和结构完整性

DBCC CHECKTABLE
:检查指定表或索引视图的数据、索引及test、ntest和image页的完整性

DBCC CHECKIDENT :如果存在大量数据删除,考虑在删除后,使用 dbcc
checkident 重置一下自增值

检查指定的当前标识值

DBCC SQLPERF(UMSSTATS):最关键的一个参考数据num
runnable:表明当前有多少个线程再等待运行,如果大于等于2,考虑CPU达到瓶颈

Scheduler ID:当前机器有多少个逻辑CPU就有多少个Scheduler
ID,具体怎麽看可以看一下我的这篇文章

SQLSERVER独特的任务调度算法”SQLOS”


 

三、DBCC 维护类命令 

DBCC CLEANTABLE (‘db_name’,’table_name’) :回收Alter table drop
column语句删除可变长度列或text

DBCC DBREINDEX :重建指定数据库的一个或多个索引 跟ALTER INDEX
REBUILD差不多

DBCC INDEXDEFRAG:对表或视图上的索引和非聚集索引进行碎片整理

DBCC PINTABLE (db_id,object_id) :将表数据驻留在内存中

查看哪些表驻留在内存的方法是:0:没有驻留 ,1:驻留

1 USE [GPOSDB]
2 GO
3 SELECT  OBJECTPROPERTY(OBJECT_ID('dbo.SystemPara'), 'tableispinned') 

 

DBCC UNPINTABLE (db_id,object_id) :撤消驻留在内存中的表

DBCC SHRINKDATABASE(db_id,int)
:收缩指定数据库的数据文件和日志文件大小 

DBCC
SHRINKFILE(file_name,int):收缩相关数据库的指定数据文件和日志文件大小


 

 

四、DBCC 性能调节命令

DBCC dllname(FREE) :在内存中卸载指定的扩展过程动态链接库(dll)

sp_helpextendedproc 查看加载的扩展PROC 

DBCC DROPCLEANBUFFERS :从缓冲池中删除所有缓冲区

DBCC FREEPROCCACHE :从执行计划缓冲区删除所有缓存的执行计划

DBCC INPUTBUFFER :显示从客户机发送到服务器的最后一个语句

DBCC OPENTRAN (db_name)
:查询某个数据库执行时间最久的事务,由哪个程序拥有

DBCC SHOW_STATISTICS :显示指定表上的指定目标的当前统计信息分布

DBCC SHOWCONTIG :显示指定表的数据和索引的碎片信息

DBCC SQLPERF (logspace) :查看各个DB的日志情况

(iostats) 查看IO情况

(threads) 查看线程消耗情况

返回多种有用的统计信息 

DBCC CACHESTATS :显示SQL Server 2000内存的统计信息

DBCC CURSORSTATS :显示SQL Server 2000游标的统计信息

DBCC MEMORYSTATS :显示SQL Server 2000内存是如何细分的

DBCC SQLMGRSTATS :显示缓冲区中先读和预读准备的SQL语句


 

五、DBCC 未公开的命令

DBCC ERRLOG :初始化SQL Server 2000的错误日志文件

DBCC FLUSHPROCINDB (db_id) :清除SQL Server
2005服务器内存中的某个数据库的存储过程缓存内容 

DBCC BUFFER (db_name,object_name,int(缓冲区个数))
:显示缓冲区的头部信息和页面信息

DBCC DBINFO (db_name) :显示数据库的结构信息 

DBCC DBTABLE :显示管理数据的表(数据字典)信息 

DBCC IND (db_name,table_name,index_id) :查看某个索引使用的页面信息 

DBCC REBUILDLOG :重建SQL Server 2000事务日志文件

DBCC LOG (db_name,3) (-1~4) :查看某个数据库的事物日志信息
显示格式可以为:-1,0,1,2,3,4 每个数字代表不同的格式

DBCC PAGE :查看某个数据库数据页面信息

DBCC PROCBUF :显示过程缓冲池中的缓冲区头和存储过程头

DBCC PRTIPAGE :查看某个索引页面的每行指向的页面号

DBCC PSS (user,spid,1) :显示当前连接到SQL Server 2000服务器的进程信息

DBCC RESOURCE :显示服务器当前使用的资源情况

DBCC TAB (db_id,object_id) :显示数据页面的结构


 

六、DBCC跟踪标记

跟踪标记用于临时设置服务器的特定特征或关闭特定行为,常用于诊断性能问题或调试存储过程或复杂的系统

DBCC TRACEON (3604) :打开跟踪标记

DBCC TRACEOFF :关闭跟踪标记

DBCC TRACESTATS :查看跟踪标记状态


 

 

七、官方使用DBCC的建议

1、在系统使用率较低时运行 CHECKDB。

2、请确保未同时执行其它磁盘 I/O 操作,例如磁盘备份。

3、将 tempdb 放到单独的磁盘系统或快速磁盘子系统中。

4、允许 tempdb 在驱动器上有足够的扩展空间。 使用带有 ESTIMATE ONLY 的
DBCC 估计 tempdb 将需要多少空间。

5、避免运行占用大量 CPU 的查询或批处理作业。

6、在 DBCC 命令运行时,减少活动事务。

7、使用 NO_INFOMSGS 选项减少一些信息的输出。

8、考虑使用带有 PHYSICAL_ONLY 选项的 DBCC CHECKDB
来检查页和记录的物理结构。

PHYSICAL_ONLY
选项:只检查物理错误,不检查逻辑错误

物理错误比逻辑出更严重,因为物理错误一般SQLSERVER都不能修复的,而逻辑错误大部分SQLSERVER都可以修复

 

很困啊 要睡了~~

sql及相关的结果如下,数据库中的数据有5W+。

前些天看到一篇文章《SQL Server 2012 – Server side paging demo using
OFFSET/FETCH
NEXT》,原文地址。作者在文中称,要SQL
Server 2012使用OFFSET/FETCH NEXT分页,比SQL Server
2005/2008中的RowNumber()有显著改进。今天特地作了简单测试。现将过程分享如下:

ROW_NUMBER():

附:我的测试环境为:
SQL Server 2012,命名实例

SET STATISTICS TIME ON

SELECT
    ROW_NUMBER() OVER(ORDER BY Id DESC) rn,Id
FROM
    dbo.T_MyCourse

Microsoft SQL Server 2012 – 11.0.2100.60 (Intel X86) 
Feb 10 2012 19:13:17 
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service
Pack 2)

运行结果

沿用上文的测试数据库和表:

图片 1

[sql] view
plain copy

UNBOUNDED
PRECEDING

 

SET STATISTICS TIME ON

SELECT
    SUM(1) OVER(ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,Id
FROM
    dbo.T_MyCourse

 print?

运行结果

  1. IF OBJECT_ID(‘DemoPager2012’) IS NOT NULL  
  2. DROP DataBase DemoPager2012  
  3. GO  
  4.   
  5. CREATE Database DemoPager2012  
  6. GO  
  7.   
  8. USE DemoPager2012  
  9. GO/*  
  10. Setup script to create the sample table and fill it with  
  11. sample data.  
  12. */  
  13. IF OBJECT_ID(‘Customers’,’U’) IS NOT NULL  
  14. DROP TABLE Customers  
  15.   
  16. CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),  
  17. CustomerNumber CHAR(4),  
  18. CustomerName VARCHAR(50),  
  19. CustomerCity VARCHAR(20) )  
  20. GOTRUNCATE table Customers  
  21. GO  
  22.   
  23. DBCC DROPCLEANBUFFERS  
  24. DBCC FREEPROCCACHE  
  25.   
  26. /*****运用CTE递归插入,速度较快,邀月注***********************/  
  27. WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS  
  28. (SELECT 1,cast(‘0000’as CHAR(4)),cast(‘Customer 0’ AS NVARCHAR(50)),cast(‘X-City’ as NVARCHAR(20))  
  29. UNION ALL  
  30. SELECT num + 1,Cast(REPLACE(STR(num, 4), ‘ ‘, ‘0’) AS CHAR(4)),  
  31. cast(‘Customer ‘ + STR(num,6) AS NVARCHAR(50)),  
  32. cast(CHAR(65 + (num % 26)) + ‘-City’ AS NVARCHAR(20))  
  33. FROM Seq  
  34. WHERE num <= 10000  
  35. )  
  36. INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)  
  37. SELECT CustomerNumber, CustomerName, CustomerCity  
  38. FROM Seq  
  39. OPTION (MAXRECURSION 0)  

图片 2

插入1万条数据后,在SQL Server 2008 R2中执行Row_Number():

通过运行之后,看到结果,使用微软官方提供的方法进行编号排序,速度明显的提高。

[sql] view
plain copy

不过我使用上述方法进行分页获取数据的时候结果又有点不一样。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注