aaron note sql server-Aaron的笔记

liuyuehua

BCP+内存文件(映射硬盘文件): sql server 2000每秒插入达5000行　　SELECT INTO 远比 INSERT INTO 的性能好
　　select * from sysobjects where xtype='TR'
　　: see all triggers in current database
　　alter database [DBName] collate SQL_Latin1_General_CP850_BIN
　　or Chinese_PRC_CI_AS
　　alter database [DBName] set RECOVERY simple
　　exec master.dbo.xp_cmdshell 'bcp "table1" out "c:\as.txt" -c -q -U "sa" -P "******"'
　　sp_who
　　select * from master.dbo.sysprocesses
　　select name from master.dbo.sysdatabases
　　select databaseproperty('database1','IsTornPageDetectionEnabled')
　　sp_configure 'cursor threshold', 0
　　sp_dboption(database options): arithabort ......
　　user options: XACT_ABORT ......
　　查看当前未提交事务：select @@TRANCOUNT
　　SET   FMTONLY  只将元数据返回给客户端。
　　相当于select   *   from   t   where   1   =   2
　　执行   SET   FMTONLY   ON   设置之后，数据库在接收到客户端对数据的请求之后，直接从系统表中获取数据列信息，生成空的结果集直接返回客户端，而不再对磁盘中存储的数据进行检索处理。
　　DBCC SHRINKDATABASE或DBCC SHRINKFILE命令来收缩一个数据库
　　读取数据页结构的命令DBCC Page
　　DBCC SHRINKDATABASE或DBCC SHRINKFILE命令来收缩一个数据库
　　读取数据页结构的命令DBCC Page
　　在 SQL Server 2005 中，行不能跨页；当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时，SQL Server 将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。
　　每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时，将会执行此操作。
　　将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后，将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。
　　若未指定索引类型，则默认为非聚集索引
　　即使分组/分段查询的，但更新频繁，建议用非聚集索引；常常被顺序访问时，最好使用clustered索引
　　频繁使用的外键可以建nonclustered索引
　　在非聚集索引创建之前创建聚集索引(否则会引发索引重建)
　　select INDEXPROPERTY (OBJECT_ID('table_name'),'index_name','IndexDepth')
　　在 SQL Server 2005可以通过SQL Server Management Studio查看
　　微软官方社区 http://technet.microsoft.com/zh-cn/library/ms187729.aspx
　　如何查看sql语句执行时磁盘I/O操作信息 SET STATISTICS IO ON
　　测试时释放缓存，2000里只能用dbcc unpintable，而且这个操作也不会立即释放表内存Buffer
　　测试时释放缓存，2005可用CHECKPOINT建立检查点、DBCC DROPCLEANBUFFERS从缓冲池中删除所有清除缓冲区、DBCC FREEPROCCACHE从过程缓存中删除所有元素、DBCC FREESYSTEMCACHE从所有缓存中释放所有未使用的缓存条目、sp_cursor_list 查看全部游标、DBCC OPENTRAN查看数据库打开事务状态、
　　如果一个表格中的两个索引共享一个盘区的同一个空间，而这两个索引并不相邻，那么最好重新建立索引让它们相邻
　　当建立一个索引时，该索引的填充因子即被设置好了，因此填充因子不能动态维护，要更新填充因子必须重建索引
　　2000以上可进行DBCC INDEXDEFRAG在线索引维护，也可以使用DBREINDEX重建索引(重建索引可以更好的消除碎片)
　　DBCC SHOWCONTIG来查看统计量的状态
　　执行该表格的UPDATE STATISTICS命令，SQL Server就刷新了关于该索引的信息了
　　每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。
　　聚集索引来实现下列功能：
　　* 　　可用于经常使用的查询。
　　* 　　提供高度唯一性。
　　聚集索引与主键不一样，列的数据可以是null，数据列可能不唯一，但是系统会替你产生一个你看不到的唯一列
　　newid 产生的新的值是不固定的，所以新的值导致索引B+树的变化是随机的。而 newsequentialid 产生的新的值是有规律的，则索引B+树的变化是有规律的
　　Guid 有规律时速度要比无规律时候快
　　Heaps是staging data的很好选择，当它没有任何Index时
　　* 　　Excellent for high performance data loading (parallel bulk load and parallel index creation after load)
　　* 　　Excellent as a partition to a partitioned view or a partitioned table
　　聚集索引叶子节点就是具体数据，所以 聚集索引的覆盖率是 100%
　　Indexing for AND时，要优化SQL执行计划，把容易查询的、匹配数量少的放在左边
　　Indexing for OR：如果有In搜索关键字的对应索引。则系统会使用这个索引
　　UNION ALL比UNION在数据量多的时候，要快很多
　　##########################
　　SQL Server 支持block nested loops、index nexted loops、sort-merge、hash join以及hash team
　　Hash Join特点：处理大量、未排序、无索引的数据
　　Hash Join也用于许多集合比较操作，inner join、left/right/full outer join、intersect、difference等等
　　Hash Join较大限制是它只能应用于等值联结(equality join)
　　Nested Loop Join (嵌套循环联结)
　　适用于一个集合大而另一个集合小的情况(将小集合做为外循环)，I/O性能不错。
　　当外循环输入相当小而内循环非常大且有索引建立在JOIN字段上时，I/O性能相当不错。
　　当两个集合中只有一个在JOIN字段上建立索引时，一定要将该集合作为内循环。
　　对于一对一的匹配关系(两个具有唯一约束字段的联结)，可以在找到匹配元组后跳过该次内循环的剩余部分(类似于编程语言循环语句中的continue)。
　　Merge Join (合并联结)
　　可以不是等值联结
　　MERGE JOIN 必须等待两个单独的SORT JOIN完成(如果使用索引作为数据源,可以跳过SORT JOIN这个步骤), 如果两个表的规模相差很大, 会很影响查询的性能。
　　当查询的两个表都很大或者都很小的时候， 则应该只用MERGE JOIN，如果两个表都很小，则表扫描和分类将进行的很快;
　　##########################
　　可以用Database Maintenance Plan Wizard ，DMPW维护和优化索引
　　每秒1500条数据、千万并发执行的经验：
　　如果一个表有上千万的数据，加索引和不加索引的select时间相差很大
　　一个表只执行bcp方式的insert和select(表上加clustered索引，用于主键或查询where中的id)，则不会引起索引页面的大量数据移动和重新排序，select和insert会一直保持很快
　　bcp方式会自动判断文件是否已经导入，或部分数据已经导入