none
Who is running identified query ? RRS feed

  • Question

  • Hi All,

    We have identified certain queries using DMVs but we couldn't find who are the users of those queries. There is no baseline in place as well as MDW (Data collection services) is also not configured - Does SQL Server stores somewhere details about user ? Those queries are not running now.

    Does objtype 'Adhoc' mean queries is being used by some user rather than application ? A bit confused, so asking for help !

    cacheobjtype objtype
    Compiled Plan 
    Adhoc
    Thursday, March 16, 2017 7:03 AM

Answers

  • Unless you installed something to capture queries as they were run, there is nothing in SQL Server which allows you to go back in time and see queries which were run at that level of detail.

    Thursday, March 16, 2017 12:49 PM
    Moderator

All replies

  • SELECT [Spid] = session_Id
    , ecid
    , [Database] = DB_NAME(sp.dbid)
    , [User] = nt_username
    , [Status] = er.status
    , [Wait] = wait_type
    , [Individual Query] = SUBSTRING (qt.text, 
                 er.statement_start_offset/2,
    (CASE WHEN er.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE er.statement_end_offset END - 
                                    er.statement_start_offset)/2)
    ,[Parent Query] = qt.text
    , Program = program_name
    , Hostname
    , nt_domain
    , start_time
        FROM sys.dm_exec_requests er
        INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
        WHERE session_Id > 50              -- Ignore system spids.
        AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
        ORDER BY 1, 2

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 16, 2017 7:33 AM
    Answerer
  • AdHoc means that it was not a stored procedure, but a loose SQL statement. Whether it was from an application or SSMS, SQL Server has no idea.

    If you want to find out which users who is running these queries, you need to set up a trace or an X-event session with the appropriate filtering.

    Thursday, March 16, 2017 8:15 AM
  • SELECT [Spid] = session_Id
    , ecid
    , [Database] = DB_NAME(sp.dbid)
    , [User] = nt_username
    , [Status] = er.status
    , [Wait] = wait_type
    , [Individual Query] = SUBSTRING (qt.text, 
                 er.statement_start_offset/2,
    (CASE WHEN er.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE er.statement_end_offset END - 
                                    er.statement_start_offset)/2)
    ,[Parent Query] = qt.text
    , Program = program_name
    , Hostname
    , nt_domain
    , start_time
        FROM sys.dm_exec_requests er
        INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
        WHERE session_Id > 50              -- Ignore system spids.
        AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
        ORDER BY 1, 2

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Hello Uri,

    Thank you for taking time to help !

    Looks like above query fetches the present workload and returns the result. We have identified a query to tune which is not in execution and we don't know when will it be executed again. Now we want to know who has run that query.

    Appreciate your response !

    Thursday, March 16, 2017 10:13 AM
  • Thank you Erland for taking time to help !!
    Thursday, March 16, 2017 10:13 AM
  • If the query was very recently run and hasn't aged out of cache, you might be able to see a login tied to SPID with sp_who or sp_who2.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, March 16, 2017 12:55 PM
    Thursday, March 16, 2017 12:03 PM
  • Unless you installed something to capture queries as they were run, there is nothing in SQL Server which allows you to go back in time and see queries which were run at that level of detail.

    Thursday, March 16, 2017 12:49 PM
    Moderator
  • Thank you Tom for taking time to help !

    Unfortunately there was no process in place to capture the workload - your point makes perfect sense to me !!

    Thursday, March 16, 2017 6:09 PM
  • Thank you sir for your response - Appreciate it !!
    Thursday, March 16, 2017 6:11 PM