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

---参考

《SQLServer2008中文版从入门到精通》

http://www.cnblogs.com/nikyxxx/archive/2012/10/08/2715423.html

http://www.cnblogs.com/chhuang/p/3582991.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2128133/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2128133/