shahgols (6/20/2012)

---

Just as a reference, the query returned the following error:

Msg 536, Level 16, State 5, Line 1

Invalid length parameter passed to the SUBSTRING function.

I "fixed" it by adding the...

Just got released today.

Procedure cache comes & goes to a degree. Sounds like you're fine. Personally, I'd turn on the ad hoc optimization. It doesn't hurt anything, but it can help a little.

Every query goes into the cache. Not just stored procedures or prepared queries (parameterized queries). All the ad hoc ones go there too (except when you've enabled 'Optimize for Ad...

I just see this self-referencing foreign key

ALTER TABLE [dbo].[AT] WITH CHECK ADD CONSTRAINT [FK_AT_AT] FOREIGN KEY([AT_id])

REFERENCES [dbo].[AT] ([AT_id])

It joins the same table to itself.

You can pull stuff straight out of the cache using dynamic management objects (DMO). The main couple that you're going to be interested in are sys.dm_exec_query_stats and sys.dm_exec_sql_text. Here's the...

matthew.peters (6/19/2012)

---

Hey Guys

Thanks for the solutions, they're all great ideas, I'm looking to change the query structure first and see how that goes.

two questions

...

Even with queries, they're stored in cache. You'll...

Nothing wrong with Olla scripts at all. I do thing Michelle Ufford's are also worth a look[/url]. They're simpler and easier to deal with I think.

You might also want to...

Duplicate question. Please direct all answers to this post.

Please don't cross-post in the different discussion groups. Most of them are read by the same people. It just leads to confusion.

True. If the queries age out of cache you're in trouble. But, you'll get the most active queries immediately and you can capture more by rerunning the query against the...

All the hints included in a query are stored with the execution plan, so you could query the plans, using XML, to pull the information out of the DMVs. There...

It's xml_deadlock_report event, which is the deadlock graph.

Hmmm... A couple of points. I encrypted a proc and made a deadlock occur with it. I did get back the statement information as part of the XML deadlock graph...

Oh, you're pulling it from the extended event data. That's great. Well done. Still, doesn't change things. I'm not sure how encryption affects it. I'm going to try to set...