SqlServerDBCC SHRINKFILE不起作用
檢查索引碎片的結果：
CREATE DATABASE test_shrinkUSE test_shrinkCREATE TABLE show_extent(a INT,b NVARCHAR(3900))DECLARE @i INT SET @i=1 WHILE @i<=100BEGININSERT 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+1END--檢查索引碎片DBCC SHOWCONTIG('show_extent')--刪除a列不是5的數據DELETE dbo.show_extent WHERE a<>5--顯示數據文件 64kbEXEC sys.sp_spaceused @objname = N'show_extent' -- nvarchar(776)DBCC SHOWCONTIG('show_extent')--查看數據庫的文件和日志大小EXEC sys.sp_helpfile--fileid為1 收縮到40MBDBCC 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_extentinfoasDBCC extentinfoDBCC extentinfo('test_shrink')INSERT INTO #extentinfo EXEC import_extentinfoSELECT [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 #extentinfoGROUP BY file_id,obj_id,index_id,partition_id,ext_sizeHAVING COUNT(*) -ceiling(SUM(pg_alloc)*1.0/ext_size)>0ORDER BY partition_id,obj_id,index_id,file_id--SQL2005以后有一個動態管理視圖sys.dm_exec_query_stats,返回緩存查詢計劃的性能統計信息--SQL會統計從上次SQL啟動以來，一共做了多少次logical讀寫，多少次physical讀，還記錄執行所用的 CPU時間總量--按照物理讀的頁面數排序 前50名SELECT TOP 50qs.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=-1THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2ELSE 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_handlefrom sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY qs.total_physical_reads DESC--SQL Trace里面有一個reads字段，記錄了某條語句完成過程中一共做了多少次讀的動作，找到read最多的語句--每個SQL Trace里有成千成萬的語句，可以使用fn_trace_gettable 像一張表一樣把trace文件里的記錄查詢出來--可以用他將記錄轉入到SQLSERVER里，然后用查詢語句進行統計分析。SELECT * INTO #SAMPLEFROM sys.fn_trace_gettable('C:\Users\Administrator\Desktop\1.trc',DEFAULT)WHERE EventClass IN(10,12)select * from sys.sysprocesses--運行下面DBCC命令釋放SQL內存緩存DBCC freesessioncacheDBCC freeproccache?
?處理過后的索引碎片：
?--SQL2005以后有一個動態管理視圖sys.dm_exec_query_stats,返回緩存查詢計劃的性能統計信息
?--SQL會統計從上次SQL啟動以來，一共做了多少次logical讀寫，多少次physical讀，還記錄執行所用的?? CPU時間總量
? --按照物理讀的頁面數排序 前50名
??
--找出使用內存比較多的語句，簡化他們，調整應用程序行為，減少工作負荷
--檢查動態管理視圖,了解每個查詢資源信號量的狀態信息。（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_countfrom 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，這個視圖應該是空的
?
--返回控制
--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名
SELECThighest_cpu_queries.*,highest_cpu_queries.total_worker_time,DB_NAME(q.dbid) AS dbname,q.[text] AS qtextfrom(SELECT TOP 50 qs.* from sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS highest_cpu_queriesCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q ORDER BY highest_cpu_queries.total_worker_time DESC?
--找到最經常做重編譯的存儲過程
?
?
--返回經常執行的100條語句
--返回最經常運行的100條語句SELECT TOP 100cp.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 statementfrom sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtINNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle=cp.plan_handleWHERE cp.plan_handle=qs.plan_handleAND cp.usecounts>4ORDER BY dbid,usecounts DESC?
轉載于:https://www.cnblogs.com/sunliyuan/p/6567911.html
總結
以上是生活随笔為你收集整理的SqlServerDBCC SHRINKFILE不起作用的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 移动端返回上一页
- 下一篇： 面向对象设计原则之开闭原则