SQLServer数据库收缩相关知识

　　1、为什么要进行数据库收缩？
　　SQL Server 数据库采取预先分配空间的方法来建立数据库的数据文件或者日志文件，比如数据文件的空间分配了300MB，而实际上只占用了20MB空间，这样就会造成磁盘存储空间的浪费。可以通过数据库收缩技术对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。从而节省服务器的存储的成本。2、数据库收缩的原理
　　官方解释：收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间。在文件末尾创建足够的可用空间后，可以取消对文件末尾的数据页的分配并将它们返回给文件系统。3、数据库收缩的限制和局限
　　收缩后的数据库不能小于数据库最初创建时指定的大小。 或是上一次使用文件大小更改操作（如 DBCC SHRINKFILE）设置的显式大小。
　　比如：如果数据库最初创建时的大小为 10 MB，后来增长到 100 MB，则该数据库最小只能收缩到 10 MB，即使已经删除数据库的所有数据也是如此。
　　不能在备份数据库时收缩数据库。 反之，也不能在数据库执行收缩操作时备份数据库。4、数据库收缩的方式4.1 收缩数据库 DBCC SHRINKDATABASE
　　介绍：收缩指定数据库中的数据文件大小。
　　语法格式：DBCC SHRINKDATABASE     ( database_name [ , target_percent ]         [ , { NOTRUNCATE | TRUNCATEONLY } ]  )
　　参数说明：database_name：是要收缩的数据库名称target_percent：是数据库收缩后的数据库文件中所要的剩余可用空间百分比。NOTRUNCATE：导致在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。TRUNCATEONLY：导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。不试图重新定位未分配页的行。使用 TRUNCATEONLY 时，忽略 target_percentis。4.2 收缩数据库文件 DBCC SHRINKFILE
　　介绍：收缩当前数据库的指定数据或日志文件的大小，或通过将数据从指定的文件移动到相同文件组中的其他文件来清空文件，以允许从数据库中删除该文件。文件大小可以收缩到比创建该文件时所指定的大小更小。这样会将最小文件大小重置为新值。
　　语法格式：DBCC SHRINKFILE  (     { file_name | file_id }      { [ , EMPTYFILE ]      | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]     } ) [ WITH NO_INFOMSGS ]
　　参数说明：file_name：要收缩的文件的逻辑名称。file_id：要收缩的文件的标识 (ID) 号。若要获得文件 ID，请使用 FILE_IDEX 系统函数，或查询当前数据库中的 sys.database_files 目录视图target_size：用兆字节表示的文件大小（用整数表示）。如果未指定，则 DBCC SHRINKFILE 将文件大小减少到默认文件大小。默认大小为创建文件时指定的大小。注意：可以使用 DBCC SHRINKFILE target_size 减小空文件的默认大小。
　　例如，如果创建一个10MB 的文件，然后在文件仍然为空的时候将文件收缩为2 MB，默认文件大小将设置为2 MB。这只适用于永远不会包含数据的空文件。EMPTYFILE：将指定文件中的所有数据迁移到同一文件组中的其他文件。由于数据库引擎不再允许将数据放在空文件内，因此可以使用 ALTER DATABASE 语句来删除该文件。NOTRUNCATE：在指定或不指定 target_percent 的情况下，将已分配的页从数据文件的末尾移动到该文件前面未分配页。文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。因此，指定 NOTRUNCATE 时，文件看起来未收缩。NOTRUNCATE 只适用于数据文件。日志文件不受影响。TRUNCATEONLY：将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。数据文件只收缩到最后分配的区。如果随 TRUNCATEONLY 指定了 target_size，则会忽略该参数。TRUNCATEONLY 只适用于数据文件。WITH NO_INFOMSGS：取消显示所有信息性消息。5、示例-- 将TestDB数据库中的TestDB文件的大小收缩到20MB。 USE TestDB ; GO DBCC SHRINKFILE (TestDB, 20) ; -- 将减小 UserDB 用户数据库中数据文件和日志文件的大小，以便在数据库中留出 10% 的可用空间 DBCC SHRINKDATABASE (TestDB, 30);  -- 清理数据库日志文件为2M USE master ALTER DATABASE TestDB SET RECOVERY SIMPLE WITH NO_WAIT ALTER DATABASE TestDB SET RECOVERY SIMPLE --简单模式 USE TestDB DBCC SHRINKFILE (N＂TestDB_log＂ , 2, TRUNCATEONLY)    --设置压缩后的日志大小为2M，可以自行指定 USE master ALTER DATABASE TestDB SET RECOVERY FULL WITH NO_WAIT ALTER DATABASE TestDB SET RECOVERY FULL --还原为完全模式
　　另附SqlServer常见问题解答 1、SqlServer数据库正在还原的解决办法
　　1）管理器不会主动刷新，需要手工刷新一下才能看到最新状态（性能方面的考虑）
　　2）很少情况下，恢复进程被挂起了。这个时候假设你要恢复并且回到可访问状态，要执行：
　　RESTORE database dbname with recovery
　　这使得恢复过程能完全结束。
　　3）如果你要不断恢复后面的日志文件，的确需要使数据库处于＂正在还原状态＂，
　　这通常是执行下面命令：
　　RESTORE database dbname with norecovery2、SQL SERVER占用CPU过高排查和优化
　　原来SQL Server对服务器内存的使用策略是用多少内存就占用多少内存，只用在服务器内存不足时，才会释放一点占用的内存，所以SQL Server 服务器内存往往会占用很高。我们可以通过DBCC MemoryStatus来查看内存状态。
　　SQL SERVER运行时会执行两种缓存:
　　1. 数据缓存：执行个查询语句，SQL SERVER会将相关的数据页（SQL SERVER操作的数据都是以页为单位的）加载到内存中来， 下一次如果再次请求此页的数据的时候，就无需读取磁盘了，大大提高了速度。
　　2.执行命令缓存：在执行存储过程，自定函数时，SQL SERVER需要先二进制编译再运行，编译后的结果也会缓存起来， 再次调用时就无需再次编译。
　　可以调用以下几个DBCC管理命令来清理这些缓存：DBCC FREEPROCCACHE  --清除存储过程相关的缓存 DBCC FREESESSIONCACHE  --会话缓存 DBCC FREESYSTEMCACHE(＂All＂)  --系统缓存 DBCC DROPCLEANBUFFERS  --所有缓存
　　但是，这几个命令虽然会清除掉现有缓存，为新的缓存腾地方，但是Sql server并不会因此释放掉已经占用的内存。SQL SERVER并没有提供任何命令允许我们释放不用到的内存。因此我们只能通过动态调整SQL SERVER可用的物理内存设置来强迫它释放内存。
　　解决SQLSERVER内存占用过高的方法：
　　１、清除所有缓存　　DBCC DROPLEANBUFFERS
　　２、调整SQLSERVER可使用的最大服务器内存。
　　在SQL管理器，右击实例名称
　　右击实例名称选择属性
　　在属性实例属性里面找到内存选项
　　最大内存建议不超过系统内存的１／２
　　把最大内存改成合适的内存，确定后内存就会被强制释放，然后重启实例。再看看任务管理器，内存使用率就降下来啦。SQL优化方法：
　　1、查看连接对象
　　USE master
　　GO
　　--如果要指定数据库就把注释去掉
　　SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])=＂gposdb＂
　　当前连接对象有67个其中‘WINAME’的主机名，‘jTDS’的进程名不属于已知常用软件，找到这台主机并解决连接问题。在360流量防火墙中查看有哪个软件连接了服务器IP，除之。
　　2、然后使用下面语句看一下各项指标是否正常，是否有阻塞，正常情况下搜索结果应该为空。
　　SELECT TOP 10
　　[session_id],
　　[request_id],
　　[start_time] AS ＂开始时间＂,
　　[status] AS ＂状态＂,
　　[command] AS ＂命令＂,
　　dest.[text] AS ＂sql语句＂,
　　DB_NAME([database_id]) AS ＂数据库名＂,
　　[blocking_session_id] AS ＂正在阻塞其他会话的会话ID＂,
　　[wait_type] AS ＂等待资源类型＂,
　　[wait_time] AS ＂等待时间＂,
　　[wait_resource] AS ＂等待的资源＂,
　　[reads] AS ＂物理读次数＂,
　　[writes] AS ＂写次数＂,
　　[logical_reads] AS ＂逻辑读次数＂,
　　[row_count] AS ＂返回结果行数＂
　　FROM sys.[dm_exec_requests] AS der
　　CROSS APPLY
　　sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
　　WHERE [session_id]>50 AND DB_NAME(der.[database_id])=＂gposdb＂
　　ORDER BY [cpu_time] DESC
　　查看是哪些SQL语句占用较大可以使用下面代码
　　--在SSMS里选择以文本格式显示结果
　　SELECT TOP 10
　　dest.[text] AS ＂sql语句＂
　　FROM sys.[dm_exec_requests] AS der
　　CROSS APPLY
　　sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
　　WHERE [session_id]>50
　　ORDER BY [cpu_time] DESC
　　3、如果SQLSERVER存在要等待的资源，那么执行下面语句就会显示出会话中有多少个worker在等待
　　SELECT TOP 10
　　[session_id],
　　[request_id],
　　[start_time] AS ＂开始时间＂,
　　[status] AS ＂状态＂,
　　[command] AS ＂命令＂,
　　dest.[text] AS ＂sql语句＂,
　　DB_NAME([database_id]) AS ＂数据库名＂,
　　[blocking_session_id] AS ＂正在阻塞其他会话的会话ID＂,
　　der.[wait_type] AS ＂等待资源类型＂,
　　[wait_time] AS ＂等待时间＂,
　　[wait_resource] AS ＂等待的资源＂,
　　[dows].[waiting_tasks_count] AS ＂当前正在进行等待的任务数＂,
　　[reads] AS ＂物理读次数＂,
　　[writes] AS ＂写次数＂,
　　[logical_reads] AS ＂逻辑读次数＂,
　　[row_count] AS ＂返回结果行数＂
　　FROM sys.[dm_exec_requests] AS der
　　INNER JOIN [sys].[dm_os_wait_stats] AS dows
　　ON der.[wait_type]=[dows].[wait_type]
　　CROSS APPLY
　　sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
　　WHERE [session_id]>50
　　ORDER BY [cpu_time] DESC;
　　4、查询CPU占用最高的SQL语句
　　SELECT TOP 10
　　total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
　　execution_count,
　　(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
　　(CASE WHEN statement_end_offset = -1
　　THEN LEN(CONVERT(nvarchar(max), text)) * 2
　　ELSE statement_end_offset
　　END - statement_start_offset)/2)
　　FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
　　FROM sys.dm_exec_query_stats
　　ORDER BY [avg_cpu_cost] DESC;
　　5、索引缺失查询
　　SELECT
　　DatabaseName = DB_NAME(database_id)
　　,[Number Indexes Missing] = count(*)
　　FROM sys.dm_db_missing_index_details
　　GROUP BY DB_NAME(database_id)
　　ORDER BY 2 DESC;
　　SELECT TOP 10
　　[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
　　, avg_user_impact
　　, TableName = statement
　　, [EqualityUsage] = equality_columns
　　, [InequalityUsage] = inequality_columns
　　, [Include Cloumns] = included_columns
　　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 [Total Cost] DESC;
　　找到索引缺失的表，根据查询结果中的关键次逐一建立索引。