Authored by Steven Schneider
yum install docker -y
wget -qO- https://get.docker.com/ | sudo sh
yum install git -y
apt-get install git -y
git clone https://github.com/Microsoft/mssql-monitoring.git
cd mssql-monitoring/influxdb
# By default, this will run without modification, but if you want to change where the data directory gets mapped, you can do that here
# Make sure this folder exists on the host.
# This directory from the host gets passed through to the docker container.
INFLUXDB_HOST_DIRECTORY="/mnt/influxdb"
# This is where the mapped host directory get mapped to in the docker container.
INFLUXDB_GUEST_DIRECTORY="/host/influxdb"
USE master;
GO
CREATE LOGIN [collectd] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW SERVER STATE TO [collectd];
GO
GRANT VIEW ANY DEFINITION TO [collectd];
GO
yum install docker -y
wget -qO- https://get.docker.com/ | sudo sh
yum install git -y
apt-get install git -y
git clone https://github.com/Microsoft/mssql-monitoring.git
cd mssql-monitoring/collectd
#The ip address of the InfluxDB server collecting collectd metrics
INFLUX_DB_SERVER="localhost"
#The port that your InfluxDB is listening for collectd traffic
INFLUX_DB_PORT="25826"
#The host name of the server you are monitoring. This is the value that shows up under hosts on the Grafana dashboard
SQL_HOSTNAME="MyHostName"
#The username you created from step 1
SQL_USERNAME="sqluser"
#The password you created from step 1
SQL_PASSWORD="strongsqlpassword"
yum install docker -y
wget -qO- https://get.docker.com/ | sudo sh
yum install git -y
apt-get install git -y
git clone https://github.com/Microsoft/mssql-monitoring.git
cd mssql-monitoring/grafana
# We use the grafana image that Grafana Labs provides http://docs.grafana.org/installation/docker/
# If you wish to modify the port that Grafana runs on, you can do that here.
sudo docker run -d -p 3000:3000 --name grafana grafana/grafana
SELECT Replace(Rtrim(counter_name), ' ', '_') AS counter_name,
Replace(Rtrim(instance_name), ' ', '_') AS instance_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE ( counter_name IN ( 'SQL Compilations/sec',
'SQL Re-Compilations/sec',
'User Connections',
'Batch Requests/sec',
'Logouts/sec',
'Logins/sec',
'Processes blocked',
'Latch Waits/sec',
'Full Scans/sec',
'Index Searches/sec',
'Page Splits/sec',
'Page Lookups/sec',
'Page Reads/sec',
'Page Writes/sec',
'Readahead Pages/sec',
'Lazy Writes/sec',
'Checkpoint Pages/sec',
'Database Cache Memory (KB)',
'Log Pool Memory (KB)',
'Optimizer Memory (KB)',
'SQL Cache Memory (KB)',
'Connection Memory (KB)',
'Lock Memory (KB)',
'Memory broker clerk size',
'Page life expectancy' ) )
OR ( instance_name IN ( '_Total',
'Column store object pool' )
AND counter_name IN ( 'Transactions/sec',
'Write Transactions/sec',
'Log Flushes/sec',
'Log Flush Wait Time',
'Lock Timeouts/sec',
'Number of Deadlocks/sec',
'Lock Waits/sec',
'Latch Waits/sec',
'Memory broker clerk size',
'Log Bytes Flushed/sec',
'Bytes Sent to Replica/sec',
'Log Send Queue',
'Bytes Sent to Transport/sec',
'Sends to Replica/sec',
'Bytes Sent to Transport/sec',
'Sends to Transport/sec',
'Bytes Received from Replica/sec',
'Receives from Replica/sec',
'Flow Control Time (ms/sec)',
'Flow Control/sec',
'Resent Messages/sec',
'Redone Bytes/sec')
OR ( object_name = 'SQLServer:Database Replica'
AND counter_name IN ( 'Log Bytes Received/sec',
'Log Apply Pending Queue',
'Redone Bytes/sec',
'Recovery Queue',
'Log Apply Ready Queue')
AND instance_name = '_Total' ) )
OR ( object_name = 'SQLServer:Database Replica'
AND counter_name IN ( 'Transaction Delay' ) )
WITH waitcategorystats ( wait_category,
wait_type,
wait_time_ms,
waiting_tasks_count,
max_wait_time_ms)
AS (SELECT CASE
WHEN wait_type LIKE 'LCK%' THEN 'LOCKS'
WHEN wait_type LIKE 'PAGEIO%' THEN 'PAGE I/O LATCH'
WHEN wait_type LIKE 'PAGELATCH%' THEN 'PAGE LATCH (non-I/O)'
WHEN wait_type LIKE 'LATCH%' THEN 'LATCH (non-buffer)'
ELSE wait_type
END AS wait_category,
wait_type,
wait_time_ms,
waiting_tasks_count,
max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( 'LAZYWRITER_SLEEP',
'CLR_AUTO_EVENT',
'CLR_MANUAL_EVENT',
'REQUEST_FOR_DEADLOCK_SEARCH',
'BACKUPTHREAD',
'CHECKPOINT_QUEUE',
'EXECSYNC',
'FFT_RECOVERY',
'SNI_CRITICAL_SECTION',
'SOS_PHYS_PAGE_CACHE',
'CXROWSET_SYNC',
'DAC_INIT',
'DIRTY_PAGE_POLL',
'PWAIT_ALL_COMPONENTS_INITIALIZED',
'MSQL_XP',
'WAIT_FOR_RESULTS',
'DBMIRRORING_CMD',
'DBMIRROR_DBM_EVENT',
'DBMIRROR_EVENTS_QUEUE',
'DBMIRROR_WORKER_QUEUE',
'XE_TIMER_EVENT',
'XE_DISPATCHER_WAIT',
'WAITFOR_TASKSHUTDOWN',
'WAIT_FOR_RESULTS',
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'WAITFOR',
'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
'LOGMGR_QUEUE',
'FSAGENT' )
AND wait_type NOT LIKE 'PREEMPTIVE%'
AND wait_type NOT LIKE 'SQLTRACE%'
AND wait_type NOT LIKE 'SLEEP%'
AND wait_type NOT LIKE 'FT_%'
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT LIKE 'BROKER%'
AND wait_type NOT LIKE 'DISPATCHER%'
AND wait_type NOT LIKE 'PWAIT%'
AND wait_type NOT LIKE 'SP_SERVER%')
SELECT wait_category,
Sum(wait_time_ms) AS wait_time_ms,
Sum(waiting_tasks_count) AS waiting_tasks_count,
Max(max_wait_time_ms) AS max_wait_time_ms
FROM waitcategorystats
WHERE wait_time_ms > 100
GROUP BY wait_category
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.