SqlServerDBCC SHRINKFILE不起作用

检查索引碎片的结果：

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名

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