Archives mensuelles : novembre 2013

Contraintes non trustées

Laisser une réponse

Une contrainte CHECK est marque comme étant digne de confiance si elle a été créée avec l’option WITH CHECK (valeur par défaut), c’est-à-dire qu’elle vérifie les données existantes dans la table à la création. Une contrainte marquée comme non digne de confiance ne pourra pas être utilisée par l’optimiseur pour éliminer certains cas dans la recherche. Cela vaut donc la peine de vérifier que vos contraintes sont dignes de confiance.
Voici une requête qui liste toutes les contraintes CHECK de votre base de données qui ne sont pas dignes de confiance :

SELECT
    OBJECT_NAME(parent_object_id) AS NomTable,
    name AS NomContrainte
FROM sys.objects
WHERE type_desc = 'CHECK_CONSTRAINT'
AND OBJECTPROPERTY([object_id], 'CnstIsNotTrusted') = 1
ORDER BY NomTable, NomContrainte;

Si vous voulez les rendre dignes de confiance, vous pouvez utiliser une instruction comme celle qui suit :

ALTER TABLE CDRRating WITH CHECK CHECK CONSTRAINT NomDeLaContrainte;
ALTER TABLE CDRRating WITH CHECK CHECK CONSTRAINT ALL; -- pour toutes les contraintes de la table

recherche dans les plans en mémoire

Laisser une réponse

Voici quelques requêtes permettant de faire des recherches dans les plans d’exécution gardés en mémoire dans le cache de plans. Ces plans sont représentés en XML, donc nous utilisons du XQuery pour effectuer les recherches.

Recherche des plans parallélisés :

SELECT TOP 10
p.*,
q.*,
qs.*,
cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
JOIN sys.dm_exec_query_stats qs
ON qs.plan_handle = cp.plan_handle
WHERE
cp.cacheobjtype = 'Compiled Plan' AND
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
OPTION (MAXDOP 1)

Recherche de tous les plans qui utilisent un index:
inspiré de http://stackoverflow.com/questions/17572261/how-to-filter-xml-execution-plan-data-in-a-where-clause-using-tsql

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @IndexName nvarchar(100) = '[I_CDRRating_Status_SubscriberId_BillingPopulationId]';

WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT OBJECT_NAME(qp.objectid, qp.dbid) as obj,
    st.text,
    cp.usecounts,
    cp.objtype,
    qp.query_plan.value('(//RelOp[IndexScan/Object/@Index = sql:variable("@IndexName")]/@PhysicalOp)[1]', 'varchar(50)') as usage,
    qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where --cp.objtype = 'Proc' and
      qp.query_plan.exist('//RelOp[
                                  (@PhysicalOp = "Index Seek" or @PhysicalOp = "Index Scan") and
                                  IndexScan/Object/@Index = sql:variable("@IndexName")
                                  ]') = 1
ORDER BY cp.usecounts DESC
OPTION (MAXDOP 1);

Retrouver les index manquants dans les plans d’exécution en cache:
Un requête est disponible pour ce faire sur cette entrée de blog de Jason Strate.
http://www.jasonstrate.com/2010/12/can-you-dig-it-missing-indexes/

exporter la structure d’une base avec Powershell

Laisser une réponse

Le code ci-dessous peut être sauvegardé dans un fichier .ps1, pour être utilisé comme script. Changez simplement l’adresse de votre serveur SQL à la ligne 3, et le chemin de sauvegarde des scripts à la ligne 17. Il va exporter toutes les structures de vos bases de données dans des sous-répertoires, dans un répertoire nommé selon le moment de l’exécution. Cela vous permet de conserver plusieurs versions de votre export, et d’utiliser un programme de diff (comme WinMerge) pour voir les différences.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" ".\SQL2008"
$so = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions";
#$so.ScriptDrops = $TRUE;
$so.IncludeIfNotExists = $TRUE;
$so.AppendToFile = $FALSE
$so.ToFileOnly = $TRUE
$so.AnsiFile = $TRUE
$so.ConvertUserDefinedDataTypesToBaseType = $TRUE
$so.DriAll = $TRUE
$so.Permissions = $TRUE
$so.Triggers = $TRUE
$so.PrimaryObject = $TRUE

#$path = "~/db_scripts/"
$path = "c:/temp/db_scripts/$((Get-Date f 'yyyyMMdd-HHmm'))/"
if (!(Test-Path -path $path)) { Mkdir $path }

foreach ($db in $srv.Databases) {
    if (!$db.IsSystemObject) {
        $localPath = $path+$db.Name+"/"
        if (!(Test-Path -path ($localPath))) { Mkdir ($localPath) }
       
        if (!(Test-Path -path ($localPath+"tables/"))) { Mkdir ($localPath+"tables/") }
        foreach ($tbl in $db.tables) {
            if (!$tbl.IsSystemObject) {
                $so.FileName = $localPath+"tables/"+$tbl.Schema+"."+$tbl.Name+".tbl.sql"
                #Write-Host "criture de $($tbl.Name) dans $($so.FileName)"
                $so.FileName
                $tbl.Script($so)
                #$tbl.Script($so) > [$tbl.Name].tbl.sql
            } # if (!$tbl.IsSystemObject)
        } # foreach $tbl
        if (!(Test-Path -path ($localPath+"procedures/"))) { Mkdir ($localPath+"procedures/") }
        foreach ($sp in $db.StoredProcedures) {
            if (!$sp.IsSystemObject) {
                $so.FileName = $localPath+"procedures/"+$sp.Schema+"."+$sp.Name+".sp.sql"
                $so.FileName
                $sp.Script($so)
            } # if (!$tbl.IsSystemObject)
        } # foreach $sp
        if (!(Test-Path -path ($localPath+"vues/"))) { Mkdir ($localPath+"vues/") }
        foreach ($vw in $db.Views) {
            if (!$vw.IsSystemObject) {
                $so.FileName = $localPath+"vues/"+$vw.Schema+"."+$vw.Name+".view.sql"
                $so.FileName
                $vw.Script($so)
            } # if (!$tbl.IsSystemObject)
        } # foreach $vw
    } # if ($db.IsSystemObject)
} # foreach $db

fonction UPDATE() dans un déclencheur

Laisser une réponse

Attention à la fonction UPDATE() utilisée dans les déclencheures (triggers).
Ne pensez pas que cela vous indique que la valeur de la colonne a changé, mais simplement
que la colonne a été mentionnée dans l’instruction. Un exemple :

USE tempdb;
GO

CREATE TABLE dbo.Contact (Id INT, Name VARCHAR(50))
GO

INSERT INTO dbo.Contact
VALUES (1, 'Fillon'), (2, 'Copé');

CREATE TRIGGER atr_u_Contact
ON dbo.Contact
AFTER UPDATE
AS BEGIN
    IF UPDATE(Name)
        PRINT 'ok'
    ELSE
        PRINT 'non'
END

UPDATE dbo.Contact
SET Name = Name
WHERE Id = 2;
-- affiche oui

UPDATE dbo.Contact
SET Id = Id
WHERE Id = 2;
-- affiche non

Donc il vaut mieux tester s’il y a eu réellement une modification avant d’exécuter tout le reste. Par exemple, à la place de ceci :

CREATE TRIGGER atr_u_Contact
ON dbo.Contact
AFTER UPDATE
AS BEGIN
    IF UPDATE(Name)
        INSERT INTO ContactHistory (Id, Name)
        SELECT Id, Name
        FROM deleted;
END

ceci :

CREATE TRIGGER atr_u_Contact
ON dbo.Contact
AFTER UPDATE
AS BEGIN
    IF @@ROWCOUNT = 0 RETURN

    IF UPDATE(Name)
        INSERT INTO ContactHistory (Id, Name)
        SELECT d.Id, d.Name
        FROM deleted d
        JOIN inserted i ON d.Id = i.Id
        WHERE d.Name  i.Name;
END

Ici nous ajoutons aussi, en toute première ligne (c’est important), l’instruction

IF @@ROWCOUNT = 0 RETURN

pour tester si le déclencheur est appelé pour une bonne raison. En effet, une instruction comme celle-ci:

UPDATE dbo.Contact
SET Name = UPPER(Name)
WHERE 1 = 0;

n’affecterait aucune ligne, mais appellerait pourtant le déclencheur, qui lancerait des requêtes pour rien. Le test sur @@ROWCOUNT permet de sortir immédiatement du trigger si aucune ligne n’a été affectée.

inspection des verrous posés sur une table

Laisser une réponse

requête sur les vues de gestion dynamique pour voir quels verrous sont posés à l’instant, sur une table, en indiquant le code des requêtes qui verrouillent.

SELECT
    l.resource_type,
    l.resource_subtype,
    l.resource_lock_partition,
    l.request_mode,
    l.request_type,
    l.request_session_id,
    t.text
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_requests r ON l.request_request_id = r.request_id
    AND l.request_session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE resource_database_id = DB_ID('MaBase')
AND resource_associated_entity_id = OBJECT_ID('MaTable');

vérifier l’impact des déclencheurs sur les performances d’écriture

Laisser une réponse

Les déclencheurs (triggers) peuvent avoir un impact important sur les performances de modifications de données sur une table. Vous pouvez utiliser le profiler pour tracer l’exécution des triggers. Cette exécution n’apparaît pas de façon détaillée si vous tracer des événements comme SQL:BatchCompleted.
Vous devez ajouter l’événement SP:stmtCompleted.
En effet, un trigger est considéré comme une procédure stockée, et cet événement vous montrera les lignes du trigger exécutées, et les informations classiques de performance (reads, writes, cpu, duration).
Pour tracer un trigger en particulier, filtrez par les colonnes DatabaseId et ObjectId de la trace. Par exemple, pour voir l’activité d’un trigger nommé atr_u_contact_check dans la base ContactDB, récupérez les Id de la base et du trigger ainsi:

SELECT DB_ID('ContactDB');
SELECT OBJECT_ID('atr_u_contact_check')