I'm hoping someone can point me in the right direction. I would like to identify and log all the poor performing queries in my data warehouse. I know this could easily be accomplished by running a trace through profiler with a filter on duration but I want to avoid running a 24/7 trace. The dmv sys.dm_exec_query_stats provides 99% of the information I need but is missing 2 very important pieces on information I need...Loginname and HostName. Not only do I want to identify the poor performing SQL but I also want to know where it originated from and who performed it. Is it possible to obtain this information without capturing it through profiler?
Any help would be appreciated!