Data Science Lab

select * from sys.dm_tran_locks;

SELECT DTL.resource_type,

CASE

WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type

WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])

WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN

(

SELECT OBJECT_NAME([object_id])

FROM sys.partitions

WHERE sys.partitions.hobt_id =

DTL.resource_associated_entity_id

)

ELSE 'Unidentified'

END AS requested_object_name, DTL.request_mode, DTL.request_status,

DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame

FROM sys.dm_tran_locks DTL

INNER JOIN sys.sysprocesses SP

ON DTL.request_session_id = SP.spid

--INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id]

CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST

WHERE SP.dbid = DB_ID()

AND DTL.[resource_type] <> 'DATABASE'

ORDER BY DTL.[request_session_id];

Create table t_bulkload (

accountkey int not null,

accountdescription nvarchar (50),

accounttype nvarchar(50),

AccountCodeAlternatekey int

)

go

-- Let us prepare the data

-- insert 110K rows into a regular table

begin tran

    declare @i int = 0

    while (@i < 110000)

    begin

        insert into t_bulkload values (@i, 'description', 'dummy-accounttype', @i*2)

        set @i = @i + 1

    end

commit

bcp adventureworksDW2012..t_bulkload out c:\t_bulkoad.dat -c -T

--truncate the table

Truncate table t_bulkload

-- convert row clustered index into clustered columnstore index

CREATE CLUSTERED COLUMNSTORE index t_bulkload_cci on t_bulkload

세션 1

세션 2

-- now bulkload the data

begin tran

bulk insert t_bulkload

FROM 'c:\t_bulkoad.dat'

WITH

(

BATCHSIZE = 103000

)

select * from sys.column_store_row_groups where object_id = object_id('t_bulkload')

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

세션 3

세션 4

begin tran

    insert into t_bulkload values (-1, 'single row', 'single row', -1)

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

CREATE TABLE [dbo].[T_ACCOUNT](

[accountkey] [int] IDENTITY(1,1) NOT NULL,

[accountdescription] [nvarchar](50) NULL

) ON [PRIMARY]

-- create a CCI

CREATE CLUSTERED COLUMNSTORE INDEX ACCOUNT_CCI ON T_ACCOUNT

세션1

세션 2

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('row-1');

select

    request_session_id as spid,

    resource_type as rt,

    resource_database_id as rdb,

    (case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd,

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks

세션 2

세션 3

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('row-2');

select

    request_session_id as spid,

    resource_type as rt,

    resource_database_id as rdb,

    (case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd,

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks