Server Too SLow to Run Profiler

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Server Too SLow to Run Profiler

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-02-07 : 06:25:06

I need to tune indexes on my sql2005 server but when I run a profiler trace using tuning template to get information about indexes, CPU becomes 50% to 70% busy
IS there any other way to get information about indexes? Can querying DMVs be trustworthy to apply the changes?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-07 : 09:38:43

See Standard Reports for CPU,I/O,Long Running Queries.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-02-07 : 12:51:25

DMVs can be useful , as long as the information is up to date.
As sodeep mentioned use the more refined reports , or you could consider , looking at you may think are some long running queries , and work from the Execution Plan to plan a index strategy.

If you take this approach , it is worth making sure your Statistics are up to date

Jack Vamvas
--------------------
http://www.ITjobfeed.com

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-08 : 04:27:13

Use a server-side trace instead of running SQL profiler. It will not affect server performance as much. You can create server-side trace script from SQL profiler then run the trace on the server.

See http://msdn.microsoft.com/en-us/library/cc297241.aspx for an example.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-08 : 06:58:10

Here is my script:

--This procedure will stop server-side trace after 30 mins:(Do some testing with this and it can be scheduled as well

--Stored Procedure to run trace for 30 minutes

Create Procedure dbo.Usp_Tracefor30minutes
as
set nocount on

declare @stop datetime 
set @stop = dateadd(mi,30,getdate())
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- Create traces and saves in folder specified
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @stop 
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on 
-- 17 - Existing Connections
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on 
-- 10 - RPC:Completed
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, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on 
-- 43 - SP:Completed
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 6, @on 
-- 37 - SP:Recompile
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @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 
-- 12 - SQL:BatchCompleted
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 
-- 13 SQL :BatchStarting
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'
set @bigintfilter = 5000-- Track queries more than 5 sec.
Exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

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

--Exec SP
Exec dbo.Usp_Tracefor30minutes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources