Post connect - minidump apagando snapshot com DDL trigger e linked server

Murphy realmente é um canalha... Estou ministrando treinamentos de SQL Server para um cliente e ontem iríamos usar um database snapshot que se encaixa perfeitamente em um cenário. para validarmos nosso script, criamos um snapshot e depois fomos removê-lo antes do primeiro uso.

DROP DATABASE XYZSnap

...

Minidump e service down. Divertido não é?!
Nos minutos seguintes tudo em pé funcionando corretamente, encontramos registro no connect e hoje reproduzi com SQL Server 2012 e, com uma variação de comportamento, no 2014.

Abri um item no Connect (https://connect.microsoft.com/SQLServer/feedback/details/1658842), por favor votem se conseguir reproduzir (não em produção, ok?!).

Deixo o texto original que escrevi para referência...

==========================================================

I found this connect item that is exactly what I went thru yesterday in a customer: https://connect.microsoft.com/SQLServer/feedback/details/474486/sql-server-assertion-file-tmpilb-cpp-line-1306-failed-assertion-null-m-ptaskmain-sos-task-getcurrent-m-ptaskmain

When dropping a database snapshot, a DDL trigger uses a linked server to delete a record from a control table. Then a minidump is generated and the service crashes. The SQL Server version is 2008 R2 (SP2) - 10.50.4321.0 (X64).

Using my machine with some instances I could reproduce the problem configuring a linked server from SQL Server 2012 to 2014 and dropping a database snapshot. Here is the beginning of the SQLDumpXXXX.txt:

Computer type is Intel(R) Core(TM) i7-2670QM CPU @ 2.20GHz.                                                    
Bios Version is DELL   - 2                                                                                    
8 X64 level 8664, 2 Mhz processor (s).                                                                        
Windows NT 6.2 Build 9200 CSD .                                                                                
                                   
Memory                            
MemoryLoad = 32%                  
Total Physical = 16278 MB          
Available Physical = 11058 MB      
Total Page File = 18746 MB        
Available Page File = 12232 MB    
Total Virtual = 134217727 MB      
Available Virtual = 134200260 MB  
**Dump thread - spid = 0, EC = 0x000000019943AB90                                                              
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL11.INST2012\MSSQL\LOG\SQLDump0008.txt  
* *******************************************************************************                              
*                                                                                                              
* BEGIN STACK DUMP:                                                                                            
*   08/12/15 08:44:42 spid 48                                                                                  
*                                                                                                              
* Location: tmpilb.cpp:1301                                                                                  
* Expression: NULL == m_pTaskMain || SOS_Task::GetCurrent() == m_pTaskMain                                    
* SPID: 48                                                                                                  
* Process ID: 5644


From SQL Server 2014 with a linked server to SQL Server 2012 the behavior is different. The connection is lost, a minidump is generated, but SQL Server doesn't goes down. The drawback of this is that now I can see an exclusive lock on the database, owned by resquest_session_id -2 (DTC is envolved) and request_owner_type EXCLUSIVE_TRANSACTION_WORKSPACE.

Process ID 52 attempted to unlock a resource it does not own: DATABASE: 20 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Msg 0, Level 20, State 0, Line 15
A severe error occurred on the current command.  The results, if any, should be discarded.

***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDump0010.txt
* *******************************************************************************                              
*                                                                                                              
* BEGIN STACK DUMP:                                                                                            
*   08/12/15 09:37:56 spid 16                                                                                  
*                                                                                                              
* Location: tmpilb.cpp:1301                                                                                  
* Expression: NULL == m_pTaskMain || SOS_Task::GetCurrent() == m_pTaskMain                                    
* SPID: 16                                                                                                  
* Process ID: 7840  

In this machine I got the following SQL Server versions. Haven't tried with other builds.
Microsoft SQL Server 2014 - 12.0.2480.0 (X64)
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

The original connect item was closed as "as Not Reproducible", hopefully you will be able to reproduce this one.

/*
Control database on the server that the linked server points to.
*/
--DROP DATABASE ControlDB
CREATE DATABASE ControlDB

USE ControlDB
GO

-- DROP TABLE DatabaseList
CREATE TABLE DatabaseList
( ID INT IDENTITY NOT NULL
, Name VARCHAR(256) NOT NULL)
 GO

USE master
GO

/*
Server where the triggers and linked server are configured.
Authentication is made using a specific security context (SQL Server authentication)
*/

USE master
GO

CREATE TRIGGER [ddl_trig_database] 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
SET ANSI_PADDING ON
    PRINT 'Database Created.'
    DECLARE @name Varchar(255)
-- CommandText is wrong in the implementation
    SELECT @name = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
    
    SET @name = replace(@name, 'Create Database ', '')
    SET @name = RTRIM(ltrim(@name))
    print @name

-- Effectively we don't need to insert nothing
    insert into [LUTIXPS\INST2012].ControlDB.dbo.DatabaseList (Name) values (@name)
GO

-- DISABLE TRIGGER [ddl_trig_database] ON ALL SERVER
ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER
GO


CREATE TRIGGER [ddl_trig_database_drop] 
ON ALL SERVER 
FOR DROP_DATABASE 
AS 
    --PRINT 'Database Droped.'
    DECLARE @name Varchar(255)
    SELECT @name = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
    print @name + char(13)
    SET @name = replace(@name, 'Drop Database ', '')
    print @name

    Delete [LUTIXPS\INST2012].ControlDB.dbo.DatabaseList WHERE Name = @name
GO

-- DISABLE TRIGGER [ddl_trig_database_drop] ON ALL SERVER
ENABLE TRIGGER [ddl_trig_database_drop] ON ALL SERVER
GO


-- DROP DATABASE Test
CREATE DATABASE Test

-- SELECT * FROM sys.master_files

CREATE DATABASE TestSnap
ON PRIMARY (NAME = 'Test', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Test_SNAP.mdf')
AS SNAPSHOT OF Test

DROP DATABASE testSnap
GO

==========================================================

Abraços

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br

DBCC PAGE – bug com índice filtrado

Post rápido...

Gravando o treinamento on-demand de indexação eu me deparei com um possível bug do DBCC PAGE, onde ele formata incorretamente a saída de uma página do índice cluster, quando criamos um índice não-cluster filtrado na mesma tabela.

O Fabiano Amorim reproduziu e o Paul Randall também, então é um bug. O fato de não ser um comando documentado não sei se vai ganhar muita atenção no Connect, mas registrei uma entrada mesmo assim: https://connect.microsoft.com/SQLServer/feedback/details/776144/dbcc-page-incorrect-output-with-filtered-indexes.

Se quiser reproduzir o problema, o script está aqui:

USE tempdb

GO

SELECT @@version

IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL

      DROP TABLE dbo.MyTable

GO

CREATE TABLE dbo.MyTable (

      ID INT IDENTITY NOT NULL PRIMARY KEY

      , Name CHAR(100) NOT NULL DEFAULT ('DBCC PAGE')

      , SomeDate DATETIME2 NOT NULL DEFAULT(SYSDATETIME())

)

GO

INSERT INTO dbo.MyTable DEFAULT VALUES

GO 30000

INSERT INTO dbo.MyTable ( Name, SomeDate )

SELECT Name, DATEADD(YY, -1, SomeDate)

FROM dbo.MyTable

GO

SELECT

      OBJECT_NAME(object_id) AS ObjectName

      , AU.*

      , P.*

FROM SYS.system_internals_allocation_units AS AU

INNER JOIN SYS.Partitions AS P

ON AU.Container_id = P.Partition_id

WHERE Object_ID IN (object_id('MyTable'))

ORDER BY object_id, type

GO

DBCC TRACEON(3604)

DBCC PAGE(Tempdb, 1, 1383, 3)

GO

-- output ok

CREATE NONCLUSTERED INDEX idxNCL_MyTable_SomeDate

ON dbo.MyTable (SomeDate)

WHERE SomeDate > '2013-01-01'

go

-- Same root page for CL, as expected

SELECT

      OBJECT_NAME(object_id) AS ObjectName

      , AU.*

      , P.*

FROM SYS.system_internals_allocation_units AS AU

INNER JOIN SYS.Partitions AS P

ON AU.Container_id = P.Partition_id

WHERE Object_ID IN (object_id('MyTable'))

ORDER BY object_id, type

GO

DBCC PAGE(Tempdb, 1, 1383, 3)

GO

-- output not ok

DROP INDEX MyTable.idxNCL_MyTable_SomeDate

-- output ok again

DBCC PAGE(Tempdb, 1, 1383, 3)

GO

Abraços,

Luciano [Luti] Caixeta Moreira

luciano.moreira@srnimbus.com.br

http://luticm.blogspot.com/

http://www.twitter.com/luticm

sr. Nimbus Serviços em Tecnologia - www.srnimbus.com.br

Artigo: Como o Query Optimizer utiliza (ou não) constraints unique e filtered indexes

Bom dia pessoal.

Aproveitando o gancho do último post, escrevi hoje um artigo com duas intenções:

1. Mostrar como o query optimizer do SQL Server se beneficia de constraints unique
2. Mostrar uma característica do optimizer com filtered indexes que me parece um bug

Como o artigo ficou grande e acho que a leitura será mais agradável com a formatação do PDF, não vou colocar ele inteiro como um post, deixo somente o link para download do PDF.

Como o Query Optimizer utiliza (ou não) constraints unique e filtered indexes




Espero que vocês gostem e deixem aqui seus comentários...

[]s
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm