June 24, 2012 at 5:00 pm
Current configuration:
Server P (OLTP, Publisher + Distributor)
--transactional,push-->
Server S (Reporting, Subscriber)
Table TimeSheetData ( BranchId, TimeID, PostedDate, etc.)
PK, Clustered = BranchID + TimeID
Index, Non-Clustered = PostedDate
Rows = 50 million
This is the config on both servers.
But most activity on either OLTP or Reporting servers happens for PostedDate > getdate()-21 (last 3 weeks)
What is the standard best practice for indexes on such systems? e.g., Clustered on PostedDate, Sliding Partition, Archive data to history table etc.
Will Clustered on PostedDate speed up Reporting server more compared to Non-clustered?
Will Clustered on PostedDate slow down either system due to page locks during insert (since all inserts happen on last page) ?
June 25, 2012 at 4:30 am
The only way to know for sure whether or not changing the location of the clustered index is going to speed things up is to test it.
If your primary access to the data is through that date range, then, yes, I suspect you'll see a pretty substantial performance improvement. On the other hand, how many new rows are inserted to that data over a three week period? You might be pulling so much data that you don't get any benefit from the cluster. It's hard to know for sure, not being there or having the structures and data to play with.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2012 at 6:34 am
Agreed, you will have to test against your reports or at least thier SQL. Taking the report SQL and executing it in SQL Performance tunner should help with that.
Knowing how these things work as well as I do, and having worked with several similar published databases, whatever your choice changing the Clustered Indexes and Primary Keys will require a re-sync of all transactional subsribers in your environment.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply