?
Solved

Index Behavior and Statistics

Posted on 2007-10-16
7
Medium Priority
?
249 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:authentify
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20089788
The order of the data in the table is defined by the clustered index. (if none defined, the heap index will define the order and that means that records are ordered the way they are entered.)
So if you define a new non clustered PK, your data is still not going to be ordered that way. Even so because the new column would be added 'at the end' the 'heap' index is not reorganized and you will still have this 'lovely' random order ...

Next to that I think - in my personal humble opinion - that a datetime column should not be added in a PK ... especially not when used for chronological reasons. Then just use a integer field with the identity turned on ... It will be a increasing number starting from 0. Much easier and you'll be able to put a clustered index on it for ordering your data in a chronological order. However - yes there is a downside to everything ! - you'll be creating a major hot spot for the table ...

Hope this helps ...

0
 
LVL 1

Author Comment

by:authentify
ID: 20097052
I'm tempted to remove the index altogether and let it be a heap. I agree that a sequential integer would be great (except if your exposed flesh is touching the last page <g>). However, I can't alter the schema. In fact, I'm dicing with the devil just to chance playing with the PK.

The PK is already non-clustered. Reindexing is completely out of the darn question. Would I be better off making an IX on the GUID with no PK, do you think? Might I then get more of a chronological order? I mean, the GUID's are nonsense but I need them to ID a row (I don't really care if they're alphabetical).
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20097164
a PK is for this scenario nothing else than a IX ... so dropping the non clustered PK and creating an non clustered IX would do absolutely no good what so ever. Using the GUID's there is no way you can get a chronological order in these records ... sorry !

A clustered PK would help to keep the hot spot out of the table, but you would get more page splits ...

Hope this helps ...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:authentify
ID: 20097281
I agree that this PK is more like an IX. When I first arrived here, there was a clustered index on the GUID. That was a real mess. We had to eat some failed sessions when I reversed that one.

What I have in mind is to combine the date col with the GUID col in the hope that my index pages could be somewhat more sequential. That way, when I kill older rows, the newer ones won't force a statistic update and 30,000 reads. Or am I still smoking off the wrong pipe?
0
 
LVL 18

Accepted Solution

by:
Yveau earned 1500 total points
ID: 20098860
As I already mentioned, I'm not very keen on datetime fields in a PK ... but in case of an emergency anything goes. I would give it a try ... a clustered IX on the datetime WILL make your table chronological ... if the hotspot at the end of the table becomes an is issue can only be experienced ... so I would give it a try ... but be prepared to undo this if it makes the situation worse ... It will at least makes sure there are no more page splits needed when inserting records, just adding new pages to the cahin, and that is a lot cheaper than splitting pages and putting new ones in the middle of the chain !

Hope this helps ...
0
 
LVL 1

Author Comment

by:authentify
ID: 20103829
Thanks, Yveau. I lashed this up in a test envir and it looks like I'm getting the behavior I want. I had to back out of a clustered situation, such as you suggest. It was too hot. Eliminating the index page splits and errant long reads was my immediate goal.

I've heard the advice about not making a PK from dates but in this case I figured it would work out because I'm combining it with the GUID. In general, I agree with you, especially since the resolution is not truly to the milisecond.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20104492
Glad I could be of any help !
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question