you need Ken Hendserson's  Guru's guide to sql server internals

isbn 0-201-70047-6

btw don't always assume table variables are created in memory, if sql server thinks it better it will write them...

I don't really understand what you are trying to achieve or why - but - if you take the i/o count and multiply by 8k then that's how much data...

select bucketid,cacheobjtype,objtype,objid,name,refcounts,usecounts,setopts,sqlbytes,sql

from master.dbo.syscacheobjects with (nolock) left outer join

xenon.dbo.sysobjects with (nolock)

on objid=id

where dbid= xxxx 

order by objid

put the dbid of your database into xxxx

adjust query to suit, you're interested in...

If sysperfinfo is empty it means the perfmon counters for sql server are not working - this is a known bug and there are lots of articles on how to...

there are a couple of dbcc commands that will clear the cache(s) but it does it for the entire server ..

sp_recompile removes the proc plans from cache.

dbcc freeproccache clears the...

Much of the consolidation is driven by "accountants" who try to do the whole thing with SAN's and servers. Lots of stats about cpu unused, disks unused, so you get...

is due to parallelism - may or may not be a problem - depends if you have lots of cxpacket waits.

Think you'll need to use a log reader, or some other tool - maybe Teratrax ( one that I use ), it's not an easy thing to set up to...

Don't confuse these counters with sql server database locking - they're not really related, well not in the way many people think. There's a really good ms press book on...

None that I can think of. Done this for several years.

much easier:-

alter database xxxx set restricted_user with rollback immediate

or

single_user

The Lumigent tool is good but you'll have to buy it to use it against your own database.

I think LOG PI gives you a full eval, I'm sure I've used...

I worry sometimes when I see posts which advocate disabling parallelism. Having worked with boxes with up to 16 processors I have never been keen to make changes. In most...

try profiler and compare rpc completion and sp completion - a difference indicates slowness with the client connection.

you might also want to check for set options in the connection string.

There are lots of factors that can affect performance other than procs. I'd run a trace for wait stats, I'd also check out disk i/o completion time and other disk...