有网友指出,SQL Server 2012中fn_PhysLocFormatter内部函数在解析数据行记录位置时存在错误,见:http://www.itpub.net/thread-1751655-1-1.html
实际测试后发现,一是2008R2中同样存在问题,二是不仅页号解析存在问题,槽号解析也存在同样问题。
下面先查看表NT_SiteInfo的数据行记录位置。
select SiteID,%%physloc%%,sys.fn_PhysLocFormatter(%%physloc%%) from NT_SiteInfo
SiteID | %%physloc%% | sys.fn_PhysLocFormatter(%%physloc%%) |
1 | 0xE900000001000000 | (1:59648:0) |
23 | 0xE900000001000100 | (1:59648:1) |
24 | 0xE900000001000200 | (1:59648:2) |
| ...... |
|
149 | 0xE900000001007F00 | (1:59648:127) |
150 | 0xE900000001008000 | (1:59648:128) |
151 | 0xE900000001008100 | (1:59648:33024) |
152 | 0xE900000001008200 | (1:59648:33280) |
| ...... |
|
226 | 0xE90000000100CC00 | (1:59648:52224) |
227 | 0xE90000000100CD00 | (1:59648:52480) |
228 | 0x4B02000001000000 | (1:587:0) |
229 | 0x4B02000001000100 | (1:587:1) |
| ...... |
|
360 | 0x4B02000001007F00 | (1:587:127) |
361 | 0x4B02000001008000 | (1:587:128) |
362 | 0x4B02000001008100 | (1:587:33024) |
363 | 0x4B02000001008200 | (1:587:33280) |
| ...... |
|
422 | 0x4B0200000100BD00 | (1:587:48384) |
423 | 0x4B0200000100BE00 | (1:587:48640) |
424 | 0x3C05000001000000 | (1:1340:0) |
425 | 0x3C05000001000100 | (1:1340:1) |
| ...... |
|
552 | 0x3C05000001008000 | (1:1340:128) |
553 | 0x3C05000001008100 | (1:1340:33024) |
|
|
|
596 | 0x3C0500000100AC00 | (1:1340:44032) |
597 | 0x9978000001000000 | (1:39288:0) |
| ...... |
|
658 | 0x9978000001003D00 | (1:39288:61) |
下面查看表NT_SiteInfo分配的数据页情况。
dbcc ind(wjgk,nt_siteinfo,0)
PagePID | IAMFID | IAMPID | PageType | IndexLevel | NextPagePID | PrevPagePID |
238 | NULL | NULL | 10 | NULL | 0 | 0 |
233 | 1 | 238 | 1 | 0 | 587 | 0 |
587 | 1 | 238 | 1 | 0 | 1340 | 233 |
1340 | 1 | 238 | 1 | 0 | 30873 | 587 |
30873 | 1 | 238 | 1 | 0 | 0 | 1340 |
Microsoft未公开的伪列%%physloc%%,类型为Binary(8),返回表中记录的RowID,格式是:前4字节表示页号,中间2字节表示文件号,最后2字节表示槽号。
对照上面的实际数据,可以发现sys.fn_PhysLocFormatter在解析记录位置时,既有采用高字节在前的BIG_ENDIAN格式,又有采用低字节在前的LITTLE_ENDIAN格式,造成采用高字节在前的BIG_ENDIAN格式解析的数据错误:
页号解析:
E9000000解析为59648(E900),错误,实际应为233(E9)
4B020000解析为576(24B),正确
3C050000解析为1340(53C),正确
99780000解析为39288(9978),错误,实际应为30873(7899)
槽号解析:
8000解析为128(0080),正确
8100解析为33024(8100),错误,应为129(0081)
转载于:http://blog.itpub.net/81227/viewspace-751651/