In my experience, SSMS 2016 Will remember how the connection was last used.  Unfortunately I haven't found a way to configure it that way in the registered servers.

Correct, if you look at my query that's what I'm doing. My question is getting an accurate number for the size of the datacache.

I understand that the old rule of 300 is not appropriate for current hardware, which is why I'm creating an adaptive formula.

Sorry, for tempDB it may be different. For user databases

CREATE DATABASE [testDB]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'testDB', FILENAME = N'q:\testDB.mdf' , SIZE = 4096KB...

How much memory is on the server, and what are your min/max memory settings?

No, SQL Will not allow you to use a disk that is not part of the cluster group.

There are multiple indexes on the table. There is one that hasn't had a seek, scan or lookup, but has been updated 223,000,000 times. There's another index...

So, possible solutions. I identified an index That is on the table, but isn't being used that I plan to remove. I also am looking into...

<deadlock-list>

<deadlock victim="process15cdabe928">

<process-list>

<process id="process15cdabe928" taskpriority="0" logused="0" waitresource="PAGE: 5:3:695694 " waittime="2961" ownerId="592144764" transactionname="SELECT" lasttranstarted="2013-10-28T18:08:06.970" XDES="0x18fbe00d80" lockMode="S" schedulerid="2" kpid="6252" status="suspended" spid="77" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2013-10-28T18:08:06.967" lastbatchcompleted="2013-10-28T18:08:06.967"...

When I refresh the sharepoint page, and then run whoisactive I do see that the SP does have 1 in the open_tran_count counter, and that seems to stay at...

I'm at a loss. I can't find the actual source of the slowdown. I've been working on this for 3 days now.

When I run a profiler trace it's like...

It's almost like it is blocking itself.

The Select queries are the actual work within the stored procedure itself. I broke out the SP, and ran it as a batch,...

Thank you for the quick response.

I was under the impression that when I would see the 'create procedure' in the sp_whoisactive that meant the SP's were recompiling. My bad. ...

The rename will fail.