SQLServer数据库收缩相关知识

　　1、为什么要进行数据库收缩？
　　SQLServer数据库采取预先分配空间的方法来建立数据库的数据文件或者日志文件，比如数据文件的空间分配了300MB，而实际上只占用了20MB空间，这样就会造成磁盘存储空间的浪费。可以通过数据库收缩技术对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。从而节省服务器的存储的成本。2、数据库收缩的原理
　　官方解释：收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间。在文件末尾创建足够的可用空间后，可以取消对文件末尾的数据页的分配并将它们返回给文件系统。3、数据库收缩的限制和局限
　　收缩后的数据库不能小于数据库最初创建时指定的大小。或是上一次使用文件大小更改操作（如DBCCSHRINKFILE）设置的显式大小。
　　比如：如果数据库最初创建时的大小为10MB，后来增长到100MB，则该数据库最小只能收缩到10MB，即使已经删除数据库的所有数据也是如此。
　　不能在备份数据库时收缩数据库。反之，也不能在数据库执行收缩操作时备份数据库。4、数据库收缩的方式4。1收缩数据库DBCCSHRINKDATABASE
　　介绍：收缩指定数据库中的数据文件大小。
　　语法格式：DBCCSHRINKDATABASE（databasename〔，targetpercent〕〔，｛NOTRUNCATETRUNCATEONLY｝〕）
　　参数说明：databasename：是要收缩的数据库名称targetpercent：是数据库收缩后的数据库文件中所要的剩余可用空间百分比。NOTRUNCATE：导致在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。TRUNCATEONLY：导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。不试图重新定位未分配页的行。使用TRUNCATEONLY时，忽略targetpercentis。4。2收缩数据库文件DBCCSHRINKFILE
　　介绍：收缩当前数据库的指定数据或日志文件的大小，或通过将数据从指定的文件移动到相同文件组中的其他文件来清空文件，以允许从数据库中删除该文件。文件大小可以收缩到比创建该文件时所指定的大小更小。这样会将最小文件大小重置为新值。
　　语法格式：DBCCSHRINKFILE（｛filenamefileid｝｛〔，EMPTYFILE〕〔〔，targetsize〕〔，｛NOTRUNCATETRUNCATEONLY｝〕〕｝）〔WITHNOINFOMSGS〕
　　参数说明：filename：要收缩的文件的逻辑名称。fileid：要收缩的文件的标识（ID）号。若要获得文件ID，请使用FILEIDEX系统函数，或查询当前数据库中的sys。databasefiles目录视图targetsize：用兆字节表示的文件大小（用整数表示）。如果未指定，则DBCCSHRINKFILE将文件大小减少到默认文件大小。默认大小为创建文件时指定的大小。注意：可以使用DBCCSHRINKFILEtargetsize减小空文件的默认大小。
　　例如，如果创建一个10MB的文件，然后在文件仍然为空的时候将文件收缩为2MB，默认文件大小将设置为2MB。这只适用于永远不会包含数据的空文件。EMPTYFILE：将指定文件中的所有数据迁移到同一文件组中的其他文件。由于数据库引擎不再允许将数据放在空文件内，因此可以使用ALTERDATABASE语句来删除该文件。NOTRUNCATE：在指定或不指定targetpercent的情况下，将已分配的页从数据文件的末尾移动到该文件前面未分配页。文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。因此，指定NOTRUNCATE时，文件看起来未收缩。NOTRUNCATE只适用于数据文件。日志文件不受影响。TRUNCATEONLY：将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。数据文件只收缩到最后分配的区。如果随TRUNCATEONLY指定了targetsize，则会忽略该参数。TRUNCATEONLY只适用于数据文件。WITHNOINFOMSGS：取消显示所有信息性消息。5、示例将TestDB数据库中的TestDB文件的大小收缩到20MB。USETestDB；GODBCCSHRINKFILE（TestDB，20）；将减小UserDB用户数据库中数据文件和ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a文件的大小，以便在数据库中留出10的可用空间DBCCSHRINKDATABASE（TestDB，30）；清理数据库ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a文件为2MUSEmasterALTERDATABASETestDBSETRECOVERYSIMPLEWITHNOWAITALTERDATABASETestDBSETRECOVERYSIMPLE简单模式USETestDBDBCCSHRINKFILE（NTestDBlog，2，TRUNCATEONLY）设置压缩后的ahrefhttps：www。bs178。comrizhitargetblankclassinfotextkey日志a大小为2M，可以自行指定USEmasterALTERDATABASETestDBSETRECOVERYFULLWITHNOWAITALTERDATABASETestDBSETRECOVERYFULL还原为完全模式
　　另附SqlServer常见问题解答1、SqlServer数据库正在还原的解决办法
　　1）管理器不会主动刷新，需要手工刷新一下才能看到最新状态（性能方面的考虑）
　　2）很少情况下，恢复进程被挂起了。这个时候假设你要恢复并且回到可访问状态，要执行：
　　RESTOREdatabasedbnamewithrecovery
　　这使得恢复过程能完全结束。
　　3）如果你要不断恢复后面的日志文件，的确需要使数据库处于正在还原状态，
　　这通常是执行下面命令：
　　RESTOREdatabasedbnamewithnorecovery2、SQLSERVER占用CPU过高排查和优化
　　原来SQLServer对服务器内存的使用策略是用多少内存就占用多少内存，只用在服务器内存不足时，才会释放一点占用的内存，所以SQLServer服务器内存往往会占用很高。我们可以通过DBCCMemoryStatus来查看内存状态。
　　SQLSERVER运行时会执行两种缓存：
　　1。数据缓存：执行个查询语句，SQLSERVER会将相关的数据页（SQLSERVER操作的数据都是以页为单位的）加载到内存中来，下一次如果再次请求此页的数据的时候，就无需读取磁盘了，大大提高了速度。
　　2。执行命令缓存：在执行存储过程，自定函数时，SQLSERVER需要先二进制编译再运行，编译后的结果也会缓存起来，再次调用时就无需再次编译。
　　可以调用以下几个DBCC管理命令来清理这些缓存：DBCCFREEPROCCACHE清除存储过程相关的缓存DBCCFREESESSIONCACHE会话缓存DBCCFREESYSTEMCACHE（All）系统缓存DBCCDROPCLEANBUFFERS所有缓存
　　但是，这几个命令虽然会清除掉现有缓存，为新的缓存腾地方，但是Sqlserver并不会因此释放掉已经占用的内存。SQLSERVER并没有提供任何命令允许我们释放不用到的内存。因此我们只能通过动态调整SQLSERVER可用的物理内存设置来强迫它释放内存。
　　解决SQLSERVER内存占用过高的方法：
　　、清除所有缓存DBCCDROPLEANBUFFERS
　　、调整SQLSERVER可使用的最大服务器内存。
　　在SQL管理器，右击实例名称
　　右击实例名称选择属性
　　在属性实例属性里面找到内存选项
　　最大内存建议不超过系统内存的
　　把最大内存改成合适的内存，确定后内存就会被强制释放，然后重启实例。再看看任务管理器，内存使用率就降下来啦。SQL优化方法：
　　1、查看连接对象
　　USEmaster
　　GO
　　如果要指定数据库就把注释去掉
　　SELECTFROMsys。〔sysprocesses〕WHERE〔spid〕50ANDDBNAME（〔dbid〕）gposdb
　　当前连接对象有67个其中‘WINAME’的主机名，‘jTDS’的进程名不属于已知常用软件，找到这台主机并解决连接问题。在360流量防火墙中查看有哪个软件连接了服务器IP，除之。
　　2、然后使用下面语句看一下各项指标是否正常，是否有阻塞，正常情况下搜索结果应该为空。
　　SELECTTOP10
　　〔sessionid〕，
　　〔requestid〕，
　　〔starttime〕AS开始时间，
　　〔status〕AS状态，
　　〔command〕AS命令，
　　dest。〔text〕ASsql语句，
　　DBNAME（〔databaseid〕）AS数据库名，
　　〔blockingsessionid〕AS正在阻塞其他会话的会话ID，
　　〔waittype〕AS等待资源类型，
　　〔waittime〕AS等待时间，
　　〔waitresource〕AS等待的资源，
　　〔reads〕AS物理读次数，
　　〔writes〕AS写次数，
　　〔logicalreads〕AS逻辑读次数，
　　〔rowcount〕AS返回结果行数
　　FROMsys。〔dmexecrequests〕ASder
　　CROSSAPPLY
　　sys。〔dmexecsqltext〕（der。〔sqlhandle〕）ASdest
　　WHERE〔sessionid〕50ANDDBNAME（der。〔databaseid〕）gposdb
　　ORDERBY〔cputime〕DESC
　　查看是哪些SQL语句占用较大可以使用下面代码
　　在SSMS里选择以文本格式显示结果
　　SELECTTOP10
　　dest。〔text〕ASsql语句
　　FROMsys。〔dmexecrequests〕ASder
　　CROSSAPPLY
　　sys。〔dmexecsqltext〕（der。〔sqlhandle〕）ASdest
　　WHERE〔sessionid〕50
　　ORDERBY〔cputime〕DESC
　　3、如果SQLSERVER存在要等待的资源，那么执行下面语句就会显示出会话中有多少个worker在等待
　　SELECTTOP10
　　〔sessionid〕，
　　〔requestid〕，
　　〔starttime〕AS开始时间，
　　〔status〕AS状态，
　　〔command〕AS命令，
　　dest。〔text〕ASsql语句，
　　DBNAME（〔databaseid〕）AS数据库名，
　　〔blockingsessionid〕AS正在阻塞其他会话的会话ID，
　　der。〔waittype〕AS等待资源类型，
　　〔waittime〕AS等待时间，
　　〔waitresource〕AS等待的资源，
　　〔dows〕。〔waitingtaskscount〕AS当前正在进行等待的任务数，
　　〔reads〕AS物理读次数，
　　〔writes〕AS写次数，
　　〔logicalreads〕AS逻辑读次数，
　　〔rowcount〕AS返回结果行数
　　FROMsys。〔dmexecrequests〕ASder
　　INNERJOIN〔sys〕。〔dmoswaitstats〕ASdows
　　ONder。〔waittype〕〔dows〕。〔waittype〕
　　CROSSAPPLY
　　sys。〔dmexecsqltext〕（der。〔sqlhandle〕）ASdest
　　WHERE〔sessionid〕50
　　ORDERBY〔cputime〕DESC；
　　4、查询CPU占用最高的SQL语句
　　SELECTTOP10
　　totalworkertimeexecutioncountASavgcpucost，planhandle，
　　executioncount，
　　（SELECTSUBSTRING（text，statementstartoffset21，
　　（CASEWHENstatementendoffset1
　　THENLEN（CONVERT（nvarchar（max），text））2
　　ELSEstatementendoffset
　　ENDstatementstartoffset）2）
　　FROMsys。dmexecsqltext（sqlhandle））ASquerytext
　　FROMsys。dmexecquerystats
　　ORDERBY〔avgcpucost〕DESC；
　　5、索引缺失查询
　　SELECT
　　DatabaseNameDBNAME（databaseid）
　　，〔NumberIndexesMissing〕count（）
　　FROMsys。dmdbmissingindexdetails
　　GROUPBYDBNAME（databaseid）
　　ORDERBY2DESC；
　　SELECTTOP10
　　〔TotalCost〕ROUND（avgtotalusercostavguserimpact（userseeksuserscans），0）
　　，avguserimpact
　　，TableNamestatement
　　，〔EqualityUsage〕equalitycolumns
　　，〔InequalityUsage〕inequalitycolumns
　　，〔IncludeCloumns〕includedcolumns
　　FROMsys。dmdbmissingindexgroupsg
　　INNERJOINsys。dmdbmissingindexgroupstatss
　　ONs。grouphandleg。indexgrouphandle
　　INNERJOINsys。dmdbmissingindexdetailsd
　　ONd。indexhandleg。indexhandle
　　ORDERBY〔TotalCost〕DESC；
　　找到索引缺失的表，根据查询结果中的关键次逐一建立索引。