Quick Start (Live Server Monitoring)
This topic describes how you can use SQL Nexus to perform
near-realtime query performance and blocking analysis
of a SQL Server 2005 instance. For information about analyzing
previously-collected Profiler trace data, see the Quick Start (Postmortem
Analysis) topic.
- Start
SQL Nexus. Connect to the SQL Server 2005 instance that you want to
monitor.
- When prompted to start the SQLDIAG data collector,
answer Yes.
NOTE: You can run SQL Nexus on a remote machine, but the data
collector always runs on the server being monitored. Before you can
perform remote monitoring, you must run SQL Nexus once on the server
itself; this will register the data collector as a service. Once the
data collector service has been registered, you can monitor and start/stop
data collection from any remote machine.
- If the
data collector was not already running when you started SQL Nexus, it may
take a minute or so for the “sqlnexus” database
to be created and for the initial set of live data to be loaded.
- Select
a report to view from the left-hand pane.
- To see
the latest data, set the auto-refresh interval in the report
toolbar. You can also manually refresh the view by clicking the
refresh icon in the toolbar.
When you exit the application, SQL Nexus will ask you
whether you wish to leave the data collector running. If you choose to leave the collector running,
you can restart SQL Nexus at any time -- hours or days later -- and see the server’s
recent performance history.
Multiple SQL Server Instances
This release of SQL Nexus only supports monitoring one
instance of SQL Server per monitored Windows server. This restriction will be relaxed in a future
release. The first time you run SQL
Nexus on a machine, SQL Nexus’ SQLDIAG service will be configured to monitor the
SQL Server instance you asked SQL Nexus to connect to. After this, if you connect SQL Nexus to another
instance on this machine, you will not be able to view live performance data for
that server; the collector will continue collecting data from the instance that
you originally connected to. If you wish
to monitor a different instance on the machine, you must first unregister the collector service. To do this:
- Run
this command to stop the collector:
sqldiag.exe
STOP /Asqlnexus
- Check
Service Control Manager to verify that the DIAG$sqlnexus
service has entered the Stopped state.
- Run
this command to unregister the service:
sqldiag.exe
/U /Asqlnexus
Data Purging
By default, a background job will purge all data older than
4 hours from the data warehouse. You can
increase this retention period by running this command in a query tool:
USE sqlnexus
EXEC sp_nexus_configure
' purge period (min)', '<new retention period (min)>'
Troubleshooting
- You
must have permission to collect Profiler trace and monitor server state.
- If the
collector was not already running, you may have to wait several seconds
for the data collection tasks to start. The reports may not show any
useful data until this happens.
- The
data collector service must be restarted if the SQL Server service is
restarted.
- To
minimize the impact that Profiler tracing can have on SQL Server, SQL
Nexus uses server-side tracing directly to files (this is the most
efficient available query tracing method). The trace analysis is
only “near-realtime” due to this. SQL
Nexus uses a time-based trace rollover mechanism; every few minutes, the
current active file is closed and trace events are directed to a new
file. As a result, you should
expect that the trace analysis reports in SQL Nexus have an approximately
5-10 minute delay. The reports will
all display the time period that the underlying data spans.