February 8, 2006 at 3:58 pm
Using SQL Server 2000.
We have some client software on a PC which is talking to our SQL database, on a server, via an ODBC connection.
Is there a way to capture the SQL commands being issued against our database that are coming through the ODBC connection?
Thanks for any and all help,
Russ
February 8, 2006 at 10:05 pm
Hi Russ,
Profiler can capture any SQL commands being submitted to the server. By default, it will capture ALL traffic to your server. If you have a busy server, you may want filter the capture to the specific host, database, or login that the application uses.
February 9, 2006 at 2:40 am
IMHO, SQL Server is short of analysis tools - Oracle, for example, comes with a tool named tkprof which does pretty much what you want.
For SQL Server, SQL Profiler is the Microsoft tool of choice. I don't find it easy to use but it will do the job.
For ODBC connections, there is an extra complication because normally the SQL being executed is wrapped within stored procedures. The important events to trace are:
TSQL
SQL:BatchStarting
SQL:BatchCompleted
Stored Procedures
SP:StmtStarting
SP:StmtCompleted
The TSQL events show you commits and rollbacks. The SP events show the actual SQL being executed.
The reason for tracing both "Starting" and "Completed" events is to show the duration of each command and the resources (CPU and I/O) it consumes.
Once you have the log of SQL being executed, the rest is up to you. You have to hunt throught it to find SQL statements which are particularly common or resource-hungry and analyse them - possibly by copying them into Query Analyser and looking at the execution plan. This can get very tedious, particularly on a busy system. I got fed up with doing it by hand and wrote my own tool to collect and analyse the SQL, and also to detect and log deadlocks and long-duration locks. I am sure there are commercial tools as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply