Le Service broker de SQL Server est un service permettant de lancer des traitements en asynchrone. Celà peut donc être intéressant pour rendre rapidement la main à un utilisateur sans avoir à attendre le résultat de calculs longs.

Toutefois, il peut arriver que l’implémentation soit défaillante et le diagnostic du problème peut être ardu car les traitements sont asynchrones et il faut aller chercher l’information dans des vues systèmes.

Après plusieurs recherches, voilà une sélection de deux articles intéressants sur le sujet des brokers:

Understanding Queue Monitors

Monitoring Service Broker

Et sinon utiliser ma Procédure stoquée : USP_CHECK_BROKER

qui va permettre d’émettre un diagnostic : EXEC USP_CHECK_BROKER @VERBOSE=1

ou d’effectuer une réparation du broker : EXEC USP_CHECK_BROKER @REPAIR=1

AddLinkedServer.sql

Procedure to make create of SQL Server Linked server easier

KillProcess

Kill all actives connections on a database.

p_alter_column

Stored procedure will make the job of altering the type of an SQL Server Table

p_Backup_DB

Procedure in order to Quick launch the backup of a database

VIEW_DEFAULT_CONSTRAINTS

get the details of the defaults constaints

p_drop_column

Easyly drop a column.

p_drop_defaultconstraint

Drop the default constraint associated to a column

p_drop_primaryKey

drops the primary key of a table.

p_export_csv

Export a Query to CSV Format

p_MaintenancePlan

Quick maintenance plan for a database.

p_rebuild_indexes

rebuild the all the indexes of a databases

p_restoreBackup

Will restore a backup from a file.

p_shrinkDB

Shrinks the current database

p_tableAllocation

Will give the size of tables and indexed and number of rows of each table.
This allow to understand how the size is used inside a database.

p_waits

Detect the processes actually waiting for the release of a lock

VIEW_COLUMNS_INDEXES

list the columns included in indexes.

Afin de minimiser la place prise par les données, il convient de choisir le type le mieux adaptée.
Smalldatetime au lieu de DateTime

Il est inutile et coûteux d’utiliser un type datetime lorsqu’un smalldatetime est suffisant.
Une colonne de type DateTime prend 8 Octets alors qu’un smalldatetime prend 4 octets.
On économise donc 4 octets par ligne.

Les colonnes de types datetime peuvent contenir des valeurs du 1er janvier 1753 au 31 décembre 9999 avec une précision de 3,33 millisecondes

Les colonnes de types smalldatetime peuvent contenir des valeurs du 1er janvier 1900 au 6 juin 2079 avec une précision à la minute

Choisir le type entier Correct

Les entiers peuvent êtres stockées dans plusieurs types de colonnes :

https://github.com/alexiscomte/SQL/blob/master/capit_scripts.sql

Capit_COLUMNS_DEFAULTS
La vue « Capit_COLUMNS_DEFAULTS » est basée sur les vues système SQL Server. Elle permet de ramener facilement les valeurs par défaut des colonnes d’une base.

Capit_sp_ConvertFormat
Capit_sp_ConvertFormat permet de convertir une colonne d’un type vers un autre

Capit_sp_DateTimeToSmallDateTime
Capit_sp_DateTimeToSmallDateTime permet de tranformer une colonne de type DateTime en une colonne de Type smalldatetime.
Une colonne de type DateTime prend 8 Octets alors qu’un smalldatetime prend 4 octets. On economise donc 4 octets par ligne.
Les colonnes de types datetime peuvent contenir des valeurs du 1er janvier 1753 au 31 décembre 9999 avec une précision de 3,33 millisecondes
Les colonnes de types smalldatetime peuvent contenir des valeurs du 1er janvier 1900 au 6 juin 2079 avec une précision à la minute

capit_sp_ConvertAllColumns
capit_sp_ConvertAllColumns permet de tranformer toutes les colonnes d’un type particulier.
Celà peut être utile lorsque l’on veut transformer toutes les colonnes de type DateTime en smalldatetime.

Capit_sp_DropColumn
Procédure permettant de supprimer une colonne d’une table

Ci dessous, un script powershell permettant de modifier l’encodage des fichiers en UTF8 puis de les formatter avec l’outil d’APEX. A noter que j’ai spécifier un profil ADS personnalisé dans APEX formatter qu’il vous faudra donc changer.

Pour que ce script fonctionne, il faut également avoir installé GIT dont je me sers pour détecter l’encodage des fichiers.

https://github.com/alexiscomte/SQL/blob/master/Convert-Encoding.ps1

Exemple d’appel :

powershell.exe .\Convert-Encoding.ps1 "c:\sql\SQL" "c:\TEMP\SQL" 

Le convert est beaucoup plus rapide que le format

declare @dStart datetime = getdate()
declare @compteur int = 0
declare @convert varchar(30)
set @compteur = 1
set @dStart = getdate()
while @compteur < 10000
begin
    set @convert = convert(varchar,getdate(),103)
    set @compteur = @compteur + 1
end
print @convert
print 'convert ' + left( datediff(ms,@dstart,getdate()), 40)
set @compteur = 1
set @dStart = getdate()
while @compteur < 10000
begin
    set @convert = format(getdate(),'dd/MM/yyyy')
    set @compteur = @compteur + 1
end
print @convert
print 'format ' + left( datediff(ms,@dstart,getdate()), 40)

Le script ci dessous permet de créer un évènement étendu pour surveiller les Dead Locks

https://github.com/alexiscomte/SQL/blob/master/DEADLOCK_EVENT.sql

Les résultats peuvent ensuite être consultés via SSMS

le DateFromParts est plus performant  

DECLARE @compteur INT;
DECLARE @date DATETIME;
DECLARE @dStart DATETIME;
DECLARE @msString INT = 0;
DECLARE @msdate INT = 0;
DECLARE @msfunc INT = 0;
SET @dStart = GETDATE();
DECLARE @boucle INT;
SET @boucle = 1;
WHILE @boucle < 10
BEGIN

    SET @compteur = 1;
    WHILE @compteur < 10000
    BEGIN
        SET @Date = '19000101';
        SET @compteur = @compteur + 1;
    END;
    SET @msString = @msString + DATEDIFF(ms, @dStart, GETDATE());
    SET @dStart = GETDATE();
    SET @compteur = 1;
    WHILE @compteur < 10000
    BEGIN
        SET @Date = Datefromparts(1900,01,01)
        SET @compteur = @compteur + 1;
    END;
    SET @msdate = @msdate + DATEDIFF(ms, @dStart, GETDATE());
    SET @boucle = @boucle + 1;
END;
PRINT 'String ' + LEFT(@msString, 20) + ' ms ';
PRINT 'Date ' + LEFT(@msdate, 20) + ' ms ';

Après un peu de recherche,  le mieux pour éviter les locks sont les curseurs

LOCAL STATIC READ_ONLY FORWARD_ONLY  

Voici quelques articles intéressants sur le sujet :  

https://blogs.msdn.microsoft.com/sqlqueryprocessing/2009/08/12/understanding-sql-server-fast_forward-server-cursors/

https://sqlperformance.com/2012/09/t-sql-queries/cursor-options

https://sqlundercover.com/2017/11/16/sql-smackdown-cursors-vs-loops/

https://www.sqlservercentral.com/forums/topic/fast_forward-cursor-blocking-issue