0

How to write bad SQL - 巧改SUM

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

How to write bad SQL - 巧用BASE DATE

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

How to write bad SQL - 别用EXISTS

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

用dm_tran_locks来观察锁

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

LOB 物理存储

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

ROW_OVERFLOW 物理存储

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