天天看點
傳回

SqlServerDBCC SHRINKFILE不起作用

檢查索引碎片的結果：

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不起作用

 處理過後的索引碎片：

SqlServerDBCC SHRINKFILE不起作用

 --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
sql 信号量 sed
上一篇: Spring Cloud調用接口過程
下一篇: spring boot 不同環境讀取不同的配置檔案(yml)

繼續閱讀