SqlServerDBCC SHRINKFILE不起作用

SqlServerDBCC SHRINKFILE不起作用

检查索引碎片的结果：

CREATE DATABASE test_shrink USE test_shrink CREATE TABLE show_extent(a INT,b NVARCHAR(3900))DECLARE @i INTSET @i=1WHILE @i<=100 BEGIN INSERT INTO show_extent VALUES(1,REPLICATE(N'a',3900)) INSERT INTO show_extent VALUES(2,REPLICATE(N'b',3900)) INSERT INTO show_extent VALUES(3,REPLICATE(N'c',3900)) INSERT INTO show_extent VALUES(4,REPLICATE(N'd',3900)) INSERT INTO show_extent VALUES(5,REPLICATE(N'e',3900)) INSERT INTO show_extent VALUES(6,REPLICATE(N'f',3900)) INSERT INTO show_extent VALUES(7,REPLICATE(N'g',3900)) INSERT INTO show_extent VALUES(8,REPLICATE(N'h',3900)) SET @i=@i+1 END --检查索引碎片 DBCC SHOWCONTIG('show_extent') --删除a列不是5的数据 DELETE dbo.show_extent WHERE a<>5 --显示数据文件 64kb EXEC sys.sp_spaceused @objname = N'show_extent' -- nvarchar(776) DBCC SHOWCONTIG('show_extent') --查看数据库的文件和日志大小 EXEC sys.sp_helpfile --fileid为1 收缩到40MB DBCC SHRINKFILE(2,40) --建立索引释放没有使用的区 CREATE CLUSTERED INDEX show_I ON dbo.show_extent(a) --检查索引碎片 DBCC SHOWCONTIG('show_extent') --收缩文件 DBCC SHRINKFILE(1,1) --查看数据库的占用空间和未分配的空间 EXEC sys.sp_spaceused @objname = N'show_extent' SELECT * FROM dbo.show_extent --找出每个区的对象理论上区数目和实际数目,然后重建大对象类型的表 USE test_shrink --建立临时表 CREATE TABLE #extentinfo ( [file_id] SMALLINT, page_id INT, pg_alloc INT, ext_size INT, obj_id INT, index_id INT, partition_number INT, partition_id BIGINT, iam_chain_type VARCHAR(50), pfs_bytes VARBINARY(10) ) CREATE PROCEDURE import_extentinfo as DBCC extentinfo DBCC extentinfo('test_shrink') INSERT INTO #extentinfo EXEC import_extentinfo SELECT [file_id],obj_id,index_id,partition_id,ext_size, 'actual extent count'=COUNT(*),'actual page count'=SUM(pg_alloc), 'possible extent count'=ceiling(SUM(pg_alloc)*1.0/ext_size), 'possible extents/actual extents'= (ceiling(SUM(pg_alloc)*1.00/ext_size)*100.00)/COUNT(*)FROM #extentinfo GROUP BY file_id,obj_id,index_id,partition_id,ext_size HAVING COUNT(*) -ceiling(SUM(pg_alloc)*1.0/ext_size)>0 ORDER BY partition_id,obj_id,index_id,file_id --SQL2005以后有一个动态管理视图sys.dm_exec_query_stats,返回缓存查询计划的性能统计信息 --SQL会统计从上次SQL启动以来，一共做了多少次logical读写，多少次physical读，还记录执行所用的 CPU时间总量 --按照物理读的页面数排序 前50名 SELECT TOP 50 qs.total_physical_reads,qs.execution_count, qs.total_physical_reads/qs.execution_count AS [avg I/O], --截取字符串 SUBSTRING(qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text, qt.dbid,dbname=DB_NAME(qt.dbid), qt.objectid, qs.sql_handle, qs.plan_handle from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_physical_reads DESC --SQL Trace里面有一个reads字段，记录了某条语句完成过程中一共做了多少次读的动作，找到read最多的语句 --每个SQL Trace里有成千成万的语句，可以使用fn_trace_gettable 像一张表一样把trace文件里的记录查询出来 --可以用他将记录转入到SQLSERVER里，然后用查询语句进行统计分析。 SELECT * INTO #SAMPLE FROM sys.fn_trace_gettable('C:\Users\Administrator\Desktop\1.trc',DEFAULT) WHERE EventClass IN(10,12) select * from sys.sysprocesses --运行下面DBCC命令释放SQL内存缓存 DBCC freesessioncache DBCC freeproccache

处理过后的索引碎片：

--SQL2005以后有一个动态管理视图sys.dm_exec_query_stats,返回缓存查询计划的性能统计信息 --SQL会统计从上次SQL启动以来，一共做了多少次logical读写，多少次physical读，还记录执行所用的   CPU时间总量  --按照物理读的页面数排序 前50名

SELECT TOP 50 qs.total_physical_reads,qs.execution_count, qs.total_physical_reads/qs.execution_count AS [avg I/O], --截取字符串 SUBSTRING(qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text, qt.dbid,dbname=DB_NAME(qt.dbid), qt.objectid, qs.sql_handle, qs.plan_handle from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_physical_reads DESC

--找出使用内存比较多的语句，简化他们，调整应用程序行为，减少工作负荷--检查动态管理视图,了解每个查询资源信号量的状态信息。（SQL里默认有两个查询资源信号量，分别处理复杂度不一样 --的查询，这样的设计有助于防止几个超大的查询把整个SQL资源用尽，连一些很简单的查询都不能响应的现象发生）

--检查语句： SELECT CONVERT(VARCHAR(30),GETDATE(),121) AS runtime, resource_semaphore_id, target_memory_kb, total_memory_kb, available_memory_kb, granted_memory_kb, used_memory_kb, grantee_count, waiter_count, timeout_error_count from sys.dm_exec_query_resource_semaphores

--resource_semaphore_id:资源信号量的非唯一ID，0表示常规资源信号量，1表示小型查询资源信号量 --target_memory_kb:该资源信号量能够授予使用的内存目标，也就是当前的使用上限 --total_memory_kb:资源信号量现在所持有的总内存，是可用内存和被授予内存的和。如果系统内存不足或频繁强制缩小内存，该值可以 --大于target_memory_kb值，但意味着这个资源信号量有内存压力 --available_memory_kb:可用于新授予的内存 --granted_memory_kb:授予的总内存--used_memory_kb:授予内存中实际使用的部分 --grantee_count:内存授予得到满足的活动查询数 --waiter_count:等待内存授予得到满足的查询数，如果不为0，意味着内存压力存在 --timeout_error_count:自服务器启动以来的超时错误总数，对于小型查询资源信号量，该值为null

--检查sys.dm_exec_query_memory_grants,返回已经获得内存授予的查询的有关信息，或依然在等待内存授予的查询的 --有关信息。无须等待就获得内存授予的查询将不会出现在此视图中。所以对一个没有内存压力的SQL，这个视图应该是空的

SELECT GETDATE() AS runtime, session_id, scheduler_id, dop, request_time, grant_time, requested_memory_kb, granted_memory_kb, used_memory_kb, timeout_sec, query_cost, resource_semaphore_id, wait_order, is_next_candidate, wait_time_ms, REPLACE(REPLACE(CAST(s2.text AS VARCHAR(4000)),CHAR(10),''),CHAR(13),'') AS sql_statement FROM sys.dm_exec_query_memory_grantsCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

--返回控制--session_id:正在运行查询的会话ID（spid） --scheduler_id：正在计划查询的SQL Processor调度的ID--dop：查询的并行度 --request_time：查询请求内存授予的日期和时间 --grant_time：向查询授予内存的日期和时间。如果尚未授予内存，则此值为null --requested_memory_kb:请求的内存总量 --granted_memory_kb:实际授予的内存总量。如果尚未授予内存，该值为null。在典型情况下，该值应该与requested_memory_kb相同 --创建索引时，除了初始授予的内存外，服务器还允许增加按需分配的内存 --used_memory_kb:此刻使用的物理内存--query_cost:估计查询开销 --timeout_sec:查询放弃内存授予请求前的超时时间 --resource_semaphore_id:查询正在等待的资源信号量的非唯一ID --wait_order:等待查询在指定的queue_id中的顺序，如果其他查询获得内存授予或超时，则给定查询的该值可以更改。如果已授予内存，则为null--is_next_candidate:下一个内存授予的候选对象：1：是  0：否 null：已授予内存 --wait_time_ms:等待时间。如果已经授予内存，则为null--plan_handle:查询计划的标志符。使用sys.dm_exec_query_plan可提取实际的xml计划 --sql_handle:查询的TSQL文本标志符。查询中使用他链接sys.dm_exec_sql_text获取实际的TSQL文本

--SQL2005 DMV SQL启动以来累计使用CPU资源最多的语句 前50名

SELECT highest_cpu_queries.*, highest_cpu_queries.total_worker_time, DB_NAME(q.dbid) AS dbname, q.[text] AS qtext from (SELECT TOP 50 qs.* from sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qORDER BY highest_cpu_queries.total_worker_time DESC

--找到最经常做重编译的存储过程

SELECT TOP 25 sql_text.text AS sqltext, sql_handle AS sqlhandle, plan_generation_num AS plangenerationnum, execution_count AS execcount, DB_NAME(dbid) AS dbname, objectid AS objectid from sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text WHERE plan_generation_num>1 ORDER BY plan_generation_num DESC

--返回经常执行的100条语句

--返回最经常运行的100条语句 SELECT TOP 100 cp.cacheobjtype, cp.usecounts, cp.size_in_bytes, qs.statement_start_offset, qs.statement_end_offset, qt.dbid, qt.objectid,SUBSTRING(qt.text,qs.statement_start_offset/2,CASE WHEN qs.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2 ELSE qs.statement_end_offset END -qs.statement_start_offset/2)AS statement from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle=cp.plan_handle WHERE cp.plan_handle=qs.plan_handle AND cp.usecounts>4 ORDER BY dbid,usecounts DESC

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。