Post reply

SQL PROFILER

  • November 15, 2010 at 4:39 am

    #226634

    Hi all,

    How can I retreive all the queries that I ran on a particular database using sql profiler?

    Any help is really appreciated

    Thanks

  • November 15, 2010 at 4:46 am

    #1250624

    Deepthy (11/15/2010)

    Hi all,

    How can I retreive all the queries that I ran on a particular database using sql profiler?

    Any help is really appreciated

    Thanks

    you cannot retrieve historical information unless a trace was already running.

    If you want to retrieve sql queries in future, you need to setup traces. Open profiler, select stadard template, write the results to a file, go to events selection tab and deselect login events and run profiler for the duration you want to investigate. then stop the profiler and use trace stored procedures to read from the trace file created. its recommended to run server side trace instead of profiler on a heavily loaded production system.


    Pradeep Singh

  • November 15, 2010 at 4:50 am

    #1250628

    Thank you very much for your reply

  • November 16, 2010 at 2:02 pm

    #1251577

    I have gotten lucky and been able to retrieve queries from the Plan Cache in the past. Keep in mind these were Ad-Hoc statements that a particular user was working with. However, there are times where you can go to the Plan Cache and retrieve histrorical queries. I've provided a basic statement you can use to do this.

    SELECT TOP 100 usecounts,

    cacheobjtype,

    objtype,

    (size_in_bytes/1024) AS size_in_KB,

    [text]

    FROM sys.dm_exec_cached_plans P

    CROSS APPLY sys.dm_exec_sql_text(plan_handle)

    WHERE cacheobjtype = 'Compiled Plan'

    AND [text] NOT LIKE '%dm_exec_cached_plans%'

    ORDER BY size_in_bytes DESC

    😀

Viewing 4 posts - 1 through 3 (of 3 total)

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