Italian Premier Center for SQL Server

Importante modifica di sicurezza a FileTable introdotta dal Service Pack 1 di SQL Server 2012

Italian Premier Center for SQL Server — Mon, 04 Mar 2013 16:46:00 GMT

Un veloce post per sottolineare una funzionalità a mio avviso poco pubblicizzata introdotta dal Service Pack 1 di SQL Server 2012. Con FileTable potete esporre il contenuto di una tabella a schema fisso all’accesso non transazionale tipico dei file systems. Non entro nei dettagli, per un approfondimento vi rimando qui: http://msdn.microsoft.com/en-us/library/ff929144.aspx. Voglio solo segnalarvi che, dal SP1 in poi, è possibile specificare una Access Control List più completa ai files. Nello specifico ora possiamo specificare:

Accesso in sola lettura.
Accesso in lettura/scrittura.
Accesso negato.

La matrice dei privilegi diventa quindi la seguente:

Privilegio logico	GRANT T-SQL
Privilegio di browse delle cartelle	SELECT
Privilegio di lettura	SELECT
Privilegio di scrittura di un file	UPDATE
Privilegio di creazione di un file	UPDATE e INSERT
Privilegio di eliminazione	DELETE

Pre service pack era possibile dare solo l'accesso in lettura/scrittura (anche a fronte di un semplice GRANT SELECT).

Da notare un paio di cose interessanti:

Dato che FileTable utilizza dietro le quinte FileStream per creare un file (anche vuoto) non basta avere il privilegio di INSERT. Bisogna avere anche il privilegio di UPDATE.
È possibile avere un principal che non può modificare i files ma li può eliminare. Ciò può sembrare bizzarro ma è perfettamente coerente con le logiche dei securable di SQL Server.

Happy coding,

Francesco Cogno

OPTIMIZE FOR e calcolo delle statistiche

Italian Premier Center for SQL Server — Tue, 12 Feb 2013 15:01:00 GMT

In questo articolo, esploreremo l’utilizzo dell’hint OPTIMIZE FOR e analizzeremo come questo impatta sulla lettura delle statistiche da parte dell’optimizer.

Quando una stored procedure viene eseguita la prima volta, i parametri con cui viene richiamata vengono salvati nella cache insieme al piano di esecuzione e utilizzati per il calcolo della cardinalità.

Questo comportamento è denominato “parameter sniffing”.

Nel caso in cui la distribuzione dei valori all’interno di una statistica sia poco uniforme, una stima errata della cardinalità, dovuta al parameter sniffing, può impattare negativamente sulle prestazioni in fase di esecuzione.

Facciamo un esempio:

USE tempdb
GO

SET NOCOUNT ON

CREATE TABLE t1 (IdRow int identity(1,1) PRIMARY KEY, Valore char(3))

DECLARE @i int

-- Inserisco 1000 righe con il valore AAA
SET @i = 0
WHILE @i < 1000
BEGIN
INSERT INTO t1 (Valore) VALUES ('AAA')
SET @i = @i + 1
END

-- Inserisco 500 righe con il valore BBB
SET @i = 0
WHILE @i < 500
BEGIN
INSERT INTO t1 (Valore) VALUES ('BBB')
SET @i = @i + 1
END

-- Inserisco 1 riga con il valore CCC
INSERT INTO t1 (Valore) VALUES ('CCC')

CREATE INDEX IX1 ON t1 (Valore)
GO

-- Analizziamo le statistiche
DBCC SHOW_STATISTICS (t1, IX1)
GO

Analizzando i piani di esecuzione possiamo constatare come le righe stimate vengano calcolate sulla base della cardinalità del parametro lanciato per primo dopo la compilazione (o ricompilazione) della stored procedure.

La figura evidenzia, ad esempio, la discrepanza tra righe stimate e quelle reali nel caso in cui la stored procedure venga richiamata con il parametro “AAA” essendo stata richiamata la prima volta con il parametro “CCC”.

Se guardiamo il piano di esecuzione in formato XML troveremo il dettaglio del parametro salvato:

</ParameterList>

In casi come quello appena descritto, possiamo utilizzare l’hint “OPTIMIZE FOR” per controllare la modalità con cui i parametri vengono salvati nel piano di esecuzione, in modo da migliorare la stima della cardinalità.

Ci sono due modi per utilizzare “OPTIMIZE FOR”:

OPTIMIZE FOR Parametro: a prescindere dal parametro con cui la stored procedure viene chiamata per prima, il piano di esecuzione verrà ottimizzato e salvato nella cache con il valore specificato e, di conseguenza, le statistiche verranno sempre calcolate in base alla cardinalità di quel parametro
OPTIMIZE FOR UNKNOWN: non verrà salvato alcun parametro nel piano di esecuzione in cache e le statistiche verranno calcolate in base alla densità

Esempio caso 1:

ALTER PROCEDURE p1
@Valore char(3)
AS

SELECT IdRow, Valore
FROM t1
WHERE Valore = @Valore
OPTION(OPTIMIZE FOR (@Valore = 'AAA'))

-- Eseguiamola passandogli prima 'CCC' e poi 'BBB'
EXEC p1 'CCC' --> 1 riga, stimate 1000
EXEC p1 'BBB' --> 500 righe, stimate 1000

Come si vede, in entrambi i casi l’optimizer stima 1000 righe che è appunto la cardinalità del valore “AAA”

Esempio caso 2:

ALTER PROCEDURE p1
@Valore char(3)
AS

SELECT IdRow, Valore
FROM t1
WHERE Valore = @Valore
OPTION(OPTIMIZE FOR UNKNOWN)

-- Eseguiamola passandogli i vari valori
EXEC p1 'CCC' --> 1 riga, stimate 500,333
EXEC p1 'BBB' --> 500 righe, stimate 500,333
EXEC p1 'AAA' --> 1000 righe, stimate 500,333

Come si vede, l’optimizer stima le righe in base alla densità, ovvero:

righe stimate = densità x numero righe totali = 0,3333333 x 1501 = 500,333

Ora vi faccio notare qualcosa di interessante :-)

Proviamo ad aggiungere un po’ di valori NULL per il campo “Valore”:

DECLARE @i int
SET @i = 0
WHILE @i < 2000
BEGIN
INSERT INTO t1 (Valore) VALUES (NULL)
SET @i = @i + 1
END

-- Analizziamo le statistiche
UPDATE STATISTICS t1 WITH FULLSCAN;
DBCC SHOW_STATISTICS (t1, IX1)
GO

Dalle statistiche possiamo notare la presenza di 2000 valori NULL per il campo “Valore”. La densità è leggermente diminuita, passando da 0,333 a 0,25.

Ricompiliamo la stored e lanciamola con il parametro CCC:

EXEC sp_recompile p1

EXEC p1 'CCC'

Secondo i calcoli fatti in precedenza ci aspetteremmo la seguente stima:

densità x numero righe totali = 0,25 x 3501 = 875,25

Se guardiamo però il piano di esecuzione notiamo che la stima non è cambiata:

Come mai ha ignorato i valori NULL?

La risposta è insita nel predicato della query:

WHERE Valore = @Valore

Nel caso in cui @Valore sia NULL, la query non restituirà alcuna riga nonostante vi siano 2000 righe a NULL, perché il risultato del confronto "Valore = @Valore" è valutato come UNKNOWN.

È interessante il fatto che l’optimizer, conscio di questo fatto, non consideri la cardinalità dei valori NULL, migliorando la stima.

Il calcolo effettuato è

[densità dei valori NOT NULL] x [numero righe totali NOT NULL]

dove densità dei valori NOT NULL si ottiene facendo l’inverso della somma di tutti i valori della colonna DISTINCT_RANGE_ROWS con l’aggiunta del numero di step – 1 (escludo la riga dei NULL).

Nel nostro caso il calcolo è [1/(0+0+0+0+4-1)] x 1501

Alla prossima!

Giuseppe Zagarrio

Selective XML Indexes

Italian Premier Center for SQL Server — Tue, 18 Dec 2012 10:20:00 GMT

Con il Service Pack 1 di SQL Server 2012 è stata aggiunta una funzionalità molto utile a chi utilizza il tipo nativo di SQL Server dedicato ai documenti XML: gli indici XML selettivi. Questi indici selettivi permettono di specificare quali elementi (o, meglio, percorsi) da indicizzare in contrasto col l’indice primario XML che indicizza tutto il documento. Come potete immaginare, è evidente che – a fronte di pattern di ricerca prevedibili – sia più efficiente indicizzare solo quei percorsi specifici.

Per maggiori dettagli vi rimando all’articolo sull’MSDN: http://msdn.microsoft.com/en-us/library/jj670108.aspx.

Vediamo insieme un esempio pratico di questo nuovo indice. Per farlo creiamo un database, una tabella molto semplice e popoliamo con XML di esempio. Sottolineo che fra i prerequisiti ci sia la presenza dell’indice clustered (http://msdn.microsoft.com/en-us/library/jj670108.aspx#prereq). Quindi niente indici XML selettivi su tabelle heap (un motivo in più… :)).

CREATE DATABASE TestSXI;
GO
USE TestSXI;
GO

CREATE TABLE testTbl(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    XmlDoc XML);
GO

INSERT INTO testTbl(XmlDoc) VALUES(
'<Meme xmlns="http://schemas.test.com/xyz">
<WorkHours>500</WorkHours>
<TaskName>Mission Xyz III</TaskName>
</Meme>');
GO 500
INSERT INTO testTbl(XmlDoc) VALUES(
'<Meme xmlns="http://schemas.test.com/xyz">
<WorkHours>1</WorkHours>
<TaskName>Mission Xyz I</TaskName>
</Meme>');
GO 
INSERT INTO testTbl(XmlDoc) VALUES(
'<Meme xmlns="http://schemas.test.com/xyz">
<WorkHours>222</WorkHours>
<TaskName>Mission Xyz II</TaskName> 
<TaskName>Mind your head!</TaskName>
<TaskName>No task name</TaskName>
</Meme>');
GO 222

INSERT INTO testTbl(XmlDoc) SELECT XmlDoc FROM testTbl;

GO 7

Come potete notare inseriamo dei documenti che potrebbero avere uno schema simile al seguente:

<?xmlversion="1.0"encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://schemas.test.com/xyz" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Meme">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="1" maxOccurs="1" nillable="false" name="WorkHours" type="xs:integer" />
        <xs:element minOccurs="0" maxOccurs="unbounded" nillable="false" name="TaskName" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Fino al Service Pack 1 di SQL Server potevamo solo indicizzare tutto il documento con un indice XML. Ad esempio con:

CREATE PRIMARY XML INDEX [PXML_XmlDoc] ON [dbo].[testTbl](XmlDoc);

GO

Creaimo un indice XML completo. Supponiamo che la nostra query ricorrente sia di questo tipo:

WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
SELECT COUNT(*)
FROM testTbl
WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1

Ovviamente sarebbe molto più efficiente indicizzare il solo percorso che identifica WorkHours dentro il tipo Meme. Per far questo cerchiamo di creare il nostro indice XML selettivo:

CREATE SELECTIVE XML INDEX sxi_Test ON [dbo].[testTbl](XmlDoc)
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
FOR
(    
    pathWH2 = '/yming:Meme/yming:WorkHours' AS XQUERY 'xs:integer' SINGLETON
);
GO

SQLMS vi dirà che non può farlo a meno che voi abilitiate la funzionalità:

Msg 9539, Level 16, State 1, Line 1

Selective XML Index feature is not supported for the current database version

Per farlo basta eseguire questo comando:

EXEC sp_db_selective_xml_index NULL, 'TRUE';
GO

Perché è necessario abilitare espressamente la funzionalità? Questo perché abilitando il supporto alla funzionalità di indici XML selettivi si aumenta la versione del database. In questo modo SQL garantisce che un database con questa funzionalità (che evidentemente è stata abilitata da un SQL 2012 almeno SP1) non venga restorato su un SQL che non la supporta (ad esempio, ma non solo, SQL 2012 RTM).

Per vederlo utilizziamo il comando non documentato né supportato DBCC DBINFO (quindi non usatelo se non per scopi didattici!):

EXEC sp_db_selective_xml_index NULL, 'TRUE';
GO
DBCC DBINFO('TestSXI') WITH TABLERESULTS;
EXEC sp_db_selective_xml_index NULL, 'FALSE';
GO
DBCC DBINFO('TestSXI') WITH TABLERESULTS;

Per maggiori informazioni vi rimando a questo post di Igor: http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/12/14/il-falso-mito-della-migrazione-da-una-versione-di-sql-all-altra.aspx.

Abilitato il supporto alla nuova funzionalità riproviamo a creare l’indice XML selettivo. Notiamo come venga specificato l’attributo SINGLETON per migliorare le performance di SQL. Questo attributo equivale a maxOccurs=”1” dell’XSD.

SQL anche in questo caso non ci permette di creare l’indice. Questa volta è perché abbiamo scelto un tipo non supportato per gli indici XML selettivi su XML untyped. Qui è chiaramente dettagliato l’elenco supportato: http://msdn.microsoft.com/en-us/library/jj670107.aspx#untyped . Essi sono:

xs:boolean
xs:double
xs:string
xs:date
xs:time
xs:dateTime

Per i nostri scopi scegliamo xs:string e riproviamo:

CREATE SELECTIVE XML INDEX sxi_Test ON [dbo].[testTbl](XmlDoc)
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
FOR
(    
    pathWH2 = '/yming:Meme/yming:WorkHours' AS XQUERY 'xs:string' SINGLETON
);
GO

Questa volta abbiamo successo.

Per testare l’efficacia dell’indice effettuiamo nuovamente la nostra query (dopo avere abilitato le statistiche con SET STATISTICS IO ON e aver abilitato la visualizzazione del piano di esecuzione effettivo in SSMS). Per vedere l’incremento di performance eseguiamo la query due volte nello stesso batch. Fra una esecuzione e l’altra droppiamo l’indice XML selettivo (per il momento non possiamo usare i query hints).

WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
SELECT COUNT(*)
FROM testTbl
WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1
GO
DROP INDEX sxi_Test ON [dbo].[testTbl]
GO
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming)
SELECT COUNT(*)
FROM testTbl
WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1
GO

Notiamo anche la differenza tangibile di IO:

Happy Coding,

Francesco Cogno

Entity Framework 5.0 e Microsoft SQL Server (parte 1)

Italian Premier Center for SQL Server — Mon, 19 Nov 2012 16:05:00 GMT

Oggi esploreremo il comportamento delle EntityFramework 5.0 su SQL Server, in particolare il comportamento che si ha utilizzando l’approccio Code First. Per questo esempio creiamo un progetto console con Visual Studio e aggiungiamo (tramite NUGet ad esempio) i riferimenti ad EntityFramework:

Ora aggiungiamo due entità (classi), una chiamata Crewman:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication3
{
    public class Crewman
    {
        public int ID { get; set; }

        public string Name { get; set; }
        public Rank Rank { get; set; }

        public Crewman ReportingOfficier { get; set; }
    }
}

E l’altra chiamata StarShip:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace ConsoleApplication3
{
    public class Starship
    {
        public Starship()
        {
            Crew = new List<Crewman>();
        }

        public int ID { get; set; }
        
        public string Name { get; set; }
        public string Designation { get; set; }

        public List<Crewman> Crew { get; set; }
    }
}

Il tipo Rank è un enumerato così definito:

public enum Rank
    {
        Captain, FirstOfficer, ChiefEngineer, Crewman, Medic
    }

Notiamo come abbiamo deliberatamente sempre specificato una proprietà pubblica di tipo int con nome ID. Inoltre notiamo come abbiamo creato una relazione fra le entità semplicemente definendo una proprietà come enumerabile di Crewman. Nel modello relazionale potremmo dire che l’entità Starship ha 0..∞ Crewman. Inoltre possiamo dire che ciascun Crewman può avere un Crewman ReportingOfficier.

Per rendere persistenti le entità definiamo una estensione di DbContext in questo modo:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace ConsoleApplication3
{    
    public class StarFleet : DbContext
    {
        public DbSet<Starship> StarShips { get; set; }
        public DbSet<Crewman> Crew { get; set; }

        public StarFleet(string nameOrConnectionString)
            : base(nameOrConnectionString)
        { }
    }
}

Notiamo l’utilizzo dell’insieme DbSet per le proprietà pubbliche da persistere su SQL Server. A questo punto l’utilizzo è semplice, nel metodo main della nostra classe console creiamo una istanza di StarFleet (con il puntamento ad una nostra istanza di SQL) e popoliamola con dati fittizi:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                StarFleet sf = new StarFleet("Server=”ServerHere”;Database=TestEFCF;Trusted_Connection=True");

                {
                    Crewman cKirk = new Crewman() { Name = "J.T. Kirk", Rank = ConsoleApplication3.Rank.Captain };
                    Crewman cSpock = new Crewman() { Name = "Spock", Rank = ConsoleApplication3.Rank.FirstOfficer, ReportingOfficier = cKirk };
                    Crewman cBones = new Crewman() { Name = "Leonard McCoy", Rank = ConsoleApplication3.Rank.Medic, ReportingOfficier = cKirk };

                    Starship s = new Starship() { Name = "USS Enterprise A" };
                    s.Crew.Add(cKirk);
                    s.Crew.Add(cSpock);
                    s.Crew.Add(cBones);

                    sf.StarShips.Add(s);
                }
                //-----------------------------
                {
                    Crewman cPicard = new Crewman() { Name = "Jean Luc Picard", Rank = ConsoleApplication3.Rank.Captain };
                    Crewman cData = new Crewman() { Name = "Data", Rank = ConsoleApplication3.Rank.FirstOfficer, ReportingOfficier = cPicard };
                    Crewman cCrusher = new Crewman() { Name = "Wesley Crusher", Rank = ConsoleApplication3.Rank.Captain, ReportingOfficier = cData };
                    Starship s2 = new Starship() { Name = "USS Enterprise C" };
                    s2.Crew.Add(cPicard);
                    s2.Crew.Add(cData);
                    s2.Crew.Add(cCrusher);

                    sf.StarShips.Add(s2);
                }
                //------------------------------
                sf.SaveChanges();

            }
            catch (Exception exce)
            {
                Console.WriteLine(exce.ToString());
            }
        }
    }
}

Notiamo subito che il Database non è necessario che esista: in caso negativo il framework cercherà di crearne uno ex-novo (ovviamente la login deve possedere i relativi privilegi perché questa creazione vada a buon fine). Eseguendo il programma otterremo un database con le seguenti tabelle:

Ma cosa succeed dietro le quinte? Attiviamo una sessione di eXtended Events (non più profiler :)) usando il template “Query detail tacking”:

Rieseguiamo il programma (supponendo di aver ripulito il db) e vedremo che i passi effettuati dall’entity framework (tralasciamo la tabella [dbo].[__MigrationHistory] che è privata del framework e non ci interessa in questo contesto).

Vedendo che non esistono le tabelle richieste, il framework le crea per noi:

CREATE TABLE [dbo].[Starships] (      
    [ID] [int] NOT NULL IDENTITY,      
    [Name] [nvarchar](max),      
    [Designation] [nvarchar](max),      
    CONSTRAINT [PK_dbo.Starships] PRIMARY KEY ([ID])  
    ) 

CREATE TABLE [dbo].[Crewmen] (      
    [ID] [int] NOT NULL IDENTITY,      
    [Name] [nvarchar](max),      
    [Rank] [int] NOT NULL,      
    [ReportingOfficier_ID] [int],      
    [Starship_ID] [int],      
    CONSTRAINT [PK_dbo.Crewmen] PRIMARY KEY ([ID])
)

In seguito vengono creati gli indici:

CREATE INDEX [IX_ReportingOfficier_ID] ON [dbo].[Crewmen]([ReportingOfficier_ID]) 

CREATE INDEX [IX_Starship_ID] ON [dbo].[Crewmen]([Starship_ID])

E poi le constraint:

ALTER TABLE [dbo].[Crewmen] 
ADD CONSTRAINT [FK_dbo.Crewmen_dbo.Crewmen_ReportingOfficier_ID] 
FOREIGN KEY ([ReportingOfficier_ID]) REFERENCES [dbo].[Crewmen] ([ID]) 


ALTER TABLE [dbo].[Crewmen] 
ADD CONSTRAINT [FK_dbo.Crewmen_dbo.Starships_Starship_ID] 
FOREIGN KEY ([Starship_ID]) REFERENCES [dbo].[Starships] ([ID])

Notiamo subito un paio cose interssanti:

L’ordine di creazione delle tabelle è quello relazionale e non quello del codice.
L’entity framework crea per noi gli indici sulla chiave esterna.

Mentre la prima è attesa, la seconda è una ottima notizia: utilizzando SSMS 2012 avremmo avuto solo la constraint e non l’indice:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Starships SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Crewmen ADD CONSTRAINT
    FK_Crewmen_Starships FOREIGN KEY
    (
    Starship_ID
    ) REFERENCES dbo.Starships
    (
    ID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 
    
GO
ALTER TABLE dbo.Crewmen SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Gli inserimenti avvengono in questa maniera:

exec sp_executesql N'insert [dbo].[Crewmen]([Name], [Rank], [ReportingOfficier_ID], [Starship_ID])
values (@0, @1, @2, @3)

select [ID]
from [dbo].[Crewmen]
where @@ROWCOUNT > 0 and [ID] = scope_identity()',

N'@0 nvarchar(max) ,@1 int,@2 int,@3 int',
@0=N'Leonard McCoy',
@1=4,
@2=3,
@3=1

Notiamo come ogni inserimento siano in realtà due statement separati:

insert [dbo].[Crewmen]([Name], [Rank], [ReportingOfficier_ID], [Starship_ID])
values (@0, @1, @2, @3)

select [ID]  from [dbo].[Crewmen]  where @@ROWCOUNT > 0 and [ID] = scope_identity()

(NB: potreste vedere comparire anche un SELECT StatMan([SC0])… ma questo non è dovuto ovviamente a EF).

Evidentemente EF usa il secondo statement per sincronizzare l’entità persisita via codice con l’ID generato da SQL. Domanda: come mai l’EF non usa la clausola OUTPUT?

La risposta più probabile è che si aspetti zero record in caso di fallimento (da where @@ROWCOUNT > 0) mentre lo statement OUTPUT restituisce righe anche se l’operazione fallisce. Per maggiori dettagli vedere http://msdn.microsoft.com/en-us/library/ms177564(v=sql.110).aspx.

Nei post successivi continueremo con l’analisi delle interazioni EF5.0-SQL.

Happy coding,

Francesco Cogno

Contained database authentication e user with password con SID identici

Italian Premier Center for SQL Server — Wed, 07 Nov 2012 10:58:00 GMT

Con SQL 2012 è possibile creare user che si autenticano direttamente nei database. In SSMS li vediamo nominati “user with password”. Questa modalità, molto utile per l’indipendenza del database dall’istanza su cui riesiede, ha tuttavia alcuni effetti collaterali. Il più evidente è il cosiddetto “login masquerading” dovuto all’algoritmo di probing di SQL in fase di autenticazione (maggiori informazioni qui http://msdn.microsoft.com/it-it/library/ff929055.aspx). In parole povere è possibile creare uno user with password con lo stesso nome di una login a livello d’istanza. SQL Server sceglierà di autenticare prima lo user with password se:

È specificato un initial database nella stringa di connessione.
L’initial database ha il supporto al contenimento parziale attivato.

Capite bene che ciò vuol dire che l’identità presentata a SQL non è più la coppia username-password ma diventa la tupla username-password-initial database.

Non gestendo correttamente questa nuova modalità ci si espone ovviamente a diversi rischi.

Oggi però voglio parlarvi di uno scenario proposto da un cliente particolarmente brillante: cosa succede se io creo due user with password con SID identici su due diversi database? Supponendo che insistano sulla stessa istanza, posso usare lo statement USE [database] per passare da uno all’altro?

Ovviamente se così fosse sarebbe un problema di sicurezza non trascurabile: ad esempio restorando in produzione un database si potrebbe aprire una porta di accesso ad altri database preesistenti. Lo scenario sarebbe:

In produzione esiste un database (chiamato Prod) con autenticazione autocontenuta.
Su Prod esiste uno user with password che ha i permessi di db_owner. La password, ovviamente, è criptata e segretata. Il SID dello user, invece, è in chiaro e facilmente ottenibile.

A questo punto avviene questo:

In sviluppo viene creato un db (chiamato Dev) con autenticazione autocontenuta.
Lo sviluppatore aggiunge una login with password con il SID dello user di Prod (sintassi CREATE USER … WITH SID=…). Lo chiamiamo SvilLogin.
Lo sviluppatore chiede il restore di Svil sulla stessa istanza di Prod.

Ciò che suddede (correttamente) è che sarà possibile collegarsi a Svil in produzione utilizzando SvilLogin.

Ciò che non deve succedere è che sia possibile accedere a Prod autenticandosi su Svil. Di fatto USE Prod deve fallire in questo contesto.

Controlliamo che ciò non succeda. Creiamo per prima cosa due DB con autenticazione autocontenuta:

CREATE DATABASE Cont1;
GO
CREATE DATABASE Cont2;
GO

ALTER DATABASE Cont1 SET CONTAINMENT=PARTIAL;
GO
ALTER DATABASE Cont2 SET CONTAINMENT=PARTIAL;
GO

A questo punto creiamo le user with password (SQLCmd mode):

:SETVAR user UserWithSameSID
:SETVAR password Passw0rd

USE Cont1;
GO
CREATE USER [$(user)] WITH PASSWORD='$(password)';
GO

DECLARE @sid VARBINARY(MAX);
SELECT @sid = SID FROM Cont1.sys.database_principals WHERE name = '$(user)';

DECLARE @stmt NVARCHAR(4000);
SET @stmt = 'CREATE USER [$(user)] WITH PASSWORD=''$(password)'', SID=' + CONVERT(VARCHAR(255), @sid, 1) + ';'
PRINT @stmt;

USE Cont2;
EXEC sp_executeSQL @stmt;
GO

SELECT * FROM Cont1.sys.database_principals WHERE name = '$(user)'
UNION ALL
SELECT * FROM Cont2.sys.database_principals WHERE name = '$(user)'

Come si nota i due user hanno SID identici... anzi hanno anche lo stesso principal_id (incidentalmente):

Ora proviamo a collegarci a Cont1 con l'utenza appena creata. A connessione avvenuta proviamo a eseguire:

SELECT * FROM sys.databases;

Il risultato è l'atteso, infatti vediamo oltre al nostro DB anche master e il TempDB ma non Cont2:

Anche USE [Cont2] non funziona come ci attendiamo:

Ora la domanda lecita è: dove SQL tiene traccia del db iniziale (che completa la tupla username-password-initial db)?

Guardiamo nella sys.dm_exec_sessions e troviamo un campo che fa al caso nostro: authenticating_database_id. Nel caso delle autenticazioni a livello di database questo campo sarà ovviamente diverso da 1:

Happy Coding,

Francesco Cogno

Cambiamento nella priorità degli Sch-S locks in SQL 2012 sotto RUI e RCSI

Italian Premier Center for SQL Server — Mon, 18 Jun 2012 09:09:00 GMT

Oggi continuiamo ad esplorare le novità di SQL 2012 andando a testare una piccola ma importante modifica alla gestione della priorità degli schema lock. Gli schema lock sono quei particolari tipi di lock utilizzati da SQL server per coordinare le operazioni con impatto a livello di schema. Alcuni esempi? Un alter table ovviamente richiede un lock di tipo schema M (modifica). Una select in read uncommitted richiede - sorpresa - un lock di tipo schema S (stabilità). Come potete immaginare (ma come potete confermare leggendo qui: http://msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx) i due diversi tipi di lock non sono compatibili fra di loro. In casi in cui non si riesca ad acquisire un lock perché ne esiste uno incompatibile SQL metterà il lock in attesa (o, meglio, il thread che ha richiesto il lock). Appena il lock sarà disponibile SQL risveglierà il thread in attesa e permetterà al lock di acquisire il possesso dell’oggetto.

Questo meccanismo si basa su una coda FIFO che garantisce che tutti i thread avranno la loro occasione di ottenere il possesso dell’oggetto (questo ovviamente a meno di deadlocks ma non ne parleremo ora).

Ad esempio supponendo di avere un lock di tipo S (condiviso) sull’oggetto A avremo una coda di questo tipo:

1: Lock S su A (attivo).

Ora supponiamo che arrivino due richieste X (esclusive) su A. X non è compatibile con S per cui viene messo in attesa.

3: Lock X su A (attesa).

2: Lock X su A (attesa).

1: Lock S su A (attivo).

Appena il lock X su A verrà rilasciato il primo thread in attesa del lock X su A verrà risvegliato per cui avremo:

2: Lock X su A (attesa).

1: Lock X su A (attivo).

Se ora arrivasse una richiesta di lock S su A anche lui andrà in coda:

3: Lock S su A (attesa).

2: Lock X su A (attesa).

1: Lock X su A (attivo).

NB: Questa è una semplificazione estrema, i lock sono molti di più e gli oggetti su cui si pongono sono vari; tuttavia è sufficiente per lo scopo di questo post.

Questo meccanismo aveva per gli schema locks – fino a SQL 2008 R2 incluso – una deroga: gli Sch-S lock posti negli isolation level read uncommitted e read committed snapshot avevano precedenza sugli altri schema lock (cioè sul Sch-M).

Ciò vuol dire che in una situazione come questa:

3: Lock Sch-M su A (attesa).

2: Lock Sch-M su A (attesa).

1: Lock Sch-S su A (attivo).

All’arrivo di un nuovo Sch-S invece che essere messo in fondo alla coda questo verrà messo in testa:

4: Lock Sch-M su A (attesa).

3: Lock Sch-M su A (attesa).

2: Lock Sch-S su A (attesa).

1: Lock Sch-S su A (attivo).

Come potete ben immaginare in determinate condizioni – ad esempio arrivando continui Sch-S in read uncommitted – i thread in attesa del Sch-M non verrebbero mai risvegliati. Questa situazione è chiamata in letteratura thread starvation.

Ora voi sarete tranquilli perchè non utilizzate l’isolation level read uncommitted. Chi lo usa dopotutto? ;). Però se ci pensate ogni volta che usate il query hint WITH(NOLOCK) lo state facendo! Quindi se voi utilizzate WITH(NOLOCK) siete esposti a questo problema.

Vi do un paio di script per provare il tutto (non in prod, mi raccomando!):

CREATE DATABASE StarvationDemo;
GO
USE StarvationDemo;
GO
CREATE TABLE NoLockSample(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Testo NVARCHAR(255));
GO
INSERT INTO NoLockSample
SELECT name FROM sys.objects;
GO 256

Ora aprite tre o quattro finestre di SSMS e lanciate contemporaneamente questo script

USE StarvationDemo;
GO

DECLARE @cnt FLOAT

WHILE 1=1
    SELECT @cnt=AVG(CONVERT(FLOAT, ID)) FROM NoLockSample
        WITH(NOLOCK)
        WHERE LEFT(Testo, 1) = 'LL';

Notate che usiamo il famigerato NOLOCK.

Se a questo punto, in un’altra finestra, lanciate un banale alter table:

USE [StarvationDemo];
GO
ALTER Table [dbo].[NoLockSample] ADD PocaRoba BIT;
GO

Attenderete parecchio :). Se controllate i lock presenti:

SELECT l.request_mode,r.transaction_id, r.blocking_session_id, r.wait_type, r.start_time, l.request_lifetime--, r.*, l.*
FROM sys.dm_tran_locks l
      inner join sys.dm_exec_requests r
            on l.request_session_id = r.session_id
where request_mode in ('Sch-S', 'Sch-M')
order by start_time desc

Vedrete gli Sch-S locks attivi con il vostro Sch-M lock in attesa (notate come l'id di transazione cambi):

Se avete sottomano un SQL 2012 vedrete che il thread in attesa di Sch-M non verrà “scavalcato”, di fatto risolvendo il thread starvation.

Ecco un altro motivo in più per passare a SQL 2012 :).

Happy coding,

Francesco Cogno

SQL 2012 e VLFs

Italian Premier Center for SQL Server — Mon, 28 May 2012 14:17:00 GMT

Un post veloce per mostrare una funzionalità molto gradita introdotta da SQL Server 2012. Come sicuramente saprete, troppi VLF (virtual log files) possono impattare negativamente le operazioni del transaction log.

Fino SQL Server 2008 R2 bisognava attivamente documentarsi per avere informazioni sul numero di VLF (ad esempio con DBCC LOGINFO). SQL Server 2012, invece, mostra un messaggio di warning nell'error log se, durante la fase di redo, l'engine identifica un database con più di 10000 (si, diecimila) VLF.

Il messaggio è il seguente:

Database testVLF has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Volete provare voi stessi? Potete usare questo script:

use [master];
GO

CREATE DATABASE testVLF;
ALTER DATABASE testVLF SET RECOVERY FULL;
BACKUP DATABASE testVLF TO DISK='nul';
BACKUP LOG testVLF TO DISK='nul';

USE [master]
GO
ALTER DATABASE [testVLF] MODIFY FILE ( NAME = N'testVLF_log', FILEGROWTH = 1KB )
GO

USE [testVLF]
GO


CREATE TABLE test(testo CHAR(1200));
GO
PRINT 'Inserimento 100 righe...'
GO
INSERT INTO test SELECT 'somedata';
GO 100

PRINT 'Inserimento massivo righe...';
GO
INSERT INTO test 
SELECT * FROM test;
GO 14

DBCC LOGINFO(testVLF);
GO

USE [master];
GO

BACKUP DATABASE [testVLF] TO DISK='I:\Samples\TooManyVLFs\testVLF.bak' WITH COMPRESSION;
GO

DROP DATABASE testVLF;
GO

RESTORE DATABASE [testVLF] FROM  DISK = N'I:\Samples\TooManyVLFs\testVLF.bak' WITH STATS=1;
GO

EXEC xp_readErrorLog

Sostanzialmente stiamo creando un database nuovo a cui impostiamo l'autogrowth a 1024 bytes (ma che SQL alzerà al minimo che è 262144 bytes). A questo punto riempiamo il tlog fino a generare almeno 10000 vlf (da cui avremo un tlog di almeno 2.5 GB circa). Ora effettuiamo un backup-drop-restore (potreste dover cambiare il percorso di backup sulla vostra macchina di test).

L'error log riporterà un warning simile a questo:

Nota: non considerate "giusto" il numero di VLF se è minore di 10000. Il valore di 10000 è preso come soglia oltre la quale i VLF sono decisamente troppi. Come sempre, l'unico vero valore giusto è quello che soddisfa le vostre esigenze. In altre parole, testate :). Lo scopo di questo warning è di evidenziare, a beneficio dei DBA, una situazione che sta sfuggendo di controllo.

Happy coding,

Francesco Cogno

DISM: Chi è costui?

Italian Premier Center for SQL Server — Fri, 27 Jan 2012 14:11:00 GMT

Il suo nome per esteso è “Deployment Image Services and Management Tool”, ed e’ la seconda volta che mi imbatto in questo tool di sistema operativo per Windows Server 2008 R2:

Si tratta di uno strumento “builtin” accessibile da command line con privilegi amministrativi “elevati” e nel caso specifico l’ho dovuto utilizzare per rimuovere i file di backup dell’installazione della “Service Pack 1” di Windows Server 2008 R2”, il comando dettagliato è il seguente:

DISM.exe /online /Cleanup-Image /spsuperseded

Et voilà, avete appena liberato 2.1 GB sul disco “C:” che fanno sempre comodo !

NOTA: Ovviamente, poi non sarete più in grado di rimuovere la SP1, ma ad oggi non ho ancora avuto notizia di un Cliente che abbia avuto bisogno di farlo.

Ah, si… Vi ho accennato ad un'altra volta in cui mi sono imbattuto in codesto tool, per l’esattezza durante una procedura di “Security Hardening” di una installazione SQL Server per un grosso cliente in italia; ebbene, utilizzando il seguente comando potete completamente rimuovere “Internet Explorer” dalla Vostra installazione (x64):

Dism /online /disable-feature /featurename:Internet-Explorer-Optional-amd64

Carino vero ? :)

Ho testato la cosa ed è assolutamente supportata, c’è solo il problema, in ambiente Cluster, di non poter più riuscire a vedere in locale il “Validation Report”, ma si può facilmente ovviare da remoto.

Buon lavoro a tutti.

--Igor Pagliai--

MSDTC & “Mount Point” NTFS

Italian Premier Center for SQL Server — Fri, 27 Jan 2012 09:19:00 GMT

Sarà un post abbastanza breve, ma l’argomento sono sicuro che interesserà molte persone dato che la cosa non è ufficialmente riportata in nessuna documentazione Microsoft, almeno in quella ufficiale, e riguarda l’utilizzo di un “Mount Point” NTFS come base per la componente MSDTC (Microsoft Distributed Transaction Coordinator) in ambiente Cluster.

Prima di passare all’argomento principale di questo post, è bene ricordare che in Windows Server 2008 R2:

- Mentre con SQL Server 2005, in ambiente Cluster, l’installazione della componente MSDTC era propedeutica all’installazione di SQL, in SQL Server 2008 non è più così, anzi, se la componente MSDTC non è richiesta dallo scenario applicativo/architetturale potete assolutamente fare a meno di installarla;

- In Windows Server 2008 Cluster, potete avere quante istanza della componente MSDTC volete, a differenza di Windows Server 2003 dove era possibile averne solo una per l’intero sistema Cluster;

Dunque, qualora Vi venisse la tentazione di utilizzare un “Mount Point” NTFS come disco di base per la componente MSDTC, sappiate che ciò non è supportato e neanche possibile, avrete sicuramente dei problemi ma non subito: all’atto dell’installazione/configurazione dell’MSDTC, nulla sembra indicare nessun tipo di problema, ma nel momento in cui tenterete di eseguire un “failover” manuale (in questo caso) su un altro nodo, l’operazione fallirà e, a seconda di quanti nodi avete e della configurazione adottata, assisterete al tipico fenomeno del “ping pong” delle risorse da un nodo all’altro, fino a che il tutto non tornerà sul nodo iniziale.

Un rapido esame dell’Event Log “Application” del Sistema del Sistema Operativo, rivelerà il seguente messaggio di errore:

A beneficio dei motori di ricerca che presto indicizzeranno questo post, riporto la stringa completa dell’errore:

Source: MSDTC Client 2

Event ID: 4101

Level: Error

Description: The DTC cluster resource’s log file path was originally configured at: \\?. Attempting to change that to: \\?\GLOBALROOT\Device\Harddisk22\Partition1. This indicate a change in the path of the DTC cluster resource’s dependent disk resource. This is not supported. The error code returned: 0x8000FFFF.

Ebbene, dopo alcune verifiche interne quello che l’errore ci dice, e che ovviamente ho sospettato, si è rivelato corretto: l’utilizzo dei “Mount Point” NTFS, in configurazione Cluster, anche sul più recente Windows Server 2008 R2, non è (ancora) supportata.

Esternamente potete trovare traccia di questa cosa sul sito Connect di Microsoft, al link seguente, Vi invito a votare a favore di questo “bug” per far sapere al nostro gruppo di sviluppo per Windows Server quanto questa cosa sia “desiderabile” avere nella prossima versione:

https://connect.microsoft.com/SQLServer/feedback/details/576545/msdtc-fails-to-restart-in-sql-server-2008-r2-clustered-group

Buon lavoro a tutti, al prossimo post.

--Igor Pagliai--

TDS and Network packet size

Italian Premier Center for SQL Server — Wed, 18 Jan 2012 08:16:00 GMT

Vi siete mai chiesti qual’e’ l’overhead del protocollo TDS? Il protocollo e’ ben documentato a questo indirizzo: http://msdn.microsoft.com/en-us/library/dd304523(v=PROT.13).aspx, tuttavia in questo post seguiremo un approccio differente da quello accademico: snifferemo i pacchetti (TCP) fra Management Studio e SQL Server per analizzarli ex-post. In questo modo avremo un pretesto per analizzare il comportamento del protocollo TDS.

Strumenti

Per la nostra analisi utilizzeremo Microsoft Network Monitor (per gli amici, NetMon) disponibile qui: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4865. Tramite NetMon e’ possibile intercettare tutti i pacchetti in ingresso ed in uscita da una interfaccia di rete.

Configurazione iniziale

Scriviamo un codice TSQL abbastanza prolisso da essere di dimensioni significative. Nel mio caso mi sono affidato al comment per ingrossare la dimensione del file.

Salviamo il codice dentro un file. Importante: assicuratevi che il formato sia ANSI (cfr 2.2.1.3 SQL Batch) altrimenti avrete dei numeri errati. Guardate la dimensione del file (proprieta’) per avere la dimensione del “payload utile” che invieremo a SQL Server:

La dimensione che ci interessa e’ il campo Size (non Size on disk). Nell’esempio il mio codice occupa 6.145 bytes. Essendo ANSI dobbiamo moltiplicare per 2 il numero di bytes occupati per avere l’occupazione Unicode. Otteniamo 12.290 bytes.

La dimensione di default del pacchetto TDS e’ 4096 (4KB). Con questa impostazione saranno necessari almeno 4 pacchetti (16.384 bytes) per inviare il SQL Batch message da SSMS a SQL Server. Supponiamo di voler minizzare il numero di pacchetti inviati per cui decidiamo di negoziare un packet size di 32.767 bytes (massimo consentito). Per farlo serve modificare l’impostazione del server SQL? La risposta e’ no per cui lasciamo il server al valore di default:

Ma assicuriamoci di modificare lato SSMS il parametro corretto:

Test

Ora attiviamo NetMon ed eseguiamo la query da SSMS. Il risultato (colorato e filtrato) e’ il seguente:

Ho evidenziato i pacchetti da SSMS a SQL. Si nota subito una cosa interessante: benche’ abbiamo impostato il packet size a 32.767 il layer sottostante (TCP) ha comunque deciso di segmentare il messaggio in piu’ pacchetti separati (nel mio caso di 1448 bytes). Cio’ e’ atteso e documentato qui http://msdn.microsoft.com/en-us/library/aa174503(v=SQL.80).aspx:

You can configure the SQL Server packet size, which is the size of the TDS packets. The size of the TDS packets defaults to 4 KB on most clients (DB-Library applications default to 512 bytes), which testing has shown to be the optimal TDS packet size in almost all scenarios. The size of the TDS packets can be larger than the size of the packets in the underlying protocol. If this is the case, the protocol stack on the sending computer disassembles the TDS packets automatically into units that fit into the protocol packets, and the protocol stack on the client computer reassembles the TDS packets on the receiving computer.

Cio’ vuol dire che per calcolare la dimensione totale del nostro messaggio TDS dobbiamo sommare tutti i pacchetti TCP componenti il messaggio TDS. Nell’esempio abbiamo 8 pacchetti TCP da 1448 bytes e 1 pacchetto (l’ultimo) da 736 bytes. In totale, quindi, il messaggio TDS ha dimensione 12.320 bytes.

Ora ripetiamo l’esperimento rinegoziando il network packet TDS a 8.192 bytes. In questo caso saranno necessari 2 pacchetti TDS per inviare il batch di cui sopra:

Sommando i pacchetti (8 da 1448 bytes e 1 da 744 bytes) si ottiene 12.328 bytes.

Risultato

Nel primo caso abbiamo utilizzato un TDS network packet size di 32.767 bytes e abbiamo effettuato un traffico TCP di 12.320 bytes. Nel secondo caso abbiamo abbassato il network packet size a 8.192 bytes e abbiamo effettuato un traffic TCP di 12.328 bytes. La differenza e’ di 8 bytes.

A cosa sono dovuti? E’ l’header in piu’ dovuto al fatto che abbiamo inviato 2 pacchetti TDS invece che 1. Vi rimando qui per maggiori dettagli http://msdn.microsoft.com/en-us/library/dd340948(v=PROT.13).aspx .

Confrontando con l’occupazione del semplice statement Unicode (12.290 bytes) avanzano 30 bytes. Esclusi gli 8 precedentemente evidenziati ne rimangono ancora 22. Questo valore e’ quello che ci attendiamo per il campo All_Header (cfr http://msdn.microsoft.com/en-us/library/dd304416(v=PROT.13).aspx ). L’esempio cita infatti:

</TotalLength>

0x16 in esadecimale equivale esattamente a 22 in decimale.

Aggiungo come riferimento la progressione dell’overhead (dell’header TDS) in percentuale per uno statement di 65.536 bytes in funzione del network packet size. Come si nota anche con un packet size di 512 bytes l’overhead e’ inferiore al 2%.

Happy coding

Francesco Cogno

Il falso mito della migrazione da una versione di SQL all'altra

Italian Premier Center for SQL Server — Wed, 14 Dec 2011 14:41:00 GMT

Moltissimi DBA conoscono benissimo la seguente regola:

“You can migrate a database that was created in an earlier version of SQL Server to a later version. This is a typical database upgrade and is supported. However, you cannot migrate a database that was created in a later version of SQL Server to an earlier version.”

Per moltissimo tempo la regola effettivamente non ha avuto eccezioni ed anche Microsoft, in moltissimi documenti e per bocca del suo Supporto, ha fatto di tutto per diffondere questo messaggio.

NOTA: Ho sottolineato il termine versione perché è proprio qui che si nasconde l’ambiguità !

Ebbene, è un altro falso mito o falsa leggenda metropolitana, ne esistono molti/molte in giro come anche il nostro buon amico Paul Randal (http://www.sqlskills.com/BLOGS/PAUL/) ha largamente documentato in questo documento di notevole interesse:

http://www.sqlskills.com/BLOGS/PAUL/post/T-SQL-Tuesday-11-Misconceptions-about-EVERYTHING!!.aspx

Beh, almeno un’eccezione esiste, come Vi illustrerò tra poco e sono sicuro che non è l’unica, magari ne parlerò in un altro blog.

Dunque, il documento che ha attirato la mia attenzione è il seguente e tratta del supporto “esteso” al numero massimo di partizioni per singola tabella, passato da 1000 a 15000 con la SP2 di SQL Server 2008 e la SP1 di SQL Server 2008 R2:

http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/Support_for_15000_Partitions.docx

La parte interessante, per quanto riguarda questo mio post, è la seguente:

SQL Server 2008 SP2 databases with support for 15,000 partitions have version number 662, while SQL erver 2008 R2 RTM supports databases with version numbers up to and including 661.

Quello che in pratica è scritto qui smentisce la regola iniziale che ho citato e che è riportata anche nel sudetto documento: in questo specifico caso, non è possibile migrare un database da una istanza SQL Server 2008 SP2 (build 10.0.x.x) ad una più recente (altro termine ambiguo !) SQL 2008 R2 SP1 (build 10.50.x.x), eppure la build è superiore !

L’ambiguità sta proprio nel termine “version” che ho sottolineato più volte, è necessario rifrasare la regola iniziale nel seguente modo:

”You can migrate a database that was created in an earlier version of SQL Server to a later version to a later version if and only if this later version understand/support the existing database version number”

In questo caso specifico:

Si può migrare un database da SQL Server 2008 SP2 a SQL Server 2008 R2 RTM se non si è abilitato il supporto esteso per le 15000 partizioni;
Si può migrare un database da SQL Server 2008 SP2 a SQL Server 2008 R2 SP1;

Un interessante ed importante corollario è il seguente:

“I can migrate a database that was created in a later version of SQL Server to an earlier version if and only if the database version number supported by the target instance is the same or higher of the one stamped in the database itself by the source instance”

Un esempio ? Se avete un database su SQL Server 2008 SP1 potete migrarlo/spostarlo su una nuova/differente istanza SQL Server 2008 RTM, quindi con un differente livello di Service Pack; lo stesso esempio si può fare con SQL Server 2005, in pratica le Service Pack, in genere, ma non è sempre garantito che sia così, non innalzano la “DB version” di un database e se così è, il relativo database può essere portato su una versione precedente (differente livello di SP ma stessa versione) di SQL Server.

Un’ultima annotazione: come si fa a controllare la “DB version” di un database ?

Il modo più semplice è fornito dal seguente comando non documentato (e quindi non supportato ! :)) ma ben noto a molti DBA:

dbcc dbinfo('AdventureWorks2008')

NOTA: per visualizzare a video il relativo output, dovete prima eseguire il comando DBCC TRACEON(3604) altrimenti il tutto finirà nell’ERRORLOG dell’istanza SQL corrente.

Ecco un estratto di cosa compare a video:

DBINFO STRUCTURE:

DBINFO @0x0000000012F5D780

dbi_dbid = 5 dbi_status = 65544 dbi_nextid = 647673355

dbi_dbname = AdventureWorks2008R2 dbi_maxDbTimestamp = 52000 dbi_version = 661

dbi_createVersion = 661 dbi_ESVersion = 0

dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2010-06-28 15:31:23.283

La parte evidenziata in rosso fornisce l’informazione che cerchiamo, in questo caso la “DB version” è 661 !

Se invece avete fatto un backup del database che volete esaminare, potete anche esaminare una delle tabella di sistema del database MSDB con la seguente semplice query:

select backup_set_id,database_version 
from msdb.dbo.backupset
where database_name = 'AdventureWorks2008R2'

Come ulteriore possibilità, se non avete le necessarie informazioni in MSDB ma solamente il file BAK, ecco il comando che fa per Voi:

restore headeronly from disk = 'C:\temp\DB_BACKUP.bak'

L’informazione che cerchiamo è immediatamente visibile nell’output del comando:

Anche per questo post è tutto, buon lavoro a tutti !

---Igor Pagliai ---

Cambiamento epocale nella gestione delle statistiche

Italian Premier Center for SQL Server — Fri, 25 Nov 2011 14:40:00 GMT

Salve a tutti,

L’argomento di oggi ha, per chi lavora con SQL Server da molti anni, un significato particolare perché va ad impattare uno degli aspetti più granitici ed immutabili nel tempo, sai che è così da (n) versioni e (m) anni, con tutti i problemi del caso, ed un bel giorno Ti svegli ed alcune di queste certezze non ci sono più ! :)

L’oggetto della mia meraviglia, e del presente post, è un nuovo meccanismo per la gestione “dinamica” delle statistiche, introdotto con la SP1 di SQL Server 2008 R2 e che sarà anche nella prossima versione di SQL Server (nome in codice “Denali”): quello che ora cambia, se abilitate il trace flag –T2371, è la magica soglia (di modifiche ad una colonna/e di una tabella) oltre la quale una certa statistica viene aggiornata automaticamente da SQL Server.

Fino a ieri, la regoletta che potete trovare in varie documentazioni su Internet era la seguente, per l’aggiornamento dinamico di una statistica (“recompilation threshold”):

Tabelle con numero di righe < 500: la soglia di aggiornamento delle statistiche è (500) modifiche;
Tabelle con numeri di righe >= 500: la soglia di aggiornamento delle statistiche è [(500) + (20% del numero di righe di una tabella)] modifiche;
Tabelle temporanee: come sopra, con una ulteriore soglia che scatta al raggiungimento delle 6 righe inserite nella tabella;

Con varie sfaccettature, questa soglia statica (cioè il numero “20%”) non è mai cambiata dalla sua introduzione in SQL 7.0, parliamo quindi di circa 13 anni fa !

Il problema di questa percentuale fissa è che, su tabelle molto grandi come numerosità di righe, tale soglia è troppo alta e si rischia che SQL Server non scateni mai l’aggiornamento automatico delle statistiche, con conseguenti problemi di accuratezza dei “query plan” generati.

Per farVi un esempio concreto, immaginate la seguente situazione:

Tabella con 100 milioni di record (credetemi, non sono tante ! :))
Ogni giorno vengono inseriti 1 milione di nuovi record;
Il 95% delle attività sulla tabella, intese come INSERT,UPDATE, DELETE ma anche SELECT, sono sui dati dell’ultima settimana, quindi gli “ultimi” 7 milioni di record;
Il database che contiene tale tabella ha, come da default, l’opzione “AutoUpdate Statistics” abilitata;
Le statistiche non legati agli indici, presenti sulla tabella, hanno un “sample” di default del 10%, cioè considerano un campione casuale/uniforme dei dati dell’intera tabella;

Riuscite a vedere il problema? Con la soglia fissa al 20%, le statistiche di questa tabella saranno aggiornate non prima di 20gg (1M di record x 20gg = 20M record =circa il 20% delle righe della tabella) !!!

Con tabelle di queste dimensioni, spesso partizionate, la soglia classica di aggiornamento delle statistiche non basta mai, per cui è altrettanto spesso necessario inserire un “job” del “SQL Agent” che periodicamente (non certo ogni 20gg !) forzi l’aggiornamento delle statistiche.

Con questo nuovo meccanismo, la soglia di auto-aggiornamento delle statistiche non è più fissa al 20% ma varia, nella sua percentuale, dinamicamente in base al numero di record di una tabella: purtroppo al momento l’informazione esatta su come varia questa percentuale non è di carattere pubblico :), ma facendo qualche test numerico è facilmente intuibile ;), il concetto importante da considerare è che più record ha una tabella e più si abbassa la percentuale oltre la quale scatta l’aggiornamento delle statistiche, ecco alcuni esempi:

Tabella con 10 milioni di record, la soglia è circa dell’1%;

L’autoupdate scatterà dopo 100mila modifiche, anziché 2 milioni di modifiche, quindi 20 volte più frequente;

Tabella con 50 milioni di record, la soglia è circa dello 0.5%;

L’autoupdate scatterà dopo 250mila modifiche, anziché 10 milioni di modifiche, quindi 40 volte più frequente;

Tabella con 100 milioni di record, la soglia è circa lo 0.3%;

L’autoupdate scatterà dopo 300mila modifiche, anziché 20 milioni di modifiche, quindi 67 volte più frequente;

Per chi non avesse ancora indovinato la funzione matematica che governa la soglia dinamica, rendo ancora più facile la cosa con il seguente grafico tratto da un post del collega Juergen Thomas (grazie !), il nostro maggiore esperto di SAP:

NOTA: Non Vi aspettate che questo meccanismo entri in azione su tabelle piccole, come potete vedere dal grafico la soglia è identica fino a (circa) 25000 righe, poi il comportamento cambia.

OK, sembra tutto fantastico, ma qualcuno dovrebbe chiedersi: pechè devo abilitare un “trace flag” per avere questo meccanismo ed invece non è un comportamento di default di SQL Server?

La cosa ha molto senso ed è una consuetudine del gruppo di sviluppo di SQL Server: ogni modifica al codice e/o nuova funzionalità che cambi il comportamento dell’engine di SQL deve essere attivata esplicitamente (e consciamente), ed i “trace flags” adempiono proprio a questo scopo, oltre ovviamente a molti altri. Vorrei poi puntualizzare che non è detto che questo meccanismo porti beneficio a tutti i clienti: è vero che le statistiche saranno aggiornate più frequentemente, ma ciò vuol dire anche un maggiore numero di “ricompilazioni” delle stored procedure e delle query adhoc, quindi (tra le altre cose) un maggiore utilizzo della CPU.

Come si abilita in maniera “permanente” questo meccanismo ? Và inserito il trace flag –T2371 negli “startup parameters” dell’istanza SQL utilizzando il tool “SQL Configuration Manager”, tab “Advanced” nelle proprietà del servizio SQL Server, riga “startup parameters”: attenzione, dovete inserire –T2371 in coda a quello che già c’è, pre-pendendo un punto e virgola (;). Dopo l’inserimento del trace flag di SQL tramite SQL Configuration Manager, e relativo reboot, verificate che nella parte iniziale dell’ERRRORLOG compaia una cosa del genere:

Registry startup parameters:

-d C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\DATA\master.mdf

-e C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\Log\ERRORLOG

-l C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\DATA\mastlog.ldf

-T 2371

IMPORTANTE: Sebbene dobbiate inserire -T2371 senza spazio tra –T e 2371 negli “startup parameters”, nell’ERRORLOG lo spazio invece compare, non Vi preoccupate è normale e deve essere così altrimenti avete commesso un errore nella configurazione;

Anche per oggi è tutto, mi raccomando: testate, testate ed ancora testate…..

--Igor Pagliai—

Come esportare il contenuto di una istanza di Reporting Services

Italian Premier Center for SQL Server — Fri, 25 Nov 2011 14:02:00 GMT

Migrare reporting services e' una operazione ben documentata. Oggi vedremo come, con poche righe di codice, sia possibile esportare una istanza di reporting services su file system. Non entrero' nel dettaglio di come collegarsi e utilizzare il web service di reporting services (per dettagli vedere qui: http://msdn.microsoft.com/en-us/library/bb522713.aspx). Mi limitero' a utilizzare questi due metodi che fanno al caso nostro:

Il primo metodo e' in grado di restituire tutti gli oggetti di una istanza di RS, il secondo estrae il contenuto come array di bytes. Salvando su file system il contenuto noteremo che si tratta dello stesso XML (o meglio, RDL) che compone il report.

Il codice a questo punto e' molto semplice (qui sotto ne pubblico uno spunto per una eventuale implementazione):

            RS2010.ReportingService2010SoapClient svc = new RS2010.ReportingService2010SoapClient();
            svc.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
            svc.Endpoint.Address = new System.ServiceModel.EndpointAddress(args[0]);

            RS2010.TrustedUserHeader tuh = new RS2010.TrustedUserHeader();

            RS2010.CatalogItem[] items = null;
            
            // Lettura di tutti gli oggetti del server
            svc.FindItems(tuh, "/", RS2010.BooleanOperatorEnum.And,
                new RS2010.Property[] { new RS2010.Property() { Name = "Recursive", Value = "true" } },
                new RS2010.SearchCondition[] { },
                out items);

            #region Extract all
            if (fExtractAll)
            {
                Console.WriteLine("Extracting all items");

                byte[] bBuffer2;
                for (int i = 0; i < items.Length; i++)
                {
                    if (items[i] is RS2010.CatalogItem)
                    {
                        RS2010.CatalogItem ci = (RS2010.CatalogItem)items[i];
                        if (ci.TypeName == "Folder")
                            continue;

                        try
                        {
                            Console.WriteLine("Extracting " + ci.Path);

                            // Estrazione definizione dell'oggetto
                            svc.GetItemDefinition(tuh, ci.Path, out bBuffer2);

                            int idx = ci.Path.LastIndexOf('/');
                            string strPath = ci.Path;
                            if (idx > 0)
                            {
                                strPath = strPath.Substring(0, idx);
                                strPath = strPath.Replace('/', System.IO.Path.DirectorySeparatorChar).Substring(1);
                            }
                            else
                            {
                                strPath = "";
                            }

                            System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(
                                strBaseDirectory + System.IO.Path.DirectorySeparatorChar +
                                strPath);
                            if (!di.Exists)
                                di.Create();

                            string strFileName = di.FullName + System.IO.Path.DirectorySeparatorChar + ci.Name;
                            if (ci.TypeName == "Report")
                                strFileName += ".rdl";
                            else
                                strFileName += ".xml";

                            using (System.IO.FileStream fs = new System.IO.FileStream(
                                strFileName,
                                System.IO.FileMode.Create,
                                System.IO.FileAccess.ReadWrite,
                                System.IO.FileShare.Read))
                            {
                                fs.Write(bBuffer2, 0, bBuffer2.Length);
                            }
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.ToString());
                        }
                    }
                }
            }
            #endregion

Un paio di note:

Ricordatevi di alzare il valore maxReceivedMessageSize del binding al WS.
Per usare l'autenticazione integrata impostate security mode="TransportCredentialOnly" e transport clientCredentialType="Windows".
Benche' sia possibile esportare anche i shared data sources, fate attenzione perche' le password non verranno esportate.

Happy coding,

Francesco Cogno

Statistiche & FULLSCAN

Italian Premier Center for SQL Server — Wed, 23 Nov 2011 14:53:00 GMT

Le statistiche sono importanti. SQL Server, se opportunamente configurato, crea e manutiene le statistiche automaticamente. Questo bellissimo articolo tratta in maniera esaustiva le statistiche: MSDN: Statistics Used by the Query Optimizer in Microsoft SQL Server 2008.

Ma come fare ad identificare le statistiche sottocampionate?

Potete provare con questo script che analizza tutti i DB di una istanza:

SET NOCOUNT ON

IF (OBJECT_ID('tempdb..#tmpstat') IS NOT NULL)
    DROP TABLE #tmpstat
IF (OBJECT_ID('tempdb..#cmd') IS NOT NULL)
    DROP TABLE #cmd
IF (OBJECT_ID('tempdb..#tmpMismatch') IS NOT NULL)
    DROP TABLE #tmpMismatch
IF (OBJECT_ID('tempdb..#tmpstatDatabase') IS NOT NULL)
    DROP TABLE #tmpstatDatabase
        
CREATE TABLE #tmpstat(
    Name VARCHAR(255), 
    Updated DATETIME,
    [Rows] INT,
    [Rows Sampled] INT,
    [Steps] INT,
    [Density] INT,
    [Average key length] FLOAT,
    [String Index Filter] VARCHAR(255),
    [Expression] VARCHAR(255),
    [Unfiltered Rows] INT)
    
CREATE TABLE #tmpstatDatabase(
    DatabaseName VARCHAR(255),
    [Object] VARCHAR(255),
    Statistic VARCHAR(255), 
    Updated DATETIME,
    [Rows] INT,
    [Rows Sampled] INT,
    [Steps] INT,
    [Density] INT,
    [Average key length] FLOAT,
    [String Index Filter] VARCHAR(255),
    [Expression] VARCHAR(255),
    [Unfiltered Rows] INT)
    
SELECT * INTO #tmpMismatch FROM #tmpstatDatabase;    

CREATE TABLE #cmd(TsqlFullScan NVARCHAR(MAX))

DECLARE @dbname SYSNAME;
DECLARE @statement NVARCHAR(MAX);

DECLARE cDatabase CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN('master', 'msdb', 'tempdb', 'model')
ORDER BY name;

OPEN cDatabase;

FETCH NEXT FROM cDatabase INTO @dbname;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT 'Working on ' + @dbname + '.';
    SET @statement =
        'DECLARE cStats CURSOR FOR 
SELECT 
SCH.name AS Sch,
O.name AS Tab, 
S.name AS Obj
FROM [' + @dbname + '].sys.stats S
INNER JOIN [' + @dbname + '].sys.objects O ON S.object_id = O.object_id
INNER JOIN [' + @dbname + '].sys.schemas SCH ON O.schema_id = SCH.schema_id
WHERE O.type = ''U''
ORDER BY S.name

DECLARE @sch VARCHAR(255)
DECLARE @tab VARCHAR(255)
DECLARE @obj VARCHAR(255)
DECLARE @cmd NVARCHAR(MAX)


OPEN cStats
FETCH NEXT FROM cStats INTO @sch, @tab, @obj

WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @cmd = 
''INSERT #tmpstat '' +
''EXEC(''''DBCC SHOW_STATISTICS(
''''''''[' + @dbname + '].['' +
@sch + ''].['' + @tab + '']'''''''', ['' + 
@obj + '']) WITH STAT_HEADER, NO_INFOMSGS'''')''
--PRINT @cmd;
EXEC sp_executeSQL @cmd;

IF EXISTS(
SELECT * FROM #tmpstat
WHERE
[Name] = @obj
AND 
ISNULL([Rows], 0) <> ISNULL([Rows Sampled], 0)
)
BEGIN
INSERT INTO #cmd
SELECT ''UPDATE STATISTICS '' + 
''[' + @dbname + '].'' +
''['' + @sch + ''].['' + @tab + ''] WITH FULLSCAN''

END

INSERT INTO #tmpstatDatabase
SELECT ''' + @dbname + ''', ''['' + @sch + ''].['' + @tab + '']'', * FROM #tmpstat

INSERT INTO #tmpMismatch 
SELECT ''' + @dbname + ''', ''['' + @sch + ''].['' + @tab + '']'', * FROM #tmpstat
WHERE
[Name] = @obj
AND 
ISNULL([Rows], 0) <> ISNULL([Rows Sampled], 0) 

TRUNCATE TABLE #tmpstat;

FETCH NEXT FROM cStats INTO @sch, @tab, @obj 
END 

CLOSE cStats;
DEALLOCATE cStats;'
        
        --PRINT @statement;
        
        EXEC master..sp_ExecuteSQL @statement;
        
        FETCH NEXT FROM cDatabase INTO @dbname;
END

CLOSE cDatabase;
DEALLOCATE cDatabase;

--SELECT * FROM #tmpstatDatabase;
SELECT * FROM #tmpMismatch;
SELECT DISTINCT * FROM #cmd;

DROP TABLE #tmpstat;
DROP TABLE #cmd;
DROP TABLE #tmpMismatch;
DROP TABLE #tmpstatDatabase;
GO

L'output sara' l'elenco di statistiche sottocampionate. Il secondo recordset e' l'elenco dei comandi T-SQL per correggere la situazione.

Happy coding,

Francesco Cogno

Uno sguardo nello schema di ShowPlanXML: come individuare i table scans con XPath

Italian Premier Center for SQL Server — Thu, 03 Nov 2011 11:33:00 GMT

Recentemente un cliente mi ha posto questa domanda: “come posso identificare le query ad-hoc che effettuano table scan?”

La risposta e’ semplice e consiste nel guardare il piano di esecuzione. Per esempio questa query:

SELECT * FROM charge;
GO

Effettuata su una tabella heap senza indici risulta in un table scan, come si puo’ vedere dal piano di esecuzione:

Ma se volessimo farlo programmaticamente? Se analizziamo lo schema del piano d’esecuzione (pubblicato all’indirizzo http://schemas.microsoft.com/sqlserver/2004/07/showplan/) vediamo che lo schema e’ semplice:

Dove StmtBlockType e’ un complexType cosi’ definito:

<xsd:complexType name="StmtBlockType">
     <xsd:annotation>
       <xsd:documentation>The statement block that contains many statements</xsd:documentation>
     </xsd:annotation>
     <xsd:sequence>
       <xsd:choice minOccurs="0" maxOccurs="unbounded">
         <xsd:element name="StmtSimple" type="shp:StmtSimpleType" />
         <xsd:element name="StmtCond" type="shp:StmtCondType" />
         <xsd:element name="StmtCursor" type="shp:StmtCursorType" />
         <xsd:element name="StmtReceive" type="shp:StmtReceiveType" />
         <xsd:element name="StmtUseDb" type="shp:StmtUseDbType" />
       </xsd:choice>
     </xsd:sequence>
   </xsd:complexType>

Per la nostra indagine ci interessano gli StmpSimpleType. Al suo interno poniamo l’attenzione su QueryPlanType:

<xsd:complexType name="StmtSimpleType">
    <xsd:annotation>
      <xsd:documentation>The simple statement that may or may not contain query plan, UDF plan or Stored Procedure plan </xsd:documentation>
    </xsd:annotation>
    <xsd:complexContent>
      <xsd:extension base="shp:BaseStmtInfoType">
        <xsd:sequence>
          <xsd:element name="QueryPlan" type="shp:QueryPlanType" minOccurs="0" maxOccurs="1" />
          <xsd:element name="UDF" type="shp:FunctionType" minOccurs="0" maxOccurs="unbounded" />
          <xsd:element name="StoredProc" type="shp:FunctionType" minOccurs="0" maxOccurs="1" />
        </xsd:sequence>
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>

Notiamo che, come atteso, possiamo avere un solo QueryPlan per StmtSimple (opzionale).

<xsd:complexType name="QueryPlanType">
    <xsd:annotation>
      <xsd:documentation>
			New Runtime information:
			DegreeOfParallelism
			MemoryGrant (in kilobytes)
			
			New compile time information:
			mem fractions
			CachedPlanSize (in kilobytes)
			CompileTime (in milliseconds)
			CompileCPU (in milliseconds)
			CompileMemory (in kilobytes)
			Parameter values used during query compilation
			</xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="InternalInfo" type="shp:InternalInfoType" minOccurs="0" maxOccurs="1" />
      <xsd:element name="MissingIndexes" type="shp:MissingIndexesType" minOccurs="0" maxOccurs="1" />
      <xsd:element name="GuessedSelectivity" type="shp:GuessedSelectivityType" minOccurs="0" maxOccurs="1" />
      <xsd:element name="UnmatchedIndexes" type="shp:UnmatchedIndexesType" minOccurs="0" maxOccurs="1" />
      <xsd:element name="RelOp" type="shp:RelOpType" />
      <xsd:element name="ParameterList" type="shp:ColumnReferenceListType" minOccurs="0" maxOccurs="1" />
    </xsd:sequence>
    <xsd:attribute name="DegreeOfParallelism" type="xsd:int" use="optional" />
    <xsd:attribute name="MemoryGrant" type="xsd:unsignedLong" use="optional" />
    <xsd:attribute name="CachedPlanSize" type="xsd:unsignedLong" use="optional" />
    <xsd:attribute name="CompileTime" type="xsd:unsignedLong" use="optional" />
    <xsd:attribute name="CompileCPU" type="xsd:unsignedLong" use="optional" />
    <xsd:attribute name="CompileMemory" type="xsd:unsignedLong" use="optional" />
    <xsd:attribute name="UsePlan" type="xsd:boolean" use="optional" />
  </xsd:complexType>

Il QueryPlan ha tantissime informazioni, inclusa quella che stiamo cercando: il RelOpType. Lascio a voi esplorare le decine di elementi e attributi che compongono questo tipo complesso. Basti per ora sapere che un attributo e’ quello che fa al caso nostro:

<xsd:complexType name="RelOpType">
    <xsd:sequence>
      <xsd:element name="OutputList" type="shp:ColumnReferenceListType" />
      <xsd:element name="Warnings" type="shp:WarningsType" minOccurs="0" maxOccurs="1" />
      <xsd:element name="MemoryFractions" type="shp:MemoryFractionsType" minOccurs="0" maxOccurs="1" />
      <xsd:element name="RunTimeInformation" type="shp:RunTimeInformationType" minOccurs="0" maxOccurs="1" />
      <xsd:element name="RunTimePartitionSummary" type="shp:RunTimePartitionSummaryType" minOccurs="0" maxOccurs="1" />     
      <xsd:element name="InternalInfo" type="shp:InternalInfoType" minOccurs="0" maxOccurs="1" />
      <xsd:choice>
        <xsd:element name="Assert" type="shp:FilterType" />
        <xsd:element name="Bitmap" type="shp:BitmapType" />
        <xsd:element name="Collapse" type="shp:CollapseType" />
        <xsd:element name="ComputeScalar" type="shp:ComputeScalarType" />
        <xsd:element name="Concat" type="shp:ConcatType" />
        <xsd:element name="ConstantScan" type="shp:ConstantScanType" />
        <xsd:element name="CreateIndex" type="shp:CreateIndexType" />
        <xsd:element name="DeletedScan" type="shp:RowsetType" />
        <xsd:element name="Extension" type="shp:UDXType" />
        <xsd:element name="Filter" type="shp:FilterType" />
        <xsd:element name="Generic" type="shp:GenericType" />
        <xsd:element name="Hash" type="shp:HashType" />
        <xsd:element name="IndexScan" type="shp:IndexScanType" />
        <xsd:element name="InsertedScan" type="shp:RowsetType" />
        <xsd:element name="LogRowScan" type="shp:RelOpBaseType" />
        <xsd:element name="Merge" type="shp:MergeType" />
        <xsd:element name="MergeInterval" type="shp:SimpleIteratorOneChildType" />
        <xsd:element name="NestedLoops" type="shp:NestedLoopsType" />
        <xsd:element name="OnlineIndex" type="shp:CreateIndexType" />
        <xsd:element name="Parallelism" type="shp:ParallelismType" />
        <xsd:element name="ParameterTableScan" type="shp:RelOpBaseType" />
        <xsd:element name="PrintDataflow" type="shp:RelOpBaseType" />
        <xsd:element name="RemoteFetch" type="shp:RemoteFetchType" />
        <xsd:element name="RemoteModify" type="shp:RemoteModifyType" />
        <xsd:element name="RemoteQuery" type="shp:RemoteQueryType" />
        <xsd:element name="RemoteRange" type="shp:RemoteRangeType" />
        <xsd:element name="RemoteScan" type="shp:RemoteType" />
        <xsd:element name="RowCountSpool" type="shp:SpoolType" />
        <xsd:element name="ScalarInsert" type="shp:ScalarInsertType" />
        <xsd:element name="Segment" type="shp:SegmentType" />
        <xsd:element name="Sequence" type="shp:SequenceType" />
        <xsd:element name="SequenceProject" type="shp:ComputeScalarType" />
        <xsd:element name="SimpleUpdate" type="shp:SimpleUpdateType" />
        <xsd:element name="Sort" type="shp:SortType" />
        <xsd:element name="Split" type="shp:SplitType" />
        <xsd:element name="Spool" type="shp:SpoolType" />
        <xsd:element name="StreamAggregate" type="shp:StreamAggregateType" />
        <xsd:element name="Switch" type="shp:SwitchType" />
        <xsd:element name="TableScan" type="shp:TableScanType" />
        <xsd:element name="TableValuedFunction" type="shp:TableValuedFunctionType" />
        <xsd:element name="Top" type="shp:TopType" />
        <xsd:element name="TopSort" type="shp:TopSortType" />
        <xsd:element name="Update" type="shp:UpdateType" />
      </xsd:choice>
    </xsd:sequence>
    <xsd:attribute name="AvgRowSize" type="xsd:double" use="required" />
    <xsd:attribute name="EstimateCPU" type="xsd:double" use="required" />
    <xsd:attribute name="EstimateIO" type="xsd:double" use="required" />
    <xsd:attribute name="EstimateRebinds" type="xsd:double" use="required" />
    <xsd:attribute name="EstimateRewinds" type="xsd:double" use="required" />
    <xsd:attribute name="GroupExecuted" type="xsd:boolean" use="optional" />
    <xsd:attribute name="EstimateRows" type="xsd:double" use="required" />
    <xsd:attribute name="LogicalOp" type="shp:LogicalOpType" use="required" />
    <xsd:attribute name="NodeId" type="xsd:int" use="required" />
    <xsd:attribute name="Parallel" type="xsd:boolean" use="required" />
    <xsd:attribute name="Partitioned" type="xsd:boolean" use="optional" />
    <xsd:attribute name="PhysicalOp" type="shp:PhysicalOpType" use="required" />
    <xsd:attribute name="EstimatedTotalSubtreeCost" type="xsd:double" use="required" />
    <xsd:attribute name="TableCardinality" type="xsd:double" use="optional" />
  </xsd:complexType>

Questo tipo e’ un enumerato (o meglio, una xsd:string ristretta) che puo’ avere uno di questi valori:

    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="Aggregate" />
      <xsd:enumeration value="Assert" />
      <xsd:enumeration value="Async Concat" />
      <xsd:enumeration value="Bitmap Create" />
      <xsd:enumeration value="Clustered Index Scan" />
      <xsd:enumeration value="Clustered Index Seek" />
      <xsd:enumeration value="Clustered Update" />
      <xsd:enumeration value="Collapse" />
      <xsd:enumeration value="Compute Scalar" />
      <xsd:enumeration value="Concatenation" />
      <xsd:enumeration value="Constant Scan" />
      <xsd:enumeration value="Cross Join" />
      <xsd:enumeration value="Delete" />
      <xsd:enumeration value="Deleted Scan" />
      <xsd:enumeration value="Distinct Sort" />
      <xsd:enumeration value="Distinct" />
      <xsd:enumeration value="Distribute Streams" />
      <xsd:enumeration value="Eager Spool" />
      <xsd:enumeration value="Filter" />
      <xsd:enumeration value="Flow Distinct" />
      <xsd:enumeration value="Full Outer Join" />
      <xsd:enumeration value="Gather Streams" />
      <xsd:enumeration value="Generic" />
      <xsd:enumeration value="Index Scan" />
      <xsd:enumeration value="Index Seek" />
      <xsd:enumeration value="Inner Join" />
      <xsd:enumeration value="Insert" />
      <xsd:enumeration value="Inserted Scan" />
      <xsd:enumeration value="Lazy Spool" />
      <xsd:enumeration value="Left Anti Semi Join" />
      <xsd:enumeration value="Left Outer Join" />
      <xsd:enumeration value="Left Semi Join" />
      <xsd:enumeration value="Log Row Scan" />
      <xsd:enumeration value="Merge Interval" />
      <xsd:enumeration value="Parameter Table Scan" />
      <xsd:enumeration value="Partial Aggregate" />
      <xsd:enumeration value="Print" />
      <xsd:enumeration value="Remote Delete" />
      <xsd:enumeration value="Remote Index Scan" />
      <xsd:enumeration value="Remote Index Seek" />
      <xsd:enumeration value="Remote Insert" />
      <xsd:enumeration value="Remote Query" />
      <xsd:enumeration value="Remote Scan" />
      <xsd:enumeration value="Remote Update" />
      <xsd:enumeration value="Repartition Streams" />
      <xsd:enumeration value="RID Lookup" />
      <xsd:enumeration value="Right Anti Semi Join" />
      <xsd:enumeration value="Right Outer Join" />
      <xsd:enumeration value="Right Semi Join" />
      <xsd:enumeration value="Segment" />
      <xsd:enumeration value="Sequence" />
      <xsd:enumeration value="Sort" />
      <xsd:enumeration value="Split" />
      <xsd:enumeration value="Switch" />
      <xsd:enumeration value="Table-valued function" />
      <xsd:enumeration value="Table Scan" />
      <xsd:enumeration value="Top" />
      <xsd:enumeration value="TopN Sort" />
      <xsd:enumeration value="UDX" />
      <xsd:enumeration value="Union" />
      <xsd:enumeration value="Update" />
      <xsd:enumeration value="Merge" />
    </xsd:restriction>
  </xsd:simpleType>

Ora, tornando alla nostra query, esaminiamo l’XML del piano d’esecuzione (menu contestuale -> Show execution plan XML...) qui semplificato per brevita’:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2769.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="100000" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.543652" StatementText="SELECT * FROM charge;&#xD;&#xA;" StatementType="SELECT" QueryHash="0x6AFC07EFC55B8B3D" QueryPlanHash="0x41C1392D57C141DB">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
            <RelOp AvgRowSize="45" EstimateCPU="0.110079" EstimateIO="0.433574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100000" LogicalOp="Table Scan" NodeId="0" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.543652" TableCardinality="100000" />             
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Ora che sappiamo dove trovare l’informazione che ci serve possiamo – ad esempio – interrogare la DMV dei piani di esecuzione in cache per identificare le query cachate che hanno eseguito table scans:

WITH 
    XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
    CTE AS
    (
        SELECT 
        P.plan_handle, P.cacheobjtype
        FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) QP
        CROSS APPLY QP.query_plan.nodes('/ShowPlanXML/.//RelOp[@LogicalOp=''Table Scan'']') AS rp(stmt)
        GROUP BY P.plan_handle, P.cacheobjtype
    )
    SELECT A.plan_handle, A.cacheobjtype, EQP.query_plan FROM CTE A
    CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) EQP

La stessa logica si puo’ adottare per identificare, ad esempio, l’utilizzo di un particolare indice o il grado di parallelismo.

Happy coding,

Francesco Cogno

La fiducia delle constraints e come questo puo’ impattare i vostri piani d’esecuzione

Italian Premier Center for SQL Server — Thu, 03 Nov 2011 11:16:00 GMT

Tutti noi sappiamo che i constraint sono un ottimo modo per assicurarci l’aderenza del nostro schema alle business rules. Quello che e’ meno noto e’ che SQL Server, dietro le quinte, utilizza i nostri constraints durante la scelta del piano d’esecuzione ottimale.

Per vederlo all’opera creiamo tre tabelle con i relativi constaints:

CREATE DATABASE Demo;
GO
USE Demo;
GO

--2000
CREATE TABLE Demo2000(id INT IDENTITY(1,1) PRIMARY KEY, txt NVARCHAR(255), dt DATETIME)
GO
ALTER TABLE [dbo].[Demo2000]  WITH CHECK 
ADD CONSTRAINT [CK_Demo2000_dt] CHECK (([dt]>='2000-01-01' AND [dt]<'2001-01-01'))
GO

--2001
CREATE TABLE Demo2001(id INT IDENTITY(1,1) PRIMARY KEY, txt NVARCHAR(255), dt DATETIME)
GO 
ALTER TABLE [dbo].[Demo2001]  WITH CHECK 
ADD  CONSTRAINT [CK_Demo2001_dt] CHECK  (([dt]>='2001-01-01' AND [dt]<'2002-01-01'))
GO

--2002
CREATE TABLE Demo2002(id INT IDENTITY(1,1) PRIMARY KEY, txt NVARCHAR(255), dt DATETIME)
GO 
ALTER TABLE [dbo].[Demo2002]  WITH CHECK 
ADD  CONSTRAINT [CK_Demo2002_dt] CHECK  (([dt]>='2002-01-01' AND [dt]<'2003-01-01'))
GO

Ciascuna tabella rappresenta un anno di record. Inseriamo alcuni valori nelle tabelle (ovviamente tenendo conto dei constraints):

INSERT INTO Demo2000(txt, dt) VALUES('a', '2000-03-30');
GO 100

INSERT INTO Demo2000(txt, dt) VALUES('b', '2000-05-10');
GO 100

INSERT INTO Demo2001(txt, dt) VALUES('a', '2001-03-30');
GO 100

INSERT INTO Demo2002(txt, dt) VALUES('a', '2002-03-30');
GO 100

Ora supponiamo che per comodita’ venga creata questa vista riepilogativa:

CREATE VIEW DemoView
AS

SELECT id, txt, dt FROM Demo2000

UNION ALL
SELECT id, txt, dt FROM Demo2001
UNION ALL
SELECT id, txt, dt FROM Demo2002

Le query di interrogazione utilizzeranno i constraints creati? Per controllarlo basta effettuare una semplice query guardando il piano d’esecuzione:

SELECT * FROM DemoView WHERE dt = '2001-03-30'

Come si vede, SQL Server sa che il risultato della query sara’ soddisfatto solo dalla tabella Demo2001 e quindi non effettua scansione delle altre due rimanenti (Demo2000 e Demo2002). Questa operazione riduce il carico di IO necessario per soddisfare la query. Quindi SQL Server utilizza attivamente le nostre constraints per ottimizzare il piano d’esecuzione.

Ora, pero’, supponiamo che per risparmiare spazio sul DB decidiamo di svuotare la tabella del 2000. Prima di farlo esportiamo i dati con BCP in modo da portere, in seguito, reimportare i dati in caso diventasse necessario. La sintassi e’ la seguente:

bcp Demo.dbo.Demo2000 out Demo2000.dat -S <your instance> -T –N

Ove al posto di <your instance> dovete ovviamente mettere la vostra instanza. Ora svuotare la tabella del 2000 e’ semplice e veloce:

TRUNCATE TABLE Demo2000

Ma se ora vi servissero di nuovo i dati del 2000? Semplice, basta reimportare il file esportato precedentemente:

bcp Demo.dbo.Demo2000 in Demo2000.dat -S <your instance> -T –N

Eseguendo la query:

SELECT * FROM DemoView WHERE dt = '2000-03-30'

Otteniamo il risultato atteso:

I dati sono stati reimportati correttamente.

Se pero’ eseguiamo la query del 2001:

SELECT * FROM DemoView WHERE dt = '2001-03-30'

Il risultato e’ corretto. Notiamo pero' che il piano d’esecuzione e’ cambiato:

Perche’ ora SQL accede sia alla tabella del 2001 che a quella del 2000? Non ci sono le constraints? La risposta e’ si, ma non sono piu’ trusted (fidate). Il tool BCP, per efficienza, importa i dati disattivando di default il controllo delle constraints. Cio’ vuol dire che – potenzialmente – e’ possibile importare dati che violino la constraint (cioe’, ad esempio, inserire record del 2001 nella tabella 2000). Dato che la constraint e’ stata potenzialmente violata, SQL non si fida piu’ e crea un piano d’esecuzione che riflette questa sua sfiducia. Cio’ e’ deleterio per le performance (pensate se la tabella del 2000 avesse miloni di record!). Come si fa a sapere quindi se una constraint e’ fidata (trusted)?

Con questa query:

SELECT is_not_trusted, * FROM sys.check_constraints WHERE name='CK_Demo2000_dt'

Il risultato, come ci aspettiamo, e’ il seguente:

Come facciamo a riguadagnare fiducia in una constraint? Bisogna dire a SQL Server di analizzare tutti i record della tabella e di assicurarsi che siano conformi alla constraint. Nel nostro caso cio’ equivale a:

ALTER TABLE Demo2000 WITH CHECK CHECK CONSTRAINT [CK_Demo2000_dt]

Che riporta la situazione in questo modo:

Questo da la garanzia necessaria a SQL Server per ottimizzare il piano d’esecuzione della nostra query originaria:

SELECT * FROM DemoView WHERE dt = '2001-03-30'

Morale: controllate periodicamente che le vostre constraints siano trusted. Ci sono tante operazioni che possono rendere le constraints insicure e l’effetto lo avete visto: i piani d’esecuzione sono peggiori.

Happy coding,

Francesco Cogno

Cumulative Update Review: SQL 2008 SP3 CU1

Italian Premier Center for SQL Server — Mon, 31 Oct 2011 14:03:23 GMT

Come spesso accade per la prima Cumulative Update (CU) dopo ogni Service Pack (SP), il tempo necessario al rilascio è molto breve, una o due settimane, per due motivi:

Dato che una certa SP, per motivi temporali e di
test, non riesce ad includere tutte ultime CU relative alla SP precedente, la
CU1 ha sempre un certo carattere di urgenza per permettere ai clienti di
applicare la nuova SP anche se hanno
installato una CU non coperta da codesta SP;

Nel momento stesso in cui la lista delle hotfix
da includere in una certa SP viene chiusa e si procede con i test finali,
parallelamente si “apre” il progetto di lavorazione per la prima CU successiva;

Un esempio concreto:

Il Cliente ha installato in produzione SQL Server 2008 SP2 con CU5 o CU6;
Come da mio post precedente, la SP3 include solamente CU1, CU2, CU3 e CU4;
In questa situazione non è consigliabile installare la SP3 altrimenti l’effetto immediato sarà quello di portare indietro il livello della versione di SQL Server a SP2 + CU4, ritornando quindi vulnerabili ai bug corretti in CU5 e CU6;

ATTENZIONE: Oltre al problema ora enunciato, ce ne potrebbero essere anche altri dovuti a questa sorta di “downgrade”; è quindi caldamente consigliato installare la CU1 per SQL Server 2008 SP3 se avete inavvertitamente installato la SP3 “sopra” la SP2 con CU5 o CU6.

Fermo restando quello appena detto, è comunque ammissibile/consigliabile installare la SP3 sopra una istanza SQL con SP2 e CU5 o CU6 a patto di installare immediatamente dopo la CU1 per SP3.

Ok, smarcata questa importante considerazione su cosa e quando installare, passiamo al pratico.

L’articolo di riferimento per la CU1 per SQL Server 2008 SP3 è il seguente:

Cumulative update package 1 for SQL Server 2008 Service Pack 3

http://support.microsoft.com/kb/2617146/en-us

NOTA: Essendo questo pacchetto la prima CU relativa alla SP3, un nuovo articolo di Knowledge Base è stato creato per tenere traccia di tutte quelle future relative alla stessa SP:

SQL Server 2008 builds that were released after SQL Server 2008 SP3 was released

http://support.microsoft.com/kb/2629969/en-us

Selezionando l’apposito link per scaricare l’aggiornamento, si può vedere che stavolta c’è un solo pacchetto da scaricare per piattaforma (x86, x64, IA64):

Vi ricordo che ogni pacchetto include gli aggiornamenti per tutte le lingue supportate, molto comodo anche se ciò comporta un sensibile aumento di dimensione totale.

Dopo il download del pacchetto da 177MB (versione x64), scompattiamo il contenuto in una directory temporanea giusto per dare un’occhiata alla struttura, per ottenere questo risultato lanciare il pacchetto da riga di comando con la solita opzione “/x” (senza virgolette); fatto questo,
dovete ancora lanciare l’eseguibile appena estratto, nuovamente con l’opzione /x”, ammetto che la cosa è fastidiosa :-( , in ogni modo, vi dico subito che non c’è nulla di eclatante, per questa volta le cose sono estremamente chiare.

Nel caso vogliate “esplorare” i possibili parametri con cui lanciare a riga di comando il setup, dovete aggiungere l’opzione “/?” come al solito, la spiegazione di quello che vedrete la potete ripescare da uno dei miei post precedenti sulla review delle CU, niente di nuovo.

Per quanto riguarda le hotfix importanti o critiche che Vi consiglio di verificare con attenzione, Vi segnalo le seguenti:

2530921: FIX: Incorrect results when you run an INSERT SELECT UNION ALL statement in SQL Server 2008 or in SQL Server 2008 R2

http://support.microsoft.com/kb/2530921

2542225: FIX: Assertion failure or other issues occur when you run a DML query against a table or view that has two indexes in SQL Server 2008 or in SQL Server 2008 R2

http://support.microsoft.com/kb/2542225

2574699: FIX: Database data files might be incorrectly marked as sparse in SQL Server 2008 R2 or in SQL Server 2008 even when the physical files are marked as not sparse in the file system

http://support.microsoft.com/kb/2574699

Se poi siete interessati agli scenari di replica, allora è consigliabile rivedere attentamente la fixlist di questa CU, ci sono 3 problemi di una certà gravità fissati.

Buon lavoro a tutti !

--Igor Pagliai--

Annunciato il nome ufficiale della prossima versione di SQL Server

Italian Premier Center for SQL Server — Thu, 13 Oct 2011 07:36:00 GMT

Salve a tutti,

Brevissimo post solo per annunciarVi che finalmente è stato reso noto il nome ufficiale per la prossima versione di SQL Server, nome in codice "Denali": come probabilmente molti di Voi avranno immaginato il nome ufficiale sarà "SQL Server 2012", la notizia è stata appena data alla PASS Conference (http://www.sqlpass.org/summit/2011) in corso a Seattle ! Sfortunatamente la data di rilascio è ancora un pò vaga, al momento le notizie ufficiali indicano "first half of 2012".

Moltissime le novità in arrivo, sicuramente sarà un argomento molto caldo anche per il nostro blog.

-- Igor Pagliai --

Tutto quello che dovete sapere sulla Service Pack 3 per SQL Server 2008

Italian Premier Center for SQL Server — Wed, 12 Oct 2011 08:58:00 GMT

Buongiorno a tutti....

Con buon anticipo rispetto alla normale frequenza di rilascio delle “Service Pack” (SP) di SQL Server 2008, lo scorso 6 Ottobre è stata rilasciata la "Service Pack 3" (SP3); come ben sapete questo blog non è uno dei tanti (troppi !) che segnalano la semplice uscita di qualche aggiornamento, quindi fatemi “incollare” i links alle componenti principali della documentazione e poi vediamo di aggiungere alcune informazioni utili:

Main Download Page

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27594

List of issues that are fixed by SQL Server 2008 Service Pack 3

http://support.microsoft.com/kb/2546951/en-us

Microsoft SQL Server 2008 SP3 Release Notes

http://social.technet.microsoft.com/wiki/contents/articles/microsoft-sql-server-2008-sp3-release-notes.aspx

Readme

http://go.microsoft.com/fwlink/?LinkId=229521

NOTA: La build di riferimento per SQL Server 2008 una volta installata la "Service Pack 3" (SP3) è la “10.00.5500.00”.

Subito tre considerazioni importanti sulla SP3:

Con l'uscita della SP3, la "Service Pack 1" (SP1) di SQL Server 2008 è ufficialmente fuori supporto, come da "Lifecycle Policy" standard Microsoft:

Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first.
For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport

Questa SP contiene tutti gli aggiornamenti (ovviamente) della SP2 più le Cumulative Update (CU) dalla 1 alla 4 comprese, quindi se avete la CU5 o CU6 (ad oggi queste sono quelle disponibili), dovete aspettare il futuro rilascio della CU1 per SQL Server 2008 SP3.

The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released

http://support.microsoft.com/kb/2402659/en-us

Ovviamente contiene anche l’unico bug (quasi :-),vedi post precedente sull’argomento in questo stesso blog) di sicurezza finora rilasciato per SQL Server 2008 (MS11-049):

http://www.microsoft.com/technet/security/bulletin/MS11-049.mspx

Dunque, a questo punto vorrei rimarcare una cosa che troppe volte viene trascurata se non proprio dimenticata: leggetevi sempre I documenti di “Readme” e “Release Notes” che accompagnano ogni rilascio perché è qui che trovate preziose informazioni del tipo: pre-requisiti, problemi di installazione, compatibilità, nuove funzionalità, istruzioni “speciali” per installazioni cluster, mirror, replica e quant’altro; tre fulgidi esempi da questi file di documentazione:

Problema noto di installazione:

If you apply a SQL Server cumulative update to an instance of SQL Server where the features are not all at the same version level, the update will fail. To avoid this failure, you must ensure that all the features in an instance of SQL Server are at the same version of SQL Server.

Falso problema di installazione:

You might see SQL Server 2008 R2 features included in the list of features to be updated by the SQL Server 2008 service pack on the Select Feature or Ready to Update page. SQL Server 2008 R2 components are not affected by the SQL Server 2008 service pack update. No action is required.

You might see SQL Server 2008 R2 components in the list of features on the Select Feature or Ready to Uninstall page when removing a SQL Server 2008 service pack update. SQL Server 2008 R2 features are not uninstalled during this process. No action is required.

Nuove funzionalità:

Enhanced upgrade experience from previous versions of SQL Server to SQL Server 2008 SP3;
Increased the performance & reliability of the setup experience;
In SQL Server Integration Services logs will now show the total number of rows sent in Data Flows;
Enhanced warning messages when creating the maintenance plan if the Shrink Database option is enabled;
Resolving database issue with transparent data encryption enabled and making it available even if certificate is dropped;
Optimized query outcomes when indexed Spatial Data Type column is referenced by DTA (Database Tuning Advisor);
Superior user experience with Sequence Functions (e.g Row_Numbers()) in a Parallel execution plan;

Altro aspetto importante: cosa non contiene questa SP3 ? Come al solito, nei pacchetti di aggiornamento di una SP, non vengono incluse le seguenti componenti per cui è necessario un download separato:

Books Online: (link non ancora disponibile)
Feature Pack: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27596

Come per le altre precedenti SP, valgono le seguenti regole per la SP3 di SQL Server 2008:

Prima e più importante: prima di installare la SP3 su una istanza SQL che ospita database relativi ad applicazioni, è necessario avere la conferma della supportabilità/compatibilità dal fornitore dell'applicazione stessa, ove possibile; ad esempio, prima di installare la SP3 su una istanza SQL che ospita uno o più database di Sharepoint è necessario avere la conferma da Microsoft che la cosa è supportata (= testata !);
Può essere disinstallata;
Supporta l’installazione in modalità “Slipstream”;
In installazioni Cluster, al contrario di SQL Server 2005 (e precedenti), si parte con l’aggiornamento dei nodi passivi; se per caso lanciate il setup su un nodo dove è attiva l’istanza che volete aggiornare un messaggio di “Warning” Vi avvertirà della cosa e Vi chiederà conferma prima di
procedere, ovviamente ciò comporterà un disservizio;

IMPORTANTE: Le SP di SQL Server 2008 (e successivi) contengono una logica per cui quando installate la SP sul [n/2]+1 – esimo nodo, cioè passata la maggioranza dei nodi, il setup eseguirà automaticamente il failover dell’istanza interessata su uno dei nodi già aggiornati. Se non volete che questo accada e volete essere Voi a decidere quando spostare l’istanza, dovete lanciare il setup a riga di comando con lo switch “CLUSTERPASSIVE” come potete verificare lanciando l’eseguibile con l’opzione “/?”:

Usage:

setup.exe /[option]={value} /[option]={value}
...

Options:

ACTION Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

ALLINSTANCES Specifies that all instances are to be included in the Setup operation. This parameter is only supported when applying a patch.

CLUSTERPASSIVE Specifies that SQL Server Setup should not manage the SQL Server services. This option should only be used in a non-Microsoft cluster environment.

HELP Displays the command line parameters usage

INDICATEPROGRESS Specifies that the detailed Setup log should be piped to the console.

INSTANCENAME Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions.

QUIET Setup will not display any user interface.

QUIETSIMPLE Setup will display progress only without any user interaction.

X86 Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.

5.In caso di configurazione in “Mirroring”, c’è un ordine ben preciso che, in generale, prevede l’applicazione prima sul “Witness” (se presente), quindi sul “Mirror”, infine sul “Principal”, i dettagli li trovate nel seguente articolo di KB valido anche per SQL 2008:

How to install service packs and hotfixes on an instance of SQL Server 2005 that is configured to use database mirroring

http://support.microsoft.com/kb/926824/en-us

6. Anche in caso di configurazione in “Replication”, c’è un ordine ben preciso, in generale si parte dal “Distributor”, quindi il “Publisher”, infine i “Subscriber”, i dettagli li trovate nel seguente articolo di KB valido anche per SQL 2008:

How to apply a hotfix for SQL Server 2005 in a replication topology

http://support.microsoft.com/kb/941232/en-us

7. Se poi avete una configurazione estremamente complessa che comprende sia “Mirroring” che “Replication”, è necessario adottare estrema cura e seguire le indicazioni del seguente articolo:

How to apply a hotfix for SQL Server 2005 or SQL Server 2008 in a transactional replication and database mirroring topology

http://support.microsoft.com/kb/977051/en-us

8. Un singolo “run” del Setup, oltre ad aggiornare le “Shared Components”, offre la possibilità di aggiornare più istanze SQL Server 2008, se presenti sulla macchina;

9. Le componenti “Shared Components” (Native Client, MMC, tools vari) verranno obbligatoriamente aggiornate al primo lancio del setup;

10. Con i path di default per l'installazione di SQL Server 2008, il file di log dell'installazione è localizzato nel seguente folder, è necessario averlo in caso di problemi relativi ad installazioni fallite ed è consigliabile verificare l'avvenuta installazione con successo:

C:\%Program Files%\Microsoft SQL Server\100\Setup Bootstrap\LOG\

Overall summary:

Final result: Passed

Exit code (Decimal): 0

Exit message: Passed

Start time: 2011-10-12 11:35:26

End time: 2011-10-12 11:41:47

Requested action: Patch

[……]

Instance <<instance name>> overall summary:

Final result: Passed

Exit code (Decimal): 0

Exit message: Passed

Start time: 2011-10-12 11:39:20

End time: 2011-10-12 11:41:44

Requested action: Patch

E anche per questa volta è tutto, buon lavoro.

--Igor Pagliai--

Attenzione a modificare la struttura di una tabella con il Table Designer del Management Studio di SQL

Italian Premier Center for SQL Server — Fri, 30 Sep 2011 06:22:00 GMT

Lo confesso, anche io non presto molta attenzione alle “Options” del SQL Management Studio (SSMS), principalmente perché uso sempre gli script TSQL per modificare (e non solo) la struttura di una tabella, ma la cosa può essere potenzialmente pericolosa per cui ho ritenuto interessante creare questo nuovo post per il nostro blog.

Dunque, lo scenario è quello relativo ad una eventuale modifica della struttura di una tabella, magari molto grossa in termini di spazio occupato e numero di righe contenute, per la quale il DBA sceglie di usare l’interfaccia grafica del “SQL Server Management Studio (SSMS)”, nello specifico il “Table Designer”. Quest è una semplice tabella a 2 campi con 1000 record banali inseriti:

Ora proviamo ad aggiungere una colonna altrettanto banale (“valore2” di tipo INT “nullable) alla suddetta tabella:

Ok, fin qui tutto bene, la colonna è stata aggiunta istantaneamente ed i valori per tutte le righe sono state impostati ovviamente a “NULL”:

Proviamo ora una cosa diversa, cioè la rimozione della stessa colonna, ancora tutto bene, nessun problema e prendendo una trace con il Profiler, la console di SQL tra le altre cose esegue il comando che ci si aspetta:

ALTER TABLE dbo.T1 DROP COLUMN valore2

Proviamo ora a rimuovere l’attributo “Allow Nulls” dalla colonna “valore2”, sempre dal “Table Designer”, non appena clicchiamo per salvare la modifica ecco quello che compare sullo schermo:

Perché appare questo messaggio ? Perché il “Table Designer” vuole distruggere questa tabella e ricrearla ?

La risposta alla prima domanda è nelle “Options” del tool, così appaiono di default (almeno su SQL 2008 R2):

La risposta alla seconda domanda è che l’interfaccia grafica talvolta non “traduce” la Vostra modifica in rapporto 1:1 con i comandi TSQL che normalmente un DBA utilizzerebbe; per verificare questo disabilitiamo l’opzione cerchiata in rosso e ripetiamo l’operazione, nel frattempo avviamo una semplice trace con il SQL Profiler per vedere cosa accade in background:

Ovviamente nessun messaggio di errore questa volta, ma andando a vedere cosa c’è nella trace del SQL Profiler abbiamo diverse cose molto interessanti:

C’è tutta una lunga serie di query di controllo che l’interfaccia esegue sul design della tabella, gli attributi, le statistiche, le permission, etc. ; fin qui nulla di strano;
Alla fine delle query precedenti, ecco la prima cosa interessante (evento SQL “SQL:stmtCompleted”):

A questo punto ho un sospetto terribile, proseguiamo nell’analisi degli eventi ed ecco cosa troviamo, un travaso brutale di dati dalla tabella originaria (T1) a quella temporanea appena creata (Tmp_T1) e per di più con un lock esclusivo a livello di tabella (TABLOCKX) per tutta la durata dell’operazione di lettura dei dati (HOLDLOCK):

Avrete già capito quello che succede, in ogni modo proseguiamo nell’analisi della trace e troviamo:

A questo punto ricapitoliamo cosa è successo a fronte della modifica alla struttura della tabella:

Il “Table Designer”, non il database engine di SQL, ha creato una tabella di appoggio (non temporanea !) con la stessa struttura della tabella originaria;
Bloccando la tabella “T1) in maniera esclusiva e continuata, ha copiato i dati sulla tabella di appoggio appena creata;
E’ stata “droppata” la tabella originaria;
E’ stata rinominata la tabella di appoggio usando lo stesso nome della tabella originaria;

Se non avete colto la pericolosità di questa cosa permettetemi di aiutarVi, immaginate che la tabella “T1” abbia qualche decina o centinaia di milioni di record o che comunque sia di dimensioni ragguardevoli:

La tabella “T1” viene bloccata in maniera esclusiva e nessun client/applicazione può acceddere: per quanto tempo non è dato saperlo, ma se la tabella è grossa e l’attività elevata la cosa si tradurrà in un blocco esteso e non desiderato;
Il caricamento dei dati nella tabella temporanea comporterà una grossa (enorme ?) crescita del transaction log del database utente: faccio notare che tale tabella non è temporanea, quindi non userà spazio nel TEMPDB, bensì nel database utente, con tutti i problemi del caso;

Un’ultima considerazione: cosa sarebbe accaduto nel caso questa tabella fosse stata riferita da una o più “Foreign Key Contraint” ? L’operazione di “drop” della tabella originaria sarebbe andata in errore oppure il Table Designer avrebbe eseguito ulteriori attività in background ? Bè, per questo magari dovrete aspettare un ulteriore post su questo blog ! :-)

A questo punto, per i DBA non avvezzi all’uso estensivo del TSQL per la modifica della struttura delle tabelle, una domanda sorge spontanea: c’è un modo più efficiente per fare questa operazione ? Ovviamente sì, ed è anche molto semplice:

alter table T1 alter column valore char(8000) not null;

Avendo abilitato le statistiche sull’I/O , l’esecuzione (con successo) del suddetto comando, ecco le informazioni:

Table 'T1'. Scan count 1, logical reads 1000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Liscio e lineare, vero ?

La lezione di oggi dal titolo: “In produzione, mai modificare la struttura di una tabella con il Table Designer senza aver prima verificato cosa realmente accade in background, specialmente se la tabella è molto grande” è finita, buon lavoro a tutti ed arrivederci al prossimo post.

--Igor Pagliai--

Cumulative Update Review: SQL 2008 SP2 CU6

Italian Premier Center for SQL Server — Fri, 23 Sep 2011 06:34:53 GMT

Ecco l’ultima Cumulative Update (CU) per SQL Server 2008 con SP2:

Cumulative update package 6 for SQL
Server 2008 Service Pack 2

http://support.microsoft.com/kb/2582285/en-us

Come al solito, la lista delle (CU) rilasciate per SQL Server 2008 dal rilascio della SP2, è disponibile al seguente link:

The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released

http://support.microsoft.com/kb/2402659/en-us

La prima cosa che salta all’occhio è la vicinanza tra il build number di questa CU (4321) e di quella precedente (4316 - CU(5)), davvero impressionante, segno che il team di sviluppo di SQL Server ha raggiunto, e non da ora, un livello qualitativo notevole.

Ecco cosa di interessante contiene:

Senza scendere nel dettaglio dei singoli problemi, se state usando la “Replica” o “Analysis Services”, sappiate che ci sono ben 5 problemi la cui soluzione Vi potrebbe interessare di cui 2 importanti (“crash”) di Analysis Services;
Di queste, 2 hotfix “interessanti” per quanto riguarda la replica di tipo “Snapshot” (“high memory usage”) e la replica di tipo “Merge” (“Non-convergence or data loss”), quest’ultima sicuramente più grave;
Come ho già scritto pochi giorni fa per l’ultima (CU) di SQL Server 2008 R2 SP1, anche qui è presente un “porting” per la soluzione di un problema che ha iniziato a manifestarsi più di un anno fa e che, data la sua subdula natura ed i suoi intreggi con l’NTFS di Windows, è da
valutare con estrema attenzione:

Database data files might be incorrectly marked as sparse in SQL Server 2008 R2 or in SQL Server 2008 even when the physical files are marked as not sparse in the file system

http://support.microsoft.com/kb/2574699/en-us

Per maggiori dettagli Vi consiglio di andare a rileggerVi il precedente “post”:

http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/09/13/cumulative-update-review-sql-2008-r2-sp1-cu2.aspx

Come sapete pongo molta attenzione a quei “bug” che potrebbero, in talune specifiche situazioni, influire sul risultati generate dalle query, li considero molto importanti e pericolosi al pari dei problemi di stabilità (“crash” o “hang”) dell’engine di SQL, ebbene ce ne sono due da considerare attentamente, ognuno nelle due citate categorie:

Assertion failure or other issues occur when you run a DML query against a table or a view that has two indexes in SQL Server 2008

http://support.microsoft.com/kb/2542225/en-us

You receive an incorrect result when you run a query that uses the row_number function in SQL Server 2008

http://support.microsoft.com/kb/970198/en-us

Rileggendo la lista che Vi ho fatto, direi che si tratta di una CU di media importanza, se non siete interessati alla parte di “Analysis Services” e/o “Replica”.

Anche per questa volta è tutto, buon lavoro a tutti.

--Igor Pagliai--

SQL Alert: Problema “Non-Yielding Scheduler”

Italian Premier Center for SQL Server — Fri, 16 Sep 2011 12:49:00 GMT

IMPORTANTE: Questo post è il primo di questo tipo, lo scopo è allertare i Clienti e tutti gli interessati su problemi non solo di una certa gravità ma anche di ampia diffusione.

Nelle due ultime settimane ho riscontrato il seguente problema da ben quattro clienti su installazioni SQL Server 2008 e SQL Server 2008 R2:

"Non-yielding Scheduler" error and SQL Server 2008 or SQL
Server 2008 R2 stops responding intermittently in Windows Server 2008 or in
Windows Server 2008 R2

http://support.microsoft.com/kb/2491214/en-us

Impatto: processo SQL Server non responsivo;

Condizioni di applicabilità: Utilizzo del SQL Native Client 10.0 da parte dell’applicazione;

Evidenza del problema: messaggi nell’ERRORLOG;

Workaround: SI, vedi articolo;

Fix:

Cumulative update package 2 for SQL Server 2008 Service Pack 2
Cumulative update package 6 for SQL Server 2008 R2

Per i dettagli, ovviamente leggete con attenzione l’articolo, in caso di dubbi, problemi o complicazioni contattate il Vostro Technical Account Manager (TAM).

Buon lavoro a tutti.

--Igor Pagliai--

Cumulative Update Review: SQL 2008 R2 SP1 CU2

Italian Premier Center for SQL Server — Tue, 13 Sep 2011 10:44:40 GMT

Speravo di fare un unico post ed includere anche la successiva CU per SQL 2008, purtroppo sta tardando e quindi procediamo con questo unico aggiornamento:

Cumulative update package 2 for SQL Server 2008
R2 Service Pack 1

http://support.microsoft.com/kb/2567714/en-us

Come al solito, la lista delle (CU) rilasciate per SQL Server 2008 R2 dal rilascio della SP1, è disponibile al seguente link:

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released

http://support.microsoft.com/kb/2567616/en-us

Ecco cosa di interessante contiene:

- Senza scendere nel dettaglio dei singoli problemi, se state usando la “Replica”, allora questa (CU2) è un “must” per Voi, ci sono ben 6 problemi risolti, alcuni di una certa gravità;

- Miglioramento dei tempi di “recovery” di un database, specialmente se avete un numero non banale di “Virtual Log Files”:

Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment

http://support.microsoft.com/kb/2524743/en-us

- Soluzione ad un problema di generazione di risultati non corretta in caso di query che contiene una “INSERT .. SELECT” abbinata ad una “UNION ALL”:

Incorrect results when you run an INSERT SELECT UNION ALL statement in SQL Server 2008 or in SQL Server 2008 R2

http://support.microsoft.com/kb/2530921/en-us

- Il pezzo forte di questa collezione è invece constituito dalla fix per il seguente annoso problema:

Database data files might be incorrectly marked as sparse in SQL Server 2008 R2 even when the physical files are marked as not sparse in the file system

http://support.microsoft.com/kb/2574699/en-us

Ho detto annoso perché non si tratta di un problema di oggi, ma di ben 3 anni fa, come da “alert” uscito a suo tempo e per cui molti Clienti sono stati direttamente avverti dai loro Technical Account Manager (TAM):

SQL Server reports operating system error 1450 or 1452 or 665 (retries)

http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx

I punti di interesse di questo problema erano (e sono) due:

1) Non si tratta di un problema causato da SQL Server bensì dall’NTFS di Windows in una concomitanza di diversi fattori (sparse files, snapshot, backup,etc.):

…This issue occurs because the file system incorrectly marks a whole database data file as sparse after an alternative stream that was originally marked as sparse is removed…

2) La procedura per venire fuori da questo problema non era (a suo tempo) banale, e comunque Vi sareste trovati con un database “offline” senza possibilità di montarlo:

To resolve this issue in SQL Server 2008 R2, apply the following hotfix, and then bring the database online. When database is brought online, SQL Server checks whether the metadata of the physical database file is marked as sparse in the file system. If it is marked as not sparse, the SQL Server metadata is updated.

Con questa hotfix, invece, nel caso Vi imbattiate nel problema, è sufficiente installarla e quindi far ripartire l’istanza SQL Server; alcune considerazioni aggiuntive sono contenute all’interno dell’articolo, da leggere in caso Vi imbattiate effettivamente in questa casistica.

Anche per questa volta è tutto, buon lavoro a tutti.

--Igor Pagliai--

Tutto quello che avreste dovuto sapere sul TEMPDB e che non avete mai osato chiedere

Italian Premier Center for SQL Server — Mon, 12 Sep 2011 05:57:00 GMT

Salve, ero indeciso sul fatto di creare un “post” sull’argomento, dato che si tratta di argomento trattato e ritrattato fino alla noia, ma all’ennesima domanda di un cliente che chiedeva chiarimenti sul numero di file da fare (vi dice niente ? :-) ) ho deciso di mettermi a scrivere questa pagina, con la promessa, però, di raccogliere nella maniera più sintetica possibile tutto quello che riguarda eventuali ottimizzazioni di performance e cercando di fugare la ridda di informazioni parziali, non corrette e vecchie che si trovano in giro su Internet, e talvolta anche nella documentazione Microsoft.
Ma perché il TEMPDB è così importante ? Principalmente per due ordini di ragioni:

E’ una risorsa al pari delle CPU, della RAM, dei dischi e della rete: ne esiste solo uno per istanza, a prescindere da quanti database utente avete, quindi un eventuale collo di bottiglia su questa importante risorsa può influenzare tutti i database e le relative applicazioni;
Molte “features” di SQL Server utilizzano il TEMPDB, direttamente o indirettamente, oltre ovviamente ad eventuali oggetti temporanei costruiti ed utilizzati dal codice applicativo; tra le “features” più voraci di spazio in TEMPDB e/o più importanti abbiamo:

Versioning (utilizzo degli “Snapshot Isolation Levels”);
Online index operations (create, alter, rebuild);
DBCC;
Cursori;
Triggers;
MARS;
Multi-statement table-valued functions (TVFs);

Dunque, ecco la lista dei punti qualificanti che vorrei presentarVi, ho evidenziato in blu le frasi più significative per darVi una visione immediata delle semplici regole da utilizzare:

1) Numero di data file

a. Questo è il punto di maggior confusione, esistono in giro varie interpretazioni basate sul numero di CPU, sull’Hyper-Threading, sui Core, e via discorrendo, fino ad arrivare alla regola che molti conoscono la quale finisce con la frase “…. ma non più di 8…”;

b. Il concetto teorico che sta dietro ad un stima corretta è: quanti thread (i.e. = query) ci possono essere, nel caso peggiore, che accedano contemporaneamente ad oggetti nel TEMPDB ? Questo dovrebbe subito rivelarVi che non importa l’Hyper-Threading o i Core, importa alla fine solo il numero di “CPU Logiche” che alla fine SQL Server si ritrova, al netto ovviamente di una eventuale “Affinity Mask” con cui è possibile limitare i Core/CPU utilizzati da una istanza di SQL Server. Se non volete affaticarVi nel conteggio, è sufficiente eseguire la seguente query per ottenere il numero esatto:

select COUNT(*) from sys.dm_os_schedulers where [status] = 'VISIBLE ONLINE'

c. OK, ora che sappiamo quante “CPU Logiche” ha la nostra istanza di SQL Server, quanti data file creiamo per il TEMPDB ? La regola più completa, ma non necessariamente la più corretta, è: “Tanti data file quante sono le CPU Logiche in uso all’istanza di SQL Server”; perché ho detto che non è necessariamente la più corretta ? Perché la regola vera sarebbe “Tanti data file fin quando non c’è più “contention” sulle pagine di allocazione di sistema contenuti nei data file stessi del TEMPDB”, ma non è molto semplice da appurare per un non esperto DBA, quindi manteniamo per semplicità la regola precedente marcata in blu.

NOTA: La famosa postilla che molti di Voi hanno sicuramente visto/sentito riguarda l’ipotetico massimo di 8 data file: intendiamoci, non è sbagliata a priori, il senso di questa regola è che se non sapete con certezza quanti data file fare per eliminare la “contention”, allora partite pure con la regola marcata in blu ma senza esagerare, un numero superiore di data file dovrebbe essere giustificato da una approfondita analisi delle performance del TEMPDB per capire se ha senso andare oltre;

d. E’ bene tener presente che fare un gran numero di data file per qualsiasi database, a maggior ragione per il TEMPDB, può avere effetti deleteri sul tempo necessario per portare “ONLINE” un database, dato che una delle fasi di “start” che opera in maniera rigorosamente seriale è appunto quella che analizza e poi apre (a livello NTFS) ogni singolo file.

e. IMPORTANTE: Vi prego di notare che ho sempre specificato “data file” e non “log file” o “transaction log file” (le definizioni sono equivalenti) dato che è assolutamente inutile fare “log file” aggiuntivi: a differenza dei “data file” che vengono usati in parallelo, i “log file” sono usati in seriale, solo al riempimento dell’uno SQL Server usa il successivo;

2) Dimensione iniziale

a. Questa è una delle più importanti ottimizzazioni, per qualche scellerato motivo, quando installate SQL Server, la dimensione iniziale dell’unico data file è di soli 8 MB, quella dell’unico transaction log file è di 1 MB, entrambi con “autogrowth“ del 10 %:

b. Se pensate che un TEMPDB può arrivare a svariati GB, se non decine o centinaia di GB, Vi immaginate quanti piccoli “pezzi” (fragment) sul file system dovranno essere allocati ? Migliaia se non milioni, e tutti di dimensioni ridicole, questo è il modo perfetto per distruggere le performance dell’NTFS anche sulle SAN più potenti !

c. La regola d’oro in questo caso è: “Immediatamente dopo l’installazione, rivedere le dimensioni iniziali del TEMPDB portando la dimensione dell’unico data file e del transaction log file ad almeno 1GB”.

NOTA: Non è detto che 1GB sia sufficiente, diciamo che l’NTFS sotto questa soglia ha performance non ottimali per i data file di SQL, su installazioni medie se non grosse consiglio almeno 4-8GB per i data file e 2-4GB per il transaction log file;

3) Autogrowth

a. Ad integrazione della precedente, aggiungo anche questa ulteriore regola: “Eliminare l’Autogrowth in percentuale ed impostare un valore fisso di incremento in MB per tutti i file, usando lo stesso valore”.

i. Che valore scegliere per l’ ”Autogrowth” ? Come raccomandazioni di massima direi almeno 1-2GB per i data file e tra 0.5 e 1.0GB per i transaction log file;

b. Nello scegliere la dimensione per l’autogrowth del transaction log file del TEMPDB, così come per ogni altro database utente considerate questa postilla aggiuntiva: “Evitate di usare il valore esattamente pari a 4GB per l’espansione del transaction log, altrimenti incorrerete in bug noto da 3 versioni di SQL Server”;

i. Il bug è descritto al seguente link:

Bug: log file growth broken for multiples of 4GB
http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx

NOTA: Ho già verificato che nella CTP3 di Denali questo annoso, nel vero senso della parola, problema è stato finalmente risolto;

c. Lo scopo ultimo dovrebbe essere quello di evitare eventi di “Autogrowth” , dato che sono eventi bloccanti (per il workload di SQL Server) per l’intera durata dell’operazione di espansione, specialmente se:

i. Si tratta di transaction log file con incrementi cospicui, dato che non è possibile beneficiare dell’ “Instant File Initialization” (vedi punto[4] seguente);
ii. Si tratta di data file con incrementi cospicui e non si sta utilizando la feature “Instant File Initialization” (vedi punto[4] seguente);

d. Se volete sapere quali, quanti e di che durata sono stati gli eventi di “Autogrowth” sulla Vostra istanza SQL, è possibile utilizzare il report “Disk Usage” dalla console di SQL, tra gli “Standard Report” a livello di singolo database:

i. E’ anche possibile catturare uno specifico evento in una trace SQL con il SQL Profiler:

e. Utilizzando le informazioni dei punti precedenti, ecco una ulteriore regola a corollario della principale: “Dopo aver riconfigurato i valori di size ed autogrowth per i data/log file del TEMPDB, verificare periodicamente la numerosità e la durata degli eventi di autogrowth ed eventualmente ritoccarli ulteriormente per diminuirne frequenza e durata”.

4) Instant File Initialization

a. Si tratta di una importantissima “feature” dell’NTFS di Windows (>= 2003) che permette la creazione o l’espansione istantanea (1-2ms) dei data file di SQL Server, ma non dei transaction log files (quindi prestate attenzione!), anche di centinaia di Gigabyte (GB) o Terabyte (TB).

b. A meno che non abbiate interesse in una attività di “Security Hardening” molto spinta, consiglio caldamente l’abilitazione di questa funzionalità, come abilitarla è presto detto nella seguente frase che riassume questa raccomandazione: “Assicurarsi che il service account di SQL Server abbia lo user-right denominato [Perform Volume Maintenance Tasks] su tutte le macchine dove girerà il servizio”.

5) Posizionamento file

a. Dato che le performance del TEMPDB sono critiche, dovrebbe essere oggetto privilegiato per avere almeno una LUN dedicata, la regola più generale (e costosa !) possibile recita: “Il database TEMPDB dovrebbe avere una LUN dedicata per i data file ed una LUN dedicata per il transaction log file”;
b.Dare due LUN al TEMPDB potrebbe non essere sempre possibile per ragioni di costo, oppure potrebbe essere non necessario perché le altre LUN condivise potrebbero reggere il carico necessario;
c.In caso ci si debba ridurre ad una sola LUN per il TEMPDB, consiglio di dedicarla ai data file e di piazzare il transaction log file sulla stessa LUN utilizzata dai transaction log dei database utente;

6) RAID

a. Qui la storia è semplice, vale la regola generale per tutti i database, quindi la regola sommaria potrebbe recitare così: “Per la LUN del transaction log file del TEMPDB, scegliere un tipo di RAID che ottimizzi le scritture sequenziali (RAID-1 o RAID-10), mentre per i data file scegliere un tipo di RAID che ottimizzi letture/scritture random, pur garantendo il necessario grado di fault tollerance”.
b.La suddetta regola si riferisce ad una situazione ottimale, è anche possibile utilizzare un tipo di RAID meno performante, ma anche meno costoso, a patto che il sottosistema di I/O sia in grado di reggere il carico necessario;
c.Per verificare le performance dell’ I/O a livello di singolo file, non è possibile fare uso dei Performance Counters di Windows, è però possibile usare la seguente query in SQL Server (specifica per tutti i file del TEMPDB):

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'),null)

d.E’ anche possibile visualizzare in modalità “live” I tempi di accesso ad ogni file di ogni database, anche per il TEMPDB quindi, utilizzando l’Activity Monitor di SQL 2008 (e successive versioni) come da figura seguente:

b. Sebbene il TEMPDB sia un database con dati “a perdere” dato che tutti i suoi file vengono distrutti e ricreati ad ogni stop/start, “Per il TEMPDB è comunque sconsigliato utilizzare volumi di tipo RAID-0”: in caso di fallimento/corruzione del disco, l’intera istanza si fermerà se il TEMPDB non sarà in grado di funzionare correttamente.

7) Trace flags

a. I principali e più importanti “trace flags” con cui si può intervenire per ottimizzare le performance del TEMPDB sono due:

i. -T1118: Utilizzato moltissimo in SQL 2000 dove non erano presenti alcune importanti ottimizzazioni per il TEMPDB, è tutt’ora valido (e utile) anche in SQL 2005 e successive versioni: dopo aver abilitato tale trace flag, l’allocazione di spazio per gli oggetti temporanei avverrà solo utilizzando “extent” di tipo “uniform”, non più di tipo “mixed”, i particolari sono nel link seguente, questo serve a diminuire possibili colli di bottiglia sulle pagine di sistema di tipo SGAM in ogni data file del database:

Concurrency enhancements for the tempdb database
http://support.microsoft.com/kb/328551/en-us

SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage
http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

Misconceptions around TF 1118
http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx

ii. -T1117: Questo è molto meno noto del precedente, ma fornisce un’interessante funzionalità: nel caso il TEMPDB abbia più data file, quando un singolo file deve andare in “Autogrowth”, allora SQL Server espande tutti i data file comtemporaneamente; su alcune grosse installazioni di SQL Server, questo serve a mantenere perfettamente bilanciato lo spazio allocato tra tutti i data file presenti, migliorando la strategia di allocazione per il TEMPDB;

IMPORTANTE: Nel caso si decida di adottare il trace flag “-T1117” e/o si abbiano numerosi data file e/o le dimensioni di “Autogrowth” siano non trascurabili, assicurarsi di aver abilitato la feature “Instant File Initialization” altrimenti le operazioni di espansione si prolungheranno per un tempo potenzialmente lungo.

b. Questi trace flag devono essere aggiunti, eventualmente, agli “startup parameters” dell’istanza SQL Server mediante il tool “SQL Configuration Manager”, separati da “;” e facendo molta attenzione ad aggiungerli alla fine di quanto già presente senza sovrascrivere i parametri esistenti, e senza spazi tra “-T” ed il valore numerico (1117 o 1118):

c. Per finire, ecco la regola sommaria: “Utilizzando il tool SQL Configuration Manager, aggiungere il trace flag –T1118, ed eventualmente anche –T1117, agli startup parameters dell’istanza SQL.

d. I parametri avranno effetto al successivo riavvio dell’istanza SQL, verificate che nell’ERRORLOG compaia una situazione del genere (deve comparire uno spazio tra –T ed il relativo valore numerico, altrimenti avete sbagliato qualcosa !):

Registry startup parameters:

-d C:\SQL2008\MSSQL10.SQL2008\MSSQL\DATA\master.mdf
-e C:\SQL2008\MSSQL10.SQL2008\MSSQL\Log\ERRORLOG
-l C:\SQL2008\MSSQL10.SQL2008\MSSQL\DATA\mastlog.ldf
-T 1118
-T 1117

8) Frammentazione esterna NTFS

a. Altro effetto sgradevole delle “Autogrowth” frequenti e/o di piccole dimensioni, è la frammentazione a livello NTFS dei data/log file non solo del TEMPDB, in questo caso, ma di tutti i database in generale, ovviamente è preferibile che tutti i data/log file di un database siano preallocati in maniera contigua evitando, per quanto possibile, susseguenti espansioni;

b. Se volete analizzare lo stato di frammentazione a livello di file system potete usare il seguente utilissimo tool dalla suite di SysInternals:

Contig v1.6 (by Mark Russinovich)
http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

c. Dato che ho già formulato, nei paragrafi precedenti, le regole relative alle dimensioni raccomandate per la size iniziale e per l’”Autogrowth”, qui non mi rimane che aggiungere la seguente regola accessoria: “E’ consigliato controllare periodicamente la frammentazione a livello di file system dei data/log file che compongono il TEMPDB, utilizzando il tool Contig di SysInternals ”; nel caso che uno o più dei suddetti file sia composto da più di una decina di “fragments” è consigliato rigenerare i file del TEMPDB con i seguenti macro passi:

i. Fermare il servizio SQL Server;
ii. Eseguire un backup “offline” di tutti i data/log file che compongono tutti i database (di sistema e utente) dell’istanza, ad eccezione del TEMPDB;
iii. Utilizzare un tool di deframmentazione disco ed attivarlo sulla/e LUN in oggetto per ricompattare lo spazio libero su disco.
iv. Riavviare l’istanza SQL: al riavvio il database TEMPDB sarà ricreato in ogni suo file, stavolta con dimensioni iniziali congrue in modo da evitare ulteriori frammentazioni dovute a valori iniziali troppo piccoli e/o autogrowth troppo frequenti;

9) Frammentazione interna VLF

a. Il transaction log di ogni database ha una sua struttura interna complessa, cercando di condensare quanto più possibile, esso è diviso in un numero variabile di unità contigue di spazio disco denominate Virtual Log Files (VLFs): quanti VLF, e di che dimensione lo decide SQL Server autonomamente senza possibilità di intervento diretto, l’unico modo di influenzarlo è stabilire la dimensione iniziale del transaction log e la dimensione delle eventuali autogrowth, in base a questi SQL Server farà le sue scelte; I particolari li potete trovare a questo link:

Transaction Log Physical Architecture
http://technet.microsoft.com/en-us/library/ms179355.aspx

b. Il problema nasce se il numero di VLF diventa elevato, questo infatti può impattare, in generale su ogni database ed in parte anche sul TEMPDB, pesantemente su attività come la recovery di un database (alla partenza di SQL), i tempi di backup/restore, le autogrowth del transaction log ed addirittura le classiche operazioni di manipolazioni dati (DML);

c. In base alle considerazioni dei due punti precedenti, ecco la regola sintetica di questo paragrafo: “Periodicamente, è necessario monitorare il numero di VLF per il database TEMPDB e se ne risultano più di 100 è necessario intraprendere azioni correttive”;

i. Per controllare il numero di VLF per tutti i database, anche per il TEMPDB, è possibile utilizzare lo script che trovate al seguente link, gentilmente fornito dal nostro buon amico Paul Randal (http://www.sqlskills.com):

http://www.sqlskills.com/BLOGS/PAUL/file.axd?file=2010%2f4%2fSQLSkillsLogInfo.zip

d. Le azioni correttive per abbassare il numero dei VLF nel database TEMPDB sono le seguenti:

i. Aumentare la dimensione iniziale del transaction log in maniera congrua, seguendo le raccomandazioni di questo documento;
ii. Aumentare la dimensione della eventuale autogrowth in maneria congrua, seguendo le raccomandazioni di questo documento;
iii. Riavviare l’istanza SQL Server per rigenerare il database TEMPDB;

10) Formattazione LUN

a. Se avete ancora la sfortuna di dover ancora installare (o gestire) SQL Server su Windows Server 2003 (o precedenti) fate molta attenzione all “Partition Alignment”, dato che tali versioni di Windows, by default, creano le partizioni “disallineate” con offset a 31.5 KB; la perdita di performance, sul sottosistema di I/O, può arrivare anche al 50%, è caldamente consigliato crearle, quindi, con offset a 1MB come da default per Windows Server 2008 e successive:

Disk Partition Alignment Best Practices for SQL Server
http://msdn.microsoft.com/en-us/library/dd758814.aspx

NOTA: In genere l’offset a 1MB è ottimale per tutte le “stripe unit” delle SAN attualmente in commercio, in ogni modo è consigliato verificare con il Vendor per eventuali differenti raccomandazioni in tale senso.

b. A meno che il Vendor della Vostra SAN non abbia differenti e specifiche raccomandazioni in materia, formattare la partizione per il TEMPDB con “allocation unit” a 64KB;

NOTA: entrambe queste raccomandazioni si applicano in generale alle LUN utilizzate per tutti i database, non solo per il TEMPDB.

In aggiunta a questa lunga lista, ecco alcune importanti informazioni e peculiarità sul TEMPDB:

- Da SQL 2008 è possibile abilitare l’algoritmo di tipo “CHECKSUM” (default in SQL 2008 e seguenti) anche sul TEMPDB;

- Il “Recovery Model” di default del TEMPDB è SIMPLE (vedi figura precedente) e decisamente non vedo ragioni per cambiarlo;

- Aspetto teorico, ma anche “capacitivo”, molto interessante del TEMPDB: al contrario dei “normali” database utente, dove sia le informazioni di “redo” (roll-forward) che di “undo” (roll-back) delle transazioni vengono inserite nel transaction log, nel TEMPDB solo le informazioni di “undo” vengono considerate in modo da poter supportare eventuali “rollback” delle transazioni utente che contengono tabelle temporanee; non è necessario avere le informazioni di “redo”, necessarie eventualmente solo nella procedura di “crash recovery” (di qualsiasi altro database), quando esso viene messo/portato “online”, perché il TEMPDB viene ricreato da zero e non passa attraverso tale procedura.

- Il processo di “Checkpoint” interno di SQL Server, è attivo anche sul TEMPDB ma si comporta in maniera leggermente differente: generalmente (esiste un’eccezione), le “dirty pages” in memoria relative al TEMPDB non vengono scaricate (flush) su disco proprio perché il TEMPDB non passa attraverso la procedura di “crash recovery” quando và online; l’eccezione è la regola per i database in SIMPLE recovery model, e cioè quando lo spazio effettivamente usato arriva al 70% della dimensione totale.

- In SQL Server 2005 è stato introdotto un meccanismo di “caching” specializzato per il TEMPDB in modo da eliminare gli storici colli di bottiglia sulle mappe di allocazione: quando si distrugge un oggetto temporaneo in TEMPDB, non tutto viene deallocato, una pagina di tipo “IAM” (index allocation map) ed un data page rimangono, in modo da poter essere eventualmente riutilizzate senza dover passar da alcune tabelle di sistema (di allocazione) di SQL Server;

- In aggiunta ai normali performance counters utilizzati per ogni database utente, il TEMPDB ne ha alcuni specializzati che è bene tenere sotto controllo:

• General Statistics -> Active Temp Tables;
• General Statistics -> Temp Tables Creation Rate;
• General Statistics -> Temp Tables for Destruction;
• Transactions -> Free Space in tempdb (KB);
• Transactions -> tutti i counters che iniziano con "Version";

- Credenza popolare assolutamente sbagliata: le variabili di tipo tabelle temporanee stanno in memoria e non in tempdb, ovviamente non è vero !

- Anche per il TEMPDB è possibile eseguire il controllo per la presenza di eventuali corruzioni con il comando “DBCC CHECKDB”, cosa per altro consigliata;

- Anche per le tabelle temporanee create in TEMPDB, è possibile creare indici e statistiche, inoltre le funzionalità a livello di database denominate “AutoCreate Statistics” e “AutoUpdate Statistics” sono attive ed è consigliato lasciarle in questo stato.

- Il “database ID” del TEMPDB è sempre (2), ricordavelo, specialmente quando analizzate eventuali blocchi, deadlock, o report di corruzione;

- Attenzione quando abilitate la “Transparent Database Encryption” (TDE) di SQL 2008 su un qualsiasi database utente perché anche il TEMPDB sarà totalmente criptato, sia nella parte relativa ai data file che transaction log files.

- Attenzione ad eventuali operazioni di “shrink” manuale sul TEMPDB, potreste causare una corruzione dello stesso database, leggete bene il seguente articolo, in particolare la sezione "Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use":

How to shrink the tempdb database in SQL Server
http://support.microsoft.com/kb/307487/en-us

- La “Collation” con cui il TEMPDB viene creato è quella di sistema che Voi avete specificato all’atto dell’installazione, fate attenzione ad usare database con tale differente impostazione perchè, nel caso le Vostre query facciano un confronto tra stringhe e/o tipi di dato strutturati (money, datetime, etc.) potrebbero esserci dei problemi o degli errori perché il confronto è tra “Collation” differenti e potrebbe essere necessario inserire delle conversioni forzate (“collate” query hint option) per rendere consistente il risultato.

Spero di avere inserito tutto, nel caso mi fossi dimenticato qualcosa non esitate a farmelo sapere.

Al prossimo post !

--Igor Pagliai--

Visita guidata ai Datacenter Microsoft

Italian Premier Center for SQL Server — Mon, 05 Sep 2011 06:23:00 GMT

Volete sapere come Microsoft gestisce, a livello di logistica ed infrastruttura, i sui Datacenter ? Volete sapere come è possibile stipare 2400 server in un container (si, avete capito bene) merci ?

Il seguente video è molto interessante, poche persone hanno avuto la possibilità di accedervi di persona, direi che vale la pena spenderci i 10 minuti della durata.

http://blog.seattlepi.com/microsoft/2011/08/05/what-is-the-cloud-take-a-video-tour-of-microsoft-data-centers/

Buona visione a tutti.

--Igor Pagliai--