SQL Server Performance

Capture Queries Run on SQL DB

Discussion in 'SQL Server 2005 General DBA Questions' started by sonnysingh, Jun 25, 2010.

  1. sonnysingh Member

    Hi All
    I need to capture T-SQLs and transactions that run on SQL Server DB. How do I capture this information into table?
    Thanks in Advance...
    sonnysingh, Jun 25, 2010
    #2

  2. Luis Martin Moderator

    You can use Profiler for that.
    Luis Martin, Jun 25, 2010
    #3

  3. sonnysingh Member

    I knew that i can view in profiler for specific db, event etc....But transfer in table for analysis?
    Thanks....
    sonnysingh, Jun 26, 2010
    #4

  4. Luis Martin Moderator

    I can't follow you.
    If you need Profiler trace into a table just select table instead file, to store all information.
    Luis Martin, Jun 26, 2010
    #5

  5. satya Moderator

    Ok now it is clear, see when you open PROFILER and connect to the server you can see templates: Standard (default) by clicking on event selection you can see what kind of events are captured, same way you have templates for TSQL_duration, TSQL_Sps & SP_counts and once you choose relevant template you can store in a table where the information can be used to query the trace information as required by you such as between the dates etc.
    Also you can go thru Brad's ebook on mastering profiler: http://www.red-gate.com/products/SQL_Response/offers/Mastering Profiler eBook.pdf a must one for every DBA.
    satya, Jul 1, 2010
    #6

  6. FrankKalis Moderator

    On a sidenote: Depending on how busy that server already is you may increase the load considerably and may collect large amounts of data.
    FrankKalis, Jul 2, 2010
    #7

  7. Luis Martin Moderator

    Don't forget to include, in profile columns: duration, start time and end time (reading your needs).
    Luis Martin, Jul 2, 2010
    #8

  8. sonnysingh Member

    Well... thanks all of you ....Satya I have download the book and found really solid on the subject.
    Thanks Once again....
    sonnysingh, Jul 3, 2010
    #9

  9. sonnysingh Member

    I am wondering that is there any DMV that give us occurrence of different statements on the server for DBs. I have found one ....but not sure if this will help correctly....
    Select * from sys.dm_exec_query_optimizer_info
    where counter in ('optimizations','elapsed time','trivial plan','tables','insert stmt','update stmt','delete stmt')
    It give us info all DBs exist on a Instance. If this is correct then how come I can get occurrences on Select statement in similiar way?
    Thanks in Advance
    sonnysingh, Jul 5, 2010
    #10

  10. satya Moderator

    Sonny
    Your question is not clear enough, if it is not related to this thread please start a new post.
    satya, Jul 5, 2010
    #11

  11. satya Moderator

    satya, Jun 29, 2010
    #12

  12. sonnysingh Member

    Ok, let me elaborate the need...
    I need to capture number of T-SQLs (sps, functions, triggers, single T-SQLs) hits on the databases exist on a server in a day for week. How can I do it. I have tried and also read the articles (and couple of others) but didn't succeeded.
    That' why I have asked this question... A small example would be great help.
    Thanks in Advance
    sonnysingh, Jul 1, 2010
    #13

  13. shabnyc Member

    Run a trace using SQL profiler and make it record data into a table. set the trace to run in all databases on the server.
    hope that helps
    shabnyc, Jul 1, 2010
    #14

  14. sonnysingh Member

    Ok, let me elaborate the need...
    I need to capture number of T-SQLs (sps, functions, triggers, single T-SQLs) hits on the databases exist on a server in a day for week. How can I do it. I have tried and also read the articles (and couple of others) but didn't succeeded.
    That' why I have asked this question...A small example would be great help.
    Thanks in Advance
    sonnysingh, Jul 1, 2010
    #15

Share This Page