SQL Server Performances

De Banane Atomic
Révision datée du 9 juillet 2018 à 14:27 par Nicolas (discussion | contributions) (→‎Entity Framework)
(diff) ← Version précédente | Voir la version actuelle (diff) | Version suivante → (diff)
Aller à la navigationAller à la recherche

Liens

Concepts

Clustered Index Structure.jpg

Clustered Index Structure
Les données sont triées par leur cluster key / primary key (ex: Id).
Les index rows permettent un accès direct aux données.

SQL Index.jpg

Ajouter un index permet de trier les données suivant un autre critère que la primary key.
Les keys permettent un accès direct aux raw pointers qui pointent vers les données.

Activity Monitor

SSMS → clique-droit sur le serveur → Activity Monitor

  • Recent Expensive Queries (clique-droit: Show Execution Plan / Edit Query Text)

Reports

SSMS → clique-droit sur le serveur → Reports → Standard Reports

  • Activity - All Blocking Transactions

Execution Plan

SSMS → Tool Bar Menu → Ligne 2 → Display Estimated Execution Plan (Ctrl + L)
N’exécute pas la requête, fais juste une estimation.
Se lit de droite à gauche et de haut en bas.

Clustered Index Scan
Index Scan
Table Scan
recherche dans une colonne non-indexée. Lit toutes les lignes d'une table, très lent si beaucoup de lignes.
Clustered Index Seek
Index Seek
recherche dans une colonne indexée.
Nested Loops Join pour chaque éléments de la première collection, parcourt les éléments de la deuxième.
Merge Join jointure entre 2 collections déjà triées.
Hash Match créé lorsqu'une recherche est faite sur une grande collection sans index.

Il faut probablement réduire la taille de la collection avec une condition plus restrictive ou ajouter un index.

Afficher le message d'aide: Clique-droit → Missing Index Details

Statistics

Tsql.svg
-- À définir avant la requête
set statistics io on
set statistics time on

# query
time

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 106 ms.

io

(40 rows affected)
Table 'T1'. Scan count 0, logical reads 91, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T2'. Scan count 1, logical reads 12023, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T3'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

logical read: lecture d'une page en mémoire ou sur disque (8kb)

Index

Sélectivité d'un index: critère de qualité d'un index. Si une valeur d'index renvoie peu de lignes il est de bonne qualités car il permet filtrer efficacement. Si par contre, une valeur d'index renvoie beaucoup de lignes, l'index est peu efficace car il filtre peu de valeurs.
Utiliser un index peu sélectif est moins performant qu'un scan de la table. SQL Optimizer n'utilisera donc pas les index peu sélectifs.

Clustered Index vs Non-Clustered Index

Clustered Index stockage des données triées suivant les valeurs de la colonne possédant le Clustered Index. Ex: Primary Key
Un seul Clustered Index par table.
Non-Clustered Index structure de pointeurs vers la valeur dans la table.

Quelles colonnes indexer

  • celles qui sont utilisées dans les clauses where
  • celles qui contiennent des clés étrangères

Index multi-colonnes

Tsql.svg
select * from Clients
where FirstName = 'John' and LastName = 'Smith'
-- condition sur 2 colonnes: utiliser un index sur 2 colonnes

L'ordre des colonnes est important: la première doit être renseignée, les autres sont optionnelles.
Si l'index porte sur FirstName et LastName, une requête uniquement sur LastName n'utilisera pas cet index car LastName n'est pas la première valeur de cet index.
Une requête uniquement sur FirstName utilisera pas cet index car FirstName est la première valeur de cet index.

Clauses LIKE

Utiliser like avec un % au début empêche l'utilisation de l'index.
Utiliser like avec peu de caractères et un % à la fin n'est pas assez sélectif, l'index ne sera pas utilisé.

Valeurs calculées

Tsql.svg
select * from Clients
where substring(email, 0, charindex('@', email, 0)) = 'john.smith'

Utiliser des valeurs calculées empêche l'utilisation de l'index.
Créer plutôt une computed column et l'indexer.

Tsql.svg
alter table Clients
add EmailPart1 as substring(email, 0, charindex('@', email, 0))

Include Column

Tsql.svg
create index IX_Clients_Email
  on Clients (Email)
  include (Firstname, Lastname)

select Email, Firatname, Lastname
  from Clients
  where Email = 'john.smith@company.org'

Un index pointe sur la valeur de la colonne indexée. Si d'autre éléments de la ligne sont sélectionné il faut les récupérer en faisant une requête.
Pour éviter cela, on peut inclure des colonnes dans l'index, ainsi il n'est pas nécessaire de faire une requête pour les récupérer.

Réécrire les requêtes

left join vs not exist

Tsql.svg
select *
from tableA A
left join tableB B
     on A.id = B.id
where B.id is null

select *
from tableA A
where not exists
(
    select 1 from tableB B where B.id = A.id
)

-- compter le nombre de lignes
select count(*) from tableA
  • left join
    • select sur tableA et tableB
    • jointure avant de filtrer
    • bonne idée si tableA possède beaucoup de lignes et tableB possède peu de lignes avec ou sans index
  • not exists
    • select uniquement sur tableA ou tableB
    • bonne idée si tableA possède peu de lignes et tableB posséde beaucoup de lignes avec un index

Tracer une requête (Extended Events)

SQL Profiler plus disponible à partir de 2016 ni SQL Azure
Extended Events 2012 et après

Permissions:

  • ALTER ANY EVENT SESSION
  • VIEW SERVER STATE (pour SSMS)

Configurer la session

SSMS → MyServer → Management → Extended Events → clique-droit sur Sessions → new Session

  • Name: MyTable DB Extended Event SQL Trace
Templates
Query Batch Tracking
  • Events: sélectionner les événements à capturer
    • Configure
      • Global Fields: sélectionner les champs des événements à capturer
        • rpc_completed & sql_batch_completed → sql_text : sql statement
      • Filter: filtrer les données à capturer
        • sqlserver.database_name = MyDatabase
    • Data Storage
      • event_file → C:\Folder\SqlTrace.xel

Démarrer / arrêter la session

SSMS → MyServer → Management → Extended Events → Sessions → clique-droit sur ma session

  • Start Session
  • Stop Session
  • Watch Live Data

Configurer la disposition de la vue

  • Ajout d'une colonne: sélectionner un évènement → Details → clique-droit sur un champs → Show Column in Table
  • Ajout d'une colonne: clique-droit dans un en-tête de colonne → Choose Columns
  • Sauvegarder la disposition de la vue: Display Settings → Save As

Analyser les données

  • Trie des colonnes
  • Filtres
  • Export: Extended Events → Export To (xel, table, csv)
  • Statistiques avec Grouping et Aggregation

Dynamic Management Views - DMV

Accès aux DMV nécessite la permission VIEW SERVER STATE

Lister tous les clients connectés à la bdd

Tsql.svg
SELECT
    database_id,    -- SQL Server 2012 and after only
    session_id,
    status,         -- running or sleeping
    login_time,     -- when the session was initiated
    cpu_time,       -- in ms
    memory_usage,   -- in 8kb blocks
    reads,
    writes,
    logical_reads,
    host_name,
    program_name,   -- by default for .Net programs: .Net SqlClient Data Provider
    host_process_id,
    client_interface_name,
    login_name as database_login_name,
    last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY cpu_time DESC;

Lister toutes les activités sur la bdd ou sur le serveur

Tsql.svg
SELECT
        [DatabaseName] = db_name(rq.database_id),
        s.session_id, 
        rq.status,
        [SqlStatement] = SUBSTRING (qt.text,rq.statement_start_offset/2,
            (CASE WHEN rq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),
            qt.text)) * 2 ELSE rq.statement_end_offset END - rq.statement_start_offset)/2),        
        [ClientHost] = s.host_name,
        [ClientProgram] = s.program_name, 
        [ClientProcessId] = s.host_process_id, 
        [SqlLoginUser] = s.login_name,
        [DurationInSeconds] = datediff(s,rq.start_time,getdate()),
        rq.start_time,
        rq.cpu_time,
        rq.logical_reads,
        rq.writes,
        [ParentStatement] = qt.text,
        p.query_plan,
        rq.wait_type,
        [BlockingSessionId] = bs.session_id,
        [BlockingHostname] = bs.host_name,
        [BlockingProgram] = bs.program_name,
        [BlockingClientProcessId] = bs.host_process_id,
        [BlockingSql] = SUBSTRING (bt.text, brq.statement_start_offset/2,
            (CASE WHEN brq.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),
            bt.text)) * 2 ELSE brq.statement_end_offset END - brq.statement_start_offset)/2)
    FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests rq
        ON s.session_id = rq.session_id
    CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as qt
    OUTER APPLY sys.dm_exec_query_plan(rq.plan_handle) p
    LEFT OUTER JOIN sys.dm_exec_sessions bs
        ON rq.blocking_session_id = bs.session_id
    LEFT OUTER JOIN sys.dm_exec_requests brq
        ON rq.blocking_session_id = brq.session_id
    OUTER APPLY sys.dm_exec_sql_text(brq.sql_handle) as bt
    WHERE s.is_user_process =1
        AND s.session_id <> @@spid
 AND rq.database_id = DB_ID()  -- Comment out to look at all databases
    ORDER BY rq.start_time ASC;

Lister les requêtes les plus lentes

Le serveur garde en mémoire les statistiques des requêtes des dernières heures, il est donc possible de connaître la plus lente.

Tsql.svg
SELECT TOP 20    
        DatabaseName = DB_NAME(CONVERT(int, epa.value)), 
        [Execution count] = qs.execution_count,
        [CpuPerExecution] = total_worker_time / qs.execution_count,  -- temps CPU pris par l’exécution de la requête
        [TotalCPU] = total_worker_time,
        [IOPerExecution] = (total_logical_reads + total_logical_writes) / qs.execution_count,
        [TotalIO] = (total_logical_reads + total_logical_writes),
        [AverageElapsedTime] = total_elapsed_time / qs.execution_count,  -- temps pris par l’exécution de la requête
        [AverageTimeBlocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,  -- temps d'attente causé par un lock
     [AverageRowsReturned] = total_rows / qs.execution_count,    
     [Query Text] = SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
            (CASE WHEN qs.statement_end_offset = -1 
                THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                ELSE qs.statement_end_offset end - qs.statement_start_offset)
            /2),
        [Parent Query] = qt.text,
        [Execution Plan] = p.query_plan,
     [Creation Time] = qs.creation_time,
     [Last Execution Time] = qs.last_execution_time   
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) p
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE epa.attribute = 'dbid'
        AND epa.value = db_id()
    ORDER BY [AverageElapsedTime] DESC; --Other column aliases can be used

Lister les colonnes qui auraient besoin d'être indexée

Tsql.svg
SELECT     
    TableName = d.statement,
    d.equality_columns, 
    d.inequality_columns,
    d.included_columns, 
    s.user_scans,           -- nb d'utilisation de l'index s'il existait
    s.user_seeks,           -- nb d'utilisation de l'index s'il existait
    s.avg_total_user_cost,  -- coût sans l'index
    s.avg_user_impact,      -- % de réduction du coût avec l'index
    -- économie de coût par statement
    AverageCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0), 3),
    -- économie de coût pour tous les statements (utiliser cette colonne pour trier les résultats)
    TotalCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0) * (s.user_seeks + s.user_scans),3)
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
    ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
    ON d.index_handle = g.index_handle
WHERE d.database_id = db_id()
ORDER BY TableName, TotalCostSavings DESC;

Tester l'utilité des index

Pour un index non-clustered si UserSeeks est faible et UserUpdates important, cet index est coûteux et n'apporte pas de bénéfice.

Tsql.svg
SELECT
    [DatabaseName] = DB_Name(db_id()),
    [TableName] = OBJECT_NAME(i.object_id),
    [IndexName] = i.name, 
    [IndexType] = i.type_desc,
    [TotalUsage] = IsNull(user_seeks, 0) + IsNull(user_scans, 0) + IsNull(user_lookups, 0),
    [UserSeeks] = IsNull(user_seeks, 0),     -- nb de fois où l'index est utilisé
    [UserScans] = IsNull(user_scans, 0), 
    [UserLookups] = IsNull(user_lookups, 0),
    [UserUpdates] = IsNull(user_updates, 0)  -- nb de fois où l'index est màj suite à une opération
FROM sys.indexes i 
INNER JOIN sys.objects o
    ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON s.object_id = i.object_id
    AND s.index_id = i.index_id
WHERE 
    (OBJECTPROPERTY(i.object_id, 'IsMsShipped') = 0)
ORDER BY [TableName], [IndexName];

Développement C#

Dynamic SQL vs Parameterized SQL vs Stored Procedure

Cs.svg
// Dynamic SQL
var cmd = $"select * from Users where UserName = {userName}";

// Parameterized SQL
var cmd = "select * from Users where UserName = @UserName";
using (var sqlCmd = new SqlCommand(cmd))
{
    sqlCmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar, 50).Value = userName;
}

Parameterized SQL plus rapide que Dynamic SQL car permet de réutiliser le plan d’exécution.
Les stored procedure sont aussi performante que les déclarations Dynamic SQL.

Auto-commit

Un commit est réaliser automatiquement après chaque requête de modification.
Utiliser des transactions pour grouper les requêtes et faire moins de commit.

Entity Framework