version.query= select left(cast(serverproperty('productversion') as varchar), 4) as 'SQLVersion' ##### Conf Queries Start conf.query.type.signatureTokens.sqlInstance=sqlHostName,sqlInstanceName perf.query.type.signatureTokens.sqlInstance=sqlHostName,sqlInstanceName conf.query.type.signatureTokens.sqlDatabase=sqlHostName,sqlInstanceName,sqlDatabaseName perf.query.type.signatureTokens.sqlDatabase=sqlHostName,sqlInstanceName,sqlDatabaseName conf.query.type.signatureTokens.sqlFile=sqlHostName,sqlInstanceName,sqlDatabaseName,sqlFileName perf.query.type.signatureTokens.sqlFile=sqlHostName,sqlInstanceName,sqlDatabaseName,sqlFileName conf.queries.count=8 conf.query.1=SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [sqlHostName] \ ,SERVERPROPERTY('ServerName') AS [SQL_Server_Name] \ ,CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER' else SERVERPROPERTY('InstanceName') END AS [sqlInstanceName] \ ,Convert(varchar(4),(SERVERPROPERTY('ProductVersion'))) AS [sqlVersion] \ ,CAST(SERVERPROPERTY('Edition') AS varchar(250)) AS [sqlEdition] \ , CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'True' ELSE 'False' END AS [sqlClustered] conf.query.supportedVersions.1=9.00,10.0,10.5,11.0 conf.query.type.1=sqlInstance conf.query.2=select CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER' else SERVERPROPERTY('InstanceName') END AS [sqlInstanceName],count(1) AS [sqlNumOfDatabases] from sys.databases conf.query.supportedVersions.2=9.00,10.0,10.5,11.0 conf.query.type.2=sqlInstance conf.query.3=select CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER' else SERVERPROPERTY('InstanceName') END AS [sqlInstanceName], cpu_count AS [sqlProcessors],physical_memory_in_bytes/1048576 AS [sqlTotalServerMemory] FROM sys.dm_os_sys_info conf.query.supportedVersions.3=9.00,10.0,10.5 conf.query.type.3=sqlInstance conf.query.4=select CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER' else SERVERPROPERTY('InstanceName') END AS [sqlInstanceName], cpu_count AS [sqlProcessors],physical_memory_kb/1024 AS [sqlTotalServerMemory] FROM sys.dm_os_sys_info conf.query.supportedVersions.4=11.0 conf.query.type.4=sqlInstance conf.query.5=SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [sqlHostName] , DB_NAME(database_id) AS [sqlDatabaseName], SUM(size*8)/1024 as [sqlDbSize] FROM sys.master_files GROUP BY DB_NAME(database_id) conf.query.supportedVersions.5=9.00,10.0,10.5,11.0 conf.query.type.5=sqlDatabase conf.query.6=SELECT DB_NAME(database_id) AS [sqlDatabaseName], COUNT(1)as [sqlDbDataFiles], SUM(size*8)/1024 as [sqlDbDataFilesSize] FROM sys.master_files where type_desc='ROWS' GROUP BY DB_NAME(database_id) conf.query.supportedVersions.6=9.00,10.0,10.5,11.0 conf.query.type.6=sqlDatabase conf.query.7=SELECT DB_NAME(database_id) AS [sqlDatabaseName], COUNT(1)as [sqlDbLogFiles], SUM(size*8)/1024 as [sqlDbLogFilesSize] FROM sys.master_files where type_desc='LOG' GROUP BY DB_NAME(database_id) conf.query.supportedVersions.7=9.00,10.0,10.5,11.0 conf.query.type.7=sqlDatabase conf.query.8=SELECT name AS [sqlFileName], DB_NAME(database_id) AS [sqlDatabaseName],PHYSICAL_NAME as [sqlFilePath], SUBSTRING(physical_name, 1,2) [sqlPhysicalDrive],(size*8)/1024 as [sqlFileSize] FROM sys.master_files conf.query.supportedVersions.8=9.00,10.0,10.5,11.0 conf.query.type.8=sqlFile ##### Conf Queries End #####Perf Queries Start perf.queries.count=16 perf.query.1=select [Batch Requests/sec] as [sqlBatchRequestsPs], \ [SQL Compilations/sec] as [sqlCompilationsPerSec], \ [Page lookups/sec] as [sqlPageLookupsPs], \ [Page reads/sec] as [sqlPageReadsPs], \ [Page writes/sec] as [sqlPageWritesPs], \ [Readahead pages/sec] as [sqlPageReadAheadsPs], \ [SQL Re-Compilations/sec] as [sqlRecompilationsPerSec], \ [Forwarded Records/sec] as [sqlFwdedRecordsPs], \ [Full Scans/sec] as [sqlFullScansPs], \ [Page Splits/sec] as [sqlPageSplitsPs], \ [Page life expectancy] as [sqlAvgPageLifeExpectancy], \ [Lazy Writes/sec] as [sqlLazyWritesPs], \ [Memory Grants Pending] as [sqlMemoryGrantsPending], \ [Index Searches/sec] as [sqlIndexSearchesPs] \ from (select distinct counter_name,cntr_value from sys.dm_os_performance_counters where \ counter_name in ('Batch Requests/sec', 'SQL Compilations/sec','Page lookups/sec','Page reads/sec','Page writes/sec','Readahead pages/sec', \ 'SQL Re-Compilations/sec','Forwarded Records/sec','Full Scans/sec','Page Splits/sec','Page life expectancy','Lazy Writes/sec','Memory Grants Pending','Index Searches/sec')) AS TableToBePivoted \ PIVOT(avg(cntr_value) for counter_name IN ([Batch Requests/sec],[SQL Compilations/sec],[Page lookups/sec],[Page reads/sec],[Page writes/sec],[Readahead pages/sec],[SQL Re-Compilations/sec],[Forwarded Records/sec],[Full Scans/sec],[Page Splits/sec],[Page life expectancy],[Lazy Writes/sec],[Memory Grants Pending],[Index Searches/sec]) ) AS PivotedTable order by 1 perf.query.supportedVersions.1=9.00,10.0,10.5,11.0 perf.query.type.1=sqlInstance perf.query.2=select database_name as [sqlDatabaseName], \ [Transactions/sec] as [sqlDbtransactionsPs], \ [Log Growths] as [sqlDbLogGrowths], \ [Log Shrinks] as [sqlDbLogShrinks], \ [Log Truncations] as [sqlDbLogTruncations] \ from ( SELECT counter_name,instance_name as 'database_name', cntr_value FROM sys.dm_os_performance_counters \ WHERE instance_name not in ('_Total','mssqlsystemresource') AND counter_name in ( 'transactions/sec','Log Growths' ,'Log Shrinks','Log Truncations' )) AS TableToBePivoted \ PIVOT (sum(cntr_value) for counter_name IN ( [Transactions/sec],[Log Growths],[Log Shrinks],[Log Truncations] ))AS PivotedTable order by 1; perf.query.supportedVersions.2=9.00,10.0,10.5,11.0 perf.query.type.2=sqlDatabase perf.query.3=SELECT name AS 'sqlDatabaseName' ,\ SUM(num_of_reads) AS 'sqlDbReadsPs' ,\ SUM(num_of_writes) AS 'sqlDbWritesPs' , \ SUM(num_of_bytes_read)/1024 as 'sqlDbReadKBps', \ SUM([num_of_bytes_written])/1024 as 'sqlDbWriteKBps' \ FROM sys.dm_io_virtual_file_stats(NULL, NULL) I INNER JOIN sys.databases D ON I.database_id = d.database_id \ GROUP BY name \ ORDER BY name; perf.query.supportedVersions.3=9.00,10.0,10.5,11.0 perf.query.type.3=sqlDatabase perf.query.4=SELECT [mf].[name] AS [sqlFileName], \ DB_NAME ([vfs].[database_id]) AS [sqlDatabaseName], \ [sqlFileReadsPs] = [num_of_reads], \ [sqlFileWritesPs] = [num_of_writes], \ [sqlFileReadKBps] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE [num_of_bytes_read]/1024 END, \ [sqlFileWriteKBps] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE [num_of_bytes_written]/1024 END, \ [sqlFileReadStalls] = [io_stall_read_ms] , \ [sqlFileWriteStalls] = [io_stall_write_ms] , \ [sqlFileReadWriteRatio] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE [num_of_reads]/[num_of_writes] END \ FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs] JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id] perf.query.supportedVersions.4=9.00,10.0,10.5,11.0 perf.query.type.4=sqlFile perf.query.5=select [Average Wait Time (ms)] as [sqlAvgWaitTime], \ [Lock Timeouts/sec] as [sqlLockTimeoutsPs], \ [Number of Deadlocks/sec] as [sqlDeadlocksPs] \ from (select counter_name,cntr_value from sys.dm_os_performance_counters where counter_name in ('Average Wait Time (ms)','Lock Timeouts/sec','Number of Deadlocks/sec') and instance_name='_Total') AS TableToBePivoted \ PIVOT(avg(cntr_value) for counter_name in ([Average Wait Time (ms)],[Lock Timeouts/sec],[Number of Deadlocks/sec]) ) AS PivotetTable order by 1 perf.query.supportedVersions.5=9.00,10.0,10.5,11.0 perf.query.type.5=sqlInstance perf.query.6=select [Lock waits] as [sqlLockWait] \ ,[Log write waits] as [sqlLogWriteWait] \ ,[Network IO waits] as [sqlNetworkIOWait] \ ,[Page latch waits] as [sqlPageLatchWait] \ ,[Page IO latch waits] as [sqlPageIOWait] \ from (select counter_name,cntr_value from sys.dm_os_performance_counters \ where instance_name like '%Average wait%' AND counter_name in ('Lock waits','Log write waits','Network IO waits','Page latch waits','Page IO latch waits')) AS TableToBePivoted \ PIVOT (sum(cntr_value) for counter_name IN ([Lock waits],[Log write waits],[Network IO waits],[Page latch waits],[Page IO latch waits])) AS PivotedTable order by 1; perf.query.supportedVersions.6=9.00,10.0,10.5,11.0 perf.query.type.6=sqlInstance perf.query.7=SELECT count(*) as [sqlNumOfBlockedProcesses] FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 perf.query.supportedVersions.7=9.00,10.0,10.5,11.0 perf.query.type.7=sqlInstance perf.query.8=select TOP 1 SQLProcessUtilization as [sqlPercentProcessor] from ( \ select record.value('(./Record/@id)[1]', 'int') as record_id, \ record.value('(./Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization \ from (select timestamp, convert(xml, record) as record \ from sys.dm_os_ring_buffers where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%%') as x ) as y \ order by record_id desc perf.query.supportedVersions.8=9.00 perf.query.type.8=sqlInstance perf.query.9=select TOP 1 SQLProcessUtilization as [sqlPercentProcessor] from ( \ select record.value('(./Record/@id)[1]', 'int') as record_id, \ record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization \ from (select timestamp, convert(xml, record) as record \ from sys.dm_os_ring_buffers where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%%') as x ) as y \ order by record_id desc perf.query.supportedVersions.9=9.00,10.0,10.5,11.0 perf.query.type.9=sqlInstance perf.query.10=SELECT instance_name as [sqlDatabaseName], cntr_value as [sqlDbPercentLogUsed] FROM sys.dm_os_performance_counters \ WHERE counter_name = 'Percent Log Used' and instance_name not in ('_Total','mssqlsystemresource') perf.query.supportedVersions.10=9.00,10.0,10.5,11.0 perf.query.type.10=sqlDatabase perf.query.11=SELECT name AS 'sqlDatabaseName', \ [sqlDbReadStalls] = SUM([io_stall_read_ms]) , \ [sqlDbWriteStalls] = SUM([io_stall_write_ms]) , \ [sqlDbReadWriteRatio] = CASE WHEN SUM(num_of_writes) = 0 THEN 0 ELSE SUM(num_of_reads)/SUM(num_of_writes) END \ FROM sys.dm_io_virtual_file_stats(NULL, NULL) I INNER JOIN sys.databases D ON I.database_id = d.database_id \ GROUP BY name \ ORDER BY name; perf.query.supportedVersions.11=9.00,10.0,10.5,11.0 perf.query.type.11=sqlDatabase perf.query.13=DECLARE @pg_size INT, @Instancename varchar(50) \ SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E' \ select [Free Pages]*@pg_size/1024 AS sqlFreePages from \ (select * from sys.dm_os_performance_counters where counter_name = 'Free Pages' AND object_name like '%Buffer Manager%' ) AS TableToBePivoted \ PIVOT(sum(cntr_value) for counter_name in ([Free Pages])) AS PivotedTable perf.query.supportedVersions.13=9.00,10.0,10.5 perf.query.type.13=sqlInstance perf.query.14=SELECT CAST( \ ( SELECT CAST (cntr_value AS BIGINT) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio' )* 100.00 \ / (SELECT CAST (cntr_value AS BIGINT) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base' ) AS NUMERIC(6,3) ) as [sqlBufferCacheHitRatio] perf.query.supportedVersions.14=9.00,10.0,10.5,11.0 perf.query.type.14=sqlInstance perf.query.15=select physical_memory_in_bytes/1048576 as [sqlAvailableMBytes] from sys.dm_os_sys_info perf.query.supportedVersions.15=9.00 perf.query.type.15=sqlInstance perf.query.12=select available_physical_memory_kb/1024 as [sqlAvailableMBytes] from sys.dm_os_sys_memory perf.query.supportedVersions.12=10.0,10.5,11.0 perf.query.type.12=sqlInstance perf.query.16=DECLARE @pg_size INT, @Instancename varchar(50) \ SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E' \ select [Free Memory (kb)]*@pg_size/1024 AS sqlFreePages from \ (select * from sys.dm_os_performance_counters where counter_name = 'Free Memory (kb)' ) AS TableToBePivoted \ PIVOT(sum(cntr_value) for counter_name in ([Free Memory (kb)])) AS PivotedTable perf.query.supportedVersions.16=11.0,12.0 perf.query.type.16=sqlInstance #####Perf Queries End