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 )
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