Lots of things like ADOc, ORMs, autogenerate code, SSIS, etc gather information about recordsets and other metadata under the covers. I recommend you use various columns in sysprocesses or...

one thing no one asked is "what do you intend to do with this single-value once you get it"?? That is by far the most significant factor in what...

Third time this morning - but yet again we have a situation where a professional can help out TREMENDOUSLY. Refactoring cursors into set-based operations can be EXCEEDINGLY difficult for...

Once again we are moving into the world where a professional would be worth his/her weight in gold - or at least silver (metals are SOOOO inflated in price these...

Usually "I" am the one (or first one) to recommend hiring a professional to help with scenarios like this. But since several others have already done that I feel...

I had to do a similar home-grown approach for a client of mine with many thousands of databases on a single server that we needed to get over to a...

1) check for blocking

2) statistics could well be old, leading to poor query plans on report server

3) get a professional in to do some performance analysis and tuning, and teach...

1) I too am confused about the actual 'question' here.

2) I don't think you want all those sp_executesqls. Probably just want a single concatenated string and use exec (@sql).

3)...

Myself and a number of other MVPs have been hitting Microsoft up to get them to make cursors perform much better on the platform, which would be a benefit to...

Perhaps a Plan Guide to force the query that you think is optimal? Note that this can lead to disasterous performance for some other input parameters though!

Maximum wait time is pretty useless as an IO performance metric. Much better is to use avg disk sec/read and avg disk sec/write for the appropriate physical volumes.

I would say to stop using the maintenance plans for maintenance activities. Use Ola Hallengren's stuff instead. Sooooo much better!

http://ola.hallengren.com/

I would recommend getting a performance tuning professional to help you out. There is a wide variety of things that could be root causes here, and such a person...

Jeff Moden (6/8/2010)

---

Sandy2704 (6/8/2010)

---

On further investigating the High transacttions/sec i noticed it is due to high number of SQL Compilations/sec......It seems some of the statements don't have the Actual Plan...

sounds like the classic bin-packing problem, or a variant of it: http://en.wikipedia.org/wiki/Bin_packing_problem

See here for some SQL Server specific goodness. Note there are multiple parts to this series,...