Determine Where a Query is Coming From

  • Del Lee

    SSCrazy

    Points: 2780

    Hey, I have enabled Query Store and I'm seeing at query that's occurring frequently doing a SELECT * on a specific table.  I would like to narrow down where this is coming from and see if we can find a better way than doing the SELECT *.  I have a query id, of course, from Query Store.  I've also recently asked my developers to include Application Name in their connection strings, which many have done.  Is there a way (perhaps using a DMV, for example) that I can find out the Application Name associated with this query?



    Del Lee

  • Kenny Jozi

    SSCrazy

    Points: 2004

    Try using the query below to get query plan, that might help you with figuring out which procedure is being executed .

    SELECT qq.query_hashqq.query_hash , qq.initial_compile_start_time, qq.initial_compile_start_time , qq.last_compile_start_time, qq.last_compile_start_time , qq.last_execution_time, qq.last_execution_time , qq.avg_compile_memory_kb, qq.avg_compile_memory_kb , qq.last_compile_memory_kb, qq.last_compile_memory_kb , qq.max_compile_memory_kb, qq.max_compile_memory_kb , qp.compatibility_level, qp.compatibility_level , , castcast (qp.query_plan (qp.query_plan asas XML) query_planXML) query_plan , t.query_sql_text, t.query_sql_text , t.statement_sql_handle, t.statement_sql_handle , qp.Query_id, qp.Query_id FROMFROM sys.query_store_query_text tsys.query_store_query_text t JOINJOIN sys.query_store_query qqsys.query_store_query qq ONON t.query_text_id = qq.query_text_idt.query_text_id = qq.query_text_id JOINJOIN sys.query_store_plan qpsys.query_store_plan qp ONON qp.query_id = qq.query_idqp.query_id = qq.query_id ORDERORDER BYBY QQ.last_compile_start_time QQ.last_compile_start_time DESCDESC

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • Del Lee

    SSCrazy

    Points: 2780

    FYI, I also posted this question on StackExchange and got a nice example of using Extended Events to give a possible solution:

    https://dba.stackexchange.com/questions/207684/where-is-this-query-coming-from/207779?noredirect=1#comment407134_207779



    Del Lee

  • Eric M Russell

    SSC Guru

    Points: 125089

    Yes, an extended event or sql profiler trace is needed to tell you the client host name and/or account name that executed the query. That information isn't contained in execution plan cache or query store.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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