[SQL Server] Compteurs de performance, afficher certains ...

CREATE PROCEDURE [dbo].[usp_Admin_conpteursPerf]

      -- Add the parameters for the stored procedure here

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

      IF object_id('tempdb..#OSPC') IS NOT NULL BEGIN

    DROP TABLE #OSPC

END

IF object_id('tempdb..#OSPerfCounters') IS NOT NULL BEGIN

    DROP TABLE #OSPerfCounters

END

IF object_id('tempdb..#OSPerfCountersLast') IS NOT NULL BEGIN

    DROP TABLE #OSPerfCountersLast

END

IF object_id('tempdb..#ObjetsPerformances') IS NOT NULL BEGIN

    DROP TABLE #ObjetsPerformances

END

CREATE TABLE #ObjetsPerformances

(

 Objet_Perf NVARCHAR(100),

 Nom_Objet_Perf NVARCHAR(100),

 DecriptionFrancais NVARCHAR(MAX),

 DecriptionAnglais NVARCHAR(MAX),

 Commentaire NVARCHAR(MAX)

)

CREATE TABLE #OSPerfCounters(

    DateAdded datetime NOT NULL

    , Batch_Requests_Sec int NOT NULL

    , Cache_Hit_Ratio float NOT NULL

    , Free_Pages int NOT NULL

    , Lazy_Writes_Sec int NOT NULL

    , Memory_Grants_Pending int NOT NULL

    , Deadlocks int NOT NULL

    , Page_Life_Exp int NOT NULL

    , Page_Lookups_Sec int NOT NULL

    , Page_Reads_Sec int NOT NULL

    , Page_Writes_Sec int NOT NULL

    , SQL_Compilations_Sec int NOT NULL

    , SQL_Recompilations_Sec int NOT NULL

    , ServerMemoryTarget_KB int NOT NULL

    , ServerMemoryTotal_KB int NOT NULL

    , Transactions_Sec int NOT NULL

)

--You'll typically only query this by one value, which is added sequentually.  No page splits!!!

CREATE UNIQUE CLUSTERED INDEX IX_#OSPerfCounters_DateAdded_U_C ON #OSPerfCounters

(

    DateAdded

) WITH (FillFactor = 100)

--Only holds one value at a time, indexes are a waste

CREATE TABLE #OSPerfCountersLast(

    DateAdded datetime NOT NULL

    , BatchRequests bigint NOT NULL

    , LazyWrites bigint NOT NULL

    , Deadlocks bigint NOT NULL

    , PageLookups bigint NOT NULL

    , PageReads bigint NOT NULL

    , PageWrites bigint NOT NULL

    , SQLCompilations bigint NOT NULL

    , SQLRecompilations bigint NOT NULL

    , Transactions bigint NOT NULL

)

DECLARE @FirstCollectionTime DateTime

    , @SecondCollectionTime DateTime

    , @NumberOfSeconds Int

    , @BatchRequests Float

    , @LazyWrites Float

    , @Deadlocks BigInt

    , @PageLookups Float

    , @PageReads Float

    , @PageWrites Float

    , @SQLCompilations Float

    , @SQLRecompilations Float

    , @Transactions Float

DECLARE @CounterPrefix NVARCHAR(30)

SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'

                            THEN 'SQLServer:'

                        ELSE 'MSSQL$' + @@SERVICENAME + ':'

                        END

--Grab the current values from dm_os_performance_counters

--Doesn't do anything by instance or database because this is good enough and works unaltered in all envirornments

SELECT counter_name, cntr_value--, cntr_type --I considered dynamically doing each counter type, but decided manual was better in this case

INTO #OSPC

FROM sys.dm_os_performance_counters

WHERE object_name like @CounterPrefix + '%'

    AND instance_name IN('', '_Total')

    AND counter_name IN( N'Batch Requests/sec'

                        , N'Buffer cache hit ratio'

                        , N'Buffer cache hit ratio base'

                        , N'Free Pages'

                        , N'Lazy Writes/sec'

                        , N'Memory Grants Pending'

                        , N'Number of Deadlocks/sec'

                        , N'Page life expectancy'

                        , N'Page Lookups/Sec'

                        , N'Page Reads/Sec'

                        , N'Page Writes/Sec'

                        , N'SQL Compilations/sec'

                        , N'SQL Re-Compilations/sec'

                        , N'Target Server Memory (KB)'

                        , N'Total Server Memory (KB)'

                        , N'Transactions/sec')

--Just collected the second batch in the query above

SELECT @SecondCollectionTime = GetDate()

--Grab the most recent values, if they are appropriate (no reboot since grabbing them last)

SELECT @FirstCollectionTime = DateAdded

    , @BatchRequests = BatchRequests

    , @LazyWrites = LazyWrites

    , @Deadlocks = Deadlocks

    , @PageLookups = PageLookups

    , @PageReads = PageReads

    , @PageWrites = PageWrites

    , @SQLCompilations = SQLCompilations

    , @SQLRecompilations = SQLRecompilations

    , @Transactions = Transactions

FROM #OSPerfCountersLast

WHERE DateAdded >(SELECT create_date FROM sys.databases WHERE name = 'TempDB')

--If there was a reboot then all these values would have been 0 at the time the server came online (AKA: TempDB's create date)

SELECT @FirstCollectionTime = ISNULL(@FirstCollectionTime,(SELECT create_date FROM sys.databases WHERE name = 'TempDB'))

    , @BatchRequests = ISNULL(@BatchRequests, 0)

    , @LazyWrites = ISNULL(@LazyWrites, 0)

    , @Deadlocks = ISNULL(@Deadlocks, 0)

    , @PageLookups = ISNULL(@PageLookups, 0)

    , @PageReads = ISNULL(@PageReads, 0)

    , @PageWrites = ISNULL(@PageWrites, 0)

    , @SQLCompilations = ISNULL(@SQLCompilations, 0)

    , @SQLRecompilations = ISNULL(@SQLRecompilations, 0)

    , @Transactions = ISNULL(@Transactions, 0)

SELECT @NumberOfSeconds = DATEDIFF(ss, @FirstCollectionTime, @SecondCollectionTime)

--I put these in alphabetical order by counter_name, not column name.  It looks a bit odd, but makes sense to me

--Deadlocks are odd here.  I keep track of the number of deadlocks in the time period, not average number of deadlocks per second.

  --AKA, I keep track of things the way I would refer to them when I talk to someone.  "We had 2 deadlocks in the last 5 minutes", not "We averaged .00002 deadlocks per second there"

INSERT INTO #OSPerfCounters(DateAdded, Batch_Requests_Sec, Cache_Hit_Ratio, Free_Pages, Lazy_Writes_Sec, Memory_Grants_Pending

    , Deadlocks, Page_Life_Exp, Page_Lookups_Sec, Page_Reads_Sec, Page_Writes_Sec, SQL_Compilations_Sec, SQL_Recompilations_Sec

    , ServerMemoryTarget_KB, ServerMemoryTotal_KB, Transactions_Sec)

SELECT @SecondCollectionTime

    , Batch_Request_Sec        =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'Batch Requests/sec') - @BatchRequests) / @NumberOfSeconds

    , Cache_Hit_Ratio        =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Buffer cache hit ratio')/(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Buffer cache hit ratio base')

    , Free_Pages            =(SELECT cntr_value FROM #OSPC WHERE counter_name =N'Free pages')

    , Lazy_Writes_Sec        =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'Lazy Writes/sec') - @LazyWrites) / @NumberOfSeconds

    , Memory_Grants_Pending    =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Memory Grants Pending')

    , Deadlocks                =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'Number of Deadlocks/sec') - @Deadlocks)

    , Page_Life_Exp         =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Page life expectancy')

    , Page_Lookups_Sec      =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'Page lookups/sec') - @PageLookups) / @NumberOfSeconds

    , Page_Reads_Sec        =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'Page reads/sec') - @PageReads) / @NumberOfSeconds

    , Page_Writes_Sec        =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'Page writes/sec') - @PageWrites) / @NumberOfSeconds

    , SQL_Compilations_Sec  =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'SQL Compilations/sec') - @SQLCompilations) / @NumberOfSeconds

    , SQL_Recompilations_Sec=((SELECT cntr_value FROM #OSPC WHERE counter_name = N'SQL Re-Compilations/sec') - @SQLRecompilations) / @NumberOfSeconds

    , ServerMemoryTarget_KB =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Target Server Memory (KB)')

    , ServerMemoryTotal_KB  =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Total Server Memory (KB)')

    , Transactions_Sec        =((SELECT cntr_value FROM #OSPC WHERE counter_name = N'Transactions/sec') - @Transactions) / @NumberOfSeconds

TRUNCATE TABLE #OSPerfCountersLast

--Note, only saving the last value for ones that are done per second.

INSERT INTO #OSPerfCountersLast(DateAdded, BatchRequests, LazyWrites, Deadlocks, PageLookups, PageReads

    , PageWrites, SQLCompilations, SQLRecompilations, Transactions)

SELECT DateAdded            = @SecondCollectionTime

    , BatchRequests            =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Batch Requests/sec')

    , LazyWrites            =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Lazy Writes/sec')

    , Deadlocks             =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Number of Deadlocks/sec')

    , PageLookups            =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Page lookups/sec')

    , PageReads                =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Page reads/sec')

    , PageWrites            =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Page writes/sec')

    , SQLCompilations        =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'SQL Compilations/sec')

    , SQLRecompilations        =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'SQL Re-Compilations/sec')

    , Transactions             =(SELECT cntr_value FROM #OSPC WHERE counter_name = N'Transactions/sec')

SELECT 'Today', * FROM (

SELECT TOP 7 *

FROM #OSPerfCounters

ORDER BY dateadded DESC

) X

UNION

SELECT 'Yesterday', * FROM (SELECT TOP 7 *

FROM #OSPerfCounters

WHERE dateadded <= GETDATE()-1

ORDER BY dateadded DESC

) Y

UNION

SELECT 'Last Week', * FROM (

SELECT TOP 7 *

FROM #OSPerfCounters

WHERE dateadded <= GETDATE()-7

ORDER BY dateadded DESC) Z

ORDER BY dateadded DESC

SELECT 'Start' --Give me a rowcount of 1

WHILE @@ROWCOUNT > 0 BEGIN

    DELETE TOP (100000)

    FROM #OSPerfCounters

    where dateadded <(GetDate() - 400)

END

INSERT INTO #ObjetsPerformances

(

 Objet_Perf ,

Nom_Objet_Perf ,

 DecriptionFrancais ,

 DecriptionAnglais ,

 Commentaire

 )

VALUES

--Agent SQL Server

('Agent SQL Server','SQLAgent:Alerts', 'Fournit des informations sur les alertes de l''Agent SQL Server.','Provides information about SQL Server Agent alerts.',NULL)

,('Agent SQL Server','SQLAgent:Jobs', 'Fournit des informations sur les travaux de l''Agent SQL Server.','Provides information about SQL Server Agent jobs.',NULL)

,('Agent SQL Server','SQLAgent:JobSteps', 'Fournit des informations sur les étapes de travail de l''Agent SQL Server.','Provides information about SQL Server Agent job steps.',NULL)

,('Agent SQL Server','SQLAgent:Statistics', 'Fournit des informations générales sur l''Agent SQL Server.','Provides general information about SQL Server Agent.',NULL)

--Service Broker

,('Service Broker','SQLServer:Broker Activation', 'Fournit des informations sur les tâches activées par Service Broker.','Provides information about Service Broker-activated tasks.',NULL)

,('Service Broker','SQLServer:Broker Statistics', 'Fournit des informations générales sur Service Broker.','Provides general Service Broker information.',NULL)

,('Service Broker','SQLServer:Broker Transport', 'Fournit des informations sur le réseau Service Broker.','Provides information on Service Broker networking.',NULL)

--SQL Server

,('SQL Server','SQLServer:Access Methods', 'Recherche et mesure l''allocation des objets de bases de données de SQL Server (par exemple, le nombre de recherches d''index ou de pages allouées aux index et aux données).','Searches through and measures allocation of SQL Server database objects (for example, the number of index searches or number of pages that are allocated to indexes and data).',NULL)

,('SQL Server','SQLServer:Backup Device', 'Fournit des informations sur les unités de sauvegarde utilisées pour les opérations de sauvegarde et de restauration, comme le débit de l''unité de sauvegarde.','Provides information about backup devices used by backup and restore operations, such as the throughput of the backup device.',NULL)

,('SQL Server','SQLServer:Buffer Manager', 'Fournit des informations sur les mémoires tampon utilisées par SQL Server, comme les pages libres et le taux d''accès au cache des tampons.','Provides information about the memory buffers used by SQL Server, such as freememory and buffer cache hit ratio.',NULL)

,('SQL Server','SQL Server:Buffer Node', 'Fournit des informations sur la fréquence à laquelle SQL Server effectue des demandes et accède aux pages libres.','Provides information about how frequently SQL Server requests and accesses free pages.',NULL)

,('SQL Server','SQLServer:CLR', 'Fournit des informations à propos du common language runtime (CLR).','Provides information about the common language runtime (CLR).',NULL)

,('SQL Server','SQLServer:Cursor Manager by Type', 'Fournit des informations sur les curseurs.','Provides information about cursors.',NULL)

,('SQL Server','SQLServer:Cursor Manager Total', 'Fournit des informations sur les curseurs.','Provides information about cursors.',NULL)

,('SQL Server','SQLServer:Database Mirroring', 'Fournit des informations sur la mise en miroir de bases de données.','Provides information about database mirroring.',NULL)

,('SQL Server','SQLServer:Databases', 'Fournit des informations sur une base de données SQL Server, comme la quantité d''espace journal disponible ou le nombre de transactions actives dans la base de données. Cet objet peut avoir plusieurs instances.','Provides information about a SQL Server database, such as the amount of free log space available or the number of active transactions in the database. There can be multiple instances of this object.',NULL)

,('SQL Server','SQL Server:Deprecated Features', 'Compte le nombre d''utilisations de fonctions déconseillées.','Counts the number of times that deprecated features are used.',NULL)

,('SQL Server','SQLServer:Exec Statistics', 'Fournit des informations sur les statistiques d''exécution.','Provides information about execution statistics.',NULL)

,('SQL Server','SQLServer:General Statistics', 'Fournit des informations sur l''activité générale à l''échelle du serveur, comme le nombre d''utilisateurs connectés à une instance de SQL Server.','Provides information about general server-wide activity, such as the number of users who are connected to an instance of SQL Server.',NULL)

,('SQL Server','SQL Server:HADR Availability Replica', 'Fournit des informations sur les réplicas de disponibilité SQL ServerGroupes de disponibilité AlwaysOn.','Provides information about SQL Server AlwaysOn Availability Groups availability replicas.',NULL)

,('SQL Server','SQL Server:HADR Database Replica', 'Fournit des informations sur les réplicas de bases de données SQL ServerGroupes de disponibilité AlwaysOn.','Provides information about SQL ServerAlwaysOn Availability Groups database replicas.',NULL)

,('SQL Server','SQLServer:Latches', 'Fournit des informations sur les verrous de ressources internes, comme les pages de bases de données, utilisés par SQL Server.','Provides information about the latches on internal resources, such as database pages, that are used by SQL Server.',NULL)

,('SQL Server','SQLServer:Locks', 'Fournit des informations sur les demandes de verrous individuelles émises par SQL Server, comme les dépassements du délai d''attente des verrous et les interblocages. Cet objet peut avoir plusieurs instances.','Provides information about the individual lock requests made by SQL Server, such as lock time-outs and deadlocks. There can be multiple instances of this object.',NULL)

,('SQL Server','SQLServer:Memory Manager', 'Fournit des informations sur l''utilisation de la mémoire de SQL Server, comme le nombre total de structures de verrous actuellement allouées.','Provides information about SQL Server memory usage, such as the total number of lock structures currently allocated.',NULL)

,('SQL Server','SQLServer:Plan Cache', 'Fournit des informations sur le cache de SQL Server utilisé pour stocker des objets tels que les procédures stockées, les déclencheurs et les plans de requête.','Provides information about the SQL Server cache used to store objects such as stored procedures, triggers, and query plans.',NULL)

,('SQL Server','SQLServer: Resource Pool Stats', 'Fournit des informations à propos des statistiques du pool de ressources du gouverneur de ressources.','Provides information about Resource Governor resource pool statistics.',NULL)

,('SQL Server','SQLServer:SQL Errors', 'Fournit des informations sur les erreurs SQL Server.','Provides information about SQL Server errors.',NULL)

,('SQL Server','SQLServer:SQL Statistics', 'Fournit des informations sur les aspects des requêtes Transact-SQL, comme le nombre de lots d''instructions Transact-SQL reçus par SQL Server.','Provides information about aspects of Transact-SQL queries, such as the number of batches of Transact-SQL statements received by SQL Server.',NULL)

,('SQL Server','SQLServer:Transactions', 'Fournit des informations sur les transactions actives dans SQL Server, telles que le nombre total de transactions et le nombre de transactions d''instantané.','Provides information about the active transactions in SQL Server, such as the overall number of transactions and the number of snapshot transactions.',NULL)

,('SQL Server','SQLServer:User Settable', 'Réalise une surveillance personnalisée. Chaque compteur peut être une procédure stockée personnalisée ou toute instruction Transact-SQL qui renvoie une valeur à surveiller.','Performs custom monitoring. Each counter can be a custom stored procedure or any Transact-SQL statement that returns a value to be monitored.',NULL)

,('SQL Server','SQLServer:Wait Statistics', 'Fournit des informations sur les attentes.','Provides information about waits.',NULL)

,('SQL Server','SQLServer: Workload Group Stats', 'Fournit des informations à propos des statistiques du groupe de charges de travail du gouverneur de ressources.','Provides information about Resource Governor workload group statistics.',NULL)

SELECT * FROM #ObjetsPerformances

DROP TABLE #OSPC

DROP TABLE #OSPerfCounters

DROP TABLE #OSPerfCountersLast

DROP TABLE #ObjetsPerformances

END