Post reply

Sporadic Performance Issues - bottleneck SQL

  • November 17, 2006 at 9:54 am

    #117627

    Hi All,

    Firstly - I'm a new registration - this is truly a great resource. Lots of very useful information to go on - hope I can learn to give as well as ask of the community.

    I'm seeing sporadic performance issues on a db that I really wouldnt expect to see, it's effectively oltp, with anywhere up to 50 users hitting hot tables at the same time.

    I've seen these types of things previously in Sybase....that's my background.

    Previously I used the following code to :

     1. Source the object/sproc being called by a spid

     2. Identify the areas of contention on the db by the types of locks, and # of locks on an object.

    I'm looking to try and port this into MS SQL, however being a complete newb on this technology is limiting me.

    I'm looking to identify the equivalent from sybase of the master..sysprocesses id column whereby the ID of the object is given against the SPID of a row in sysprocesses, this will allow me to report the object in use in a nice simple select statement, like the one below :

    SELECT

    CONVERT(CHAR(4), spid) 'Spid',

    SUBSTRING(suser_name( suid ), 1, 13) 'Username',

    SUBSTRING(program_name, 1, 16) 'Program',

    SUBSTRING(CONVERT(CHAR(4), blocked), 1, 3) 'Blk',

    cmd 'Command',

    cpu 'CPU Ticks',

    physical_io 'Physical IO',

    CONVERT(CHAR(40), RTRIM(db_name(dbid)) + '..' + object_name(id, dbid)) 'SProc'

    FROM master..sysprocesses

    WHERE ltrim(rtrim(cmd)) != 'AWAITING COMMAND'

    Any thoughts please? ---I'm hoping this will be a REAL easy one

    Apologies if I posted this in the wrong forum -- it is P&T related !

  • November 20, 2006 at 6:00 am

    #672910

    as far as I know the columns are the same, think sp4 might have modified sysprocesses a little, your error is in the use of object_name() which doesn't take the database parameter.

    sp_who2 active will assist in returning results.

    There are plenty of sripts published, look in script library, which cover this type of query.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply