none
%%bmk%% 是什么 RRS feed

  • 问题

  • 在服务器上看到这样的语句,116这个spid在创建一个非聚集索引,sqlserver版本为sql2008r2 64位 企业版

    表名:SpecialItem

    116 0 277508 0 108 7 NULL CREATE INDEX CXPACKET NULL t [dbo].[SpecialItem] select *, %%bmk%% from [dbo].[SpecialItem 2015-10-22 17:36:50.197 Shared memory Serializable 10 11 <local machine> NTLM 2015-10-22 17:41:27.677 0x06002100B845F72F40013C7E040000000000000000000000

    %%bmk%% 是什么呢,从来未见过


    Love SQL

    2015年10月22日 9:44

答案

  • 找到了

    http://www.sqldbadiaries.com/2010/09/06/select-bmk-from-tablename-while-rebuilding-the-index/

    insert [DatabaseName].[dbo].[TheActualTableName] select *, %%bmk%% from [DatabaseName].[dbo].[TheActualTableName]


    Even though this whitepaper talks about Online Reindex, it talks about how an Index is rebuilt in general. During the Build Phase of rebuilding the index, the new index is built by Bulk Inserting data from the old index. During the bulk insertion, the above insert statement is issued. Since the table in question was 50 GB in size and the disk subsystem was slow, this query ran for a long time and it was captured by Idera Diagnostic Manager.

    sqlserver使用bulkt insert将表数据导出,然后导入到新索引里面,一般只出现在,with online=ON的情况下


    Love SQL

    2015年10月22日 10:00