Index Behavior and Statistics
Posted on 2007-10-16
I'm taking session hits from a web server. The web server is passing in a GUID which is my Primary Key (non-clustered) for transactions. I also have an index on a date column. These sessions are replicated to a repository for analysis. Daily, I remove rows over 168 hours old (around 40,000 rows a day). During the course of this purge operation, a session comes along that wants to update a row, based on the GUID. That update forces an update of the PK statistics and generates tens of thousands of reads. The delay caused by this statistic update takes too long and fails the session (which forces me to pay the client back).
Could I recast the PK such that it's a combination of datetime and GUID with the datetime portion coming first? Would this create a more chronological sequence of rows, rather than the fully random one I'm 'enjoying' today?
Note that this system runs very hot. I essentially have no true maintenance window. My preferred solution is one I can effect on-line.