As long as you aren't I/O or CPU bound, moving indexes to their own file group (preferrably on their own set of physical disks/controller) should improve performance - perhaps significantly...

I love having new tools to help me and my clients do our jobs more efficiently, but unfortunately I don't have time to install a big software package and play...

May I recommend that you have scripted files of every object you keep in your databases. Back them up, and also put them into source code control system as...

Personally I see the file system as the best place to store files and the database the best place to store other stuff. I can rattle off numerous benefits...

Hire an SAN-experienced sql server expert to give your systems a review. Way too many things can be mis/poorly configured in this setup!!

search support.microsoft.com for this

Error 17803 Insufficient memory available sql server 2000

and you can find numerous causes for this. See if any match your situation.

Also, perhaps you can...

I do think I can explain the cache miss: select * from table is a trivial plan and these are not cached.

+1 to the group that has never heard about capitolization mattering for cache hits for stored procedure calls. ad hoc I did know about. Very surprised about sprocs...

You can also use dbcc inputbuffer() to see executing code associated with a particular spid.

Are you seeing any blocking?

Definitely looks like someone or something is running some massive code.

It probably doesn't matter as long as the default setting of Autocreate statistics is adhered to and left ON. Then the optimizer will likely gather statistics and come up...

Per Microsoft (sorry, can't find a link) you should not set min and max memory equal. I recommend leaving a GB between them. Something about SQLOS internal memory...

Memory usage isn't the big problem . . . it is winding up with nested loop query plans accessing huge numbers of records that will kill performance.

Note that it is VERY important which order the columns are placed in the index however. Assuming all 3 columns are referenced in all queries, you should put the...

Jeff Moden (3/19/2008)But, I do understand your points. I just haven't run into any of those problems in the last 11.5 years. First 1/2 year was a bugger...

If you have small datasets AND any joins you do to the table variable do not result in large numbers of joined rows, then you are perfectly OK. You...