HoneyHoney

总是不喜欢UI，点得慢死了~

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @dbDataPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 
DECLARE @NoExec bit
DECLARE @debug bit


-- 2 - Initialize variables 
SET @dbName = 'KEY_WORD_IN_YOUR_BACKUP_FILE' 
SET @backupPath = 'D:\' 
SET @dbDataPath = 'D:\Data\'
SET @NoExec = 1
SET @debug = 1

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE 'Servlet.' + @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastFullBackup + '''' + CHAR(10) + 'WITH RECOVERY, REPLACE,'  + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_dat'' TO N''' + @dbDataPath + @dbName + '.mdf'','   + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_log'' TO N''' + @dbDataPath + @dbName + '_log.LDF'',  NOUNLOAD,  STATS = 5'

IF @debug = 1
PRINT @cmd 
IF @NoExec <> 1
EXEC (@cmd)

可能大家都习惯了SELECT * FROM TABLE，或者SELECT DATA FROM TABLE WHERE ID = 1。一般来说，无非就是INDEX SEEK，或者CLUSTERED INDEX SCAN。但下面这个例子，却两者都不是。

IF OBJECT_ID('DBO.T1') IS NOT NULL
    DROP TABLE t1

CREATE TABLE T1(
    ID BIGINT,
    ANOTHER_ID INT,
    DATA VARCHAR(200),
    CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO T1(ID, ANOTHER_ID,DATA)
SELECT n,
    n%4, 
    CASE WHEN n % 4 = 1 THEN 'pHoEnIx'
                    WHEN n % 4 = 2 THEN 'eRiK'
                    WHEN n % 4 = 3 THEN 'dBe'
                    ELSE 'Papa'
                    END
FROM dbo.getnums(50000)

CREATE NONCLUSTERED INDEX idx_t1_another_id ON t1(another_id)

SELECT ID FROM T1
SELECT DATA FROM T1

对于第二个SELECT query，很明显，DATA不被包含在non-clustered index里面，所以只能够用CLUSTERED INDEX SCAN。但是第一个SELECT query，却是INDEX SCAN。WHY？是错误的执行计划？不是。
事实是，很明显non-clustered index占用的空间比clustered index小，而且non-clustered index的最后一个节点就是ID，不用它用谁。此外，如果一个表上面有多个non-clustered index，SQL SERVER会用INDEX占用最小的INDEX。



如果一切这么简单就好，这样的query单独列出来可能稍微仔细看就发现问题了。继续上面的SQL script

CREATE TABLE T2(
    ID INT
)

INSERT INTO T2(ID)
SELECT n*4
FROM dbo.getnums(50000)

DELETE FROM T2 WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE T1.ID = t2.ID)

当T2的数据级和T1的数据级差不多的时候，SQL SERVER就不会SCAN T2再去T1里面进行CLUSTERED INDEX SEEK。相反，SQL SERVER会对两个表都进行SCAN，再HASH MATCH。这里很隐晦地把SELECT ID FROM TABLE转换成SELECT 1 FROM TABLE WHERE ID ...。满心以为这个很明显的是CLUSTERED INDEX SEEK啊， WHERE ID = 啊。等你看执行计划的时候，你就傻眼了。啥？！INDEX SCAN？？


一旦是INDEX SCAN，一般都是PAGE级别的锁，放在non-clustered index上，一不留神就会导致长时间BLOCKING。