Blocking

Nov 19, 2013 at 4:24 PM
Love this tool!

I'm currently running into issues with blocking however. As expected the command 'IndexStaging' can take some time to run, and while it is running any other command 'IndexStaging' against other tables becomes blocked by the first 'IndexStaging' command.

My database uses the default read committed isolation level and I'm hesitant to change this, could you an option be added to use read uncommitted for the connection string used in the solution? Any other ideas?
Coordinator
Nov 19, 2013 at 4:55 PM

Hi Anthony – glad you like the tool. The index creation operation should not be blocking a different index creation on another table. Let’s see what resource is really causing the block. Could you take a snapshot of sys.dm_exec_requests during one of these blocking episodes and share it with me?

Thanks – Stuart

Nov 19, 2013 at 7:05 PM

Blocking details below. Also, looks like another user described similar behavior (without your tool) here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3a0db7df-4e00-46c9-83ad-c31868c8081b/lckms-blocking?forum=sqldatabaseengine. There are no foreign keys on either table, both tables share the same partition function and partition scheme.

Blocking session_id SQL Statement

CREATE CLUSTERED INDEX [myIndexHere] ON [dbo].[myTable]

(

[myIndexColumn] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, DATA_COMPRESSION = PAGE) ON [July_2013]

Blocked session_id SQL Statement (wait_type = LCK_M_S)

(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000))SELECT

tbl.name AS [Name],

tbl.object_id AS [ID],

tbl.create_date AS [CreateDate],

tbl.modify_date AS [DateLastModified],

ISNULL(stbl.name, N'') AS [Owner],

CAST(case when tbl.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],

SCHEMA_NAME(tbl.schema_id) AS [Schema],

CAST(

case

when tbl.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = tbl.object_id and

minor_id = 0 and

class = 1 and

name = N'microsoft_database_tools_support')

is not null then 1

else 0

end

AS bit) AS [IsSystemObject],

CAST(OBJECTPROPERTY(tbl.object_id, N'HasAfterTrigger') AS bit) AS [HasAfterTrigger],

CAST(OBJECTPROPERTY(tbl.object_id, N'HasInsertTrigger') AS bit) AS [HasInsertTrigger],

CAST(OBJECTPROPERTY(tbl.object_id, N'HasDeleteTrigger') AS bit) AS [HasDeleteTrigger],

CAST(OBJECTPROPERTY(tbl.object_id, N'HasInsteadOfTrigger') AS bit) AS [HasInsteadOfTrigger],

CAST(OBJECTPROPERTY(tbl.object_id, N'HasUpdateTrigger') AS bit) AS [HasUpdateTrigger],

CAST(OBJECTPROPERTY(tbl.object_id, N'IsIndexed') AS bit) AS [HasIndex],

CAST(OBJECTPROPERTY(tbl.object_id, N'IsIndexable') AS bit) AS [IsIndexable],

CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],

tbl.uses_ansi_nulls AS [AnsiNullsStatus],

CAST(OBJECTPROPERTY(tbl.object_id,N'IsQuotedIdentOn') AS bit) AS [QuotedIdentifierStatus],

CAST(0 AS bit) AS [FakeSystemTable],

ISNULL(dstext.name,N'') AS [TextFileGroup],

ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < @_msparam_0), 0) AS [RowCount],

tbl.is_replicated AS [Replicated],

tbl.lock_escalation AS [LockEscalation],

CAST(case when ctt.object_id is null then 0 else 1 end AS bit) AS [ChangeTrackingEnabled],

CAST(ISNULL(ctt.is_track_columns_updated_on,0) AS bit) AS [TrackColumnsUpdatedEnabled],

tbl.is_filetable AS [IsFileTable],

ISNULL(ft.directory_name,N'') AS [FileTableDirectoryName],

ISNULL(ft.filename_collation_name,N'') AS [FileTableNameColumnCollation],

CAST(ISNULL(ft.is_enabled,0) AS bit) AS [FileTableNamespaceEnabled],

CASE WHEN 'FG'=dsidx.type THEN dsidx.name ELSE N'' END AS [FileGroup],

CASE WHEN 'PS'=dsidx.type THEN dsidx.name ELSE N'' END AS [PartitionScheme],

CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],

CASE WHEN 'FD'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamFileGroup],

CASE WHEN 'PS'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamPartitionScheme]

FROM

sys.tables AS tbl

LEFT OUTER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))

INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < @_msparam_1

LEFT OUTER JOIN sys.data_spaces AS dstext ON tbl.lob_data_space_id = dstext.data_space_id

LEFT OUTER JOIN sys.change_tracking_tables AS ctt ON ctt.object_id = tbl.object_id

LEFT OUTER JOIN sys.filetables AS ft ON ft.object_id = tbl.object_id

LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id

LEFT OUTER JOIN sys.tables AS t ON t.object_id = idx.object_id

LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and idx.index_id < 2

WHERE

(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)

Coordinator
Nov 19, 2013 at 7:34 PM

Ah – ok. The blocking is happening to a SELECT query against sys.indexes generated by SMO behind the scenes (in the same way as browsing Object Explorer in SSMS is often blocked during an Index Build). Simply issuing a CREATE INDEX TSQL statement would NOT be blocked, however this partition tool is built around SMO currently.

I will investigate whether there are any tricks to get by this. Thanks for pointing it out…

- Stuart

Nov 19, 2013 at 7:38 PM

Thanks!

Coordinator
Nov 19, 2013 at 7:54 PM

Anthony -- Which version of SQL Server are you using?

From: Stuart Ozer
Sent: Tuesday, November 19, 2013 11:34 AM
To: '[email removed]'
Subject: RE: Blocking [SQLPartitionMgmt:470467]

Ah – ok. The blocking is happening to a SELECT query against sys.indexes generated by SMO behind the scenes (in the same way as browsing Object Explorer in SSMS is often blocked during an Index Build). Simply issuing a CREATE INDEX TSQL statement would NOT be blocked, however this partition tool is built around SMO currently.

I will investigate whether there are any tricks to get by this. Thanks for pointing it out…

- Stuart

Nov 19, 2013 at 7:59 PM

2012 Enterprise SP1 CU6

Dec 4, 2013 at 8:00 PM

Hi Stuart, have you come across any tricks that I may be able to use to work around this problem?

Coordinator
Dec 4, 2013 at 9:11 PM
I suggest trying this: Instead of running the Partition Management tool in usual mode, execute it in Script Only mode -- e.g.

/ScriptOption:O /ScriptFile:<unique_file_name>

This will generate TSQL scripts containing the DDL commands for each requested PartitionManagement operation, and you can then execute that script and it should not block other such scripts that may be running in parallel. You can invoke osql to run the generated scripts in the same calling batch file as the one invoking ManagePartition.
Dec 4, 2013 at 9:41 PM

Thanks, I’ll give it a shot.

Dec 23, 2013 at 8:49 PM

I’ve was living with the blocking, but now I have some time to implement your suggestion.

However, I don’t see understand how this option will get around the blocking issues. For instance, a clustered index was building on a staging table that is partitioned and I executed the tool using /ScriptOption and /ScriptFile and the resulting SQL was still blocked from running until the index build completes.

I’m I not interpreting your suggestion correctly?

Coordinator
Dec 23, 2013 at 9:23 PM

The Clustered Index build should only block operations against the same staging table. I had interpreted your problem as blocking operations against different staging tables occurring concurrently.

Can you share more specifics about commands / scripts that appear to be blocking eachother?

n Stuart

Dec 24, 2013 at 1:49 PM

You did interpret the problem correctly. Clustered index builds block select operations against sys.indexes. Other tables that use the utility need to query sys.indexes, particularly in step 3 outlined below in an SSIS package(s):

1. Get Partition number for date being loaded (Execute SQL task)

2. Drop Table_Staging staging table if exists (Execute SQL task)

3. Create Table_Staging (Execute Process task)

a. Utility- /C:CreateStagingNoIndex

b. Blocked if other tables are creating CIX’s

c. Needs to query sys.indexes to get the filegroup to build table on to match target table

d. Could this command be altered or other switch added to not query sys.indexes and use a default filegroup since /C:IndexStaging command will put table on the correct filegroup at that point?

4. Load data to Table_Staging (Data flow task)

5. Index Table_Staging

a. Utility- /C:IndexStaging

b. Can take between 5-25 minutes due to large data volume

6. Partition switch Table_Staging to Table (Execute SQL task)

Note: This process runs for many tables concurrently

Coordinator
Dec 24, 2013 at 5:34 PM
Yes, but it needs to read sys.indexes during script generation and should not be blocked during script execution, if all scripts have been generated in advance.

Is there a way for you to first generate all scripts, and only then begin to execute?

Dec 30, 2013 at 5:18 PM

Ahh, I see now. Will take some re-work, but I’ll give it a shot. Seems like it should work.

Jan 6, 2014 at 4:50 PM

Hi Stuart, after some rework this is working great, no more blocking issues! Thanks.

Coordinator
Jan 6, 2014 at 5:08 PM

Thanks for the note, Anthony. Could you share with me what you did to clear up the blocking?

--Stuart

Jan 6, 2014 at 5:35 PM