2012年4月 的存档
2012四月29

待建立的索引及要删除的索引

sql server 评论关闭

1,查询待建立的索引。sys.dm_db_missing_index_groups,sql如下:


SELECT * FROM (
	SELECT TOP 50
	ROUND(s.avg_total_user_cost * (s.avg_user_impact/100) *
	(s.user_seeks + s.user_scans),0) AS [improvement_measure]
	, s.avg_user_impact
	, d.statement AS TableName
	, d.equality_columns
	, d.inequality_columns
	, d.included_columns,
	  'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)
		  + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'
		  + ' ON ' + d.statement
		  + ' (' + ISNULL (d.equality_columns,'')
			+ CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
			+ ISNULL (d.inequality_columns, '')
		  + ')'
		  + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
	FROM sys.dm_db_missing_index_groups g
	INNER JOIN sys.dm_db_missing_index_group_stats s
	ON s.group_handle = g.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details d
	ON d.index_handle = g.index_handle
	ORDER BY improvement_measure DESC)M
WHERE m.improvement_measure > 10000 --大于10000者需要重建

2,注:1),avg_user_impact,用户查询可能获得的平均百分比收益,因是数字,故要除以100,以取得可获取的提高性能的倍数。2),此方法有局限性,指定的建立索引列的并不都是正确的,要手工判定

3,查询可删除的索引,很少使用。


Select Top 30 database_id, t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) ,t.name,ix.name,sc.name
    from sys.dm_db_index_usage_stats ius
    JOIN sys.tables t ON ius.object_id = t.object_id
    JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id
    JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id
    JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id
    where user_updates > 10 * (user_seeks+user_scans)
    and ius.index_id > 1
    AND CHARINDEX('merge',t.name) <1
    order by user_updates / (user_seeks+user_scans+1) DESC
    
2012四月28

SQL Server DBA工作内容详解

在Microsoft SQL Server 2008系统中,数据库管理员(Database Administration,简称为DBA)是最重要的角色。DBA的工作目标就是确保Microsoft SQL Server 2008系统正常高效地运行。DBA的工作也是最繁忙的工作,无论是性能调整,还是灾难恢复,都离不开DBA的支持。

  一般地,作为一个DBA,至少应该做好以下12项任务:

  •   任务一:安装和配置;
  •   任务二:容量规划;
  •   任务三:应用架构设计;
  •   任务四:管理数据库对象;
  •   任务五:存储空间管理;
  •   任务六:安全管理;
  •   任务七:备份和恢复;
  •   任务八:性能监视和调优;
  •   任务九:调度作业;
  •   任务十:网络管理;
  •   任务十一:高可用性和高可伸缩性管理;
  •   任务十二:故障解决;

  下面简单描述这些DBA的任务

  任务一:安装和配置。

   DBA的第一项任务是安装和配置Microsoft SQL Server 2008软件系统,为顺利使用Microsoft SQL Server 2008软件创建良好的环境。无论是安装还是配置,都应该根据实际需要来进行,使得系统满足用户的实际需求。需要注意的是,系统配置不是一劳永逸的,应该 随时根据需求的变化和环境的需要,进行监视和适当地调整。

  任务二:容量规划。

  容量规划是对整个Microsoft SQL Server 2008系统进行一个总体的规划。规划的重点应该放在解决瓶颈问题上。可以从内容和期限两个方面考虑系统的容量规划。

   从内容上来看,应该考虑的主要内容包括:硬件容量规划、软件规划、网络规划。硬件容量规划包括磁盘空间、CPU、I/O等规划。软件规划包括操作系统的 安装和配置规划、数据库规划、数据库对象内容和数量规划等。网络规划包括网络硬件、网络软件和协议、网络客户数量流量和分布、网络拓扑结构等规划。

   从期限上来看,应该考虑短期、中期和长期规划。短期规划的目的是满足当前日常业务的需要。中期规划主要是满足业务发展和扩大的需要。长期规划主要是满足 业务极限需要等。例如,如果预测某个系统的当前并发用户数量是1000,3年后的用户可能达到1000万,那么这时既不能按照1000用户的需求来设计, 也不能一下子按照1000万用户的需求来设计,一定要采取一个折中的形式。

  任务三:应用架构设计。

  应用架构设计包括数据库设计、应用程序设计和相应的技术架构设计。

  数据库设计应该考虑数据库的逻辑需求、数据库的创建方式和数量、数据库数据文件和日志文件的物理位置等。一般情况下,可以在Microsoft SQL Server 2008系统成功安装之后,根据规划的目标,手工创建数据库。

  应用设计应该考虑开发工具的选择、API技术、内部资源和外部资源的结合、应用架构的分布等。需要强调是在应用设计时,DBA应该与开发人员共同工作,确保他们编写出优化的代码,尽可能地使用服务器的资源。

  技术架构设计主要包括表示层、逻辑层和数据层的分布。这些分布不应该考虑到硬件资源和用户需求。既不能片面地追求过高的硬件资源,也不能仅仅局限于当前的环境,一定要按照可扩展的观点来综合考虑。

  任务四:管理数据库对象。

  管理数据库对象是使用数据库的最基本、最重要的工作。这些对象包括表、索引、视图、存储过程、函数、触发器、同义词等。为了完成管理数据库对象的工作,DBA应该能够很好地回答诸如下面的这些问题。

  •   系统应该包括哪些数据?
  •   应该怎样存储这些数据?
  •   应该在系统中创建哪些表?
  •   应该在这些表中创建哪些索引,以便加速检索?
  •   是否应该创建视图?为什么要创建这些视图?
  •   应该创建哪些存储过程、函数、CLR对象?
  •   应该在哪些表上创建触发器?应该针对哪些操作创建触发器?
  •   是否应该创建同义词?

  任务五:存储空间管理。

  存储空间管理任务就是怎样为数据分配空间、怎样保持空间可以满足数据的不断增长。随着业务量的继续和扩大,数据库中的数据也会逐渐地增加,事务日志也不断地增加。存储空间管理任务主要围绕下面几个问题。

  •   当前的数据库由那些数据文件组成?
  •   事务日志的大小应该如何设置?
  •   数据的增长速度是多大?
  •   如何配置数据文件和日志文件的增长方式?
  •   数据库中的数据何时可以清除或转移到其他地方?

  任务六:安全管理。

  安全性是DBA重要的日常工作之一。安全管理的主要内容包括账户管理和权限管理。账户管理就是在数据库中应该增加哪些账户、这些账户应该组合成哪些角色等等。权限管理是对象权限和语句权限的管理,应该回答下面这些问题:

  •   这些账户或角色应该使用哪些对象?
  •   这些账户或角色应该对这些对象执行哪些操作?
  •   这些账户或角色应该在数据库中执行哪些操作?
  •   如何设置架构?如何建立架构和对象、架构和用户的关系?

  任务七:备份和恢复。

   无论系统运行如何,系统的灾难性管理是不可缺少的。天灾、人祸、系统缺陷都有可能造成系统的瘫痪、失败。怎样解决这些灾难性问题呢?办法就是制订和实行 备份和恢复策略。备份就是制作数据的副本,恢复就是将数据的副本复原到系统中。备份和恢复工作是DBA的一项持续性的重要工作,其执行频率根据数据的重要 程度和系统的稳定程度来确定。

  任务八:性能监视和调优。

  根据企业的经营效益评价企业的管理水平,根据学生的考试成绩 评价学生的学习好坏。作为一个大型软件系统,Microsoft SQL Server 2008系统的运行好坏必须得到正确地监视、评价和相应的调整。这是DBA的一项高级工作。借助一些工具和运行性能指标,DBA应该能够监视系统的运行。 如果某些运行指标出现了问题,DBA应该及时地采取补救措施,使得系统始终保持高效运行状态。

  任务九:调度作业。

  DBA不可能一天24小时不停地盯住系统的运行,及时地执行某些指定的操作。Microsoft SQL Server 2008系统提供了许多工具,DBA应该充分利用这些工具和机制,解决下面一些问题。

  •   调度哪些作业应该由系统执行?
  •   这些作业应该在何时执行?
  •   如何确保这些作业可以正确地执行?
  •   如果自动执行的作业执行失败时,应该如何处理?
  •   如何使得系统可以均衡地执行相应的操作?

  任务十:网络管理。

  作为一种分布式的网络数据库,网络管理的任务更加的重要。Microsoft SQL Server 2008系统提供了网络管理工具和服务,DBA应该借助这些工具进行服务规划和管理网络操作。

  任务十一:高可用性和高可伸缩性管理。

   作为一个DBA,必须保持系统具有高可用性和高可伸缩性。可用性是一项度量计算机系统正常运行时间的指标。可伸缩性描述应用程序可以接受的并发用户访问 的数量问题。影响系统可用性的主要因素包括:网络可靠性、硬件故障、应用程序失败、操作系统崩溃、自然灾害等。无论是数据库系统管理员,还是应用程序设计 人员,都应该最小化系统破坏的几率,最大化系统的可用性。在设计系统的可用性时,应该确定采取什么样的可用性策略来满足可用性的需求。

   可用性的需求可以通过3个方面描述,即运行的时间、连接性需求和数据的紧密和松散要求。在确定可用性的需求时,首先考虑系统的运行时间。一般地,数据库应 用程序有两种运行时间,即在工作时间是可用的和在任何时间都是可用的。如果只是要求在工作时间是可用的,那么可以把系统的维护等工作安排在周末进行。但 是,有许多应用程序要求每天运行24小时、每周运行7天,例如,在线超市等,这时必须采取措施保证系统总是运行的。不同的应用程序有不同的连接性要求。大 多数的应用程序和电子商务解决方案要求采用可靠的网络连接。这时,要求永久性的在线连接,必须最小化各种异常现象的发生。有些应用程序允许用户离线使用。 这时,系统的可用性要求降低了。大多数应用程序要求数据是同步使用的。用户对数据的请求,系统必须立即做出回应。这是紧密型的数据要求,这种情况必须保证 系统的高可用性。有些应用程序不需要数据是同步的,对用户的请求可以延迟回应。这种要求是数据松散型的要求,这时系统的可用性需求比较低。

  任务十二:故障解决。

   虽然不希望Microsoft SQL Server 2008系统出现故障,但是故障可能是无法避免的。这些故障可能每天都会发生。有些故障是人为不小心造成的,有些故障可能是系统中的缺陷形成的,有些故障 可能是莫名其妙的。作为一个DBA,在系统中的其他用户心目中是Microsoft SQL Server系统的权威。无论是大事还是小事,DBA都应该做到迅速诊断、准确判断、快速修复。从这个意义上来说,DBA是一个数据库系统的专业医生。

原文出处:http://www.mysqlops.com/2011/11/03/sql-server-dba.html
2012四月24

sql基线建立-知识准备

sql server 评论关闭
  1. 1,SQLdiag is a successor of PSSdiag. SQL Nexus is a tool used to load and analyze performance data collected by SQLdiag
  2. SQLdiag作用是收集以下信息。
  • Windows服务器的软硬件信息(通过MSInfo32公用程序提取)
  • Windows性能计数器,事件记录
  • SQLSERVER服务器Profiler跟踪
  • SQLSERVER错误记录
  • SQLSERVER封锁信息
  • SQLSERVER分配信息
  • 自定义信息。
  • 可用pssdiag图形化配置:http://diagmanager.codeplex.com/

   3,SQLdiag信息

  • 存在于\Program Files\Microsoft SQL Server\90\Tools\Binn目录下
  • 日志文件放在\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLdiag目录下。
  • 指定配置文件产生地址:sqldiag -PC:\SQLdiag,会产生以下配置文件

    •  MSDiagProcs.sql:对sqlserver实例进行的初始化操作,多是在Tempdb系统库上建立对象。
    • SQLDiag.XML:默认读取的配置文件,提供标准的数据搜集,包含Perfmon,SqlServer错误处理,设置状态等。
    • SD_Detailed.XML:设置SQLDiag.XML更详细的信息
    • SD_General.XML:设置SQLDiag.XML精简的信息
    • SQLDiag_Schema.XSD:用来编写,修改,验证既有的XML配置文件
  • 4,SQLDiag.XML配置。各节点中,EventlogCollector:收集系统信息,PerfmonCollector收集监控系统,Instances收集SQLSERVER信息
  • 默认的Instances的事件是各跟踪事件信息,对应事件可在sys.trace_events中查看。
  • sqldiag的唯一缺点是不会定期查询系统视图,

    所以若加上PerfStatsScript.sql监视脚本输出,就比较全面了。
    PerfStatsScript:说明:建立两个存储过程,
        1)sp_perf_stats09:–列出最耗时的请求及找到阻塞源头,#tmp_requests:查询当前活动的请求,任务,等基本信息–#tmp_requests2:据#tmp_requests查询当前的活动事务,阻塞的进程
        2)sp_perf_stats_infrequent09:收集sqlserver的性能计数器,如内存管理,CLR等sys.dm_os_performance_counters,CPU运行情况,I/O情况
        每10分钟执行一次sp_perf_stats09,每小时执行一次sp_perf_stats_infrequent09

    定制执行SQL
    <CustomTask enabled="true" groupname="ByronSP" taskname="ReusePlanPercentage" type="TSQL_Command"  point="Startup"  wait="OnlyOnShutdown" cmd="exec tempdb.dbo.GetTable" />
    输出结果在:
    SQLDI\taskname_Startup.OUT

    sql是执行一次就结束了,若想多次执行,

    则调用spPollSP存储过程,定时执行。
    if object_id('dbo.spPollSP') IS NOT NULL –加在MSDiagProcs.sql内
    DROP PROC DBO.spPollSP
    GO
    PRINT ''

    RAISERROR('====建立存储过程dbo.spPollSP,以重复调用其他存储过程',0,1)
    WITH NOWAIT
    GO

    CREATE PROC dbo.spPollSP @spName sysname, @interval char(8)='00:01:00'
    AS
    DECLARE @SQL NVARCHAR(200)
    SET @SQL = 'EXEC ' +@spName + '; WAITFOR DELAY ''' + @interval + ''''
    WHILE 1=1
        EXEC(@SQL)
    GO

    sqldiag参数
    如:sqldiag -Pc:\sqldiag /B +00:01:00 /E +00:10:00 /n 1 /L
    1分钟后开始执行,10分钟后停止。连续运行

    以服务运行,直到10:19分时停止
    sqldiag -P"C:\sqldiag" -E20120508_10:23:00 -N2 -R
    net start sqldiag

    卸载:
    net stop sqldiag
    sqldiag -u

    日志文件转成csv文件:
    relog SQLDIAG.BLG -f csv -o "C:\sqldiag\SQLDIAG_00001\a
    .csv"

    relog sqldiag.blg -q -o counter.txt — 查看记数器

    /I configuration_file
    设置 SQLdiag 要使用的配置文件。默认情况下,/I 设置为 SQLDiag.Xml。

    /O output_folder_path
    将 SQLdiag 输出重定向到指定文件夹。如果未指定 /O 选项,则 SQLdiag 输出结果将会写入 SQLdiag 启动文件夹下名为 SQLDIAG 的子文件夹中。如果 SQLDIAG 文件夹不存在,则 SQLdiag 将会尝试创建该文件夹。

    /P support_folder_path
    设置支持文件夹路径。默认情况下,将 /P 设置存放 SQLdiag 可执行文件的文件夹。

    /B [+]start_time
    按照以下格式指定开始收集诊断数据的日期和时间:

    按照以下格式指定开始收集诊断数据的日期和时间:

    YYYYMMDD_HH:MM:SS

    使用 + 并且不带日期(只使用 HH:MM:SS),可以指定相对于当前日期和时间的时间。例如,如果指定 /B +02:00:00,则 SQLdiag 将会在 2 小时后开始收集信息。

    不要在 + 和指定的 start_time 之间插入空格。

    /E [+]stop_time
    按照以下格式指定停止收集诊断数据的日期和时间:

    使用 /B +02:00:00 /E +03:00:00 指定开始时间和结束时间,则 SQLdiag 将会在 2 小时后开始收集信息,经过 3 小时信息收集后停止收集并退出。

    /R
    将 SQLdiag 注册为服务。您将 SQLdiag 注册为服务时指定的所有命令行参数,都将留到以后用来运行该服务。

    /U
    撤消 SQLdiag 服务注册。

    /N output_folder_management_option ,如:sqldiag /N 1
    设置 SQLdiag 在其启动时,是覆盖还是重命名输出文件夹。可用选项包括:

    1 = 覆盖输出文件夹(默认)

    2 = 当 SQLdiag 启动时,将输出文件夹重命名为 SQLDIAG_00001、SQLDIAG_00002 等等。重命名当前输出文件夹之后,SQLdiag 将输出写入默认输出文件夹 SQLDIAG。

    /L:连续运行sqldiag

    5,一般设置提纲为:记录两小时,每4秒记录一次,记录一天,每30秒记录一次,记录5天,每180秒记录一次,以免影响系统性能,且能放映系统持久变化。

  • 要收集的perfmon信息在PerfmonCounters下面增加:

1,内存
<PerfmonObject name="\Memory" enabled="true">
                          <PerfmonCounter name="\Available MBytes" enabled="true" />
                          <PerfmonCounter name="\Page Faults/sec" enabled="true" />
                          <PerfmonCounter name="\Pages/sec" enabled="true" />
                        </PerfmonObject>
 Pages/sec:磁盘进行读取或写入的频率,平均值应低于20
 Available MBytes:低于物理内在的10%,就会性能影响,100M以上是严重性能问题
2,磁盘
  <PerfmonObject name="\PhysicalDisk(*)" enabled="true">
                          <PerfmonCounter name="\% Disk Time" enabled="true" />
                          <PerfmonCounter name="\Avg. Disk Queue Length" enabled="true" />
                          <PerfmonCounter name="\Current Disk Queue Length" enabled="true" />
                        </PerfmonObject>
% Disk Time:磁盘读取/写入操作的时间百分比,若平均值>70%,说明硬盘有问题,可进一步查询是否是内存不足导致,当使用RAID设备时,此值有可能>100%,此时要看PhysicalDisk:Avg.Disk Queue Length计数器的值,此值越低越好。
Current Disk Queue Length:正在等待磁盘访问的请求,小于2,越低越好。
3,处理器
 <PerfmonObject name="\Processor(*)" enabled="true">
                          <PerfmonCounter name="\% Processor Time" enabled="true" />
                          <PerfmonCounter name="\% Privileged Time" enabled="true" />
                        </PerfmonObject>
% Processor Time:持续值应低于80
Privileged Time:执行Windows系统核心命令的时间百分比,持续小于10
4,内存文件
 <PerfmonObject name="\Paging File(*)" enabled="false">
                          <PerfmonCounter name="\% Usage" enabled="false" />
                        </PerfmonObject>
% Usage:比率值高于70%,表示要加内存了。
 

2012四月22

知识链接

sql server 评论关闭

SQL SERVER BEST PRACTICES:

http://technet.microsoft.com/en-us/sqlserver/bb671430

DMV:

http://gallery.technet.microsoft.com/scriptcenter/site/search?f[0].Type=RootCategory&f[0].Value=databases&f[0].Text=%E6%95%B0%E6%8D%AE%E5%BA%93&f[1].Type=SubCategory&f[1].Value=sqlserver&f[1].Text=SQL%20Server&f[2].Type=Tag&f[2].Value=DMV&sortBy=Ratings

http://www.sqlcrunch.com/PerformanceTuning/tabid/100/Default.aspx

http://www.cnblogs.com/flysun0311/

2012四月22

查询当前事务中的锁,及相关sql语句

sql server 评论关闭

SELECT TL.resource_type AS ResType
,TL.resource_description AS ResDescr
,TL.request_mode AS ReqMode
,TL.request_type AS ReqType
,TL.request_status AS ReqStatus
,TL.request_owner_type AS ReqOwnerType
,TAT.[name] AS TransName
,TAT.transaction_begin_time AS TransBegin
,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
,ES.session_id AS S_Id
,ES.login_name AS LoginName
,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
,PARIDX.name AS IndexName
,ES.host_name AS HostName
,ES.program_name AS ProgramName
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2 阅读全文…

2012四月22

查询当前活动的事务

sql server 评论关闭

SELECT CASE WHEN TDT.database_id = 32767
THEN ‘MSSQLSystemResource’
ELSE DB.name END AS DatabaseName
,REQ.start_time AS ReqStart
,TAT.transaction_begin_time AS TransBegin
,TAT.name AS TransName
,CASE TDT.database_transaction_type
WHEN 1 THEN N’Read/Write’
WHEN 2 THEN N’Read-only’
WHEN 3 THEN N’System’
ELSE N’Unkown’ END AS TransType
,CASE TAT.transaction_state
WHEN 0 THEN N’Not initialized’
WHEN 1 THEN N’Not started’
WHEN 2 THEN N’Active’
WHEN 3 THEN N’Ended’
WHEN 4 THEN N’DTC active’
WHEN 5 THEN N’Preparing’
WHEN 6 THEN N’Committing’
WHEN 7 THEN N’Being rolled back’
WHEN 8 THEN N’Rolled back’
ELSE N’Unkown’
END AS TransState
,REQ.[status] AS ReqStatus
,TDT.database_transaction_log_record_count AS LogRec
,TDT.database_transaction_log_bytes_used AS LogBytes
,SES.login_name AS LoginName
,REQ.wait_type AS ReqWaitType
,REQ.percent_complete AS [ReqCompl%]
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE REQ.statement_end_offset END – REQ.statement_start_offset) / 2
) AS SqlStatement
FROM sys.dm_tran_active_transactions AS TAT
INNER JOIN sys.dm_tran_database_transactions AS TDT
ON TAT.transaction_id = TDT.transaction_id
INNER JOIN sys.databases AS DB
ON TDT.database_id = DB.database_id
LEFT JOIN sys.dm_tran_session_transactions AS TST
ON TAT.transaction_id = TST.transaction_id
LEFT JOIN sys.dm_exec_requests AS REQ
ON TAT.transaction_id = REQ.transaction_id
LEFT JOIN sys.dm_exec_sessions AS SES
ON REQ.session_id = SES.session_id
CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST
WHERE TAT.transaction_id > 255
AND ISNULL(REQ.session_id, -1) <> @@SPID
AND TDT.database_id <> DB_ID(N’tempdb’)
ORDER BY DatabaseName
,TransBegin
,TransName;

2012四月21

sqlcmd

sql server 评论关闭
1,进入cmd,sqlcmd 
1)进入本机,直接输入:sqlcmd 即:已可信连接到本地计算机上的 SQL Server 的默认实例 
2)进入目标机,sqlcmd -S SERVERNAME -U USERNAME -P PASSWORD 注,输入语句后按GO 才会把语句发送到 SQL Server 实例执行,相当于mysql中的分号
 
 2.主要参数: 
[-S 服务器]指定 server_name 将连接到该服务器中 SQL Server 的默认实例。指定 server_name\instance_name 将连接到该服务器中 SQL Server 的命名实例。如果未指定服务器,sqlcmd 将连接到本地计算机上的 SQL Server 的默认实例 
[-U 登录 ID] [-P 密码] [-d 使用数据库名称] 
[-A 专用管理连接]使用专用管理员连接 (DAC) 登录到 SQL Server。此类型连接用于排除服务器故障。这只适用于支持 DAC 的服务器。如果 DAC 不可用,sqlcmd 会生成错误消息 
[-E 可信连接]:使用可信连接而不是用户名和密码登录 SQL Server。
默认情况下,sqlcmd 将使用可信连接选项。 -E 选项会忽略可能的用户名和密码环境变量设置,例如 SQLCMDPASSWORD。如果将 -E 选项与 -U 选项或 -P 选项一起使用,将生成错误消息。 
[-i 输入文件]标识包含一批 SQL 语句或存储过程的文件。可以指定要按顺序读取和处理的多个文件。文件名之间不要使用任何空格。sqlcmd 将首先检查所有指定的文件是否都存在。如果有一个或多个文件不存在,sqlcmd 将退出。 
[-o 输出文件]标识从 sqlcmd 接收输出的文件。 如: C:\Documents and Settings\ant>sqlcmd -o result.txt 1> select name from sys.databases 2> go 
 
3,详细参数 用法: Sqlcmd [-U 登录 ID] [-P 密码] [-S 服务器] [-H 主机名] [-E 可信连接] [-d 使用数据库名称] [-l 登录超时值] [-t 查询超时值] [-h 标题] [-s 列分隔符] [-w 屏幕宽度] [-a 数据包大小] [-e 回显输入] [-I 允许带引号的标识符] [-c 命令结束] [-L[c] 列出服务器[清除输出]] [-q "命令行查询"] [-Q "命令行查询" 并退出] [-m 错误级别] [-V 严重级别] [-W 删除尾随空格] [-u unicode 输出] [-r[0|1] 发送到 stderr 的消息] [-i 输入文件] [-o 输出文件] [-z 新密码] [-f <代码页> | i:<代码页>[,o:<代码页>]] [-Z 新建密码并退出] [-k[1|2] 删除[替换]控制字符] [-y 可变长度类型显示宽度] [-Y 固定长度类型显示宽度] [-p[1] 打印统计信息[冒号格式]] [-R 使用客户端区域设置] [-b 出错时中止批处理] [-v 变量 = "值"...] [-A 专用管理连接] [-X[1] 禁用命令、启动脚本、环境变量[并退出]] [-x 禁用变量情况] [-? 显示语法摘要]

--远程连接
sqlcmd -S 121.12.105.65 -U sa -P xxxxx

--列出在本地配置的服务器和在网络上广播的服务器的名称
Create table #Server ( [Server] [varchar](128) )
Insert Into #Server
Exec xp_cmdshell 'sqlcmd /Lc'

--执行查询
Declare @query varchar(max)
SET @query = 'select * from frmuser'
--Run the query on each server
Declare @OpenServerRunQuery varchar(8000)
SET @OpenServerRunQuery = 'sqlcmd -E -S "' + @@SERVERNAME + '" -d Business  -q "' + @query + '"'

exec master..xp_cmdshell @OpenServerRunQuery

--查询-SQLCMD模式

:SETVAR DatabaseName "brm_test"
:SETVAR SchemaName "dbo"
:SETVAR TableName "mdlist"
USE $(DatabaseName);
SELECT *
FROM $(SchemaName).$(TableName);
2012四月21

乘积聚合函数

sql server 评论关闭

MSSQL中没有直接的如SUM一样的乘积函数,不过可通过变换得到。
由log(a) + log(b) + loa(c) = log(a*b*c) 得到:
a*b*c = exp(log(a) + log(b) + loa(c)) (exp:e的指数, 如EXP(10) = e^10 ),
因log(a) + log(b) + loa(c)可以通过加法得到,故乘积也可得到。

如下:
select EXP(sum(LOG(quantity))) from(
select 1 quantity
union select 2
union select 3
union select 4
union select 5
)m

2012四月21

除0判断

sql server 评论关闭

为防止分母中出现0,可用NULLIF与0做判断,若等于0,则返回NULL,任何数与NULL运算都为NULL,否则,返回原值。
declare @i int
set @i = 0
select(2/nullif(@i,0)

2012四月20

SQL Server启动顺序

sql server 评论关闭

SQL Server在启动或运行过程中遇到的问题,都会在日志文件有记录,
日志文件地址:\MSSQL.X\MSSQL\LOG目录下,默认保留7天的日志,每次SQL Server重启,
都会新建一个errorlog文件,原errorlog文件更改为errorlog.1,原errorlog.1改为errorlog.2,依次类推。
1,从注册表读取SQL Server启动信息,如启动参数,网络配置信息等。
2,检测硬件,配置内在及CPU
3,初始化MS DTC
4,系统数据库启动,顺序:master,mssqlsystemresource,model和tempdb,只要其中之一不能启动,SQL Server启动就会失败。
5,准备网络连接:TCP/IP,Shared Memory等。
6,在此步前,SQL Server服务已可用,接下来会启动msdb数据库和其他用户数据库。

2012四月20

DBCC SHRINKFILE有时收缩文件不成功的原因

sql server 评论关闭

DBCC SHRINKFILE收缩的是区一级的数据,会将没在使用中的区进行删除。但会有许多空页分布在区内,造成区在使用,故这样的区得不到删除。
若想删除,方式是:重建聚集索引,整理页数据。
注:页是SQL Server数据存储的最基本单位,页的大小是8KB,
每区由8个页组成,这意味着SQL Server数据库中每MB有128页,16个区。

2012四月20

将数据库系统在一台新服务器上恢复

sql server 评论关闭

实例:将数据库系统在一台新服务器上恢复
下面是参考步骤。请注意,在命令行下运行的指令,是大小写敏感的。
1. 确认备用服务器的SQL Server版本和原服务器一致。
因为我们需要恢复系统数据库,需要保证我们恢复的master和msdb要能够和备用机的resource数据库一致。否则SQL Server将不能正常工作。所谓版本一致,指的是“select @@version”返回的号码必须完全一样。

2. 在备用服务器的命令行窗口,用指令以单用户模式启动SQL Server服务。
NET START MSSQLSERVER /m

命令如果成功执行,应该返回如下信息:

The SQL Server (DR) service is starting..
The SQL Server (DR) service was started successfully.

3. 在命令行窗口,用sqlcmd这个命令行工具连接SQL Server。
sqlcmd -E -S sql2005pc

如果连接成功建立,应该返回下面的信息。

1>

4. 首先恢复Master数据库。
4.1 在sqlcmd的那个连接里,运行下面恢复语句(假设备份文件为’c:\lab\master.bak’)。
restore database master from disk = ‘c:\lab\master.bak’
go

它应该返回类似于下面的信息:

Processed 360 pages for database ‘master’, file ‘master’ on file 1.
Processed 4 pages for database ‘master’, file ‘mastlog’ on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

SQL Server服务自动停止了。

4.2 由于恢复的master数据库里记载的其他数据库的路径和现在的路径不一致,这时候重新启动SQL Server会失败。必须要用trace flag 3608来启动。
net start MSSQLSERVER /f /m /T3608

如果正常,应该返回下面的信息。
The SQL Server (DR) service is starting.
The SQL Server (DR) service was started successfully.

4.3 用sqlcmd连接修改其他数据库的文件路径到现有的正确路径(’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\’)。
在命令行窗口,用sqlcmd再次作连接。

sqlcmd -E -S sql2005pc

用下面语句修改各个系统数据库的文件路径。

alter database mssqlsystemresource modify file (name =data, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.mdf’);
go

如果正常,应该返回下面的信息。
The file “data” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database mssqlsystemresource modify file (name =log, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.ldf’);
go

如果正常,应该返回下面的信息。
The file “log” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database msdb modify file (name =MSDBData, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf’);
go

如果正常,应该返回下面的信息。
The file “MSDBData” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database msdb modify file (name =MSDBLog, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf’); go

如果正常,应该返回下面的信息。
The file “MSDBLog” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database model modify file (name =modeldev, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf’);
go

如果正常,应该返回下面的信息。
The file “modeldev” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database model modify file (name =modellog, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf’);
go

如果正常,应该返回下面的信息。
The file “modellog” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database tempdb modify file (name =tempdev, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\tempdb.mdf’);
go

如果正常,应该返回下面的信息。
The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

alter database tempdb modify file (name =templog, filename=’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\templog.ldf’);
go

如果正常,应该返回下面的信息。
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

全部修改完毕后,运行“exit”命令退出sqlcmd连接。

4.4 关闭SQL Server。
net stop MSSQLSERVER

如果正常,应该返回下面的信息。
The SQL Server (DR) service is stopping.
The SQL Server (DR) service was stopped successfully.

4.5 用正常模式启动SQL Server。
net start MSSQLSERVER
这时,SQL Server可以正常启动。但是它使用的系统数据库除了master以外,都是原先备用服务器自己的。我们要用生产服务器上的备份来替换它们。

5. 恢复msdb.
在运行下面命令之前,要先关闭SQL Server Agent服务。然后用restore命令恢复mdsb,将其指向新的文件路径。
restore database msdb from disk = ‘c:\lab\msdb.bak’
with move ‘MSDBData’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf’,
move ‘MSDBLog’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf’, replace

如果正常,应该返回下面的信息。
Processed 600 pages for database ‘msdb’, file ‘MSDBData’ on file 1.
Processed 7 pages for database ‘msdb’, file ‘MSDBLog’ on file 1.
RESTORE DATABASE successfully processed 607 pages in 0.841 seconds (5.907 MB/sec).

6. 恢复model.
restore database model from disk = ‘c:\lab\model.bak’
with move ‘modeldev’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf’,
move ‘modellog’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf’, replace

如果正常,应该返回下面的信息。
Processed 152 pages for database ‘model’, file ‘modeldev’ on file 1.
Processed 3 pages for database ‘model’, file ‘modellog’ on file 1.
RESTORE DATABASE successfully processed 155 pages in 0.174 seconds (7.273 MB/sec).

7. 修改服务器名称
7.1 运行下面的语句你会发现,返回的还是原先的服务器名字。这时因为master是从那台机器来的。
Select @@servername

7.2 运行下面语句修改服务器名。

Sp_dropserver ‘<原先服务器名>‘
Go
Sp_addserver ‘SQL2005PC’, ‘local’
Go

7.3 重启SQL服务,再运行下面语句,就可以看到返回现在的服务器名字了。
Select @@servername
Go

做完这些操作后,原先SQL Server的所有配置都能够恢复到新的服务器上。只是用户数据库都是质疑状态,因为新服务器上没有它们的文件。接下来就可以使用前文介绍的恢复方法,将用户数据库依次恢复。

2012四月20

返回当前数据库的连接活动

sql server 评论关闭

1,Sys.dm_exec_connections:返回与SQL SERVER实例建立的连接的相关信息。
2,Sys.dm_exec_sessions:当前执行的会话,如查询分析器窗口就是一个会话
3,Sys.dm_exec_requests:正在执行或处于等待状态的请求。
流程:connection->session->request

SELECT DB_NAME(r.database_id) DB,
substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)/2
) as ‘SQL statement’,
qt.text batch,s.status,c.client_net_address,s.login_name, s.program_name,s.host_name,
s.login_time,c.connect_time,s.last_request_start_time, s.last_request_end_time,s.session_id,
s.host_process_id, s.client_version, s.client_interface_name,c.net_transport, c.net_packet_size,
r.request_id, r.start_time, r.status, r.command, r.user_id, r.blocking_session_id, r.wait_type,r.wait_time,
r.last_wait_type, r.wait_resource, r.open_transaction_count,r.transaction_id, r.percent_complete, r.cpu_time, r.reads, r.writes,r.granted_query_memory
FROM Sys.dm_exec_connections c
LEFT JOIN Sys.dm_exec_sessions s ON c.session_id = s.session_id
LEFT JOIN Sys.dm_exec_requests r ON s.session_id = r.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) as qt
ORDER BY c.client_net_address,s.login_name

2012四月16

阻塞与死锁

sql server 评论关闭

1,锁发生在事务中。事务的4个属性是:原子性,一致性,隔离性,持久性。(ACID)
1)原子性:对于数据的修改,要么全部执行,要么全部不执行,不存在一部分修改而另一部分未变的情况,即使执行一半发生断电的情况,下次启动时也会读取日志将上次未完的操作执行下去(故对于事务,日志优先写入)。
2)隔离性:对于数据的修改,同一时间只能由一个事务处理
3)一致性:事务完成时,必须使所有数据都保持一致状态。
4)持久性:事务完成之后,它对于系统的影响是永久性的。

2,隔离级别:mssql通过对共享锁申请和释放机制的不同处理,实现不同事务隔离级别。
1)隔离等级:
隔离级别 是否申请共享锁 何时释放 有无范围锁
未提交读 否 无 无
已提交读 是 当前语句执行完 无
可重复读 是 事务提交时 无
可序列化 是 事务提交时 有

在事务里面:
未提交读就是你读的同时我可以读写
已提交读取就是你读时我也可以读,但你读完后我才可以写,读操作共享锁时间一直到读取结束。
可重复读:事务提交时我才能写,读操作共享锁时间一直到事务结束。
注:未提交读:允许脏读,因此一个事务可能看见其他事务所做的尚未提交的更改。
已提交读:允许事务读取另一个事务以前读取(未修改)的数据,而不必等待第一个事务完成。 数据库引擎保留写锁(在所选数据上获取)直到事务结束,但是一执行 SELECT 操作就释放读锁。 这是数据库引擎默认级别。
已提交读快照:与未提交读一样,不加锁,但在事务未提交时,不能读取刚修改的数据,而是读取事务修改前的数据。
nolock等同于未提交读
参考msdn 设置隔离级别

2)默认隔离级别是已提交读
3)设置隔离级别:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
BEGIN TRAN

COMMIT

4)SELECT中设置NOLOCK,可以让mssql不去申请共享锁(S), 不过可能把没有提交事务的数据也显示出来,若之后事务回滚,select就会出现脏数据。

3,锁的类型:读锁(共享锁),申请修改锁(U),修改锁(X)
1共享(S):用于读取操作,如SELECT
2)更新(U):申请修改资源,做申请者登记,当资源释放时,可以第一个修改资源,它用于可更新的资源中,数据真正修改时再转化为排他锁。一次只有一个事务可以获得资源的更新锁(U 锁)。如果事务修改资源,则更新锁(U 锁)转换为排他锁(X 锁)。在已提交读级别以下(包含已提交读),因为共享锁在语句执行完之后就会释放,故先得到U锁的事务能接着转化为共享锁。在已提交读级别以上,更新锁作用不大,假设两个事务对同一资料都获取了共享锁,都执行更新操作,那么在事务结束前因都不会释放,故U锁将一直等待,转化不成排它锁进行修改,故会出现死锁。

3)排他(X):用于数据修改操作,如INSERT,UPDATE,DELETE
4)意向(I):用于建立锁的层次,一般是父层次,它有三种类型,意向共享(IS),意向排他(IX),意向排他共享(ISX)。
5)架构(SCH):包含两种类型,架构修改(Sch-M),架构稳定(Sch-S)
6)大容量更新。
7)键范围。
注:意向锁,锁定表或页,用它可以提高性能。原因:假设去桃花源景点,规定只有桃花源内无游客是,才允许下一位游客进入。现在来了一位新游客,判断是否他应该进入景点。方法有二:1,派景区管理员进入桃花源,在景区的山山水水排查,全部排查一遍,若无游客,则安排下一游客进入,若有,则下一游客等待。2,当前一游客进入时,将景区是否有游客进入状态设为TRUE,否则为False,那么当下一游客申请进入景区是,可以非常容易的判断出是否可以进入。 由这个问题知,方法2性能高一些。意向锁就相当于这个作用,当查询数据时,将数据所在的页或表设置为再用,以避免申请其他锁时的大范围判断。故知:意向锁可以提高性能,因为数据库引擎仅在表级检查意向锁,确定事务是否能安全的获取该表上的锁,而不需要检查表上的每行或每页。

8)锁与锁的申请:读与修改互斥,即若资源上现有共享锁,那么不能加排它锁,可加读锁,申请修改锁。若资源上有修改锁(X),那么不能读,也不能申请申请修改锁(U),换句话说,S锁与U锁是相互兼容的,但都与X锁不兼容。

9)可以加锁的资源分类:(1)RID,用于锁定堆上的某一行。(2)KEY:索引上的一行,或某个索引键。(3):数据页或索引页。(4):包含所有数据和索引的整个表。(5):DATABASE:整个数据库

2012四月2

MsSql事务处理

sql server 评论关闭
TRUNCATE TABLE tmp
BEGIN TRAN
BEGIN TRY
	INSERT INTO dbo.tmp( c1 ,c2 ,c3 )
	VALUES(1,1,1)
	SELECT 1/0
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 BEGIN ROLLBACK  END
END CATCH

IF @@TRANCOUNT > 0 BEGIN COMMIT  END
注,@@TRANCOUNT:
1)BEGIN TRANSACTION 语句将 @@TRANCOUNT 加 1。
2)ROLLBACK TRANSACTION 将 @@TRANCOUNT 递减到 0,
但 ROLLBACK TRANSACTION savepoint_name 除外,它不影响 @@TRANCOUNT。
3)COMMIT TRANSACTION 或 COMMIT WORK 将 @@TRANCOUNT 递减 1。
2012四月1

MsSql限制IP所引发的失误

开发遇到的问题 评论关闭

现需要限制MsSql上的登录IP,错误的直接将以下语句执行,而没有改192.168.1.1,此时连接服务器就再也连接不上,需要用DAC解决
注:限制IP访问最好用IPsec策略做,以免影响性能。


1,失误代码

USE master

GO

CREATE TRIGGER tr_LoginCheck

ON ALL SERVER

FOR LOGON

AS

DECLARE @ClientHost varchar(100)

SET @ClientHost= EVENTDATA().value(‘(/EVENT_INSTANCE/ClientHost)[1]‘, ‘varchar(15)’)

IF ClientHost <>’127.0.0.1′ AND ClientHost <> ‘<local machine>’

ROLLBACK TRAN

GO

–此触发器可在[服务器对象 - 触发器下]查看

可能要给账号开权限

USE [master]
GO
CREATE USER [myuser] FOR LOGIN [myuser]
GO
–grant select on ipcheck to myuser
 
grant VIEW SERVER STATE to myuser
 

2,失误后登录提示:login failed due to a TRIGGER

3,解决方案:

开始-运行-cmd: sqlcmd -S LocalHost -d master -A

1> DROP TRIGGER tr_LoginCheck ON ALL SERVER

2> GO