Archives mensuelles : mars 2014

optimize for adhoc workloads

SQL Server maintient un cache de plans d’exécution. Ces plans permettent au moteur de ne pas ré-optimiser les requêtes si elles sont exécutées une deuxième fois. C’est valable bien sûr pour les procédures stockées, mais aussi pour les requêtes ad-hoc. Le problème avec les requêtes ad-hoc (celles qui sont générées dans le code de l’application au lieu de faire un appel de procédure stockée), c’est que le plan concerne une requête, et non pas une requête paramétrée. En d’autres termes, ces deux requêtes :

SELECT * FROM dbo.Client WHERE Nom = 'MAUGHAN';
SELECT * FROM dbo.Client WHERE Nom = 'LLOYD';

Vont générer deux plans en mémoire (probablement identiques), parce que SQL Server ne paramétrise pas la requête automatiquement, car le plan pourrait être différent selon le nombre de valeur à retourner par paramètre.
Nous pouvons voir la taille occupée par ces plans qui ne sont utilisés qu’une fois à l’aide de la requête suivante :

SELECT SUM(CAST(cp.size_in_bytes as bigint)) / 1024 / 1024 as adhoc_once_mb
FROM sys.dm_exec_cached_plans AS cp
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
OPTION (RECOMPILE);

Pour économiser cette mémoire, une option de l’instance disponible à partir de SQL Server 2008 peut être activée. Son nom est « optimize for adhoc workloads ». Vous la trouvez dans les propriétés de l’instance, ou vous pouvez la modifier comme suit :

SP_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO

Cette option modifie le comportement de SQL Server de la manière suivante : lorsqu’un plan de requête ad-hoc est calculé, il n’est plus directement stocké dans le cache, seul l’est un résumé du plan (un stub) qui permettra de reconnaître le même plan ensuite. Si le même plan doit être calculé une deuxième fois, il sera cette fois gardé en cache.
Je vous recommande d’activer cette option systématiquement sur tous vos serveurs SQL.
Voici une requête synoptique pour vous rendre compte de la situation sur votre serveur:

SELECT
    cacheobjtype,
    objtype,
    'total' as [type],
    SUM(CAST(size_in_bytes as bigint)) / 1024 / 1014 as size_in_mb
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
UNION ALL
SELECT
    cacheobjtype,
    objtype,
    '1x',
    SUM(CAST(size_in_bytes as bigint)) / 1024 / 1014 as size_in_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
GROUP BY cacheobjtype, objtype
ORDER BY cacheobjtype, objtype, [type];

Modifier les colonnes TEXT en VARCHAR(MAX)

Le type de données TEXT est déprécié depuis SQL Server 2005. Outre les problèmes de gestion dans le code et sa non compatibilité avec plusieurs fonctions de chaînes, et est gourmand en espace disque et ralentit les opérations de lecture et d’écriture, parce que le moteur de stockage doit créer une allocation spécifique aux LOB pour chaque ligne insérée.
Le type qui le remplace s’appelle VARCHAR(MAX), et l’allocation dans la page ou en LOB se fait dynamiquement selon le contenu inséré dans la colonne. Dans la pratique, cela prend beaucoup moins d’espace en base.

Voici un code se basant sur une vue de catalogue, pour générer les instructions ALTER TABLE pour convertir les types de données, et ensuite pour reconstruire les tables (à partir de SQL Server 2008). Vous pouvez au besoin modifier le code pour accommoder des colonnes NTEXT, et IMAGE (à remplacer par VARBINARY(MAX)) si vous en avez.

SELECT
    'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] VARCHAR(MAX) '
    +CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'text';

SELECT
    'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] REBUILD;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'text'
GROUP BY TABLE_SCHEMA, TABLE_NAME;