January 18, 2011 at 4:52 am
Hi There,
I ran a trace on SQL 2000 to identify long running queries.
I saved my results to a table in the master db "TraceResultsLongRunningQueries" there is 58000 rows in the table.
How can I see which queries took the longest time to execute?
Below is the create to script:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TraceResultsLongRunningQueries](
[RowNumber] [int] IDENTITY(1,1) NOT NULL,
[EventClass] [int] NULL,
[TextData] [ntext] NULL,
[NTUserName] [nvarchar](128) NULL,
[ClientProcessID] [int] NULL,
[ApplicationName] [nvarchar](128) NULL,
[LoginName] [nvarchar](128) NULL,
[SPID] [int] NULL,
[Duration] [bigint] NULL,
[StartTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[CPU] [int] NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'Build', @value=2039 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TraceResultsLongRunningQueries'
GO
EXEC sys.sp_addextendedproperty @name=N'MajorVer', @value=8 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TraceResultsLongRunningQueries'
GO
EXEC sys.sp_addextendedproperty @name=N'MinorVer', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TraceResultsLongRunningQueries'
GO
Your assistance will be much Appreciated!
Thank you!
January 18, 2011 at 6:46 am
Well, if anyone should ever need this...
Use a cursor to check the query run time...
DECLARE @SPID int
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT SPID
FROM TraceResultsLongRunningQueries
OPEN c1
FETCH NEXT FROM c1
INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT [RowNumber],[EventClass],[TextData],[NTUserName],[ClientProcessID],[ApplicationName]
,[LoginName],[SPID],[Duration],[StartTime],[Reads],[Writes],[CPU]
FROM
[master].[dbo].[TraceResultsLongRunningQueries]
WHERE [SPID] = @SPID
FETCH NEXT FROM c1
INTO @SPID
END
CLOSE c1
DEALLOCATE c1
It takes a while to execute but it makes life much easier to see the long running queries.
January 18, 2011 at 6:56 am
Unless I'm missing something here, there's no need to use a cursor. I think it's as simple as this:
SELECT * FROM TraceResultsLongRunningQueries
ORDER BY Duration DESC
John
January 19, 2011 at 1:53 am
Thanks for the reply John.
I checked the duration, the problem is there is NULL & 0 values in the results and it is in microseconds format. That is why I used the cursor or am I missing something and should just ignore the NULL & 0 results? Let me see if I can convert the microseconds to seconds.
January 19, 2011 at 2:07 am
Fox87 (1/19/2011)
am I missing something and should just ignore the NULL & 0 results?
I don't know whether you can ignore those results, since you haven't provided any sample data. But presumably if you have a 0 or a NULL, then it either executes so quickly it can be ignored, or it's from a batch start rather than a batch finish, or something else that you don't need to worry about. You can filter by EventClass so that you only include event types that are useful to you (or, better still, make sure that you only include those events when you make your trace).
You'll end up with something like this:
SELECT * FROM TraceResultsLongRunningQueries
WHERE EventClass IN (<List of events you want to include>)
AND Duration > 0
AND Duration IS NOT NULL
ORDER BY Duration DESC
John
January 19, 2011 at 2:43 am
Hi John,
This below is the conversion from Milliseconds to Seconds.
Do you know what the conversion should look like for Microseconds to Seconds?
The research that I got from a Microsoft site says the Duration table is by default saved in Microseconds.
http://msdn.microsoft.com/en-us/library/ms175848%28v=SQL.110%29.aspx
Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.
I know this states from SQL 2005 but I am sure SQL 2000 should be the same?
SELECT [RowNumber],[EventClass],[TextData],[NTUserName],[ClientProcessID],[ApplicationName]
,[LoginName],[SPID],((Duration%(1000*60*60))%(1000*60))/1000 as Seconds,[StartTime],[Reads],[Writes],[CPU]
FROM
[master].[dbo].[TraceResultsLongRunningQueries]
order by Seconds desc
Thanks for all your help!
January 19, 2011 at 2:49 am
That looks very complicated. To convert milliseconds to seconds, just divide by 1000. To convert microseconds, divide by 1000000. I don't really understand why you need to do the conversion at all, though - you're interested in the longest running queries, and they are going to be the same whichever units you use.
John
January 19, 2011 at 3:14 am
Thanks for you help John.
I finally got a query that the time is right (I know this because there was a wait for delay for 1 minute in the result and it matched up with the Minutes tab.
SELECT [RowNumber],[EventClass],[TextData],[NTUserName],[ClientProcessID],[ApplicationName]
,[LoginName],[SPID],(Duration%(1000*60*60))/(1000*60)as Minutes,[StartTime],[Reads],[Writes],[CPU]
FROM
[master].[dbo].[TraceResultsLongRunningQueries]
order by Minutes desc
January 20, 2011 at 9:11 am
You are going about this the wrong way. What is MORE important (usually) than a single bad plan is how bad things are IN AGGREGATE. Which is more important to tune - something that runs for 5 minutes (that doesn't shut down the system anyway) but only runs ONE time a day or something that takes 1/2 second but gets called 10 times per second?? Aggregate trace analysis will reveal this and more.
Best is to replace parameters in the text with a placeholder (such as a question mark) and then roll things up. I have a quickie-simple way that avoids the need for that most of the time. here is an example:
SELECT top 100 substring(textdata, patindex('%exec %', textdata), 30) as textdata, avg(duration/1000.0) as avgdur, avg(reads) as avgreads,
sum(duration/1000.0) as sumdur, sum(reads) as sumreads, sum(cpu) as sumcpu,
min(starttime) as minstart, max(starttime) as maxstart, count(*) as cnt
FROM yourtracetable
WHeRE 1 = 1
--and textdata like '%nodeschedule%'
group by substring(textdata, patindex('%exec %', textdata), 30)
order by cnt desc
order by avgreads desc
order by sumreads desc
--order by avgdur desc
order by sumdur desc
You can easily change what you are looking for with the patindex/where clause, substring lengths, order by, throw in a HAVING clause, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply