CODEWITHSUNDEEP
sql-serversql-server-profiler
Dejan
Dejan

Reputation: 511

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

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 )

Upvotes: 2

Views: 3388

Answers (3)

gbn
gbn

Reputation: 432200

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

Upvotes: 1

Galwegian
Galwegian

Reputation: 42227

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

Upvotes: 1

hollystyles
hollystyles

Reputation: 5039

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.

Upvotes: 1

Related Questions