2014 年 2月随笔档案 - 笑东风

TSQL--验证身份证是否有效

摘要：/****** Object: UserDefinedFunction [dbo].[udf_IsvalidIDCard] Script Date: 02/27/2014 16:03:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[udf_IsvalidIDCard](@IDCardNo VARCHAR(50)='')RETURNS BITAS/**************************************************************** 阅读全文

posted @ 2014-02-28 09:07 笑东风阅读(1486) 评论(2) 推荐(0) 编辑

连接池--sp_reset_connection

摘要：--当客户端使用连接池访问数据库时，客户端使用OPEN来重用数据库连接，使用CLOSE来断开数据库连接，但并不物理上新建和断开连接，因此可以提高程序运行速度并降低性能损耗。--ADO和ADO.NET程序会默认启用连接池--如果一个连接未提交或回滚事务便在客户端显示close,则事务会被一直保持到该连接被重用时被释放.--第一次建立的连接不会调用sp_reset_connection--sp_reset_connection会在SQLSERVER里做些什么？--1、清除连接现有所有内部数据结构。包括：--清除所有openxml打开的document句柄--关闭所有的游标(cursor)--释放所阅读全文

posted @ 2014-02-25 17:39 笑东风阅读(6609) 评论(0) 推荐(1) 编辑

疑难杂症--性能计数器注册表配置单元一致性

摘要：背景:在XP上安装SQL SERVER 2008 R2时，在安装检查时报错"性能计数器注册表配置单元一致性",网上查找了下，成功找到解决方案。问题原因：由于各种软件安装和卸载中出现问题，导致性能计数器的相关的注册表值不一致解决办法：修改注册表，使性能计数器的相关的注册表值一致解决步骤：1. 运行命令"REGEDIT" 来打开注册管理表编辑器2. 找到注册表项"[HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Windows NT/CurrentVersion/Perflib]",如果系统为中文,则选择子注册阅读全文

posted @ 2014-02-25 16:23 笑东风阅读(9564) 评论(2) 推荐(0) 编辑

CHARPTER 3--INDEX DMVs

摘要：1.查找最重要的缺失的索引　--=======================================================--查找最重要的缺失的索引SELECT TOP(20)DB_NAME() AS DBName,ROUND(s.avg_total_user_cost*s.avg_user_impact*(S.user_seeks+S.user_scans),0) AS [TotalCost],D.[statement] AS TableName,D.equality_columns,D.inequality_columns,D.included_columnsINTO 阅读全文

posted @ 2014-02-24 23:08 笑东风阅读(265) 评论(2) 推荐(0) 编辑

连接池--在密码修改的影响

摘要：群里有朋友问起连接池在密码修改后的变化，特地做了下测试使用连接池，连接字符串为：string connString = "Data Source=.;Initial Catalog=DemoDB1;Integrated Security=False;User ID=DBA;Password=XXXXX;Pooling=True;Connect Timeout=60";测试1数据库连接打开后不断开(不调用close方法)，连续请求using (SqlConnection conn = new SqlConnection(connString)) { ... 阅读全文

posted @ 2014-02-19 21:26 笑东风阅读(907) 评论(0) 推荐(1) 编辑

统计--追踪数据更新情况

摘要：背景: 在统计自动更新设置为ON时(默认设置),当更新次数达到一定阀值后便会触发自动更新。本文通过一些测试来探究SQL SERVER 如何追踪数据更新情况和如何计算更新次数。--==========================================--可以使用以下系统表来查看列变化情况... 阅读全文

posted @ 2014-02-18 12:04 笑东风阅读(483) 评论(0) 推荐(0) 编辑

统计--自动更新统计

摘要：当数据库设置为自动更新统计后，SQL Server 监控表中的数据更改，当更改满足一下条件之一时更新：1.向空表插入数据时 2.少于500行的表增加500行或者更多 3.当表中行多于500行时，数据的变化量大于20%时（在SQL SERVER 2000中,指的是20%的行被修改，而在SQL SERVER 2005/2008中，指的是20%的列数据被修改）可以手动使用UPDATE STATISTICS 或EXEC sys.sp_updatestats来更新统计UPDATE STATISTICS 需要锁表UPDATE STATISTICS table_or_indexed_view_name 阅读全文

posted @ 2014-02-18 11:57 笑东风阅读(3171) 评论(0) 推荐(0) 编辑

Index--复合索引的思考1

摘要：在创建复合索引时，除了考虑索引键的选取外，还需考虑索引键的先后顺序。下面借助一些场景来讲解。场景1表dbo.UserLoginStats记录每个用户每天的登录统计，目前表中存放10亿数据，每天新增数据500W（每天每个用户很少几条条记录），目前系统有用户8000W,有查询：SELECT * FROM dbo.UserLoginStatsWHERE UserID=@userIDAND LoginDay=@loginDay对于此查询，可以创建索引：CREATE INDEX IX_UserID_LoginDay ON dbo.UserLoginStats(UserID,LoginDay)或CREAT 阅读全文

posted @ 2014-02-17 12:16 笑东风阅读(2272) 评论(12) 推荐(6) 编辑

SQL SERVER 数据压缩

摘要：从SQL SERVER 2008开始，SQL SERVER 提供了对数据进行压缩的功能，启用数据压缩无须修改应用程序。数据压缩可有效减少数据的占用空间，读取和写入相同数据花费的IO也响应减少，从而可以有效缓解IO压力，但由于数据在读取和写入时需要压缩和解压缩，因此会消耗CPU资源，但不代表在相同负载下，启用数据压缩会导致CPU的使用率变高，某些操作会因为数据页数量的减少而降低CPU资源的消耗。可以配置数据压缩的对象有：1>存储为堆的整个表。2>存储为聚集索引的整个表。3>整个非聚集索引。4>整个索引视图。5>对于已分区表和已分区索引，可为每个分区配置压缩选项，且对阅读全文

posted @ 2014-02-11 20:32 笑东风阅读(11086) 评论(0) 推荐(0) 编辑

Replication--复制与CDC和镜像

摘要：复制和CDC 都是使用logreader来从日志中读取数据的变更，然后写入到分发库(复制)或变更表中。1> 单独创建复制发布在该情况下，会在分发服务器上创建日志读取代理作业(ServerName_DBName_logreaderID)2> 单独使用CDC在该情况下，会在当前实例的代理上创建两个作业‘cdc.DBName_capture’(调用sys.sp_MScdc_capture_job来读取日志)和‘cdc.DemoDB3_cleanup’（调用sys.sp_MScdc_cleanup_job来清理过期的变更历史记录）3>先创建复制发布，再使用CDC创建复制时在分发服务器阅读全文

posted @ 2014-02-10 22:35 笑东风阅读(3046) 评论(1) 推荐(0) 编辑

Mirror--如何在主库上增加文件

摘要：由于各种原因，如磁盘不空不足，需要对主库增加数据库文件到其他磁盘上，而镜像服务器上没有对应盘符，很多人会选择删除镜像，重新完备还原来搭建镜像，这种方式耗时耗力。在做此类操作时，需要对主服务器和镜像服务器的环境镜进行检查，依据情况采用不同的方式处理。1> 主库服务器和镜像服务器配置一样，且新增加文件的路径保持一样在这种情况下，只需要保证镜像服务器上有相同的目录存在即可,SQL Server会将新增文件操作自动同步到镜像库上（镜像服务器上目录需手动建立）2>主库服务器和镜像服务器配置不一样，或新增文件的路径需要更改如新增文件路径为F:\DB\DB1.ndf,而镜像服务没有F盘，需要放置阅读全文

posted @ 2014-02-10 15:38 笑东风阅读(486) 评论(1) 推荐(1) 编辑

曲苑杂坛--收缩数据库文件

摘要：很多人在删除大量数据后收缩数据库，却发现没法收缩到预期效果。由于使用DBCC SHRINKFILE来收缩数据文件时，是针对数据区来收缩，因此可以先使用DBCC SHOWFILESTATS来查看文件中未使用的分区数(TotalExtents-UsedExtents)，如果删除大量数据但未使用分区数比较小，可以先重建索引再收缩数据文件。测试数据：USE DB006GOCREATE TABLE TB1( ID INT IDENTITY(1,1), C1 CHAR(8000))GOINSERT INTO TB1(C1)SELECT '1' FROM sys.all_columnsGOD 阅读全文

posted @ 2014-02-10 12:57 笑东风阅读(242) 评论(0) 推荐(0) 编辑

常用脚本--SQL Server获取OS日志

摘要：--===================================================--SQL Server获取OS日志：declare @start_date varchar(21), @end_date varchar(21), @out_server varchar(50), @cmd varchar(200)select @start_date='12/31/2012,11:59:59PM', @end_date='04/11/2013,11:59:59PM', @out_server='ebj2454'select 阅读全文

posted @ 2014-02-10 12:49 笑东风阅读(358) 评论(0) 推荐(0) 编辑

常用脚本--查看数据库最近备份

摘要：--===========================================================================--查看最近一次备份时间SELECT D.name AS DatabaseName,FB.FullBackupTime,LB.LogBackupTimeFROM master.sys.databases D INNER JOIN sys.database_mirroring dm on D.database_id=dm.database_idAND (dm.mirroring_guid IS NULL OR dm.mirroring_rol. 阅读全文

posted @ 2014-02-10 12:47 笑东风阅读(396) 评论(0) 推荐(0) 编辑

常用脚本--将指定的字符串拆分多行数据

摘要：--==============================================--将指定的字符串拆分多行数据--==============================================ALTER FUNCTION dbo.ufn_SplitString( @SourceSql VARCHAR(MAX) , @StrSeprate VARCHAR(10))RETURNS @temp TABLE (C1 VARCHAR(MAX) )ASBEGIN DECLARE @i INT SET @SourceSql = RTRIM(LTRIM(@Sour... 阅读全文

posted @ 2014-02-10 12:44 笑东风阅读(889) 评论(0) 推荐(0) 编辑

常用脚本--查看数据库文件大小

摘要：--============================================================================--查看数据库文件大小SELECT DB_NAME(F.database_id) AS DBName,F.name AS LogicName,F.size*8/1024 AS SizeMBFROM sys.master_files FINNER JOIN sys.databases DON D.database_id=F.database_idINNER JOIN sys.database_mirroring dm on D.databas 阅读全文

posted @ 2014-02-10 12:37 笑东风阅读(640) 评论(0) 推荐(0) 编辑

常用脚本--查看数据库中各表的行数和大小

摘要：--======================================--查看各表的行数--is_ms_shipped=0代表用户创建的表--(I.indid=1 OR I.indid=0)代表聚簇索引和堆表SELECT T.name AS TableName,I.rows AS Rows... 阅读全文

posted @ 2014-02-10 12:36 笑东风阅读(2371) 评论(0) 推荐(0) 编辑

Snapshot--使用Snapshot来还原数据库

摘要：在数据库升级时，为防止升级失败造成的影响，我们通常需要：1.准备回滚脚本，用于失败后回滚2.在升级前备份数据库，用于失败后恢复但回滚脚本需要花费很长时间准备，而备份恢复会导致数据库长时间不可用，为解决此类问题，可以使用数据库快照，数据库快照生成速度和还原速度都远快于备份，且操作简单。--==========================================================--创建数据库快照CREATE DATABASE BackupTestDB_SNAP ON (NAME='BackupTestDB',FILENAME='D:\DB\Bac 阅读全文

posted @ 2014-02-10 12:31 笑东风阅读(2012) 评论(0) 推荐(0) 编辑

DMV--sys.dm_os_ring_buffers

摘要：DMV 'sys.dm_os_ring_buffers' 可以用来诊断数据库连接和数据库内存方面的问题，但MSDN上找不到相应的介绍，网上找到以下相关资料：1>sys.dm_os_ring_buffers 的创建脚本和相关介绍http://www.g-productions.nl/index.php?name=dm_os_ring_buffers&version=2005RTM2>利用Ring Buffer在SQL Server 2008中进行连接故障排除http://blogs.msdn.com/b/apgcdsd/archive/2011/11/21/r 阅读全文

posted @ 2014-02-08 10:54 笑东风阅读(1053) 评论(0) 推荐(1) 编辑

Replication--复制相关的作业

摘要：复制使用下列作业来执行计划维护和按需维护作业名称说明默认调度代理历史记录清除：分发从分发数据库中删除复制代理历史记录。每十分钟运行一次分发清除：分发从分发数据库中删除复制的事务。停用在最大分发保持期内尚未同步的订阅。每十分钟运行一次过期订阅清除从发布数据库检测和删除过期的订阅。每天凌晨 1:00 运行重新初始化数据验证失败的订阅检测所有未通过数据验证的订阅并标记它们以进行重新初始化。下次合并代理或分发代理运行时，订阅服务器上将应用新快照。无默认调度（默认情况下未启用）。复制代理检查检测未积极记录历史信息的复制代理。如果作业步骤失败，它将写入 Microsoft Wi 阅读全文

posted @ 2014-02-07 23:04 笑东风阅读(309) 评论(0) 推荐(0) 编辑

优化案例--多语句表值函数的影响

摘要：在SQL SERVER中，自定义函数可以划分成：1.内联表值函数2.多语句表值函数3.标量值函数上述三类自定义函数如果使用不当，就会造成性能问题，本片重点关注“多语句表值函数”。在多语句表值函数在每次调用时都需要使用到一个临时表来存放返回值，因此如果频繁调用该函数，会影响tempdb的性能。测试代码：--=========================================================================--创建测试表GOSELECT * INTO TB001 FROM sys.all_objectsGOSELECT * INTO TB002 FRO 阅读全文

posted @ 2014-02-07 16:00 笑东风阅读(2746) 评论(1) 推荐(0) 编辑

常用脚本--Kill所有连接到指定数据库上的回话

摘要：USE [master]GO/****** Object: StoredProcedure [dbo].[Sp_KillAllProcessInDB] Script Date: 02/07/2014 12:04:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[usp_KillAllProcessInDB] @DbName VARCHAR(100)ASBEGIN IF DB_ID(@DbName) = NULL BEGIN PRINT 'DataBase dos... 阅读全文

posted @ 2014-02-07 12:08 笑东风阅读(438) 评论(0) 推荐(0) 编辑

常用脚本--查看当前锁信息

摘要：USE [master]GO/****** Object: StoredProcedure [dbo].[p_lockinfo] Script Date: 02/07/2014 11:54:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROC [dbo].[usp_lockinfo]@kill_lock_spid BIT=0, --是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock BIT=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示ASBEGIN ... 阅读全文

posted @ 2014-02-07 12:02 笑东风阅读(529) 评论(0) 推荐(0) 编辑

常用脚本--查看死锁和阻塞usp_who_lock

摘要：USE [master]GO/****** Object: StoredProcedure [dbo].[sp_who_lock] Script Date: 02/07/2014 11:51:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_who_lock]ASBEGINDECLARE @spid INT,@bl INT,@intTransactionCountOnEntry INT, @intRowcount INT, @intCou... 阅读全文

posted @ 2014-02-07 11:54 笑东风阅读(834) 评论(0) 推荐(0) 编辑

常用脚本--在线重建或重整实例下所有索引

摘要：不二话，直接上CODEUSE [master]GO/****** Object: StoredProcedure [dbo].[usp_auto_indexdefrag_online] Script Date: 02/07/2014 11:44:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_auto_indexdefrag_online]ASBEGINSET NOCOUNT ON DECLARE @Db_name NVARCHAR(256) ,@SchemaNa... 阅读全文

posted @ 2014-02-07 11:50 笑东风阅读(742) 评论(2) 推荐(0) 编辑

常用脚本--生成Agent Job的创建脚本

摘要：USE [master]GO/****** Object: StoredProcedure [dbo].[DumpJobsql] Script Date: 02/07/2014 11:38:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_DumpJobsql]ASBEGIN/********************************************************************* 过程说明: 只适用于不带邮件、报警的作业 ... 阅读全文

posted @ 2014-02-07 11:45 笑东风阅读(1090) 评论(0) 推荐(0) 编辑

常用脚本--归档ERRORLOG

摘要：SQL Server error log 7组日志文件默认情况下不会自动切换到下一个文件,一般在SQL Server 重新启动后才会切换error log,如果SQL Server长期未重启或因为某些错误导致当前文件包含大量日志文件，可以使用DBCC ErrorLog来切换或者使用EXEC sp_c... 阅读全文

posted @ 2014-02-07 11:15 笑东风阅读(558) 评论(0) 推荐(0) 编辑

疑难杂症--由于系统缓冲区空间不足或队列已满，不能执行套接字上的操作

posted @ 2014-02-07 11:06 笑东风阅读(46613) 评论(3) 推荐(1) 编辑

常用脚本--生成指定表的INSERT 语句

摘要：--=================================================--摘抄自http://www.cnblogs.com/sunth/archive/2013/06/05/3118312.html--用法:--EXEC [dbo].[usp_GenInsertSQL] 'dbo.TB1'--=================================================USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate proc [dbo].[u 阅读全文

posted @ 2014-02-06 10:34 笑东风阅读(728) 评论(0) 推荐(2) 编辑

02 2014 档案

导航

公告

搜索

随笔分类 (383)

随笔档案 (417)

文章分类 (9)

SQL SERVER