0
Posted on Sunday, January 22, 2017 by 醉·醉·鱼 and labeled under
总是不喜欢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)




0
Posted on Monday, January 16, 2017 by 醉·醉·鱼 and labeled under
可能大家都习惯了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。