TheSQLGuru (8/22/2013)
1) Sorry, but I cannot ignore having session state stored in SQL Server. :w00t:2) You have a varchar(100) as your clustered index. Does it look like a GUID by any chance? If not, does it have new values throughout the range of characters? If so, you are likely getting page splits all over the place during inserts. You are also carrying that fat field as the pointer on the NC index too, bloating it
3) You are very likely NOT getting in-place updates on your first update. Both fields are NULLABLE and as such when you give them a value for that update they become LARGER. Page split potential again. And for that you are moving about 4K of data to a new page.
4) All of the above is exacerbated by having a defaulted fill factor. Bad news there.
5) Have you done any analysis to determine if the second update actually increases field sizes much if at all? Review log file records to see what is really getting written there. ApexSQL has some nice capabilities for that, and there is the semi-documented fn_dblog command you can use as well.
6) I would analyze page splits and fragmentation and adjust fill factor(s) as needed.
7) Consider removing all NULLs and using appropriate DEFAULTs if you can.
Above all else I would move my session storage to an appropriate system. 😀
1 - yes...unfortunately thats the way it is for now. On the brighter side, there is a project in motion to move it.
2 - yes it is a GUID and yes we are definitely getting page spits. This table is extremely un-healthy, at it mature state it basically works at a little better than one row per page. Yes you are correct inserts are page splitting as I would expect. But, my question deals with the updates...why updateA would perform worse than updateB?
3 - Yes agreed, but these rows will be updated thousands of times before either timing out or being logged out. The null values should only be present until the first update. Every update after that should be in-place. Unless I am misunderstanding something.
4 - I'm not sure there is a good fill factor for a table of this nature. There is definitely session bloat present. 7400 bytes average per session with a max of 50,000 bytes.
5 - yes, the SessionItems is a volatile column that will fluctuate with size so my assumption is that the second update is truly an EXPENSIVE update.
6 - What would you suggest for fill factor on a table like this? 100%?
7 - This would be a good start.
Thank You for the response