SQLServer常见问题

SQLServer常见问题

================== 数据库文件和日志： ================== SQL Server2008系统是以页为最小物理空间单位来存储的。每一个页大小8KB； 一个数据库最少需两个关了的存储文件: 1 数据文件 2 日志文件 也可以有辅助数据文件 (1)主数据文件: 包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可以存储在此文件中， 也可以存储在辅助数据文件中。每个数据库只能有一个主数据文件，默认文件扩展名是.mdf; (2) 辅助数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上， 辅助文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个Windows文件的最大限制， 可以使用辅助数据文件，这样数据库就能继续增长。辅助数据文件的默认文件扩展名是.ndf; (3) 事物日志文件保存用于恢复数据库的日志信息。每个数据库必须只是一个日志文件，他的默认文件扩展名是.ldf。 SQL Server不强制这3种类型文件必须使用带mdf,ndf和ldf扩展名，但使用它们指出文件类型是个良好的文件命名习惯； 为了便于分配和管理，可以将数据文件集合起来，放在文件组中。 文件组是针对数据文件而创建的，是数据库中数据文件的集合。 利用文件组，可以优化数据存储。 例如： 可以在三个磁盘驱动器上创建3个文件:sys_School_data1.ndf、sys_School_data2.ndf、sys_School_data3.ndf, 然后将他们分配给文件组School_FG。 这样，可以明确的在文件组中创建一个表。对表中数据的查询将分散到三个磁盘上，从而提高性能。 创建与使用文件组需要遵守如下规则： 1:主要数据文件必须存储于主文件组中； 2:与系统相关的数据库对象必须存储于主文件组中； 3:一个数据文件只能存于一个文件组，不能同时存于多个文件组； 4:数据库的数据信息和日志新不能放在同一个文件组中，必须分开存放； 5:日志文件不能存放在任何文件组中； 查看数据库文件： select * from sys.database_files; 查看文件组： select * from sys.filegroups; ============ 系统数据库： ============ 系统数据库，随安装程序一起安装，用于协助SQL Server2008系统共同完成管理操作的数据库； 1.master master数据库是SQL Server2008的最重要的数据库，它位于SQL Server2008的核心，如果该数据库被损坏，SQL Server将无法正常工作。 master数据库中包含如下重要信息: (1)所有的登录名或用户ID所属的角色； (2)所有的系统配置设置(例如,数据库排序信息、安全实现、默认语言)； (3)服务器中的数据库的名称及相关信息； (4)数据库的位置； (5)SQL Server2008如何初始化； /* 定期备份master数据库非常重要。确保备份master数据库是备份策略的一部分。 */ 2.model数据库 创建数据库时，总是以一套预定义的标准为模型。 例如,若希望所有的数据库都有确定的初始大小，或者都有特定的信息集，那么可以把这些信息放在model数据库中，以model数据库作为其他数据库的模板数据库。 如果想要使所有的数据库都有一个特定的表，可以把改表放在model数据库里； model数据库是tempdb数据库的基础。对model数据库的任何改动都将反映在tempdb数据库中，所以，在决定对model数据库有所改变时，必须预先考虑好并多加小心； 3.tempdb数据库 tempdb数据库，是一个临时性的数据库,存在于SQL Server2008会话期间，一旦SQL Server2008关闭，tempdb数据库将丢失。 当SQL Server重新启动时，将会重建全新的、空的tempdb数据库； tempdb数据库用作系统的临时存储空间，其主要作用是存储用户建立的临时表和临时存储过程； 存储用户说明的全局变量值； 为用户排序创建临时表； 存储用户利用游标说明所筛选出来的数据； 4.msdb数据库 msdb给SQL Server2008代理提供必要的信息来运行作业，是SQL Server2008中另一个是否重要的数据库。 许多进程使用msdb,例如： 当创建备份或执行还原时，将用msdb来存储有关这些任务信息。 不能在msdb数据库中执行以下操作： (1)更改排序规则。默认排序规则为服务器排序规则； (2)删除数据库； (3)从数据中删除guest用户； (4)删除主文件组、主数据文件或日志文件； (5)重命名数据库或主文件组； (6)将数据库设置为OFFLINE； (7)将主文件组设置为READ_ONLY。 ======================= SQL Server服务作用简介 ======================= /* http://blog.csdn.net/qq_31971935/article/details/50633642 */ 1、SQL Server(MSSQLSERVER)是必须要开启的，这个是数据库引擎服务，就像汽车的发动机一样。 2、SQL Server代理(MSSQLSERVER)是代理服务，比如你有一些自动运行的，定时作业，或者是一些维护计划，比如定时备份数据库等操作，那么就要打开，否则，就不会备份数据库了。  SQL Server Reporting Services (MSSQLSERVER)是报表服务，一般不用开启，除非你做了报表，通过这个组件来提供报表服务，才需要开启。 3、SQL Server Analysis Services (MSSQLSERVER)是分析服务，一般不用开启，除非你做多位分析，和数据挖掘，才需要开启。 4、SQL Full-text Filter Daemon Launcher (MSSQLSERVER)是全文检索服务，如果你没有使用全文检索技术，那么也不需要开启。  5、SQL Server VSS Writer MicrosoftSQLServer的SQL编写器服务，允许备份和还原应用程序以便在VolumeShadowCopyService(VSS)框架中进行操作。 服务器上的所有SQL实例只有一个SQL编写器服务实例。 影拷贝服务（VolumeShadowCopyService，VSS）是Microsoft在WindowsServer2003中开始引入的服务，它能让用户在没有IT专业人员协助的情况下，更轻松地恢复丢失的文件。 这个让sql server应该是sqlwriter和windows交互，实现这个影拷贝服务的。 不过关闭，也没什么影响，因为在sql server中，还是要通过常规的数据库备份、日志备份来保证数据的安全，这个vss只是个很虚的东西，没什么用出。  6、Sql Browser 服务 一般你要进行远程访问，不需要开启sql browser开启sql browser，通过：服务器ip,端口 这种方式就可以访问远程的服务器。 那么这个sql browser主要是指在你的服务器上有多个sql server实例的情况下，通过这个sql browser来提供一些服务： 浏览可用服务器列表，比如你的机器上有3个实例，那么就会返回这3个实例的信息给你  连接到正确的服务器实例  连接到专用管理员连接 (DAC) 端点 一般如果你知道要连接哪个实例，建议你关闭这个服务 /* SQL Server Express 是由Microsoft所开发的SQL Server的其中一个版本，这个版本是免费且可自由转散布（需经注册） */ ===================== SQL Server备份与恢复 ===================== ...... ===================== SQL Server分离与附加 ===================== 主要解决日志文件大的问题； 1:分离数据库(建议分离之前先备份) 2:附加数据库时不选择日志文件 ========================== SQL Server日志文件过大问题 ========================== ---查看日志文件使用情况 dbcc sqlperf(logspace) ---SQLServer2012 ---http://www.tuicool.com/articles/NzE3uqN ---1 确认LOG状态 select log_reuse_wait_desc from sys.databases where name='DBNAME' ---2 备份并回收日志 USE [dbname] GO backup log dbname to disk='D:\dbbackup\2014-08-24-2.log' GO DBCC SHRINKFILE (N'a23648263485_Log' , 700, TRUNCATEONLY) GO ---exec sp_spaceused  ========================== SQL Server临时文件过大问题 ========================== http://blog.sina.com.cn/s/blog_6cfb9cc50102v85j.html 1：可以重启，一般会初始化成几M大小 2：可以考虑换一个磁盘，方法如下 １、检查tempdb的逻辑名字和它的存在位置。可以使用下面语句： SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb'); ２、暂停数据库服务． ３、拷贝原来tempdb的文件到新的位置（原来文件位置可以通过上述查询得到）。 ３、拷贝完毕后，启动数据库服务。 ４、执行如下命令： USE master; GO ALTER DATABASE tempdb  MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb\tempdb.mdf'); GO ALTER DATABASE  tempdb  MODIFY FILE (NAME = templog, FILENAME = 'E:\tempdb\templog.ldf'); GO 在这里，name=tempdev 和templog是tempdb的逻辑名字，FileName='E:\temdb\tempdb.mdf'是tempdb的新位置． ５、最后检查tempdb移动是否成功。 SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb'); ===================== SQL Server内存问题 ===================== http://www.cnblogs.com/oer2001/archive/2012/11/09/2762094.html sqlserver内存释放心得 SQL Server 2008 或者R2的默认内存分配是2147483647MB, 差不多算是无穷大，对于系统内存的管理策略是有多少占多少。 SQLserver会把所有处理过的SQL操作缓存在内存里，这样就不用总去读硬盘了。 但是如果长时间运行SQL Server, 系统内存被用的差不多，再开启其他程序就有可能会报内存不足。这时候就需要释放内存缓存啦。 http://blog.itpub.net/22392018/viewspace-1139661/ SQL Server 2008 R2占用内存越来越大两种解决方法 2014-04-10 17:49:11 SQL Server 2008 R2运行越久，占用内存会越来越大。 第一种： 有了上边的分析结果，解决方法就简单了，定期重启下SQL Server 2008 R2数据库服务即可，使用任务计划定期执行下边批处理： net stop sqlserveragent net stop mssqlserver net start mssqlserver net start sqlserveragent 第二种： 进入Sql server 企业管理器（管理数据库和表的，这个都不知道就不用往下看了）， 在数据库服务器名称上点击【右键】，选择【属性】，然后，找到【内存】选项，在右边的【使用AWE分配内存】（sqlServer64的应该不用勾）左边把对勾打上。 在最大服务器内存（MB）上填入适当的大小（具体填多大，肯定不能超过计算机的物理内存， 当然，也可以在任务管理器中查一下，sqlserver.exe占有多大时，系统会变慢作为参考），记得是以M（兆）为单位，点确定，重启一下Sql服务器！OK！ ---http://bbs.csdn.net/topics/390319133 ---各位稍等，我一个个试一下，限制内存上限那个我试过，不行，会突破那个值继续增长 ---数据库内存使用量随使用而增长很正常的，数据库会根据一定的策略尽量地把经常访问的数据块放在内存里，以减少磁盘IO带来的性能瓶颈。 ---内存是SQL Server的生命线，所以内存占用越多，对SQL Server性能提升越有帮助。 如果要限制内存，可以使用max server memory来配置buffer pool最大内存上限，这个基本上也就限制了SQL Server内存，SQL 其它部分占用的内存相对来讲比较少的。 试试: --强制释放内存   --清除所有缓存   DBCC DROPCLEANBUFFERS   --打开高级配置   exec sp_configure 'show advanced options', 1   --设置最大内存值，清除现有缓存空间   exec sp_configure 'max server mem…… ---http://zhidao.baidu.com/link?url=jfE_xh7BiY0mkNAZGNeTa150_qJvYPIZtE2firiBvW33uI9szQn73BGwfSYISZiYOzJKhJVhZrNINdjIHOrZ4a sql server 在查询大数据量的数据时，总会占用大量的内存，并且居高不下，一不小心就会死机。 下面这个是我从网上找到的： 当你查询数据的数据量比较大时，sqlserver会把查询结果缓存在内存中，保证你下次查询同样的记录时会很快得到结果，所以内存使用量会激增。 在你完成此次查询后，sqlserver不会马上释放内存，数据会仍然放在内存中，这是sqlserver的优化策略，sqlserver会不断地占用你的系统内存，来加快sqlserver的运行速度，当你的系统中的其它服务也需要内存时，它才会自动释放部分内存。一句话，sqlserver不会让你的系统有闲置的内存，除非你设置sqlserver的最大内存使用量。这样也没什么不好，如果你的系统很大，单独给sqlserver一台机器，这样会提高它的性能。 如果你只是开发用，要想让sqlserver释放内存，重启sqlserver的服务就行了。如果不想让sqlserver占用太多内存，设置sqlserver的最大内存占用量. 设置最大内存后效果好了不少！ ==================== SQLServer锁查询 ==================== --查询数据库状态 select * from sys.databases; --查询数据库状态 select name,user_access,user_access_desc,     snapshot_isolation_state,snapshot_isolation_state_desc,     is_read_committed_snapshot_on from sys.databases ---禁用并行（如果报表查询并发量很小可不禁用） sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO sp_configure 'max degree of parallelism', 1; GO RECONFIGURE WITH OVERRIDE; GO /* 配置选项 'show advanced options' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。 配置选项 'max degree of parallelism' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。 */ ---启用快照隔离等级 ---此调整需要数据库上无连接 ALTER DATABASE 数据库名称 SET READ_COMMITTED_SNAPSHOT ON; --进入TEST数据库 use TEST --查看锁和会话信息 sp_who2 --查看锁和会话对应SQL dbcc inputbuffer(52) use TEST  sp_who2 dbcc inputbuffer(57) dbcc inputbuffer(52) sp_who2  --杀掉阻塞的SQL     kill 52 ================================== Max Degree of Parallelism最大并行度 ================================== ---查看数据库启动的参数(可以查看到max degree of parallelism参数值) sp_configure http://www.cnblogs.com/chhuang/p/3582991.html 1）在OLTP服务器环境中 a）对并发度要求高，对每个用户请求都要及时响应，一般建议将max degree of parallelism  设置为1；因此cost threshold for parallelism  的值没有作用，默认为5即可 b）对并发度要求不高，经常会有复杂查询的数据库，若cpu个数<8,一般建议将max degree of parallelism 设置为cpu数量;若cpu个数>=8建议将值设为8；cost threshold for parallelism 默认即可 2）在OLAP服务器环境中 由于查询均比较复杂，则可将max degree of parallelism 设置为0，同时如果考虑到等待消耗问题，则可以考虑将cost threshold for parallelism 的值设置为10，以降低并发执行的可能 3）在OLTP和OLAP混合环境中 a）对并发度要求高，则可以考虑将max degree of parallelism  设置为<=4，cost threshold for parallelism 值默认即可 b）对并发度要求不高，经常会有复杂查询的数据库，若cpu个数<8,一般建议将max degree of parallelism 设置为cpu数量;若cpu个数>=8建议将值设为8；cost threshold for parallelism 默认即可 ====================== SQLSERVER查看表大小 ====================== http://www.cnblogs.com/nikyxxx/archive/2012/10/08/2715423.html SQL Server查看所有表大小，所占空间 复制代码 create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))  declare @name varchar(100)  declare cur cursor  for      select name from sysobjects where xtype='u' order by name  open cur  fetch next from cur into @name  while @@fetch_status=0  begin      insert into #data      exec sp_spaceused   @name      print @name        fetch next from cur into @name  end  close cur  deallocate cur  create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)  insert into #dataNew  select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data,  convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data   select * from #dataNew order by data desc     复制代码 --主要原理：  exec sp_spaceused '表名' --取得表占用空間  exec sp_spaceused ''--數據庫所有空間  还有一个简单的办法 SELECT   a.name, b.rows FROM      sysobjects AS a INNER JOIN                  sysindexes AS b ON a.id = b.id WHERE   (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC