SQL Server Performance

Transaction backlog counter/query?

Discussion in 'General DBA Questions' started by KirbyWallace, May 18, 2006.

  1. KirbyWallace New Member


    Is there a perfmon counter, or a query of any sort that can be executed to determine the number of transactions queued for processing?

    I'm assuming this might be possible because MSDE (free SQL) has a limitation of 8 concurrent queries and will leave you little nastygrams in the event viewer if you start exceeding that.

    So it MUST be able to count how many queries it is currently processing or attempting to process. If MSDE can count how many transactions it has queued, I'm hoping I can too in Full SQLServer2000.


    Thanks,

    Kirby



  2. cmdr_skywalker New Member

    i am not sure what you mean by "transaction queued...limitation of 8 concurrent queries...".

    the number of transaction is not dependent on the number of connections (queries) you currently have. To count the numbers of existing connection, use the query below:

    create table #t (spid int, ecid int, status varchar(100), loginname VARCHAR(100), hostname varchar(100), blk int, dbname varchar(100), cmd varchar(100))
    go

    insert into #t
    exec sp_who
    go

    select count(*)
    from #t
    where isnull(hostname,'') <> ''
    go

    drop table #t
    go

    to count active transaction for the current connection, use the @@TRANCOUNT
    to count open transaction, use DBCC OPENTRAN




    May the Almighty God bless us all!
    www.empoweredinformation.com
  3. KirbyWallace New Member

    quote:Originally posted by cmdr_skywalker

    i am not sure what you mean by "transaction queued...limitation of 8 concurrent queries...". the number of transaction is not dependent on the number of connections (queries) you currently have.

    I think that's actually answering the question. I was assuming that since MSDE could tell me (through the event log) that it was "maxing out" that it therefore had some way of counting how many transactions were pending. You seem to be saying that MSDE limits itself to 8 simultaneous queries by limiting itself to 8 simultaneous connections. It is simply counting concurrent connections, not concurrent transactions, and that would make sense. Dunno why I didn't think of it right away.


    So, I guess my question is, does anyone know of a way to tell how many queries are, at any given moment, backlogged and awaiting execution.

    Another way of visualizing my question: I submit 5000 queries all at the exact same time. How can I tell how far along it is in the process of executing them all... I'm looking for something that says "There are 4000 queries pending execution... 3000, 2000, 1000,..." All the way down to "ZERO pending".




  4. satya Moderator

    I guess that depends on the memory available to SQL server and in order to continue the transactions there shouldn't be any interruption between the servers.<br /><br />Refer<a target="_blank" href=http://vyaskn.tripod.com/com_isolation_level.htm>http://vyaskn.tripod.com/com_isolation_level.htm</a> link and it refers <i>SQLServer<img src='/community/emoticons/emotion-2.gif' alt=':D' />atabases Transactions/Sec Indicates the number of transactions per second. </i>... that might help you.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>

Share This Page