J’avais tenter d’expliquer le fonctionnement et l’audit des indexes et des statistiques dans ce post.

Pour aller plus loin sur ce sujet, je voulais partager les possibilités que nous propose le site https://ola.hallengren.com?

Ce site nous délivre des scripts afin de créer tous les objets (procédures stockées, tables d’administrations, jobs) dont nous avons besoin pour mettre en place le plan de maintenance de nos instances SQL server : voir le lien « MaintenanceSolution.sql ».

Ce site propose également une documentation des options, des paramètres que l’on peut configurer pour s’approprier ces scripts : voir le menu de gauche

Vous pourrez adapter le script « MaintenanceSolution.sql » en fonction de vos besoins :

C’est vraiment pas mal d’être accompagné pour ce genre de tâches…

Si vous avez des tips sur les plans de maintenances SQL server…N’hésitez pas à laisser des commentaires!

« select
exe.UserName,
exe.TimeStart,
exe.Format,
cat.Path,
cat.Name
from
[ReportServer].[dbo].[ExecutionLogStorage] exe,
[ReportServer].[dbo].[Catalog] cat
where
cat.ItemID = exe.ReportID  »

Avec ces tables systèmes SSRS, 60 jours de logs sont disponibles par défaut. En vous connectant à l’instance SSRS et en faisant un clique droit> propriétés vous pourrez modifier le nombre de jours assez facilement et le fait qu’il y ait ces logs.

Utilisez vous d’autres requêtes pour bien cerner l’utilisation de votre plateforme de rapports ?

• batch mode adaptive joins
• batch mode memory grant feedback
• interleaved execution for multi-statement table valued functions

Pour activer cette option ALTER DATABASE [mon_datawarehouse] SET COMPATIBILITY_LEVEL = 140;

Rappel rapide du fonctionnement d’SQL server sur l’exécution des requêtes :

• Etape 1 : Liste de divers scénarios de plan d’exécution.
• Etape 2 : estimation en coût (nb de lignes, en % CPU) de ces divers scénarios => ce coût calculé peut-être faux si les dernières statistiques ne sont pas à jour par exemple. Pour en savoir plus sur ces coûts, voici un petit article sympa
• Etape 3 : selection du scénario  le plus faible en coût d’exécution
• Etape 4 : exécution de la requête en fonction de ce plan d’exécution sélectionné

Détaillons maintenant chaque option :

le batch mode adaptive joins :

SQL server utilise actuellement 3 algorithmes pour procéder à une jointure :  »nested loop », « merge », et  « hash match ». Pour choisir le plus approprié, SQL server se base sur les coûts estimés.

Grâce à cette nouvelle option voici comment va fonctionner SQL server 2017 : si nb lignes de l’entrée de la jointure  <= au seuil défini par cette option alors « nested loop » sinon « hash match ». Cette analyse est faite dynamiquement lors de l’exécution de la requête.

Note : La jointure  »hash match » est utilisée dans ce cas lorsqu’

• un index columnstore est présent dans la requête globale
• ou quand la jointure référence directement la table d’index columnstore

le batch mode memory grant feedback :

Cette option permet de calculer pour chaque requête la mémoire nécessaire à l’exécution de cette dernière et de mettre à jour le  « cached plan » (l’endroit ou les plans d’exécution sont mis en cache pour réutilisation si une requête similaire venait à être exécutée). La gestion de la mémoire nécessaire à l’exécution des requêtes est mieux gérée. Une trop grande allocation mémoire bloquante pour d’autres requêtes ou une allocation mémoire trop juste ralentissant les performances de la requête ne seront plus possibles. Cette option nécessite l’utilisation des « columnstore indexes  »

voici un exemple bien mené pour comprendre avec ou sans cette option

Interleaved execution for multi-statement table valued functions :

Ce genre de fonction existait déjà sur les versions précédentes SQL. Elle a été améliorée dans SQL server 2017 afin d’augmenter son efficacité. Les couts d’exécution sont calculés sur chaque sous requête de la fonction et donc propose un plan d’exécution plus précis et plus performant sur le nombre de lignes à prendre en compte et donc la mémoire à allouée.

Avez vous déjà activées et utilisées ces options?

Voici ma source de données

La BI traditionnelle n’en sera pas évincée pour autant car le croisement de divers sources d’informations fait qu’un datawarehouse et des traitements d’agrégations aux préalables seront toujours nécessaires. Ce nouveau système nous permet juste, d’avoir une souplesse en plus, une nouvelle possibilité de satisfaire les demandes opérationnelles de nos utilisateurs.

L’analytique en temps réel utilise principalement les « columnstore index »  modifiables sur une table rowstore. Sur le site de Microsoft cette partie est vraiment bien forumlée : « Le « columnstore index » gère une copie des données pour que les charges de travail OLTP et analytiques soient exécutées sur des copies distinctes des données. Cela réduit l’impact sur les performances de ces deux charges de travail en cours d’exécution en même temps. SQL Server gère automatiquement les modifications d’index pour que les changements OLTP soient toujours à jour pour l’analytique. Grâce à cette conception, il est possible et pratique d’exécuter l’analytique en temps réel sur des données à jour. Cela fonctionne aussi bien pour les tables sur disque que pour les tables optimisées en mémoire. »

Que pensez vous de cette super option?

voici ma source

• les chargements  et le stockage en masse : une compression des données 10 fois supérieure à la taille des données non compressées. Ces taux de compression améliorent les performances des requêtes en utilisant moins de mémoire et donc en permettant à SQL Server d’exécuter davantage d’opérations.
• les requêtes en lecture seule : des requêtes 10 fois plus performantes  en comparaison à un stockage orienté lignes traditionnel

Détails techniques intéressants :

• le « cluster columnstore index » est modifiable et accompagnera tous les traitements massifs d’insertions, mises à jour  et suppressions.
• un nouveau mécanisme d’exécution de requête, appelé « exécution en mode batch » a été ajouté à SQL Server pour réduire considérablement l’utilisation de l’UC

Scénarios BI :

Cas où cela peut être intéressant :

• pour le stockage de grandes « tables de faits » ou « tables de dimensions »
• pour les requêtes qui utilisent des analyses de table complètes

Cas où cela ne convient pas :

• pour les requêtes qui effectuent des opérations de recherche de données sur une valeur particulière par exemple.

L’avez vous déjà mis en œuvre? Et dans quel cas?

Voici mes sources :
source 1
source 2
 

Je vous propose lors des articles à venir de décrire ces options en restant sur un angle BI pour une lecture plus facile et orientée.

Commençons donc par l’option « In Memory OLTP ». Cette option apporte performance pour les traitements transactionnels en :
- supprimant des verrous entre les transactions exécutées simultanément.
- optimisant en mémoire, les algorithmes de stockage des données, les accès et les traitements des données.

Les objets qui permettent de bénéficier de ces performances sont :

• Les « Memory optimized tables » : ces types de tables sont utilisées par exemple
  • pour les « table-valued parameters » : permettant de faire du chargement en bulk de plusieurs lignes de données en une fois.
  • dans les procédures stockées, pour les jeux de données intermédiaires stockés dans des tables de type de « Memory optimized tables » . Ces dernières hériteront des avantages des « non durable memory optimized tables  » : accès efficace aux données et absence d’E/S

Ce type d’objet ne réduit pas l’utilisation du processeur.
(si la plupart des requêtes effectuent des opérations d’agrégation sur de grandes plages de données  : utiliser plutôt des index columnstore)

• Les « non durable tables memory optimized tables » : Le stockage principale de ces tables est la mémoire principale. Les modifications apportées à ces tables n’entraînent aucune E/S
• Les « natively compiled T-SQL modules » (procédures stockées, déclencheurs et fonctions scalaires ) permettent d’accélérer encore plus l’exécution d’une transaction individuelle en réduisant les cycles processeur requis pour traiter les opérations.

Les scénarios BI qui peuvent nous intéresser :

Use case 1 : Remplacer certains objets standards de la tempdb :
- les « tables temporaires » (#temp) par les « Non durable tables memory optimized tables » qui réduisent généralement l’utilisation du processeur
- ou encore les « variables de table » par les « table-valued parameters » qui suppriment complètement les E/S de journal.

Use case 2 : au niveau de l’ETL

- Pour charger et mettre à jour d’importants volumes de données provenant de nombreuses sources différentes : utiliser des « non durable tables memory optimized tables » pour la mise en lots des données. Elles suppriment complètement les E/S et optimisent l’efficacité de l’accès aux données.

-Pour accélérer les transformations sur la table de mise en lots : utiliser des « natively compiled T-SQL modules« . De plus pour toujours plus de performance, il sera possible de paralléliser ces transformations.

J’espère que ce point est un peu plus clair…Dites moi ce que vous en pensez…Si vous avez déjà utilisés ces options…

Voici dans cet article une comparaison des différentes versions d’SQL server. On constate vraiment les avancés technologiques faites en 10 ans. Le consultant BI se doit d’être un couteau suisse.

J’ai retranscrit sur Excel les options car je trouvais ça plus lisible. Voici ma source de données.

En creusant le sujet, j’ai pu lire sur les forums que cette taille qui augmente pouvait avoir 4 causes:
- Cause 1 : le nombre de snapshot stockés par rapport
- Cause 2 : des rapports qui mettent plus de 10 minutes à s’exécuter
- Cause 3 : la non exécution de la procédure de clean up toutes les 10 minutes.
- Cause 4 : une base qui existe depuis longtemps qui n’a jamais été cleanée

- Solution 1 :
Etape 1 : Aller sur le « report manager », allez dans « Site Settings », et cocher « Limit the copies of report history to : 10  » au lieu du paramètre par défaut.
Etape 2 :Ensuite faire du ménage sur votre plateforme de rapport s’il y en a besoin en supprimant des snapshots non utilisés.

- Solution 2 :
Revoir ces rapports qui prennent trop de temps. Aller sur la base « Reportservertempdb » et exécuter la requête suivante pour les identifier :
 »
select Name, exe.UserName, exe.TimeStart, exe.TimeEnd, DATEDIFF(second,exe.TimeStart, exe.TimeEnd) as DureeEnSeconde
from [ReportServer].[dbo].[ExecutionLogStorage] exe,
[ReportServer].[dbo].[Catalog] cat
where cat.ItemID = exe.ReportID
ORDER BY DATEDIFF(second,exe.TimeStart, exe.TimeEnd) desc »

- Solution 3 :
Aller dans le fichier de Log de votre instance SSRS et regarder s’il y a un message d’erreur concernant la tâche CleanBatch

- Solution 4 :
Possibilité 1 : Vous pouvez choisi cette première possibilité peu extrême et faire ces étapes :
Etape 1 : éteindre le service report serveur
Etape 2 : exécuter un TRUNCATE des tables
Etape 3 : faire un shrink des fichiers de base de données
Etape 4 : redémarrer le service report serveur

Possibilité 2 : ou exécuter ce script pour un nettoyage en douceur :
 »
begin transaction

declare @cleanedSnapshots table (SnapshotDataId uniqueidentifier) ;
declare @cleanedChunks table (ChunkId uniqueidentifier) ;
declare @cleanedSegments table (ChunkId uniqueidentifier, SegmentId uniqueidentifier) ;
declare @deleteCount int ;

insert into @cleanedSnapshots
select distinct SnapshotDataId
from SegmentedChunk
where SnapshotDataId not in (select SnapshotDataID from SnapshotData)

– clean up chunks
set @deleteCount = 1 ;
while (@deleteCount > 0)
begin
delete top(20) SC
output deleted.ChunkId into @cleanedChunks(ChunkId)
from SegmentedChunk SC with (readpast)
join @cleanedSnapshots cs on SC.SnapshotDataId = cs.SnapshotDataId ;
set @deleteCount = @@ROWCOUNT ;
end ;

– clean up unused mappings
set @deleteCount = 1 ;
while (@deleteCount > 0)
begin
delete top(20) CSM
output deleted.ChunkId, deleted.SegmentId into @cleanedSegments (ChunkId, SegmentId)
from ChunkSegmentMapping CSM with (readpast)
join @cleanedChunks cc ON CSM.ChunkId = cc.ChunkId
where not exists (
select 1 from SegmentedChunk SC
where SC.ChunkId = cc.ChunkId )
and not exists (
select 1 from [ReportServerTempDB].dbo.SegmentedChunk TSC
where TSC.ChunkId = cc.ChunkId ) ;
set @deleteCount = @@ROWCOUNT ;
end ;

– clean up segments
set @deleteCount = 1
while (@deleteCount > 0)
begin
delete top(20) S
from Segment S with (readpast)
join @cleanedSegments cs on S.SegmentId = cs.SegmentId
where not exists (
select 1 from ChunkSegmentMapping csm
where csm.SegmentId = cs.SegmentId ) ;
set @deleteCount = @@ROWCOUNT ;
end

commit
 »

Possibilité 3 :ou encore exécuter :
DBCC CLEANTABLE (ReportServerTempDB,’dbo.Segment’, 0)
WITH NO_INFOMSGS;

Et vous? Avez vous déjà rencontré ce problème?