SelectEtoile : Base de connaissance SGBD

Posts Tagged 'profiler'

A propos des Trace Profiler

Plusieurs possibilités sont disponibles pour lancer une trace SQL Server. On peut gérer avec l'outil SQL Server Profiler ou directement en ligne de commande.

Gestion des Traces avec SQL Server Profiler

 Il existe pleins d'articles sur le sujet sur le net...

 

Gestion d'une Trace en ligne de commande

Création d'une trace

declare @TraceFileName nvarchar(256)
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
set @TraceFileName = 'D:\admin\logs\SQLServerJPS.trc'

exec sp_trace_create @TraceID output, 0, @TraceFileName, @maxfilesize, NULL 

 

Visualisation des Traces existantes
select * from sys.traces

Exemple de résultat :

id    status    path    max_size    stop_time    max_files  

1    1    D:\SQLSERVER\MSSQL10.SQLSERVER\MSSQL\Log\log_8.trc    20    NULL    5

2    1    C:\Temp\TraceAuditLogin_SQLSERVER.trc    50    NULL    1   

3    0    D:\admin\logs\SQLServerJPS.trc.trc    50    NULL    1

Ici, nous avons 2 traces. La trace avec l'id 1 est la trace par défaut de SQLServer (Voir paragraphe en dessous).

La trace 2 est active (status=1) et se fait dans un fichier 'C:\Temp\TraceAuditLogin_SQLSERVER.trc'.

Notre trace est la 3. Elle est d'ailleurs inactive pour le moment. Il nous faut, avant de l'activer, définir les événements que l'on veut tracer.

 

Définition des événements à tracer

La procédure décrite ci dessous permet d'ajouter des événements sur une trace. On va pouvoir ajouter / supprimer des événements.

Les tables de descriptions des différents événements sont développées ici

Exemple :

declare @on bit
declare @TraceID int
set @TraceID = 3
set @on = 1
    exec sp_trace_setevent @TraceID, 14, 3, @on
    exec sp_trace_setevent @TraceID, 14, 11, @on
    exec sp_trace_setevent @TraceID, 14, 23, @on
    exec sp_trace_setevent @TraceID, 14, 8, @on
    exec sp_trace_setevent @TraceID, 14, 10, @on
    exec sp_trace_setevent @TraceID, 14, 12, @on
    exec sp_trace_setevent @TraceID, 14, 14, @on
    exec sp_trace_setevent @TraceID, 14, 26, @on
    exec sp_trace_setevent @TraceID, 14, 9, @on
    exec sp_trace_setevent @TraceID, 20, 3, @on
    exec sp_trace_setevent @TraceID, 20, 7, @on
    exec sp_trace_setevent @TraceID, 20, 11, @on
    exec sp_trace_setevent @TraceID, 20, 15, @on
    exec sp_trace_setevent @TraceID, 20, 23, @on
    exec sp_trace_setevent @TraceID, 20, 8, @on
    exec sp_trace_setevent @TraceID, 20, 12, @on
    exec sp_trace_setevent @TraceID, 20, 9, @on
    exec sp_trace_setevent @TraceID, 20, 6, @on
    exec sp_trace_setevent @TraceID, 20, 10, @on
    exec sp_trace_setevent @TraceID, 20, 14, @on
    exec sp_trace_setevent @TraceID, 20, 26, @on
    exec sp_trace_setevent @TraceID, 15, 7, @on
    exec sp_trace_setevent @TraceID, 15, 15, @on
    exec sp_trace_setevent @TraceID, 15, 23, @on
    exec sp_trace_setevent @TraceID, 15, 8, @on
    exec sp_trace_setevent @TraceID, 15, 12, @on
    exec sp_trace_setevent @TraceID, 15, 9, @on
    exec sp_trace_setevent @TraceID, 15, 6, @on
    exec sp_trace_setevent @TraceID, 15, 10, @on
    exec sp_trace_setevent @TraceID, 15, 14, @on
    exec sp_trace_setevent @TraceID, 15, 26, @on
    exec sp_trace_setevent @TraceID, 15, 3, @on
    exec sp_trace_setevent @TraceID, 15, 11, @on

 

Activer notre trace
declare @TraceID int
set @TraceID = 3
exec sp_trace_setstatus @TraceID, 1

Vérification

select * from sys.traces

Exemple de résultat :

id    status    path    max_size    stop_time    max_files  

1    1    D:\SQLSERVER\MSSQL10.SQLSERVER\MSSQL\Log\log_8.trc    20    NULL    5

2    1    C:\Temp\TraceAuditLogin_SQLSERVER.trc    50    NULL    1   

3    1    D:\admin\logs\SQLServerJPS.trc.trc    50    NULL    1

On voit alors que notre trace est active.

 

 

Filtrer une trace

Exemple:

set @intfilter = 2
exec sp_trace_setfilter @TraceID, 21, 0, 1, @intfilter

 

Supprimer une trace

declare @TraceID int
set @TraceID = 3
exec sp_trace_setstatus @TraceID, 2

-- To check

select * from sys.traces

 

Trace par défaut

Par défaut depuis SQLServer version 2005, il est paramétré automatiquement une trace.

Pour le vérifier taper les commandes suivantes :

sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'default trace enabled'
go

 

-- For informations about the default trace

SELECT * FROM ::fn_trace_getinfo(default)

 

 

Les tables et fonctions systèmes

sys.traces : liste les traces et leur statut

sys.trace_events : événements à rattacher aux différentes traces

fn_trace_getinfo() : info sur une trace (équivalent à une mise en forme de sys.traces)

fn_trace_gettable()

 

Exemple complet d'une trace :

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 01/25/2013  08:09:18 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'd:\perfmon\traceprofiler_liqor', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 96abcad4-651d-4c18-a711-7399bcf6cd47'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

 

 

 

 

Les liens pratiques de SQL Server

Cet article contient une liste de sites web pratiques pour l'administration SQLServer

 

Les docs Microsoft SQLServer

Les vidéos microsoft : http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx

 

Gestion du journal de log

Log Management :
http://www.sqlservercentral.com/articles/Stairway+Series/94552/
http://www.sqlservercentral.com/articles/Transaction+Logs/71415/

Log Tuning : http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/

 

Monitoring :

SQLServer Creation d'une alerte/montoring SQLServer : http://www.sqlservercentral.com/articles/SQL+Monitoring/100615/

 

Les partitions

Gestion des partitions : http://www.simple-talk.com/content/article.aspx?article=1587

Ex d'un switch et split de partition : http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/08/13/oops-i-forgot-to-leave-an-empty-sql-table-partition-how-can-i-split-it-with-minimal-io-impact.aspx

 

Les indexes

https://www.simple-talk.com/sql/performance/14-sql-server-indexing-questions-you-were-too-shy-to-ask/?utm_source=ssc&utm_medium=publink&utm_content=indexingquestions

 

Trace Profiler

Mise en place de trace profiler sous SQL SERVER : http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

 

AlwaysOn :

http://blogs.msdn.com/b/sqlcat/archive/2014/02/03/alwayson-availability-groups-listener-named-instances-port-numbers-etc.aspx

 

Performances Monitoring :

http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm

http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

https://www.simple-talk.com/sql/database-administration/baselining-with-sql-server-dynamic-management-views/

https://www.simple-talk.com/sql/database-administration/eight-steps-to-effective-sql-server-monitoring/

 

Pratique

Comment regrouper un RTM et un SP2 sous SQLServer 2008 (slipstream) : http://blogs.msdn.com/b/petersad/archive/2010/10/14/creating-a-merged-slipstream-drop-containing-sql-server-2008-rtm-and-service-pack-2.aspx

Faire de la pagination sous SQLServer : http://www.databasejournal.com/features/mssql/paging-data-with-tsql.html

 

Change Data Capture

http://www.databasejournal.com/features/mssql/getting-starting-with-change-data-capture-cdc-in-sql-server-part-1.html

Les deadlocks

Surveiller les deadlocks : http://www.opsvault.com/how-to-monitor-deadlocks-in-sql-server/

Des exemples tres detaillés : https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/?utm_source=ssc&utm_medium=publink&utm_campaign=simpletalk&utm_term=additionalarticle

 

Tout sur la base tempdb :

tempdb contention : http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/

 

Hashage / Cryptage :

http://blog.developpez.com/sqlpro/p12496/langage-sql-norme/hachage-nest-pas-cryptage-de-la-securite-des-donnees-chiffrees-dans-les-sgbdr

 

Encryption / Chiffrement...

http://www.dbi-services.com/index.php/blog/entry/transparent-data-encryption-key-management-and-backup-strategies

 

Les traceflags :

Les traceflag sous SQLServer : http://www.sqlservercentral.com/articles/trace+flags/70131/

 

Sauvegarde et restauration

Backup / restore avec powershell : https://www.simple-talk.com/content/article.aspx?article=1802

 

SQLServer 2014 :

New Features : http://www.databasejournal.com/features/mssql/whats-new-with-sql-server-2014.html

AlwaysOn 2014 : http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/27/alwayson-in-sql-server-2014-ctp1.aspx

 

Performances sur les disques :

SSD : http://henkvandervalk.com/maximizing-sql-server-2008-r2-table-scan-speed-from-dsi-solid-state-storage

Tester ces io : https://www.simple-talk.com/sql/database-administration/the-sql-server-sqliosim-utility/

 

Hekaton :

Procedure stockée en code natif : http://msdn.microsoft.com/fr-fr/library/dn424864.aspx

gestion des tables Hekaton : http://conseilit.wordpress.com/2013/07/01/sql-server-2014base-de-donnes-et-premire-table-hekaton/

http://blog.developpez.com/mikedavem/p12490/sql-server-2014/tables-in-memory-indexes-hash-et-paramtrage-du-nombre-de-buckets

 

Perfs et tuning :

Parameter sniffing : http://www.sqlservercentral.com/articles/Parameter+Sniffing/98481/


Gestion des licences

http://www.sqlservercentral.com/articles/108317/

http://conseilit.wordpress.com/2011/11/04/licences-sql-server-2012/
http://blogs.msdn.com/b/editeurs_de_logiciels/archive/2013/10/30/licensing-comprendre-le-mode-de-fonctionnement-des-licences-sql-server-2012.aspx

 http://conseilit.wordpress.com/2011/11/04/licences-sql-server-2012/

 

Quelques outils utiles

SQL Query Stress :

http://www.mssqltips.com/sqlservertip/2730/sql-query-stress-tool/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012717

 

 

http://sqlquerystress.software.informer.com/download/

 

 

Quelques exemples pratiques de code SQL sous SQLServer :

Les fonction window in :

https://www.simple-talk.com/sql/t-sql-programming/window-functions-in-sql/

https://www.simple-talk.com/content/article.aspx?article=1927

 

Des scripts SQL et powershell :

http://gallery.technet.microsoft.com/scriptcenter/site/search?f[0].Type=RootCategory&f[0].Value=databases&f[0].Text=Databases&f[1].Type=SubCategory&f[1].Value=sqlserver&f[1].Text=SQL%20Server

 

Les sites et blogs utiles :

http://thesqlagentman.com/

http://drsql.org

http://www.simple-talk.com/

http://thomaslarock.com/

http://dba.stackexchange.com/

http://brentozar.com

http://www.scarydba.com/

 

Utilisation du Profiler SQLServer

SQLServer Trace Profiler permet de tracer l'activité d'un serveur SQLServer, Analysis Services... Il peut être pratique pour récupérer les ordres SQL, le temps d'exécution, les entrées / sorties...

 

Lancement en Ligne de commande

Cmd :

profiler /E /A WinServer\ASServer /T"Standart" /O c:\jp\temp\ASServer.trc /M "02-19-10 16:40:00"

Description des options : cliquer ici

 

Trace profiler par défaut :

Il existe une trace par défaut sur SQLServer. Pour la désactiver, il suffit de lancer les commandes suivantes :

EXEC master.dbo.sp_configure 'allow updates', 1;

GO

EXEC master.dbo.sp_configure 'show advanced options', 1;

GO

EXEC master.dbo.sp_configure 'default trace enabled', 0;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC master.dbo.sp_configure 'show advanced options', 0;

GO

EXEC master.dbo.sp_configure 'allow updates', 0;

GO

 

Trace des requêtes et procédures stockées

Pour tracer les requêtes et les procédures stockées, j'ai choisi de  tracer les events suivants :

  • Stored Procedures
    • RPC:Completed
    • SP:Completed
    • SP:StmtCompleted
  • TSQL
    • Exec Prepared SQL
    • Prepare SQL
    • SQL:StmtCompleted
    • Unprepare SQL

Dans le cas ou nous choisissons de sauvegarder (et donc de rediriger) dans une table, il suffit d'aller dans Trace properties / onglet General, cocher la case 'save to table'. Il sera alors facile de requêter sur cette table pour analyser les compteurs.

Nous redirigeons le résultat vers la table perfs_jps..trace_queries

La requête suivante permet de visualiser ce que le ClientProcessID 6772 lance comme requête et procédures stockées

select StartTime,EndTime,Duration,RowCounts,ObjectName,LineNumber,TextData

from perfs_jps..trace_queries

where ClientProcessID=6772 order by StartTime