Bloqueos Sch-S y Sch-M con tablas particionadas

Bloqueos Sch-S y Sch-M con tablas particionadas

En este post analizamos el impacto que lleva implícito el bloqueo Sch-S que se establece cuando lanzamos una select y a continuación, y de forma paralela, tratamos de lanzar alguna operación que lleve asociado un bloqueo de tipo Sch-M como puede ser un truncado a una partición de la tabla que se consulta.

Supongamos el escenario en el que tenemos una tabla (CounterDataPart) con tres particiones sobre la que queremos consultar datos ubicados en las dos primeras particiones y, a continuación y de forma paralela, necesitamos hacer un truncado de los datos de la partición 3, que no estamos consultando. Dicha tabla tiene 3 millones de filas repartidas en las tres particiones en base a su campo GUID.

Función de partición

CREATE PARTITION FUNCTION pruebaPart (int)
AS RANGE RIGHT FOR VALUES (1000000, 2000000)

Definición de esquema de partición

CREATE PARTITION SCHEME SchPartDemo
AS PARTITION pruebaPart
TO (FG_1, FG_2, FG_3);

Definición de la tabla

CREATE TABLE CounterDataPart (

[GUID] [int] IDENTITY(1,1) NOT NULL,
[CounterID] [int] NOT NULL,
[RecordIndex] [int] NOT NULL,
[CounterDateTime] [datetime] NOT NULL,
[CounterValue] [float] NOT NULL,
[FirstValueA] [int] NULL,
[FirstValueB] [int] NULL,
[SecondValueA] [int] NULL,
[SecondValueB] [int] NULL,
[MultiCount] [int] NULL,
[Date] [datetime] NOT NULL,
[TimeKey] [int] NOT NULL

PRIMARY KEY CLUSTERED
(

[GUID] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON SchPartDemo (GUID)
) ON SchPartDemo (GUID)

Cargamos la tabla con 3MM de registros

DECLARE @contador int
set @contador=1

WHILE @contador <= 3000000
BEGIN

INSERT INTO CounterDataPart values(@contador, @contador+1, @contador+2, @contador+3, @contador+4, @contador+5, @contador+6);
SET @contador = @contador+1;

END

Comprobamos la carga por particiones

DECLARE @TableName NVARCHAR(200) = N’CounterDataPart’

SELECT

SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(i.object_id) AS [object]
, p.partition_number AS [p#]
, fg.name AS [filegroup]
, p.rows
, au.total_pages AS pages
, CASE boundary_value_on_right
WHEN 1 THEN ‘less than’
ELSE ‘less than or equal to’ END as comparison
, rv.value
, CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
SUBSTRING (au.first_page, 5, 1))) + ‘:’ + CONVERT (VARCHAR(20),
CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
SUBSTRING (au.first_page, 1, 1))) AS first_page

FROM sys.partitions p

INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id

WHERE o.object_id = OBJECT_ID(@TableName);

• Partición 1, 999999 registros con valores de GUID => GUID < 1000000
• Partición 2, 1000000 registros con valores de GUID => 2000000 > GUID >= 1000000
• Partición 3, 1000001 registros con valores de GUID => GUID >= 2000000

Lanzamos, en primer lugar, la siguiente consulta: select * from CounterDataPart s1 with (nolock), CounterDataPart s2 with (nolock) where s1.GUID < 1500000

Y seguidamente, de forma paralela, el truncado de la partición 3 de la tabla a la que se accede en la consulta anterior: TRUNCATE table [test].[dbo].[CounterDataPart] WITH (PARTITIONS (3))

Puede observarse que en la consulta se acceden a datos que no se encuentran en la partición 3 que es la que pretendemos truncar. Podríamos pensar que si vamos a borrar datos a los que no estoy accediendo en la primera consulta, podríamos ejecutar este proceso sin problemas. Veamos qué ocurre.

Al lanzar la primera consulta se observa con sp_whoisactive, que efectivamente en la tabla que consultamos se establece un bloqueo con Sch-S para evitar cambios de estructura de esquema durante la ejecución.

exec sp_whoisactive @get_locks=1

<Database name=”Test”>
<Locks>
<Lock request_mode=”S” request_status=”GRANT” request_count=”1″ />
</Locks>
<Objects>
<Object name=”CounterDataPart” schema_name=”dbo”>
<Locks>
<Lock resource_type=”HOBT” index_name=”PK__CounterD__15B69B8EA8DE22E9″ request_mode=”Sch-S” request_status=”GRANT” request_count=”1″ />
<Lock resource_type=”OBJECT” request_mode=”Sch-S” request_status=”GRANT” request_count=”1″ />
</Locks>
</Object>
</Objects>
</Database>

¿Qué ocurre cuando lanzamos truncate de la partición 3? Mantenemos lanzada la primera consulta y lanzamos el truncado de la partición 3: TRUNCATE table [test].[dbo].[CounterDataPart] WITH (PARTITIONS (3))

Al lanzar el truncado se observa en la jerarquía de bloqueos que éste se encuentra bloqueado por la ejecución de la consulta:

Con sp_whoisactive vemos los bloqueos de cada caso:

exec sp_whoisactive @get_locks=1

<Database name=”Test”>
<Locks>
<Lock request_mode=”S” request_status=”GRANT” request_count=”1″ />
</Locks>
<Objects>
<Object name=”CounterDataPart” schema_name=”dbo”>
<Locks>
<Lock resource_type=”HOBT” index_name=”PK__CounterD__15B69B8EA8DE22E9″ request_mode=”Sch-S” request_status=”GRANT” request_count=”1″ />
<Lock resource_type=”OBJECT” request_mode=”Sch-S” request_status=”GRANT” request_count=”1″ />
</Locks>
</Object>
</Objects>
</Database>

<Database name=”Test”>
<Locks>
<Lock request_mode=”S” request_status=”GRANT” request_count=”1″ />
</Locks>
<Objects>
<Object name=”CounterDataPart” schema_name=”dbo”>
<Locks>
<Lock resource_type=”OBJECT” request_mode=”Sch-M” request_status=”WAIT” request_count=”1″ />
</Locks>
</Object>
</Objects>
</Database>

Se observa que el truncado intenta establecer un bloqueo SCH-M que es impedido porque previamente se ha establecido un bloqueo SCH-S por la ejecución de la consulta. Por diseño, TRUNCATE TABLE incluye un bloqueo de esquema (SCH-M) que, en nuestro caso, está siendo impedido por el bloqueo SCH-S provocado por la select que ha sido lanzada previamente. Esto provoca que sea necesario que la primera consulta finalice antes que pueda entrar la ejecución de dicho trucado.

Sch-S (Schema Stability) es un tipo de bloqueo que se establece cuando un proceso no quiere que ese objeto cambie su definición. En el caso de la select tiene sentido que si estoy leyendo un conjunto de filas a través de una tabla, quiera impedir que, por ejemplo, una columna cambie su definición o desaparezca en mitad de la ejecución. Este tipo de bloqueo no es excluyente, es decir, SQL ServerSQL Server permite más de un bloqueo Sch-S en un objeto para que pueda ser consultada de forma concurrente.

El problema viene cuando, al mismo tiempo que estamos consultando un objeto (una tabla, por ejemplo) y establezco el bloqueo Sch-S, trato de establecer un bloqueo Sch-M (Schema Modification), que es el que precisa Truncate Table para su ejecución. Sch-M hace referencia a un tipo de bloqueo que se establece en un objeto para su modificación (se establece el bloqueo, se completa la modificación y, a continuación, se libera dicho bloqueo). En este caso y al contrario de lo que ocurría con Sch-S, SQL Server solo permite un bloqueo Sch-M y además no puede haberse establecido sobre él otro tipo de bloqueo, que es justo lo que sucede en nuestro caso, al intentar establecer un bloqueo Sch-M en un objeto al que previamente se ha establecido un bloqueo Sch-S. Este escenario provoca que la petición de bloqueo Sch-M (en nuestro caso, truncate table) tenga que esperar a que finalice el bloqueo Sch-S (select iniciada previa al truncate) para poder establecerse.

Además el truncado de la tabla bloqueará el resto de consultas que traten de hacer cualquier tipo de bloqueo de esquema ya sea SCH-S o cualquier otro hasta que este no finalice o sea interrumpido como vemos en el siguiente ejemplo:

Lanzamos una select, a continuación y de forma paralela, truncamos y seguidamente tratamos de lanzar otra select sobre los mismos objetos:

Select 1: select * from CounterDataPart s1 with (nolock), CounterDataPart s2 with (nolock) where s1.GUID < 1500000
Truncado: TRUNCATE table [test].[dbo].[CounterDataPart] WITH (PARTITIONS (3))
Select 2: select * from CounterDataPart s1 with (nolock) where s1.GUID < 1500


En base a la jerarquía de bloqueos anterior, la consulta del proceso 56 podría ejecutarse si finalizara el proceso de truncado (proceso 55) independiente de si la consulta inicial (proceso 51) haya terminado o no ya que, como se ha indicado previamente, se pueden establecer varios bloqueos Sch-S al mismo tiempo sobre un mismo objeto.

¿Qué podemos hacer en estos casos?

Tenemos pocas opciones:

• Optimizar al máximo el tiempo de ejecución de nuestras consultas para que sean ejecutadas en el menor tiempo posible y minimizar el tiempo de bloqueo Sch-S que genera.
• En caso que no tengamos opción de más optimización y reducción de tiempo de ejecución de la consulta, podemos utilizar como workaround el uso de SET LOCK_TIMEOUT para limitar el tiempo de bloqueo y reintentarlo en caso que se sobrepase el umbral que se haya definido.

— Definimos un máximo de reintentos de 5
DECLARE @num_reintentos int
set @num_reintentos=1

WHILE @num_reintentos <= 5
BEGIN
— Definimos un timeout de 2 segundos

BEGIN TRY

SET LOCK_TIMEOUT 2000
TRUNCATE table [test].[dbo].[CounterDataPart] WITH (PARTITIONS (3))

END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

SET @num_reintentos = @num_reintentos+1;

END
— Al final de la operación restablecemos el tiempo de espera de bloqueo para que espere indefinidamente y no expire nunca
— que es el comportamiento predeterminado que ya está establecido al principio de cada conexión.
SET LOCK_TIMEOUT -1
IF @num_reintentos > 5 THROW 51000, ‘Sobrepasado número de reintentos TRUNCATE’, 1;

En el caso que se sobrepase el número de reintentos, el truncado devolverá un error permitiendo la ejecución del resto de consultas que permanecía bloqueadas hasta la finalización de dicho truncado.

Not all virtual machines are the same

Not all virtual machines are the same

It is not uncommon to find a wide range of situations among our customers in terms of virtual machine performance with SQL Server. In many cases, we find situations where performance levels are far from ideal but, in general terms, virtual machines themselves are not to blame. What usually happens is that when we move SQL Server to a virtual machine, we become constrained by a maximum or limited amount of resources (CPU/ memory/ IO) that is significantly different to that of the physical machine. (more…)

Azure Files Premium + SQL Server Failover Cluster instance = simplified OnPremise to Cloud

Azure Files Premium + SQL Server Failover Cluster instance = simplified OnPremise to Cloud

One of the issues that many of our customers face when attempting to migrate OnPremise instances to the Cloud is the lack of a simple “shared storage”. Although there are some alternatives supported by third-party software or SDS solutions that allow us to configure a Failover Cluster instance in Azure, these are highly complex, therefore adding significant further costs to the solution’s TCO.
(more…)

Azure Database integrated authentication with SSIS

Azure Database integrated authentication with SSIS

In many scenarios, we face the need to use integrated authentication in order to gain access to the required data sources to feed our analytical system. In view of Azure’s increasingly widespread use, as is the case with at least part of our infrastructure, some of these sources are hosted in Azure databases. In this case, we will discuss an actual error that we have come across when configuring and using integrated authentication in Azure databases with SSIS.
(more…)

Converting Excel files into csv and uploading files using SSIS

Converting Excel files into csv and uploading files using SSIS

Although SQL Server Integration Services, hereinafter SSIS, is capable of uploading Excel files, in most cases it can be time consuming because any small modifications to the Excel files can make the SSIS crash. For that reason, the best option is usually to transform those Excel files into .csv format, since uploading text files will cause you significantly less issues than the Excel files.

You can quickly save any Excel file as csv manually by saving as .csv from within Excel. However, it becomes an issue when you have to do the same for a lot of Excel files, or in cases were you need the change to be done automatically.

In this post, we will explain how to do this format change automatically using PowerShell and how to loop through files in the same directory in order to upload several Excel files together using SSIS as the main tool for the whole process.

(more…)

Creating PowerPoint presentations from R

Creating PowerPoint presentations from R

Regardless of the tools used for data analysis, normally the way to display the results is a Word document or a PowerPoint presentation.

In this post, we will create a PowerPoint presentation and insert a series of graphics and text programmatically, using the OfficeR and rvg packages together. We will also take advantage of the occasion to present (for those who do not know) the ‘Pipe’ operator, very useful when nesting functions.

(more…)

Azure ASR and SQL Server

Azure ASR and SQL Server

In an on-premises environment when we propose solutions to geographical disasters, the most common option is log shipping. The use of asynchronous database mirroring or availability groups with asynchronous replicas is also common but includes an additional risk that is not usually contemplated. We refer to the “speed” with which the changes are transferred, as quickly as the network and the target system allow us. This means that when the disaster has a human origin, an important error when we become aware of it, we will have this error replicated and applied. Obviously, a better solution would be to combine both options, which are not exclusive, with which we would cover more disaster scenarios increasing the cost of the solution. (more…)