February 27, 2009 at 12:17 am
Hi people. I've just started using parameter queries from ASP pages and I beleive the SQL Profiler is the best way to debug errors in the SQL. I'm using SQL 2005 Workgroup and it doesn't come with SQL Profiler. I'm happy to pay for a third party product but don't want to pay £5,548.75 for SQL Server 2005 Standard. Can someone recommend a SQL Profiler product, or another way I can debug parameter queries for that matter? I've just installed this http://www.teratrax.com/tpm/?gclid=CNCvuuzV-pgCFQ5GQwodpFUgmg but it doesn't show error SQL only completed SQL.
Thanks for your help.
February 27, 2009 at 6:13 am
Profiler doesn't debug queries. It captures performance and behavior. I'm not aware of a third party tool that does everything that it does.
However, just because you don't have Profiler doesn't mean you can't gather that data. You can still run server side trace commands to create a trace and capture the data. You can capture the performance metrics out to a file and then load that file into a table to run queries against it. This is a better way to go for serious operation against your production system anyway. Look up sp_trace_create to get started.
You have another way too. If you are trying to capture performance information about your queries, then an alternate place to go is to the dynamic management views and functions within SQL Server. You can get aggregate performance metrics of all the queries currently in cache from sys.dm_exec_query_stats. You can see the status of currently executing queries from sys.dm_exec_query_requests. You can see the code that was passed to the query through sys.dm_exec_sql_text. You can even get the compiled plans that are stored in cache from sys.dm_exec_query_plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2009 at 8:26 am
Hi Grant, your post has been really helpful. I've been doing some reading and have managed to get the following working but I think it's only looking at system processes. How would I change this to look at a certain DB? Also, will the query below show me SQL with errors or just completed SQL? It's the errors I'm mainly after at the moment. Many thanks.
SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],last_execution_time,
(SELECT SUBSTRING(text,1,1000)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
February 27, 2009 at 8:53 am
Ah, if you want to collect errors, this won't help at all. It's only going to show completed queries. You'll need to set up a server side trace.
Howerver, a slightly better way to write the query:
SELECT TOP 5
( s.total_worker_time / s.execution_count ) AS [Avg CPU Time],
s.last_execution_time,
SUBSTRING(q.text, 1, 1000)
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS q
ORDER BY [Avg CPU Time] DESC
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2009 at 2:42 pm
I've been looking at the following post (http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx%5B/url%5D) on creating a trace but I'm getting errors trying to run the following code.
declare @traceidout int
declare @maxfilesize bigint
declare @on bit
set @on = 1
set @maxfilesize = 50
exec sp_trace_create @traceidout output, 2, N'C:\Trace\mytrace', @maxfilesize, NULL
exec sp_trace_setevent @traceidout, 12, 1, @on
exec sp_trace_setevent @traceidout, 12, 3, @on
Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070003(error not found).
I'm starting to think than SQL 2005 Workgroup not only doesn't come with SQL Profiler but it also doesn't have the SP procedures. Can anyone confirm this and perhaps help me find a way to view failed SQL statements for debugging. Many thanks.
February 28, 2009 at 6:40 am
Thanks for your help with this. As I've learnt more this post is drifting off topic so I'll make new post to keep it a useful thread.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply