July 25, 2008 at 8:00 am
4 Processor, 6GB RAM, SQL Server E.E 2000 (SP4)
Total No. of tables 60.
Table detail:
Name: tbl_tracking_dtl
Description: Our site's every web page has Page_ID, and when user hit any page it record page_ID and hit time of that session
Columns:
1- Tracking_Dtl_Id int PrimaryKey (Non-Clustered Index) - auto ID
2- session_id varchar(50)
3- DateStamp datetime - Clustered Index on (session_id, DateStamp)
4- Page_ID int
5- BandwidthScheduleAvailability Bit
Sample session_ID:
1- '137EC51C298540321'
2- '6A576A85323851AED'
I have following SP and profiler catched this SP as heavy query:
CREATE PROC usp_SetBandwidthAvailabilityBit
@session_id varchar(50)
As
Update tbl_tracking_dtl
set BandwidthScheduleAvailability = 1
where session_id=@session_id
and Tracking_Dtl_Id = (Select MAX(Tracking_Dtl_Id) from tbl_tracking_dtl where session_id=@session_id)
There are almost 25821780 rows in this table.
Could you plz suggest:
1- proper indexs for this table and
2- Correct above query.
July 25, 2008 at 9:06 am
It looks like SessionID is some sort of GUID-like value ?
These are usually poor candidates for being in the clustered index, unless they are always generated in an ascending order.
If SessionID is random in nature, you get heavy fragmentation of the clustered index.
I would suggest the clustered index be narrow and on a value that is continually ascending in value, like a datestamp or the Identity column.
July 25, 2008 at 9:31 am
I would cluster on the Identity column which will help with the MAX and the filter on the max. And depending on the the other queries affecting this table a non-clustered index on session_id. Then your update should use a non-clustered index seek on the index on session_id and you subquery should to a clustered index seek on the identity index. This may not be the best indexing for other queries and updates on the table as well.
You definitely want to remove sessionid from your existing clustered index as that is causing huge fragmentation of the clustered index. A clustered index should be on a monotonically increasing value like a date stamp or identity column. All non-clustered indexes include the clustered index key.
If you need to because of other queries put a non-clustered on DateStamp with datestamp as the first column.
At the very least reverse the order of your clustered index column to DateStamp, SessionID.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply