SQL Profiler et Tuning Advisor

Voici quelques liaisons qui devraient vous aider à démarrer dans votre quête de performance.

Quelles ressources existent pour le réglage de la base de données?

astuces de réglage des performances préférées

Quelles techniques génériques peuvent être appliquées pour optimiser SQL Questions?

meilleur moyen d'améliorer les performances (et d'inclure un basculement en quelque sorte)

Si vous pouvez utiliser le profileur pour stocker les événements vers une table, il n'est pas possible d'utiliser le conseiller de syntonisation de la base de données (DTA) pour optimiser la base de données de la table des journaux, mais personnellement, je n'utilise pas le DTA du tout . Il faut beaucoup de temps pour utiliser le DTA et je veux plus de contrôle sur ce qui se passe.

Si vous pouvez convaincre le propriétaire du serveur de créer une nouvelle base de données appelée quelque chose comme "sqltoolkit" et vous donner des droits aux procédures, j'ai quelques procédures qui vous aideront à choisir les bons indices. P >

    CREATE PROCEDURE [ADMIN].[spMissingIndexes]
AS
SELECT
      mid.statement,
      mid.equality_columns,
      mid.inequality_columns,
      mid.included_columns,
      migs.user_seeks,
      migs.user_scans,
      migs.last_user_seek,
      migs.avg_user_impact,
      user_scans,
      avg_total_user_cost,
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.*
   FROM
      sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig
         ON (migs.group_handle = mig.index_group_handle)
      INNER JOIN sys.dm_db_missing_index_details AS mid
         ON (mig.index_handle = mid.index_handle)
   ORDER BY
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;

GO

Sur demande, j'envoie un autre script utile pour déterminer la fréquence et la manière dont toute index est bloquée en raison du mécanisme de verrouillage dans SQL:

CREATE PROCEDURE [ADMIN].[spIndexContention]
    @dbname sysname
WITH EXECUTE AS CALLER
AS
declare @dbid int
select @dbid = DB_ID(@dbname)
declare @sql nvarchar(1000)
SET @sql = N'SELECT dbname=DB_NAME(database_id), tablename=object_name(s.object_id, s.database_id)
    , indexname=i.name, i.index_id
    , row_lock_count, row_lock_wait_count
    , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
    , row_lock_wait_in_ms
    , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
FROM sys.dm_db_index_operational_stats (' + convert(nvarchar(5),@dbid) + ', NULL, NULL, NULL) s
    INNER JOIN ' + @dbname + N'.sys.indexes i 
        ON i.object_id = s.object_id
        AND i.index_id = s.index_id
ORDER BY row_lock_wait_count desc'
print @sql
exec sp_executesql @sql


GO

Ce script peut être utilisé pour déterminer si vous avez choisi les bons index. Vous devez examiner la fréquence à laquelle l'index est utilisé pour la recherche et la comparer à la fréquence à laquelle l'index est mis à jour. Rechercher des performances sur le coût des performances de mise à jour. Et ce qui est pire, lorsque l'index est fréquemment mis à jour, vous entraînez que l'index soit fragmenté et que les statistiques doivent être obsolètes.

Vous devez également comparer la plage_scan_count à singleton_lookup_count. La balayage de la plage est préférée avant la recherche Singleton. Une recherche singleton peut être la cause et la recherche d'index et une opération de recherche clé. C'est-à-dire que, pour chaque ligne trouvée dans l'index de la recherche, SQL recherchera la date de données de l'indice en cluster, et c'est accepte pour dire quelques milliers, mais pas pour des millions de lignes. P>

CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = object_name(iu.object_id, iu.database_id)
        , i.name
        ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
        ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
        ,''user writes'' = iu.user_updates
        ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
where 
    iu.database_id = ' + @dbid + '
    and iu.index_id=i.index_id
    and iu.object_id=i.object_id
    and (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = object_name(o.object_id, o.database_id),
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO