September 25, 2008 at 12:12 am
Comments posted to this topic are about the item Profiler - Logging Results to a Table
September 25, 2008 at 1:40 am
Good article.
September 25, 2008 at 1:45 am
I have used a lot of queries in where I am searching for the existance of the certain data.Suppose I have table Users and I am searching for the existance users from the county 'BerkShire' then I seen lot of programmers using
IF EXISTS (Select * from users where county ='BerkShire') then the results in the profiler shows all the rows which meet the where clause.
I always suggest them to use
IF EXISTS (Select 1 rom users where county ='BerkShire') I think the results in the profiler shows only 1
September 25, 2008 at 2:47 am
This article did a good job going over how the 'rowcount' option behaves but I didn't see how to log it to a table. Will that be part 2?
September 25, 2008 at 4:51 am
I found that the Logging to table option was only allowed in "live" profiler traces. I couldn't schedule a regular trace to use this via a SQL command.
But in the context of "proactive" and also once every couple of months this is probably acceptable operating practice.
September 25, 2008 at 6:29 am
The article was meant to cover rowcount and not logging, not sure if I got it wrong when I submitted it, or if Steve erred when posting, though I'd prefer to think it was his fault!
September 25, 2008 at 7:07 am
I found that the Logging to table option was only allowed in "live" profiler traces. I couldn't schedule a regular trace to use this via a SQL command.
I always have a problem (when running live capture) to table over a long period. Something always seems to mess up and its stops the trace.
So I dump everything to file, and then load the trace file into a table using the profiler. Takes a while on large trace files, but is much more reliable in my experience.
-I like the points on rowcount. One of the first things I look at along side duration. Gives a indication if duration is long and rows count is small. And of course a large row count on it's own is something to be investigated.
September 25, 2008 at 7:18 am
Ashwin M N (9/25/2008)
I have used a lot of queries in where I am searching for the existance of the certain data.Suppose I have table Users and I am searching for the existance users from the county 'BerkShire' then I seen lot of programmers usingIF EXISTS (Select * from users where county ='BerkShire') then the results in the profiler shows all the rows which meet the where clause.
I always suggest them to use
IF EXISTS (Select 1 rom users where county ='BerkShire') I think the results in the profiler shows only 1
You think, or you know???
It's been proven over and over and over again. if exists (Select *) vs if exists (Select 'Whatever else you can think of here') provide the exact same results, both in data and SPEED.
September 25, 2008 at 7:36 am
The article is fine and the rowcount analysis that he advocates is a good idea but I'm not a big fan of Profiler. It sucks up memory and is a performance killer. Rather, I use Profiler as a tool to save some typing. I configure the events I want to capture and then export the script trace definition (from the File menu) which generates the T-SQL calls to sp_trace_create, sp_trace_generateevent, sp_trace_setevent, sp_trace_setfilter, and sp_trace_setstatus. I then run the trace manually on the server saving the results into a file (not a table) rather than streaming the results across the network. Since this is what Profiler runs under the covers anyway, I can avoid the memory cost of the UI and I'm not competing with my production processes for the database resources associated with writing to a table. I can always import the file into a table later, and on a non-production server, for analysis. Here's an example of a trace script generated by Export.
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 09/25/2008 09:29:07 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 0f447290-e591-4a99-8017-43807f957eab'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
"Beliefs" get in the way of learning.
September 25, 2008 at 7:47 am
By the way, I learned about the technique for generating the trace script I described in my earlier post from a presentation by Andy Kelly at the New England SQL Server User Group. I'm embarrassed that I forgot to mention that in my original post as I think it's important to properly cite sources. I've used this technique many times now and it is very effective.
"Beliefs" get in the way of learning.
September 25, 2008 at 7:55 am
Robert, there is a time and place for both techniques, and I do use both. The point of the article though was just to talk about functionality and I thought that showing it visually worked a lot better than just adding one line to a script.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply