help finding query

I'm running version 10.2.1 of SQL DM.

Over this past weekend we ran some index rebuilds. Some of the rebuilds took a significant amount of time and I tried to find the queries in the history mode, but was unable to. I did many types of seach including wildcard using the 'include sql text' where I would put '%rebuild index%'. I tried to search for the specific database where I know the SPROC is calling the rebuild index process and couldn't find anything. The code is embedded/dynamic SQL so I don't know if that is the reason why I cannot find it, but I was hoping one of you guys could help me out and what I'm doing wrong.

Thanks,

Phil

  • If you use the history browser in the Sessions....Details page, you should be able to find the SQL command listed during one of the collection intervals while it was running (but while it was still incomplete). The Queries views are based around *completed* events, so for something that ran for a long time it can be a little tricky to find the listing. It's also possible that if the rebuild errored out or timed out that it might not be captured at all.

    Another approach to finding it, if you don't mind a bit of spelunking, would be to dive right into the DM repository. The SQLStatements and SQLSignatures tables have all the collected SQL text, and if you find the problem statement, you can join back to the QueryMonitorStatistics table to find references to when that SQL was executed.
  • In reply to Vicky Harp:

    Thanks Vicky. I'll check it out.