December 3, 2007 at 8:46 am
I am looking for a FREE tool that is mainly useful for the following tasks in SQL Server 2000 envrironment:
1. trapping and displaying the SQL being run by a particular process (or all the processes). It should be able to trap the SQL being run by Stored Procedures.
2. overall performance of the query in terms of which indexes are being used and what kind of scans are being used.
The item-1 is important while item-2 is optional.
I know Profiler can do this but I am looking for something easier and better, and a tool that is FREE.
Any recommendations?
Thanks!
December 3, 2007 at 12:44 pm
What's not easy about using profiler?
If you have access to the stored procedure code, you wouldn't need to capture it every time, just the parameters being passed with the procedure call. Unless there's a particular statement within a stored proc you want to keep an eye on.
And sorry, don't know of any free tools that can do the same thing.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 3, 2007 at 12:56 pm
You're asking a lot here.
Profiler is free and does this. Use that. No one else will likely develop a stable tool that can do this without a cost.
December 3, 2007 at 3:42 pm
... I know Profiler can do this but I am looking for something easier ....
Profiler is *very* easy I don't understand why are you saying that you need something "easier"
* Noel
December 3, 2007 at 3:47 pm
I don't mind running Profiler in a Dev environment but when there is a problem in Production environment I would rather prefer a tool that will quickly capture and show the offending queries.
December 3, 2007 at 3:49 pm
U (12/3/2007)
I don't mind running Profiler in a Dev environment but when there is a problem in Production environment I would rather prefer a tool that will quickly capture and show the offending queries.
SQL Trace will do that for you and is nothing but the "scripts" that you can generate from the profiler inteface.
* Noel
December 3, 2007 at 3:54 pm
U (12/3/2007)
I don't mind running Profiler in a Dev environment but when there is a problem in Production environment I would rather prefer a tool that will quickly capture and show the offending queries.
But Profiler does quickly do that? What is it about Profiler that you think could be easier?
December 3, 2007 at 3:58 pm
noeld (12/3/2007)
U (12/3/2007)
I don't mind running Profiler in a Dev environment but when there is a problem in Production environment I would rather prefer a tool that will quickly capture and show the offending queries.SQL Trace will do that for you and is nothing but the "scripts" that you can generate from the profiler inteface.
I know but to find the worst performing query I will have to run the Profiler on the whole database. Is it ok to put a trace on the Production database? Also, one has to go through the vast amount of profiler output to find out the required info.
Does SQL Server store info about latest SQL in system views/tables?
December 3, 2007 at 4:02 pm
U (12/3/2007)
noeld (12/3/2007)
U (12/3/2007)
I don't mind running Profiler in a Dev environment but when there is a problem in Production environment I would rather prefer a tool that will quickly capture and show the offending queries.SQL Trace will do that for you and is nothing but the "scripts" that you can generate from the profiler inteface.
I know but to find the worst performing query I will have to run the Profiler on the whole database. Is it ok to put a trace on the Production database? Also, one has to go through the vast amount of profiler output to find out the required info.
Does SQL Server store info about latest SQL in system views/tables?
Because you are doing this post on a 2005 forums I can tell you that all you need is to install SP2 and Performance Dashboard, All that info is at your finger tips. By the way by default SQL Server 2005 RUNS a trace on your server and it does *NOT* causes significant overhead ...
* Noel
December 3, 2007 at 4:05 pm
noeld (12/3/2007)
U (12/3/2007)
noeld (12/3/2007)
U (12/3/2007)
I don't mind running Profiler in a Dev environment but when there is a problem in Production environment I would rather prefer a tool that will quickly capture and show the offending queries.SQL Trace will do that for you and is nothing but the "scripts" that you can generate from the profiler inteface.
I know but to find the worst performing query I will have to run the Profiler on the whole database. Is it ok to put a trace on the Production database? Also, one has to go through the vast amount of profiler output to find out the required info.
Does SQL Server store info about latest SQL in system views/tables?
Because you are doing this post on a 2005 forums I can tell you that all you need is to install SP2 and Performance Dashboard, All that info is at your finger tips. By the way by default SQL Server 2005 RUNS a trace on your server and it does *NOT* causes significant overhead ...
Sorry, I posted it on the wrong forum. I am using SQL Server 2000 and should have posted the query on that forum.
December 3, 2007 at 4:14 pm
Sorry, I posted it on the wrong forum. I am using SQL Server 2000 and should have posted the query on that forum.
But Regardless the answer that an sql trace "given that the number of events generated are nominal" does *NOT* affects a production server REMAINS!
* Noel
December 3, 2007 at 4:43 pm
ANY tool will have to look at every query. Every bit of traffic to find what's offending.
You can limit Profiler to one database, even one type of batch (T-SQL and/or stored proc), even filter by some performance measure. Put those all in place, then you have to go through the data. There's no way around it, but you can load the profiler data into a table on any SQL Server and run queries to find what's run the most often and takes the longest.
December 3, 2007 at 4:49 pm
Steve Jones - Editor (12/3/2007)
ANY tool will have to look at every query. Every bit of traffic to find what's offending.You can limit Profiler to one database, even one type of batch (T-SQL and/or stored proc), even filter by some performance measure. Put those all in place, then you have to go through the data. There's no way around it, but you can load the profiler data into a table on any SQL Server and run queries to find what's run the most often and takes the longest.
Ok. Does Profiler show every query/SQL that gets executed by a Stored Procedure if one of the session is executing a SP?
December 4, 2007 at 12:15 am
If you capture the event SP_StmtCompleted (under stored procedures), yes.
Profiler/SQL trace have massive numbers of events that can be traced. Just depends what you want to capture.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2007 at 8:42 am
What you are looking for is a tool that will read in all of the SQL queries and then spit out only problem queries. You can configure Profiler to do this, but you have to be able to define what a problem query is. The purpose of using profiler is to gather information and metrics in order for a SQL Server professional to perform analysis on to determine where the problems are. If you know that you want to only look at certain queries, say queries with a duration greater than 15 seconds or a query that uses more that x amount of CPU or queries with high reads, configure your Profiler trace to use filters and only report on the ‘problem’ queries that you’ve defined. Profiler is only smart enough to report on what you tell it to. It can be a very powerful tool when used correctly (and it’s free!).
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply