sqlmon

Collects events from SQL Server and saves them to Elasticsearch for further analysis.

You can use sqltop or Kibana to analyze these data.

Installation

npm install -g sqlmon

Sample

$ sqlmon \
    --ss-address sql.example.com \
    --ss-user sa \
    --ss-password p@ssw0rd \
    --es-address elasticsearch.example.com \
    --trace-directory 'c:\trace' \
    --events RPCCompleted SQLBatchCompleted \
    --fields TextData TextDataHash QueryHash LoginName Duration Reads Writes CPU \
    --duration 60s

Connecting to sql.example.com...
Creating trace...
Trace file path: c:\trace\20190729103344280.trc
Starting trace...
Collecting events for 60s until 14:13:20... (Press [c] to stop)
Stopping trace...
Reading trace...
Saving 18120 events to elasticsearch.example.com...
100.00% 18120/18120
Disconnecting...

Usage

Trace directory

sqlmon needs to write trace files to a temporary directory on the server first. Use the ---trace-directory option to specify the location of this directory:

--trace-directory 'c:\trace'

Note 1: The SQL Server service account (usually NT Service\MSSQLSERVER) must have Write permission on this directory.

Note 2: These files are not removed when the program ends and must be manually deleted.

Duration

Use the --duration or -d parameter to specify the duration that the trace must run:

--duration 1m

You can use any specifier like 30s, 10m, 2h, or 1d. The default is 60s.

Max trace size

By default, sqlmon limits the size of trace files to 1 GB. You can change this by using the --max-size option (in megabytes):

--max-size 2048 # 2 GB

Events to capture

Use the --events parameter to specify the events to be captured:

--events RPCCompleted SQLBatchCompleted

See all supported events.

Fields to include

Use the --fields parameter to specify the fields to be included:

--fields TextData Reads Writes CPU Duration RowCounts

See all supported fields.

Index name

The default Elasticsearch index name is sql-YY.MM.DD which can be changed using the --index-prefix option:

--index-prefix trace-

Collect only

If you only want to collect events without saving them to Elasticsearch, you can use the --collect-only option.

Importing trace files

If you have a saved trace file (created by either sqlmon or SQL Server Profiler), you can import it into Elasticsearch using the --import option:

--import 'c:\trace\20190728042508732.trc'

Delayed start

You can use the --delay option to delay starting the operations until a later time:

--delay 1h

Hooks

There are a few hooks defined that let you run any program at certain stages of the execution:

--schedule-hook ./myscript.sh
--collection-start-hook ./myscript.sh
--collection-end-hook ./myscript.sh
--save-start-hook ./myscript.sh
--save-end-hook ./myscript.sh
--error-hook ./myscript.sh
--interrupt-hook ./myscript.sh

The following environment variables are available to use in your hooks:

SQLMON_SQLSERVER_ADDRESS
SQLMON_SQLSERVER_PORT
SQLMON_SQLSERVER_USER
SQLMON_SQLSERVER_PASSWORD
SQLMON_SQLSERVER_TIMEOUT
SQLMON_ELASTICSEARCH_ADDRESS
SQLMON_ELASTICSEARCH_PORT
SQLMON_ELASTICSEARCH_TIMEOUT
SQLMON_TRACE_DIRECTORY
SQLMON_INDEX_PREFIX
SQLMON_EVENTS
SQLMON_FIELDS
SQLMON_DURATION
SQLMON_MAX_SIZE
SQLMON_BATCH_SIZE
SQLMON_DELAY
SQLMON_COLLECT_ONLY
SQLMON_IMPORT
SQLMON_START_DATETIME
SQLMON_START_TIME
SQLMON_TRACE_FILE_PATH
SQLMON_STOP_DATETIME
SQLMON_STOP_TIME
SQLMON_TRACE_FILE_PATH
SQLMON_ERROR

Computed fields

TextDataHash

TextDataHash is the hash of the TextData field.

QueryHash

QueryHash is created by first removing all parameters and variables from TextData and then hashing it. It is useful for grouping queries to find the top resource consuming ones.

Supported events and fields

See SQL Server Event Class Reference for detailed information about these fields and events.

Events

AssemblyLoad
Attention
AuditAddDBUserEvent
AuditAddLoginEvent
AuditAddLogintoServerRoleEvent
AuditAddMembertoDBRoleEvent
AuditAddRoleEvent
AuditAppRoleChangePasswordEvent
AuditBackupRestoreEvent
AuditBrokerConversation
AuditBrokerLogin
AuditChangeAuditEvent
AuditChangeDatabaseOwner
AuditDatabaseManagementEvent
AuditDatabaseObjectAccessEvent
AuditDatabaseObjectGDREvent
AuditDatabaseObjectManagementEvent
AuditDatabaseObjectTakeOwnershipEvent
AuditDatabaseOperationEvent
AuditDatabasePrincipalImpersonationEvent
AuditDatabasePrincipalManagementEvent
AuditDatabaseScopeGDR
AuditDBCCEvent
AuditFulltext
AuditLogin
AuditLoginChangePasswordEvent
AuditLoginChangePropertyEvent
AuditLoginFailed
AuditLoginGDREvent
AuditLogout
AuditObjectDerivedPermissionEvent
AuditObjectGDREvent
AuditSchemaObjectAccessEvent
AuditSchemaObjectManagementEvent
AuditSchemaObjectTakeOwnershipEvent
AuditServerAlterTraceEvent
AuditServerObjectGDREvent
AuditServerObjectManagementEvent
AuditServerObjectTakeOwnershipEvent
AuditServerOperationEvent
AuditServerPrincipalImpersonationEvent
AuditServerPrincipalManagementEvent
AuditServerScopeGDREvent
AuditServerStartsandStops
AuditStatementPermissionEvent
AutoStats
BackgroundJobError
BitmapWarning
BlockedProcessReport
BrokerActivation
BrokerConnection
BrokerConversation
BrokerConversationGroup
BrokerCorruptedMessage
BrokerForwardedMessageDropped
BrokerForwardedMessageSent
BrokerMessageClassify
BrokerMessageUndeliverable
BrokerQueueDisabled
BrokerRemoteMessageAcknowledgement
BrokerTransmission
CPUthresholdexceeded
CursorClose
CursorExecute
CursorImplicitConversion
CursorOpen
CursorPrepare
CursorRecompile
CursorUnprepare
DatabaseMirroringStateChange
DatabaseSuspectDataPage
DataFileAutoGrow
DataFileAutoShrink
DeadlockGraph
DegreeofParallelism
Deprecated
DeprecationAnnouncement
DeprecationFinalSupport
DTCTransaction
ErrorLog
EventLog
Exception
ExchangeSpillEvent
ExecPreparedSQL
ExecutionWarnings
ExistingConnection
FTCrawlAborted
FTCrawlStarted
FTCrawlStopped
HashWarning
LockAcquired
LockCancel
LockDeadlock
LockDeadlockChain
LockEscalation
LockReleased
LockTimeout
LockTimeoutNonZero
LogFileAutoGrow
LogFileAutoShrink
MissingColumnStatistics
MissingJoinPredicate
MountTape
ObjectAltered
ObjectCreated
ObjectDeleted
OLEDBCallEvent
OLEDBDataReadEvent
OLEDBErrors
OLEDBProviderInformation
OLEDBQueryInterfaceEvent
Performancestatistics
PlanGuideSuccessful
PlanGuideUnsuccessful
PreConnectCompleted
PreConnectStarting
PrepareSQL
ProgressReportOnlineIndexOperation
QNdynamics
QNparametertable
QNsubscription
QNtemplate
RPCCompleted
RPCOutputParameter
RPCStarting
ScanStarted
ScanStopped
ServerMemoryChange
ShowplanAll
ShowplanAllForQueryCompile
ShowplanStatisticsProfile
ShowplanText
ShowplanTextUnencoded
ShowplanXML
ShowplanXMLForQueryCompile
ShowplanXMLStatisticsProfile
SortWarnings
SPCacheHit
SPCacheInsert
SPCacheMiss
SPCacheRemove
SPCompleted
SPRecompile
SPStarting
SPStmtCompleted
SPStmtStarting
SQLBatchCompleted
SQLBatchStarting
SQLFullTextQuery
SQLStmtCompleted
SQLStmtRecompile
SQLStmtStarting
SQLTransaction
TMBeginTrancompleted
TMBeginTranstarting
TMCommitTrancompleted
TMCommitTranstarting
TMPromoteTrancompleted
TMPromoteTranstarting
TMRollbackTrancompleted
TMRollbackTranstarting
TMSaveTrancompleted
TMSaveTranstarting
TraceFileClose
TransactionLog
UnprepareSQL
User0
User1
User2
User3
User4
User5
User6
User7
User8
User9
UserErrorMessage
XQueryStaticType

Fields

ApplicationName
BigintData1
BigintData2
BinaryData
ClientProcessID
ColumnPermissions
CPU
DatabaseID
DatabaseName
DBUserName
Duration
EndTime
Error
EventClass
EventSequence
EventSubClass
FileName
GUID
Handle
HostName
IndexID
IntegerData
IntegerData2
IsSystem
LineNumber
LinkedServerName
LoginName
LoginSid
MethodName
Mode
NestLevel
NTDomainName
NTUserName
ObjectID
ObjectID2
ObjectName
ObjectType
Offset
OwnerID
OwnerName
ParentName
Permissions
ProviderName
QueryHash
Reads
RequestID
RoleName
RowCounts
ServerName
SessionLoginName
Severity
SourceDatabaseID
SPID
SqlHandle
StartTime
State
Success
TargetLoginName
TargetLoginSid
TargetUserName
TextData
TextDataHash
TransactionID
Type
Writes
XactSequence

Version History

1.4
- Added TextDataHash field.
1.3
- Added support for hooks.
1.2
- Added --delay option.
1.1
- Added --collect-only option.
1.0
- Initial release.

Author

Soheil Rashidi

Copyright and License

Licensed under the The MIT License (the "License"); you may not use this work except in compliance with the License. You may obtain a copy of the License in the LICENSE file, or at:

http://www.opensource.org/licenses/mit-license.php

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

soheilpro/sqlmon