Since you've posted this question to a second location, we're now getting into cross-chatter. I made several recommendations over there for how to fix it. It depends on your data,...

Ha! In short, no, it doesn't "stay shrunk". As you add data (or junk mail) to the system, it's going to need to grow to contain that additional data. You...

I would love to...

OK. Please follow my advice about investigating the execution plans.

Modifying the query will cause a recompile. But you also get recompiles from other sources. You can get a recompile because...

I strongly recommend looking over the execution plan for the query and do your own investigation and tuning. The DTA is notorious for making poor choices at times and you...

You've conflated rows with pages. The number of rows doesn't really matter. It's the number of pages that affects how index defragmentation occurs within the index. If the index is...

Pretty sure Koen has nailed it already. The JOIN is likely to change the order in which data is retrieved, so a sort operation will be necessary.

It sounds like you're hitting bad parameter sniffing. But, without seeing the "good" and "bad" execution plans, it's hard to know for sure. You need to capture both plans and...

mister.magoo (4/29/2014)

---

Except that will be a Command Timeout, not...

Assuming you don't have throttling enabled and just have the default group, you can get it from here:

SELECT * FROM sys.dm_os_performance_counters AS dopc

WHERE dopc.counter_name = 'cpu usage %'

AND dopc.instance_name =...

According to the statistics you're moving 197 million rows with no where clause. Don't bother with an index. It won't do any good. As currently built out, there's almost nothing...

Yeah, but that's index fragmentation. Shrinking and growing a database file fragments at the OS level. There's a decent little article about how to look at this over here at...

TheSQLGuru (4/29/2014)

---

I think you can support multiple VMs without having a "killer laptop", especially if you have an SSD attached (or even a fast USB3 flash drive, like my Patriot...

Try using the WAITFOR command in T-SQL. That'll pause the execution of the query and you should see a timeout.

You can get the plan_handle by querying sys.dm_exec_query_stats or sys.dm_exec_proc_stats if it's a stored procedure. If you need to use sys.dm_exec_query_stats, you can combine it with sys.dm_exec_sql_text in order to...