Looking for scale out option for my SQL server database.
I have a table that has 15 million rows and it adds 10,000 rows every day.
From various options available to scale out I opted for partitioning, as changes need to be done only in database and the application code will remain untouched.
Table I m partitioning is BOOKING table it has following columns:-
1) Bookingid
2) Ordered
3) Userid
4) Starttime
5) Endtime
And many more
I decided to partition this table on yearly bases, so I selected Starttime column as my partition key. First I created a copy of BOOKING table as BOOKING_Partition, imported all the data of Booking table into Booking_Partition table.
Steps followed for partitioning:-
1) Create file groups
2) Attach files to file groups
3) Create partition function
4) Create partition scheme
6) Created cluster index with Starttime (partition key) and Bookingid (I used combination) on partition scheme
7) Finally I checked by running query if partitioning was successful. Yes my table was partitioned successfully.
Now I wanted to check my existing query if there was any performance change I checked query that was most frequently used, if fetches data for 40 days whenever application loads
SELECT Bookingid, OrderId, UserId FROM Booking_Partition WHERE Starttime >= GETDATE()-20 AND Starttime < GETDATE()+20
Above is just an example, on production this query has joins to get data from other table.
When I run this query on partition table and on non partition table profiler shows me almost same duration, there is no difference in performance.
Image for Select query execution plan:-
Above plan is for partitioned table, it shows hitting the partition, but as I said profiler is showing no change in duration for partitioned and non partition table. Also I can’t understand why it is hitting 4 partitions for fetching 40 days of data.
Now my main concern arrives when I do UPDATE or DELETE.
When I update or delete on partition table it shows eager spool in execution plan.
Image for Update query execution plan showing eager spool:-
I can’t understand why it is doing eager spool only when I am updating or deleting on partition table, same query when I run on non partition table it does not do eager spool.
