SQLEYE: A SQL Server Analysis Tool

Peter Williams
Microsoft Corporation

Created: October 6, 1994

Click to open or copy the files in the SQLEYE sample application for this technical article.

Abstract

This article describes SQLEYE, which is a Windows NT™–based open data services (ODS) application that can be used to view or log the information being sent between SQL Server and its clients.

Introduction

SQLEYE is a "pass-through" gateway application that can be used to view or log the information being sent between SQL Server and its clients. A client application that normally connects directly to SQL Server can instead connect to SQLEYE. SQLEYE will, in turn, connect to SQL Server, providing a link that is transparent to the application.

Once the communication link has been established, SQLEYE allows you to view and or log the following:

• Queries being sent to SQL Server
  
  
• Results returned
  
  
• Row counts
  
  
• Messages
  
  
• Errors
  
  
• Attentions

It is also possible to attach timing information to each of the above.

Information displayed by SQLEYE (the main window title reflects the startup parameters)

The Transact-SQL™ queries can be recorded in script files on a per-connection basis. These files can be used to replay the activity of the client application.

Files that hold Statistics IO and Statistics TIME information can also be created. These script files can be used to populate statistics tables against which ad-hoc queries can then be run.

Most SQL Server applications work transparently through SQLEYE without any modification, including open database connectivity (ODBC) applications such as Microsoft® Access® version 2.0, when you use the SQL Server ODBC driver.

Typical Uses of SQLEYE

• Application developers can use SQLEYE as a simple way of confirming the queries being sent to SQL Server.
  
  
• If third-party applications are suspected of causing system problems, systems administrators and support staff can monitor their activity using SQLEYE. Scripts can also be built to replay this activity.
  
  
• Statistical information can be collected on a serverwide basis.
  
  
• The visual display can be used to provide a high level view of server activity.
  
  
• If only selected users connect via SQLEYE, it is possible to pause SQLEYE and restrict new logins for those users only.

Screen Display

If you choose to display information on the screen, a new child window is created for each new user connection. These windows display the information using various color codes to distinguish the different information types. When these windows are minimized, the icon can be related to the specific application. The icon is mapped to the application name using the SQLEYE.MAP file, which should be in the same directory as SQLEYE.EXE.

The format of SQLEYE.MAP is:

<SQL application name> <tab> <The icon file (normally the application's .EXE)> <cr,lf>

The supplied SQLEYE.MAP file contains mappings for the standard SQL Server applications. This file can be edited as needed.

When minimized, various overlay icons are used to display the state of the respective connection:

• Up arrow: Transact-SQL being sent to SQL Server
  
  
• Up arrow with RPC: Stored procedure being called on SQL Server
  
  
• Hourglass: Waiting for SQL Server to process the request
  
  
• Down arrow: Results being returned to the client
  
  
• A ghost: A window that remains after the connection is closed

If no application-specific icon has been specified in SQLEYE.MAP, a default face icon is used and the hourglass overlay is replaced with the eyes opening on the default icon.

Menu Options

The File Menu

Excluding the log file, SQLEYE can produce three types of files listed in the File menu:

• Build Transact-SQL scripts creates spid.SQL scripts on a per-connection basis. (Files are named spid.SQL, where spid is the server connection ID displayed in the window titles.) Note: If the same connection number is reused, the Transact-SQL is appended to the existing file.
  
  
• Record Statistics IO generates two .SQL files (SQLREADS.SQL and SQLWRITE.SQL) in which it records page reads and page writes. The structure of the files is a series of insert statements that can be used to populate statistics tables on a SQL database. In SQLREADS.SQL, each line contains the user name, application name, host name, object name, scan count, logical reads, physical reads, and the first 255 bytes of the query. SQLWRITE.SQL contains similar information for page writes.
  
  
• Record Statistics Time generates a file called SQLCPU.SQL. Each line in this file contains the user name, application name, host name, CPU time, elapsed time, and the first 255 bytes of the query.

The View Menu

The displayed data is controlled by the View menu:

• Set the clear buffer flag clears all of the display windows when they next receive a data item to display.
  
  
• Queries, Results, Row counts, Messages, Errors, Attentions toggle the display of various data elements. The current data in the window remains unchanged, but new information is filtered in line with the new menu selections.
  
  
• Timings prepends the current time to each new line within the window as it is generated.

The View menu is grayed if the gateway is started without the Screen Display check box selected.

The Log Menu

You can log all information in a file called SQLEYE.LOG. This file can be configured dynamically any time through the options in the Log menu, which are similar to the options in the View menu.

The Gateway Menu

The gateway can be controlled using the Gateway menu.

• Start the gateway starts the open data services (ODS) gateway.
  
  
• Pause the gateway prevents new users from connecting via SQLEYE. Users who are already connected are unaffected. The only exception is the System Administrator who can still make new connections. The System Administrator can pause SQLEYE remotely by sending the pause sqleye command from a query window. SQLEYE will recognize this command and will not pass it to SQL Server.
  
  
• Restart the gateway reverses the effects of pausing the gateway. The System Administrator can restart SQLEYE remotely by sending the restart sqleye command from a query window. SQLEYE will recognize this command and will not pass it to SQL Server.

Shutting down the gateway prevent new users from connecting via SQLEYE (including the System Administrator). Once all existing connections though SQLEYE have been closed, SQLEYE will display a message and terminate itself. This can only be done remotely and is analogous to the SQL Server Shutdown command. The System Administrator must send the shutdown sqleye command from a query window. SQLEYE will recognize this command and will not pass it to the SQL Server.

Although it is possible to terminate SQLEYE from the console, this is analogous to shutdown with "no wait" (it will not wait for users to disconnect before termination). Although warnings are displayed, it is up to the user to ensure that it is safe to shut down SQLEYE in this way.

The Messages Menu

You can use the Send to all users option to send a DB-Library message to all users connected via SQLEYE. Some applications may not display these messages.

The Window Menu

This menu controls the windows and their properties.

• Auto-Tile makes effective use of the screen area through retiling. When this option is selected, as new connections are established or existing connections dropped, the windows that are not minimized are retiled and windows that are minimized are rearranged. Several of the System menu options are grayed out if this option is selected. (This option may not be appropriate in environments where the connections change rapidly.)
  
  
• Auto-Scroll sets display windows to scroll automatically when new information is displayed, giving an up-to-date view of activity. (This option is processor-intensive. It is recommended that you keep the number of non-minimized windows to a minimum when using this option.)
  
  
• Start Minimized creates new display windows in an initially minimized state. (This is normally the least processor-intensive option.)
  
  
• Ghosts keeps the screen information for a client connection after the client connection has been disconnected. A special icon identifies these windows, and the Close option in the System menu is enabled. (If left unattended, this option can result in very large numbers of windows within the display area.)

The recommended configuration (and the default starting option) is for new windows to be created in the minimized state using little processor time and giving a general overview of server activity. Any windows that are of particular interest can be restored. They will automatically be tiled and will begin to scroll as new information arrives.

Connecting to Other Servers

Although a default server name is specified when SQLEYE is started, it is possible to override this setting and log into several different servers simultaneously via one instance of SQLEYE. To do this, the client application should append @servername to the login ID. For example, user Fred who wishes to connect via SQLEYE to a SQL Server called OtherServer should specify a user name of Fred@OtherServer. SQLEYE will then login to OtherServer using the user name Fred.

Installing SQLEYE

Files Needed

To run SQLEYE in the default minimum configuration, you will need the files listed below.

Application-specific files:

General SQL Server DLLs (supplied with SQL Server) and normally installed in the \SQL\DLL directory:

Important Note   OPENDSNT.DLL ships with all version of SQL Server and can also be licensed separately.

Please read your Microsoft SQL Server and Microsoft Open Data Services license agreements for further details. The following installation instructions assume that you have valid licenses for the necessary DLLs.

If you are using a protocol other than named pipes, the relevant Net-Libraries are required.

Setup Modes

1. Separate machines

If you are installing SQLEYE on a server that is not running SQL Server, no further setup is necessary. SQLEYE will start on the default pipe (\\.\pipe\sql\query). This will appear to the client application as if it were SQL Server.

2. SQLEYE on an alternate pipe

If you are installing SQLEYE on the same machine as SQL Server, either SQL Server or SQLEYE will need to be started on an alternate pipe.

To start SQLEYE on an alternate pipe:

• Create a key in the registry called HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer\SQLEYE. (It need not be called SQLEYE, this is only a suggested name.) This name must be specified as the registry key when starting SQLEYE.
  
  
• Add a value within this key called ListenOn with a value of SSNMPNTW,\\.\pipe\sqleye\query. (Again, the name SQLEYE is a suggestion.)

  At this point, you can start SQLEYE by leaving the server field blank (this will default to the local server) and specifying sqleye as the registry key.

• To allow a client to connect to SQLEYE, you must now use the advanced section of the client configuration utility to add a new entry. For example, on a Windows® workstation, specify a DLL name of dbnmp3 and a connection string of \\<servername>\pipe\sqleye\query.

Note   Only those clients that have been configured in this way can connect via SQLEYE. All other clients will connect directly to the SQL Server.

Other Net-Libraries may be used; please see the SQL Server Configuration Guide for further information.

3. SQL Server on an alternate pipe

• Add the registry key for SQLEYE as above, except that we will now set the ListenOn for SQLEYE to SSNMPNTW,\\.\pipe\sql\query.
  
  
• Modify the ListenOn for SQL Server held in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer\Server to an alternate pipe name.
  
  
• To allow SQLEYE to connect, use the advanced section of the client configuration utility on the server to add a new entry for SQL Server's new pipe name. This time, we use the DLL name dbnmpntw and the pipe name now used for the ListenOn in the Server section.

Note   No client configuration is now necessary; all clients will connect via SQLEYE. However, if SQLEYE is not running, no clients will be able to connect to SQL Server (unless you inform them of the new pipe name).

Other Net-Libraries may be used; please see the SQL Server Configuration Guide for further information.

4. Multiple copies of SQLEYE

To run several instances of SQLEYE on the same machine, you will need to create a registry key for each instance, because each instance must have a unique pipe name.

Starting SQLEYE

When you start SQLEYE, an initialization dialog that requires the following information is displayed:

• SQL Server to Direct Queries to: The default SQL Server to which clients connecting to SQLEYE will be routed.
  
  
• SQL Registry Key: The registry key that contains the ListenOn information, as discussed in the "Installing SQLEYE" section above.
  
  
• Screen Display: Controls the presence of any data display windows. If this option is not checked, the View menu will be grayed.

Command-Line Options

You can specify various command-line parameters to allow SQLEYE to be started from a batch file: