October 6, 2010 at 8:11 am
Performance Tuning
October 6, 2010 at 8:13 am
Hi, I am using the "Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) " version and I am not ahving SQL profiler. Yet I want to capture the trace somehow through query writing. Can anybody help me on this pls????????
October 6, 2010 at 8:24 am
I use this stuff:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
October 6, 2010 at 9:21 am
If you stop the profiler trace, then click on File, Export, Script Trace Definition, and then select the version of SQL Server you want to output it for, you can generate a TSQL trace script.
For more details check out Gail Shaw's article[/url].
"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
October 6, 2010 at 10:05 am
Standard edition has profiler, though you should probably rather use a server-side trace.
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
October 6, 2010 at 1:13 pm
October 7, 2010 at 6:50 am
Thanks for the quick reply.
But the problem is the company does NOT allow to run the profiler due to some policies. hence I was trying to find if ther is any way to capture the trace by writing queries using system tables like sys.trace_events and find the performance leakage points.
I have been using MS SQL past from 6 Years and thought ther might be some way to do this. Please let me know if this is possible.
October 7, 2010 at 6:55 am
I posted a link in my earlier reply.
October 7, 2010 at 7:06 am
vijay1327-891581 (10/7/2010)
Thanks for the quick reply.But the problem is the company does NOT allow to run the profiler due to some policies. hence I was trying to find if ther is any way to capture the trace by writing queries using system tables like sys.trace_events and find the performance leakage points.
I have been using MS SQL past from 6 Years and thought ther might be some way to do this. Please let me know if this is possible.
Right, like I said, you don't want to run Profiler on the production system, you want to use it to generate a script. You can use Profiler to lay out which events & columns you want to capture, and then export that to a script. It's just an easy way to generate the script. If you follow that approach you'll have a TSQL script that uses sp_tracecreate & sp_tracesetevent, just like what you want.
Don't run the Profiler GUI against production systems, ever. That's a good policy the organization has.
"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
October 7, 2010 at 7:08 am
vijay1327-891581 (10/7/2010)
But the problem is the company does NOT allow to run the profiler due to some policies.
Do they forbid server-side traces as well (thought that is a bit splitting hairs)
You can use some of the DMVs, but you will not get everything.
http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/
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
October 7, 2010 at 7:44 am
I used a proc called NOW. not sure where I got it, but when you want to see all the queries that are on the server.. when the server is slow, this one is the bomb.
USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[now] Script Date: 10/07/2010 09:42:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[now]
as
set nocount on
declare @handle binary(20),
@spid smallint,
@rowcnt smallint,
@output varchar(500)
declare ActiveSpids CURSOR FOR
select sql_handle, spid
from master.dbo.sysprocesses
where
--sql_handle not in (0x0000000000000000000000000000000000000000)
spid <> @@SPID
-- and (upper(cmd) not in (
-- 'AWAITING COMMAND'
-- ,'LAZY WRITER'
-- ,'CHECKPOINT SLEEP')
--)
order by cpu desc
OPEN ActiveSpids
FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid
set @rowcnt = @@CURSOR_ROWS
print '===================='
print '= CURRENT ACTIVITY ='
print '===================='
print ' '
set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)
print @output
WHILE (@@FETCH_STATUS = 0)
BEGIN
print ' '
print ' '
print 'O' + replicate('x',120) + 'O'
print 'O' + replicate('x',120) + 'O'
print ' '
print ' '
print ' '
select 'loginame' = left(loginame, 30),
'hostname' = left(hostname,30),
'database' = left(db_name(dbid),30),
'spid' = str(spid,4,0),
'block' = str(blocked,5,0),
'phys_io' = str(physical_io,8,0),
'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END ,
'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),
'program_name' = left(program_name,50),
'command' = cmd,
'lastwaittype' = left(lastwaittype,15),
'login_time' = convert(char(19),login_time,120),
'last_batch' = convert(char(19),last_batch,120),
'status' = left(status, 10),
'nt_username' = left(nt_username,20)
--into #working1
from master..sysprocesses
where spid = @spid
--and status <> 'sleeping'
print ' '
print ' '
-- Dump the inputbuffer to get an idea of what the spid is doing
dbcc inputbuffer(@spid)
print ' '
print ' '
-- Use the built-in function to show the exact SQL that the spid is running
select * from ::fn_get_sql(@handle)
FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid
END
close ActiveSpids
deallocate ActiveSpids
GO
October 7, 2010 at 7:44 am
also be sure to output to text not grid!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply