SQL Server Performances
Liens
Concepts
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.
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
|
-- À définir avant la requête set statistics io on set statistics time on # query |
| time |
SQL Server Execution Times: |
| io |
(40 rows affected) |
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
|
|
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
|
|
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.
|
|
alter table Clients
add EmailPart1 as substring(email, 0, charindex('@', email, 0))
|
Include Column
|
|
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
|
|
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
| 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
- Global Fields: sélectionner les champs des événements à capturer
- Data Storage
- event_file → C:\Folder\SqlTrace.xel
- Configure
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
|
|
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
|
|
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.
|
|
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
|
|
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.
|
|
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
|
// 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.
