I've built my own monitoring system several times. I wrote it all down in Chapters 2 & 3 of my book. I use a combination of performance monitor, SQL Server's...

You would already have to have in place auditing. Capturing RPC Starting from Extended Events or Trace Events and then comparing that to RPC Complete will let you know when...

Yes & no. It just stops allowing writes. You get errors. Reads work fine. All writes stop because there's nowhere for them to go.

It's not a question of which tables are accessed. It's a question of the queries being run against the indexes and statistics in support of those queries. Instead of looking...

Your choices are pretty limited. You can remove data and then shrink the database. You can use storage compression if you're using Enterprise in SQL Server. You can look at...

ChrisM@Work (11/29/2012)

This gets my vote,...

It's possible. Once you're in a VM, you can't trust those numbers. However, I'd check to see that you have Max memory set appropriately so that it is using what...

Yep. Looked at the first plan NO_INTO, and it's a timeout from the optimizer. That means, even the "good" plan is not necessarily an adequate plan. But that does explain...

Check this, look at the properties of the SELECT operator for all the plans you have. Considering the complexity of what you're doing, I suspect you'll see that the reason...

Pretty sure it updates the physical storage and then the indexes... except where the index is a clustered index, which you didn't mention. Then, the index is the physical storage...

I have yet to see a third party tool that makes good recommendations for what to do about poor query performance. You can get some generic checks on the low-hanging...

Personally, unrestricted with lots and lots and lots of monitoring and alerts in place so that I can try to prevent the disks from filling up. Either approach has downsides....

You can query the Dynamic Management Objects for this information. sys.dm_exec_query_stats will show you which queries, currently in cache, are using the most ram, cpu & disk. You can combine...

Just remember, SQLIO is a stress test tool. If you're talking about validating your production system, you will be applying a load to your disks that is very likely to...

Thinking about it some more, take a look at sys.dm_os_wait_stats. That's going to tell you, in aggregate, what is causing your system to run slow. That will indicate where the...