随笔分类 - SQL Server 管理
摘要：从一个存在的库，抽取其表结构，对象，权限等，再部署成一个不包含数据的”空库“的方法有很多种。如自带的Generate Scripts功能，自定义脚本提取创建脚本等。 在实际使用中，我更喜欢使用DAC的方式。特别是它能跟PowerShell结合使用。 什么是DAC，它能干什么？ 数据层应用程序 (D...
阅读全文
摘要：背景 事务复制中发布表有分区表，如何配置发布项，使分区结构传播到订阅库？有何限制？ 测试环境 CodeUSE [master]GOCREATE DATABASE [OMS_Test] ON PRIMARY ( NAME = N'OMS_Test_data1', FILENAME = N'C:\P...
阅读全文
摘要：问题 对某个库新增了一个订阅节点，然后需要把一些应用切到新订阅库，以分散负载。当应用切换后，有一个应用每次启动不到30秒，总是报超时的错误，而error log中又没有任何记录： Timeout expired. The timeout period elapsed prior to complet...
阅读全文
摘要：SQL Server创建索引时，可以指定Unique使之成为唯一索引。“唯一”顾名思义，但是两都到底有什么区别呢？因为索引也是一种物理结构，所以还是要从存储和结构上分析。 索引结构分叶级和非叶级，分析时我们要分开来看，这个很重要。 文中涉及的索引行大小计算，参考MSDN估计数据库大小索引部分。 1. 非唯一聚集索引和唯一聚集索引 创建两个测试表，各10000条整数，tb1唯一，tb2非唯一，有1000条为9999的重复值。 Codecreate table tb1(col1 int);declare @i int=1while @i90000go由前文的分析可知上图所有索引的minSize和.
阅读全文
摘要：自调整的数据库引擎(Self-tuning Database Engine) 长期以来，微软都致力于自调整(Self-Tuning)的SQL Server数据库引擎，用以降低产品的总拥有成本。从SQL Server 2005开始，SQL Server就是动态管理内存使用，并且调整内存使用时，不需要重启数据库引擎。 所以它也不提供内存分配的微调项。各个组件的内存分配，完全由数据库引擎自动管理，不能手动分配。但是这货还是提供了一些配置项，能够影响数据库引擎如何使用内存。 是否使用这些配置项来替代默认值，取决于操作系统版本，SQL Server版本，可用物理内存和处理器架构等。 SQL Serve.
阅读全文
摘要：第三章 High CPU Utilization.CPU使用率过高问题很容易被发现，但是诊断却不是很容易。CPU使用过高很多时候会成为其它问题的替罪羊，所以在确认和故障诊断时要抽丝剥茧。调查CPU压力三个主要的工具：性能监视器，SQLTrace,DMV. 性能监视器：首先用它来确认是SQL Server还是其它进程使用了过多的CPU。主要计数器有： Processor/ %Privileged Time :在特权模式下进程线程执行代码所花时间的百分比。基本可以认为是Windows核心使用的CPU Processor/ %User Time :处理器处于用户模式的时间百分比。应用程序的使用的CP
阅读全文
摘要：当实例没有做DDL Trigger和其它一些监控时，如何知道谁删除了某个表？通过系统函数fn_dblog,fn_dump_dblog和默认跟踪可以找到。1. 创建测试环境：新建个表，插入一条数据，然后drop掉CREATE DATABASE testgoUSE testgoCREATE TABLE dbo.fnlog_test(id INT IDENTITY ,val VARCHAR(10) DEFAULT 'x')GOCREATE CLUSTERED INDEX IX_ft_idON dbo.fnlog_test (ID) GOINSERT INTO dbo.fnlog_te
阅读全文
摘要：第二章 Disk I/O Configuration。对于SQL Server,磁盘I/O的配置主要针对数据库工作负载，考虑和权衡两个点： 1. 磁盘容量VS磁盘吞吐量 一个1TB的库放在一块2TB的磁盘上，容量是够了，但是磁盘吞吐量能满足工作负载吗？通常会使用RAID，合适的RAID级别也是容量与吞吐量权衡的一种结果。 2. 顺序IO VS. 随机I/O 数据库日志文件操作通常是顺序IO，数据文件通常随机IO会多很多。而磁盘的顺序IO性能要高于随机IO，因为前者需要移动磁头，后者不需要。 以工作负载不同的IO方式在存储上对数据库做隔离就很重要了。选择正确的RAID级别(Chose the r
阅读全文
摘要：第一章 A Performance Troubleshooting Methodology。 确定具体的方法论用于SQL Server故障诊断是件很难的事情。因为根据问题和环境的不同，解决方法也不一样。一个准确的故障诊断，不仅是收集各种信息，而且要明白它们的含义。千万不要只见树木不见森林。作者通过一个简单的诊断示例，演示基本的方法和借助的工具：1. 首先查看DMV sys.dm_os_wait_stats来确定操作系统层面的主要的资源等待。2. 假设发现主要等待类型为PAGEIOLATCH_SH。PAGEIOLATCH_SH发生的原因是磁盘子系统的速度不能满足DB对页的请求速度。 看起来是IO
阅读全文
摘要：在与朋友们聊到怎么让SQL Server的性能更好时，发现一些问题。对于SQL Server，除了遵循OS和应用程序的最佳实践外，还有哪些优化方法？硬件级别的优化同样也很重要，特别是存储的优化。聊完这些，我们开始从DBA的角度去解释SAN的问题。有许多的因素影响着磁盘的IO性能，比如共用或专用磁盘，RAID级别，总线速度，HBA的配置等。还有一个重要的但经常被忽略的因素：磁盘分区对齐(alignment in disk partitions).还有一些非磁盘因素，也可能出现瓶颈： -HBA和网卡 -FC的处理能力和iSCSI端口 -服务器和存储的路径数 -交换机了解和考虑架构中相关组件的限制是
阅读全文
摘要：某一个镜像会话中的主库所在服务器磁盘容量不足了，现要新添加一个磁盘分区用于扩容。通常的做法是：加完分区后，添加新数据文件(或者新文件组)到新分区，之后再重做镜像。如果此库比较大，则重做镜像会是件耗时和拖累性能的事情。会不会有更好的方法呢？在实际操作中，我以”数据库文件布局方式”分为三种情况：1. 主库和镜像库的文件布局一样，而且新增加分区盘符在两台机上都存在。 “一样”指mdf/ndf和ldf文件目录地址相同。在创建镜像时，还原数据库未指定“MOVE”则会一样。如主库包含C:\DB_Test\Mirror_test_data.mdf和C:\DB_Test\Mirror_test_log.ldf
阅读全文
摘要：有时候会查询一下实例下所有数据库文件的可用空间，SSMS上没有汇总的地方，仪表盘又只日志文件的可用空间，于是写了一个SP自己用./** author:Joe.TJ date:20130328 desc:get the free space of database file**/create proc usp_GetFreeSpaceasset nocount on;declare csr cursor for select name from sys.databases where database_id>4;declare @cmd varchar(2000);declare @db
阅读全文
摘要：最近新建立了多个热备镜像，于是把如何管理和监控镜像的认识总结一下。镜像在建立好之后，就需要去管理其运行状况。最重要是性能状况评估和关键指标的自动监控警报。 1. 首先我们要获取镜像状态的信息，才能做出判断或者监控。要获取这些信息，就要了解两个重要的镜像监控组件：数据库镜像状态表和数据库监控作业。 1
阅读全文
摘要：数据页缓存是SQL Server的内存使用主要的方面，也是占用量最大的部分。在一个稳定的DB Server上，这部分内存使用会相对较稳定。SQL Server会把经常使用到的数据缓存在内存里(就是数据页缓存)，用以提高数据访问速度。因为磁盘访问速度远远低于内存，所以减少磁盘访问量同样是数据库优化的重要方面。当数据页缓存区出现内存不足，则会出现查询慢，磁盘忙等等问题。分析方法：主要是用到性能计数器。 查看如下性能计数器： 1. SQL SERVER:Buffer Manager-Lazy Writes/sec:内存不足则会频繁调用Lazy Writer把数数据写入磁盘，此值会经常不为0. 2.
阅读全文
摘要：对于包含敏感数据的库，要实现备份加密。即备份文件别人拿到也不能还原和查看其中的数据。想到TDE(Transparent Data Encryption)。TDE MSDN 说明:“透明数据加密”(TDE) 可对数据和日志文件执行实时 I/O 加密和解密。这种加密使用数据库加密密钥 (DEK)，该密钥存储在数据库引导记录中以供恢复时使用。DEK 是使用存储在服务器的 master 数据库中的证书保护的对称密钥，或者是由 EKM 模块保护的非对称密钥。TDE 保护“处于休眠状态”的数据，即数据和日志文件。它提供了遵从许多法律、法规和各个行业建立的准则的能力。软件开发人员籍此可以使用 AES 和 3
阅读全文
摘要：事务复制的发布库同时也是镜像的主库，测试的目的是当镜像发生故障转移时，事务复制关系是否能同时自动转移。环境：JOEPC\SQLJOEC,DB_TEST1,DB_TEST2_VM三台机都是Win2008_R2_SP1+SQLServer2008_R2_SP1. JOEPC,DB_TEST1都是物理机，DB_TEST2_VM是建立在DB_TEST1上的虚拟机。关系说明：JOEPC\SQLJOEC是镜像关系的主体，是事务复制关系的发布者， DB_TEST1是镜像关系的见证者，是事务复制的分发者和订阅者， DB_TEST2_VM是镜像关系的镜像端。1. 首先在JOEPC\SQLJOE上创建测试库和表。
阅读全文
摘要：前些天我遇到一个问题，一个200Gb的库，其中一个表大约2000W行数据，我删除了其中约600W行数据。我想把删除后未使用的空间腾出来。 按照以往的经验，重建这个表上的聚集索引就可以了。可是这次表上只有一个非聚集主键索引。如何释放这个堆表未使用的空间 首先来模拟出我遇到的情况：USE masterGOCREATE DATABASE TEST;GOUSE TEST GO --每一行占一个PAGE. ...
阅读全文
摘要：目的：分散主库的负载，用于报表服务数据源和读写分离环境：Win2008_R2_X64,SQL2008_R2_X64,WORKGROUP网络模式准备： 1. 修改发布服务器、订阅服务器和分发服务器上的SQLServer服务账号和SQLServer Agent的服务账号，使三者之间的用户名和密码一致。 ...
阅读全文
摘要：在做复制时，要删除一个发布，遇到错误：Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.解决方法：这个库是我直接从一个2K5备份文件上恢复到2K8实例的，没有做其它设定。因为我是在sa登录遇到此错误USE masterGOEXEC sys.sp_dbcmptlevel @dbname = N'My
阅读全文
摘要：生产环境要上镜像，自己又做了一遍测试，总结记录一下。 目的：实现关键数据库的热备和故障自动切换。 环境：Win2008_R2_X64,SQLServer2008_R2_X64,WorkGroup网络模式。 数据库：people 主机：192.168.1.3 镜像机：192.168.1.110 见证机：192.168.1.111 准备工作： 打开每个实例的RemoteDACEnable.方法：SSMS选中实例右键Facets(方面)在Facet列表选中"Surface area configuration"设定"RemoteDacEnabled"为
阅读全文
浙公网安备 33010602011771号