資料庫調校思路分享3 新建索引注意

目前的goal是要為資料量大的table新增cluster index

零,邏輯檔案如何分配?

1,filegroup

2,file

3,partition

一,用何種方法新建索引?
 1, 更名,重新塞入 (1,create new table with correct index 2,)
 (1)TABLE可拆分?
 (2)保留多久?
 (3)可掛維護?

2, 直接新建cluster index(量小才行)

二,確定方法後, 如何創建正確的索引?

1, 找尋Database目前/曾經running的語法,從中判斷.

正在執行的sql query:

SELECT      r.scheduler\_id as 排程器識別碼,  
            status         as 要求的狀態,  
            r.session\_id   as SPID,  
            r.blocking\_session\_id as BlkBy,  
            substring(  
                ltrim(q.text),  
                r.statement\_start\_offset/2+1,  
                (CASE  
                 WHEN r.statement\_end\_offset = -1  
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2  
                 ELSE r.statement\_end\_offset  
                 END - r.statement\_start\_offset)/2)  
                 AS [正在執行的 T-SQL 命令],  
            r.cpu\_time      as [CPU Time(ms)],  
            r.start\_time    as [開始時間],  
            r.total\_elapsed\_time as [執行總時間],  
            r.reads              as [讀取數],  
            r.writes             as [寫入數],  
            r.logical\_reads      as [邏輯讀取數],  
            -- q.text, /* 完整的 T-SQL 指令碼 */  
            d.name               as [資料庫名稱]  
FROM        sys.dm\_exec\_requests r   
            CROSS APPLY sys.dm\_exec\_sql\_text(sql\_handle) AS q  
            LEFT JOIN sys.databases d ON (r.database\_id=d.database\_id)  
WHERE       r.session\_id > 50 AND r.session\_id <> @@SPID  
ORDER BY    r.total\_elapsed\_time desc

曾經執行過的sql query:

SELECT TOP 1000  
--建立時間  
QS.creation\_time,  
--查詢語句  
SUBSTRING(ST.text,(QS.statement\_start\_offset/2)+1,  
((CASE QS.statement\_end\_offset WHEN -1 THEN DATALENGTH(st.text)  
ELSE QS.statement\_end\_offset END - QS.statement\_start\_offset)/2) + 1  
) AS statement\_text,  
--執行文字  
ST.text,  
--執行計劃  
QS.total\_worker\_time,  
QS.last\_worker\_time,  
QS.max\_worker\_time,  
QS.min\_worker\_time  
FROM  
sys.dm\_exec\_query\_stats QS  
--關鍵字  
CROSS APPLY  
sys.dm\_exec\_sql\_text(QS.sql\_handle) ST  
WHERE  
--QS.creation\_time BETWEEN '2018-05-08 09:00:00' AND '2018-05-08 18:00:00'  
ST.text LIKE '%ggDCDataDetails%'  
ORDER BY  
QS.creation\_time DESC

```

SELECT TOP 1000  
--建立時間  
QS.creation\_time,  
--執行文字  
ST.text  
FROM  
sys.dm\_exec\_query\_stats QS  
--關鍵字  
CROSS APPLY  
sys.dm\_exec\_sql\_text(QS.sql\_handle) ST  
--WHERE  
--QS.creation\_time BETWEEN '2018-05-08 09:00:00' AND '2018-05-08 18:00:00'  
--AND ST.text NOT LIKE '%SELECT * FROM T\_LOCATIONINFO WHERE STRCLIPLOGICID in(%'  
ORDER BY  
QS.creation\_time DESC

``` 2,詢問開發者目前所用到的SQL Script, 從中判斷.

參考來源:

https://blog.miniasp.com/post/2010/10/13/How-to-get-current-executing-statements-in-SQL-Server

• ← SQL Server--SQL的Lookup(查閱)執行計畫不會提示
• Excel--自訂快速存取工具列 →