SqlServerDBCC SHRINKFILE不起作用

2022-03-20 01:30:47

检查索引碎片的结果：

CREATE DATABASE test_shrink

 USE test_shrink

 CREATE TABLE show_extent(a INT,b NVARCHAR(3900))

DECLARE @i INT

SET @i=1

WHILE @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_grants

CROSS 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 q

ORDER 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

码农公寓

相关文章