SqlServerDBCC SHRINKFILE不起作用

原创

zhangyongshizhi 2022-08-06 00:19:48 博主文章分类：数据库SQL Server ©著作权

©著作权归作者所有：来自51CTO博客作者zhangyongshizhi的原创作品，请联系作者获取转载授权，否则将追究法律责任

检查索引碎片的结果：

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

SqlServerDBCC SHRINKFILE不起作用_sed

处理过后的索引碎片：

SqlServerDBCC SHRINKFILE不起作用_信号量_02

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