Showing posts with label sql server internals. Show all posts
Showing posts with label sql server internals. Show all posts
0

SQL SERVER中的RESOURCE_SEMAPHORE

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

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