0
Posted on Wednesday, July 05, 2017 by 醉·醉·鱼 and labeled under
最近数据库服务器很慢,跑了一下whoIsActive,发现有很多waittype是RESOURCE_SEMAPHORE,发现很是好玩,整理一下。

一个成功的查询操作会引入3个内存的使用,即查询语句的编译,查询计划的缓存以及数据的缓存。

如何查看编译需要的内存,数据缓存,以及cached plan size

    1. 通过Actual query plan是最快的方法。第一个就是cached plan size(KB), 此外还有CompileMemory(B)和MemoryGrantInfo下的GrantedMemory(KB)。
    2. 在执行计划中查看
    3. 用SQL profiler捕获show plan XML for query compile event,进而在XML查看。其实拿到的数据和第二步一样。

如何模拟RESOURCE_SEMAPHORE

    1. https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/ 上例举了两个query。在SSMS里面,多开几个窗口,同时跑第二个query就行了。

RESOURCE_SEMAPHORE情况下,有哪些有趣的数据

    1. sp_whoIsActive 执行sp_whoIsActive 就可以直观看到哪个session已经相应的query
    2. sys.dm_exec_query_resource_semaphores 可以看到当前的内存情况
    3. sys.dm_exec_query_memory_grants 可以看到session获取内存的情况

RESOURCE_SEMAPHORE情况下,所有query都会suspend么?

    1. 不会。如果你的query还是像Brent Ozar提到的第二个query一样需要很多的内存,那就妥妥地等着吧。如果你的query需要的内存很少,那SQL server会直接执行。

引用

https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/
https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/
0
Posted on Friday, June 09, 2017 by 醉·醉·鱼 and labeled under
熊在微信上找我,问解数独的三种方法。我第一反应是写段代码去解,就哼哧哼哧地写了。后来才发现,她只是要方法而已,就直接丢给链接过去了。(http://www.conceptispuzzles.com/zh/index.aspx?uri=puzzle/sudoku/techniques

但自己还是把这段代码写完了,试了一下,能够解出唯一结果,如果有多个可能就不行了(暂时把可能性列出来了而已)。
最新代码已经上传到 https://github.com/zhongxiao37/zhongxiao37.github.com/blob/master/sudo/sudo.rb

0
Posted on Monday, May 22, 2017 by 醉·醉·鱼 and labeled under ,
Python 和 C 一样,有形参和实参的概念。但是我用ruby的时候,却没有这样的感觉。我每次都会data = function(data),这样的话,我就不需要去担心到底是传递值还是传递指针。但是,就Ruby而言,传递的是对象的引用。详情可以参考:http://www.iteye.com/topic/1117575




0
Posted on Thursday, May 04, 2017 by 醉·醉·鱼 and labeled under
今天发现个新问题。发现在循环中声明变量并不会reset 变量值。

DECLARE @i INT = 0;

WHILE @i < 10
BEGIN
    DECLARE @b int

    print CONCAT('PREV:', @b)

    SET @b = @i

    PRINT CONCAT('NOW:', @b)

    SET @i = @i+1

END


执行上面的query,你会发现PREV的输出并不一致是空,而是有值的。顺手搜了一下,发现很多人都发现这个问题,但是却没有一个官方文档解释这个。立即创建一个规则去避免这坑。
0
Posted on Wednesday, April 26, 2017 by 醉·醉·鱼 and labeled under
Antlr的全程是ANother Tool for Language Recognition,可以用来实现编程语言的解析。由于项目的需要,最近一个月和同事完成了一个针对TSQL的语法分析,进而创建一些规则去审查代码(连这都自动化掉,我实在是太懒了!)。
Antlr是一个比较老的项目了,最新的版本是4.7。个人觉得,这个项目现在之所以成功,还是因为开源以后,很多人都来贡献Antlr的语法文件。在github上,你可以找到主流编程语言的语法文件(https://github.com/antlr/grammars-v4/)。有了语法文件,你就可以直接通过简单的教程使用Antlr。
我们在使用Antlr的时候,已经有了TSQL.g4文件了。语法不是很完整,但我们可以自己贡献代码去帮助其更加完整。可以预见的是,这些语法文件会越来越完整的。

学习Antlr

最经典的还是这个工具开发者自己写的书《The Definitive ANTLR 4 Reference》。快速入门,可以参考https://tomassetti.me/antlr-mega-tutorial/#working-with-a-listener 以及 http://jakubdziworski.github.io/java/2016/04/01/antlr_visitor_vs_listener.html
此外,中文的可以参考https://dohkoos.gitbooks.io/antlr4-short-course/content/getting-started.html

分析TSQL

这个项目的目的就是分析TSQL,进而审查代码,以便开发避免一些坏的编程习惯。要分析TSQL,我们第一反应都是用正则表达式。很不幸,在这个事情上,正则表达式简直是弱爆了!我找到一个比较成熟的语法分析工具,不过是针对pg数据库的。https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html。文中作者在无数次尝试以后,也发现正则表达式的无力。
通常,我们会看微软自己有没有开发的工具或者API用来分析TSQL。最好的情况就是SSMS里面有个语法分析的组件单独分离出来使用。其实后来我也找到了这样的工具,叫 microsoft.sqlserver.transactsql.scriptdom。但是后来我们考虑到可能还要支持其他类型的数据库,所以我还是选择了Antlr。
在Github上面找到tsql的语法文件,再按照快速入门的步骤,配置好环境,执行下面的命令,就可以拿到整个语法树了。
antlr4 tsql.g4 && javac *.java && grun tsql tsql_file -gui test.sql

Vistior 和 Listener之争

这只是分析语法的两种不同方式而已,看自己的情况选择。一般来说,如果你只想分析某一个rule以及他下面的rule,可以考虑用Visitor。用Listener的话,可以自动的遍历所有的rule。个人而言,还是喜欢Listener。
比如,我们要求在存储过程中,不能够使用PRINT语句,因为我们不希望存储过程返回除了respond code之外的信息。用Listener的话,我只需要在进入和退出存储过程的时候,设置一个flag。然后再判断所有的PRINT语句,如果当前flag为true,就表明这个PRINT是在存储过程里面。


0
Posted on Monday, February 27, 2017 by 醉·醉·鱼 and labeled under
计算某列的和,可以直接用SUM。但如果和SIGN结合起来,事情就有可能复杂了。比如,SUM可以替换成下面一行


SUM((1 - SIGN(Amount)) * Amount / 2) + SUM((1 + SIGN(Amount)) * Amount / 2)

其中,前一部分是算所有正数和,比CASE WHEN 巧妙。

SELECT SUM((1 + SIGN(Amount)) * Amount / 2)

后一部分是算负数和的绝对值。

SELECT SUM((1 - SIGN(Amount)) * Amount / 2)

巧妙+巧妙却不一定等于巧妙!

此外,还有求绝对值的和

SUM((1 + SIGN(Amount)) * Amount / 2) - SUM((1 - SIGN(Amount)) * Amount / 2)
0
Posted on Monday, February 27, 2017 by 醉·醉·鱼 and labeled under
如果要计算昨天的时间,第一反应都是

SELECT DATEADD(DAY, -1, GETUTCDATE())

但还有另外一种写法。因为SQL SERVER的base date是1900-01-01,所以,你可以试着写成下面这样,同样可以得到昨天。

SELECT GETUTCDATE() + '1899-12-31'

可能时间长了,大家会免疫这种写法,你还可以改成,1899-12-30,即-2天。


SELECT DATEADD(DAY, 1, GETUTCDATE()) + '1899-12-30'

还可以这样

SELECT GETUTCDATE() - 1
0
Posted on Monday, February 27, 2017 by 醉·醉·鱼 and labeled under
有这么一个需求,查某个customer在某个时间段内是否买个产品。一般情况下,都会用JOIN,然后再去重即可。比如

SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849

这样做有个问题,如果customer对应的orders比较多的时候,IO开销就不一样了。因为执行计划会每次读取所有的orders,然后再去重。

相反,如果用EXISTS,执行计划就会去判断是否买过产品,买过即停止,不会继续扫描。

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849

对比IO开销,前者比后者开销更多。在复杂query和数据量比较多的情况下,这种差距更加明显。

此外,如果query一直这么简单,那么SQL SERVER会自动优化,会在执行计划中加入TOP操作,这样就不会把所有记录都查出来再去重。

所以,尽所有可能,把EXISTS都替换成JOIN,增加IO开销吧!






use master;

IF NOT EXISTS(SELECT 1 FROM SYS.DATABASES WHERE NAME = 'test_db')
    CREATE DATABASE test_db;
GO

use test_db;
GO


IF OBJECT_ID(N'customers') IS NOT NULL
    DROP TABLE customers;

CREATE TABLE customers (
    id int,
    CONSTRAINT pk_customers PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO customers(id)
SELECT n
FROM dbo.getnums(500000)


IF OBJECT_ID(N'orders') IS NOT NULL
    DROP TABLE orders;

CREATE TABLE orders (
    id int identity(1, 1),
    customer_id int,
    order_dt datetime,
    CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (ID),
    CONSTRAINT fk_orders__customer_id FOREIGN KEY (customer_id) REFERENCES customers(id)
)


INSERT INTO orders(customer_id, order_dt)
SELECT n/100+1, DATEADD(DAY, n/700, '2000-01-01')
FROM dbo.getnums(5000000)

INSERT INTO orders(customer_id, order_dt)
SELECT 43849, DATEADD(DAY, n/70, '2000-01-01')
FROM dbo.getnums(500)


SELECT DISTINCT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
AND c.id=43849
OPTION(RECOMPILE, MAXDOP 1)

SELECT c.id
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.order_dt BETWEEN '2017-02-01' AND '2017-03-01'
    )
AND c.id=43849
OPTION(MAXDOP 1)

0
Posted on Wednesday, February 22, 2017 by 醉·醉·鱼 and labeled under
背景是,有两个report的金额不平,需要找出里面不平的原因。很早以前是用这个工具去实现的。
新建一个excel文件,在文件里A、B栏输入report A的key和value,在D、E栏输入report B的key和value,然后执行下面的命令就行了。



代码


require 'slop'
require 'logger'
require 'simple_xlsx_reader'


# Usage
# ruby discrepancy_finder.rb [options]
# options:
# -v print the version
# -d enable debug mode
# -m model:
#     model = 1 check both keys and values
#     model = 2 only check keys
# -s sheet index: 0 will be the first sheet
# example:
# ruby discrepancy_finder.rb
# ruby discrepancy_finder.rb -d
# ruby discrepancy_finder.rb -m 2
# ruby discrepancy_finder.rb -d -m 2 -s 0


opts = Slop.parse do |o|
  o.integer '-m', '--model', 'model', default: 1
  o.integer '-s', '--sheet_index', 'specific sheet number', default: 0
  o.bool '-d', '--debug', 'enable debug mode'
  o.on '-v', 'print the version' do
    puts '0.0.1'
    exit
  end
end

def logger
  @logger ||= Logger.new(STDOUT)
end

def load_col_data(wb, col_index)

  _hash = Hash.new

  lastrow = 1

  while wb.rows[lastrow] && wb.rows[lastrow][col_index]

      _hash["#{wb.rows[lastrow][col_index]}"] = (_hash["#{wb.rows[lastrow][col_index]}"].nil? ? 0 : _hash["#{wb.rows[lastrow][col_index]}"]) + ( wb.rows[lastrow][col_index+1].nil? ? 0 : wb.rows[lastrow][col_index+1].to_f.round(2) )
      lastrow = lastrow + 1
  end

  _hash

end

doc = SimpleXlsxReader.open("revenue_report_diff.xlsx")

wb = doc.sheets[opts[:sheet_index]]

logger.debug "Running in model #{opts[:model]}" if opts.debug?

#remove duplicate records
left_hash = load_col_data(wb, 0)
right_hash = load_col_data(wb, 3)


leftSum = left_hash.values.inject(:+).round(2)
rightSum = right_hash.values.inject(:+).round(2)


revenueDiff = leftSum - rightSum
puts '##########REVENUE DIFF(LEFT - RIGHT)##############'
if opts[:model] == 2
  puts "LEFT: " + leftSum.to_s
  puts "RIGHT: " + rightSum.to_s
  puts "DIFF: " + revenueDiff.round(2).to_s
end

lostRecordsInRight =  left_hash.keys - right_hash.keys
lostRecordsInLeft = right_hash.keys - left_hash.keys

unless lostRecordsInRight.empty?
  puts 'Missing records in RIGHT side:'
  lostRecordsInRight.each do |t|
      puts t + "\t" + left_hash["#{t}"].round(2).to_s
  end
end

unless lostRecordsInLeft.empty?
  puts 'Missing records in LEFT side:'
  lostRecordsInLeft.each do |t|
      puts t + "\t" + right_hash["#{t}"].round(2).to_s
  end
end

if opts[:model] == 2
  puts 'Discrepancy:'
  left_hash.each do |k, v|
      if right_hash["#{k}"].nil?
        # puts k + " is missing."
      elsif left_hash["#{k}"].round(2) != right_hash["#{k}"].round(2)
          puts k + "\t" + left_hash["#{k}"].round(2).to_s + "\t" + right_hash["#{k}"].round(2).to_s
      end
  end
end
0
Posted on Tuesday, February 14, 2017 by 醉·醉·鱼 and labeled under ,
之前介绍过通过Extended Event观察锁,这次介绍通过dm_tran_locks来观察锁。



BEGIN TRANSACTION

DELETE TOP (1) p FROM tbl p 

EXEC ##TranLocks

ROLLBACK TRANSACTION



##TranLocks SP

CREATE PROCEDURE ##TranLocks
    @objectName NVARCHAR(255) = '%',
    @requestMode VARCHAR(10) = '%',
    @processID INT = @@SPID,
    @ignoreSharedDatabaseLocks BIT = 1
AS
SELECT 
    DB_NAME() AS database_name,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END AS object_name,
    in1.name AS index_name,
    pa1.partition_number,
    dt1.resource_type,
    dt1.request_mode,
    COUNT(*) AS locks,
    dt1.request_owner_id,
    dt1.request_session_id
FROM
    sys.dm_tran_locks dt1
    LEFT OUTER JOIN sys.partitions pa1 ON(dt1.resource_associated_entity_id = pa1.hobt_id)
    LEFT OUTER JOIN sys.indexes in1 ON(pa1.object_id = in1.object_id AND pa1.index_id = in1.index_id)
WHERE 
    (request_session_id = @processID OR @processID = 0) 
    AND request_mode LIKE @requestMode
    AND(NOT(dt1.resource_type = 'DATABASE' AND dt1.request_mode = 'S') OR @ignoreSharedDatabaseLocks = 0)
    AND dt1.resource_database_id = DB_ID()
    AND 
        CASE 
            WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
            WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
            ELSE dt1.resource_description
            END LIKE @objectName
GROUP BY
    dt1.resource_type,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END,
    in1.name,
    pa1.partition_number,
    dt1.request_mode,
    dt1.request_session_id,
    dt1.request_owner_id
ORDER BY database_name, object_name, index_name, partition_number, request_mode, locks;
GO

0
Posted on Tuesday, February 14, 2017 by 醉·醉·鱼 and labeled under ,
直接上测试脚本。LOB可以存储VARCHAR(MAX), TEXT, IMAGE这些数据类型。但是各个类型的behavior又不太一样。

和OVERFLOW DATA 一样,数据页会有一个pointer指向LOB root structure,在LOB root structure上,又会分别指向其他LOB data page。如果数据超过32KB,LOB root structure又会引入中间层,像INDEX中的B tree一样。




NVARCHAR(MAX)

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 nvarchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),16000));


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('LobData'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 623876, 3); 
GO


当Column为NVARCHAR(MAX)且16000字符串(实际长度为32000)的时候,SQL server不会单独开辟一个新的page去存储LOB root structure。相反,LOB pointer会存在当前page。1个IN_ROW数据页,4个LOB 数据页。指针留在第一个数据页。

DBCC PAGE第一个数据页,可以看到BLOB Inline Root。其中,LOB pointer和ROW_OVERFLOW的pointer类似,如下


  1. 04 00000001 00000020 1c0000 metadata
  2. 68 1f0000 length
  3. 01 850900 page number
  4. 01 00 file number
  5. 0000 slow number

page number按照前一个文章的方法,实际应该是0x98501,即623873。同理可以拿到剩下3个pointer。

d0 3e000002 850900
01 000000
38 5e000003 850900
01 000000
00 7d000037 520900
01 000000




TEXT


可能是对deprecated 数据类型支持不好,对于TEXT直接就开辟一个新的page为LOB root structure。DBCC PAGE LOB root structure,可以看到LOB pointer。

VARCHAR(MAX)

和NVARCHAR(MAX)一样的行为,当数据比较短的时候,存为BLOB Inline Root。当数据更短的时候,直接就存在当前IN_ROW page。


IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 varchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),400));

在这个例子中,只有两个page。一个是IAM,另外一个就是数据页,没有单独的LOB page。
DBCC page数据页,可以看到BLOB Inline Data。



参考:

  1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
  2. http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/





0
Posted on Monday, February 13, 2017 by 醉·醉·鱼 and labeled under ,

SQL Server有IN_ROW,ROW_OVERFLOW和LOB 3种page。

首先,来看看ROW_OVERFLOW是怎么存储的吧。

首先创建一个Table,插入一下数据。ID和Col1会存储在第一个page上,即IN_ROW page。


IF OBJECT_ID('RowOverflow') IS NOT NULL
    DROP TABLE RowOverflow;
GO
create table dbo.RowOverflow 
( 
    ID int not null, 
    Col1 varchar(8000) null, 
    Col2 varchar(8000) null 
);

insert into dbo.RowOverflow(ID, Col1, Col2) 
values (1,replicate('a',8000),replicate('b',8000));


验证一下表的数据页
SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('RowOverflow'),
                                          null, null, 'DETAILED');
GO

结果如图。一共有4个page。两个IAM,一个数据页,另外一个LOB或者Overflow page。

执行DBCC PAGE,可以看到第一页的存储情况。

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 610871, 3); 
GO



数据的最后一共有24bytes,这些都是字节交换的,所以需要倒着读。

  1. 02000000 01000000 29000000 401f0000 是metadata attribute。
  2. 31520900 是page number
  3. 0001 是file number
  4. 0000 是slot number

在Kalen写的SQL SERVER 2012 internals里面提到了metadata attribute。

The first 16 bytes of a row-overflow pointer

Bytes
Hex value
Decimal value
Meaning
0
0x02
2
Type of special field: 1 = LOB2 = overflow
1–2
0x0000
0
Level in the B-tree (always 0 for overflow)
3
0x00
0
Unused
4–7
0x00000001
1
Sequence: a value used by optimistic concurrency control for cursors that increases every time a LOB or overflow column is updated
8–11
0x00007fc3
32707
Timestamp: a random value used by DBCC CHECKTABLE that remains unchanged during the lifetime of each LOB or overflow column
12–15
0x00000834
2100
Length

31520900应该读作0X00095231,换做十进制的610865。


继续往下看,你可以看到Col2的RowId记录的page number。这个也印证了我们从dm_db_database_page_allocations里看到的情况。
来看看ROW_OVERFLOW page的情况。就可以看到Col2的数据了。


DBCC PAGE ('test_db', 1, 610871, 3); 
GO


参考
  1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
  2. https://www.microsoftpressstore.com/articles/article.aspx?p=2225060
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。