May 23, 2018 at 2:26 pm
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?
May 29, 2018 at 9:03 am
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
May 29, 2018 at 9:54 am
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
May 29, 2018 at 12:52 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply