Data Science Lab

If RCSI is enabled on the database containing one or more tables with CCI, all tables other than CCI can be accessed with non-blocking semantics under read committed isolation level but not for CCI

select

    is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state

from sys.databases where name='AdventureWorksDW2012'

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

세션3

use AdventureWorksDW2012

go

-- Do a DML transaction on CCI but don't commit

begin tran

    insert into T_ACCOUNT (accountdescription )

    values ('value-1');

set transaction isolation level read committed

go

select * from t_account

--You will see CCI query is blocked on session-1 as shown using the query below

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

Msg 35371, Level 16, State 1, Line 26

SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.

set transaction isolation level repeatable read

go

begin tran

select * from t_account

set transaction isolation level serializable

go

begin tran

    select * from t_account

go

CREATE TABLE dbo.t_colstore (

c1 int NOT NULL,

c2 INT NOT NULL,

c3 char(40) NOT NULL,

c4 char(1000) NOT NULL

)

go

set nocount on

go

-- load 2000000 rows

declare @outerloop int = 0

declare @i int = 0

while (@outerloop < 2000000)

begin

    Select @i = 0

    while (@i < 2000)

    begin

        insert t_colstore values (@i + @outerloop, @i + @outerloop, 'a',

        concat (CONVERT(varchar, @i + @outerloop), (replicate ('b', 950))))

        set @i += 1;

    end

set @outerloop = @outerloop + @i

set @i = 0

end

go

CREATE CLUSTERED COLUMNSTORE INDEX t_colstore_cci ON t_colstore with (maxdop = 1)

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

select cast(round((75-25)*rand()+25, 0) as integer) as RandBetweenValue

create function randbetween(@bottom int, @top int)

returns int

as

begin

    return (select cast(round((@top-bottom)*rand() + @bottom,0) as integer))

end

go

create view vRandomNumber

as

    select rand() as RandomNumber

go

create function randbetween(@bottom int, @top int)

returns int

as

begin

    return (select cast(round((@top-@bottom)* RandomNumber + @bottom,0) as integer) from vRandomNumber)

end

go

select dbo.randbetween(25, 75)

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

SET @xml =CAST((

SELECT TOP 5 --Change number accordingly

su.Session_ID AS 'td','',

ss.Login_Name AS 'td','',

rq.Command AS 'td','',

su.Task_Alloc AS 'td','',

su.Task_Dealloc AS 'td','',

--Find Offending Query Text:

(SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1,

(CASE WHEN statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max),text)) * 2

ELSE statement_end_offset

END - rq.statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS 'td'

FROM

(SELECT su.session_id, su.request_id,

SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc,

SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc

FROM sys.dm_db_task_space_usage AS su

GROUP BY session_id, request_id) AS su,

sys.dm_exec_sessions AS ss,

sys.dm_exec_requests AS rq

WHERE su.session_id = rq.session_id

AND(su.request_id = rq.request_id)

AND (ss.session_id = su.session_id)

AND su.session_id > 50 --sessions 50 and below are system sessions and should not be killed

AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results

ORDER BY su.task_alloc DESC --The largest "Task Allocation/Deallocation" is probably the query that is causing the db growth

FOR XML PATH ('tr'), ELEMENTS ) AS NVARCHAR(MAX))

--BODY OF EMAIL - Edit for your environment

SET @body ='<html><H1>Tempdb Large Query</H1>

<body bgcolor=white>The query below with the <u>highest task allocation

and high task deallocation</u> is most likely growing the tempdb. NOTE: Please <b>do not kill system tasks</b>

that may be showing up in the table below.

<U>Only kill the query that is being run by a user and has the highest task allocation/deallocation.</U><BR>

<BR>

To stop the query from running, do the following:<BR>

<BR>

1. Open <b>SQL Server Management Studio</b><BR>

2. <b>Connect to database engine using Windows Authentication</b><BR>

3. Click on <b>"New Query"</b><BR>

4. Type <b>KILL [type session_id number from table below];</b> - It should look something like this: KILL 537; <BR>

5. Hit the <b>F5</b> button to run the query<BR>

<BR>

This should kill the session/query that is growing the large query. It will also kick the individual out of the application.<BR>

You have just stopped the growth of the tempdb, without having to restart SQL Services, and have the large-running query available for your review.

<BR>

<BR>

<table border = 2><tr><th>Session_ID</th><th>Login_Name</th><th>Command</th><th>Task_Alloc</th><th>Task_Dealloc</th><th>Query_Text</th></tr>'

SET @body = @body + @xml +'</table></body></html>'

--Send email to recipients:

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'dba@domain.com', --Insert the TO: email Address here

@copy_recipients ='dba_Manager@domain.com', --Insert the CC: Address here; If multiple addresses, separate them by a comma (,)

@body = @body,@body_format ='HTML',

@importance ='High',

@subject ='THIS IS A TEST', --Provide a subject for the email

@profile_name = 'DatabaseMailProfile' --Database Mail profile here