Tasty Performance scenario to feast on, looking for general feedback and theories only

M_E_K (1/18/2013)

Looking for some ideas on a performance scenario we are seeing in production. I can't make any changes to production for a while, and I haven't found a way to repro in non-PROD, so just looking for feedback and ideas right now.

First I'll give some background on the setup, then a description of the problem, then my only theory so far

BACKGROUND
-----------
We have a tall-skinny table that we use as a caching mechanism in our application. It's only 2 columns wide and we have 1 unique index on ColumnA asc, columnB asc.

As far as the data shape and DML behavior goes, here are some notes:

- The first column is a unique identifier for a cache entry NVARCHAR(50), and the second column are the actually values for the cache entry NVARCHAR(30). The combination of GUID/Value is unique.

- Cardinality on the first column is low, and on the second column it is very high.

- For the majority of the day, the count of the table is zero rows

- for 2 hours between 12am and 2am midnight the business processes which use this cache table come to life and slam this table with inserts and deletes (no updates). One cache entry (ColumnA - GUID) can contain between a couple 100K to a couple Million entries (ColumnB - Value). Volume and activity do vary from night to night, but generally heavy during these 2 hours.

- I call it the accordian table. It can grow from zero to 100 million and then back to zero within a very small amount of time.

- the sub-select queries used to populate the tables are static and predictable nothing very complex.

- Most of the time the insert/delete queries all perform well

PROBLEM
--------
Every 3-4 weeks we get a hanging query that never finishes. This is a query that runs every day, and normally it runs in less than a minute. On the days we see the problem, I've let it run for over 6 hours and it hasn't completed. The minute I kill it and run it again, it completes in the sub-minute time frame again.

THEORY
------
I think the problem could be due to contention triggered by auto statistics in this scenario. SQL server can't keep up with constantly having to recompute stats for the index while the heavy insert/delete activity is going on. At some point query picks up a very bad plan and hangs. Has anyone else ever seen something similar caused by auto-stats?

QUESTIONS
----------
- Any way to pull the execution plan being used by a currently running query?
- Would it be advisable to turn off auto-stats for my index based on the described scenario above?
- Possibly unrelated question: From my understanding of Fill Factor, I think it would be preferable to have a low fill factor for the index on the table described above, but would there potentially be a huge performance gain if I lowered the FF from current 95% to say 70%?

I'm working to somehow reproduce this, but in the meantime, I was hoping I could get some general feedback on this kind of scenario.

CREATE TABLE [dbo].[M_C_CONTACT] (
[GUID] [nvarchar](50) NOT NULL,
[QUALIFIED_ID] [nvarchar](30) NOT NULL
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [M_C_CONTACT_U1] ON [dbo].[M_C_CONTACT]
(
[GUID] ASC,
[QUALIFIED_ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]