Post reply

Performance Tuning

  • October 6, 2010 at 8:11 am

    #229130

    Performance Tuning

  • October 6, 2010 at 8:13 am

    #1232581

    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

    #1232600

    I use this stuff:

    http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

  • October 6, 2010 at 9:21 am

    #1232703

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 6, 2010 at 10:05 am

    #1232757

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 6, 2010 at 1:13 pm

    #1232967

    It should be in tools tab in SSMStudio.

    Look for it or turn on your activity monitor and right click on any SPID click trace it opens profiler aoutomatically.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • October 7, 2010 at 6:50 am

    #1233486

    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

    #1233492

    I posted a link in my earlier reply.

  • October 7, 2010 at 7:06 am

    #1233514

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 7, 2010 at 7:08 am

    #1233517

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 7, 2010 at 7:44 am

    #1233567

    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

    #1233568

    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