Is there an alternative to the SQL Profiler for SQL Server 2000

Question

I am trying to optimize some stored procedures on a SQL Server 2000 database and when I try to use SQL Profiler I get an error message "In order to run a trace against SQL Server you have to be a member of sysadmin fixed server role.". It seems that only members of the sysadmin role can run traces on the server (something that was fixed in SQL Server 2005) and there is no way in hell that I will be granted that server role (company policies)

What I'm doing now is inserting the current time minus the time the procedure started at various stages of the code but I find this very tedious

I was also thinking of replicating the database to a local installation of SQL Server but the stored procedure is using data from many different databases that i will spend a lot of time copying data locally

So I was wondering if there is some other way of profiling SQL code? (Third party tools, different practices, something else )

Answer 1

In query analyser:

SET STATISTICS TIME ON

SET STATISTICS IO ON

Run query and look in the messages tab.

It occurs to me this may require same privileges, but worth a try.

Answer 2

Your hands are kind of tied without profiler.

You can, however, start with tuning your existing queries using Query Analyzer or any query tool and examining the execution plans. With QA, you can use the Show Execution Plan option. From other tools you can use the

SET STATISTICS PROFILE ON / OFF

Answer 3

There is a workaround on SQL 2000 to obfuscate the Profiler connection dialogue box to limit the sysadmin connection to running traces only.

SQLTeam

Blog