SqlServerDBCC SHRINKFILE不起作用

SqlServerDBCC SHRINKFILE不起作用

检查索引碎片的结果：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117

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名

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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资源用尽，连一些很简单的查询都不能响应的现象发生）

 

1 2 3 4 5 6 7 8 9 10 11 12

--检查语句：  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，这个视图应该是空的

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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名

1 2 3 4 5 6 7 8 9

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

 

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

1 2 3 4 5 6 7 8 9 10 11

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条语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

--返回最经常运行的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