Table Partition Size

Ignition
1

Hey guys,

I have my tag history tables set to 1 month partitions, I checked last months table and it was over 10 million rows. It seems like the trending was starting to slow down towards the end of the month, so I am getting a little concerned. Should I consider a smaller partition size or is this ok?


I know more history stuff will be added in the future so if these tables have a max size/performance limit I’d like to try and curb it now. I’m using MSSQL 2005 (it will move to a clustered database in a few weeks to so that may improve performance)

Also, if I change from month partition to week partition or something like that how will it affect the tables that are already there in month partition? Will it ignore them or start over from scratch?

2

Hi,

A number of things:

  1. If you change the partition size, the previous partitions will be left alone, the current partition will be capped at the current time, and a new one will be made for the size you specify. New data will go into that one.

  2. The ideal partition size is really related to what you normally query. If most of your queries are within the last week, changing to that size would probably be a good idea.

  3. In regards to performance, though, a few points:
    i) We’ve found that making the index on the “t_stamp” column clustered can help a lot. In the SQL Manager, you can just expand the table, find the index, edit it and change it to “clustered”. This will lock the table for a few minutes, so you might not want to do it in the middle of a lot of action, but I would recommend trying it at some point and seeing how it affects performance for the 1 month table. In 7.3, we’ve made it so it makes that kind of index for you automatically. In the mean time, you would just have to change it by hand at the start of each month.
    ii) Also in 7.3, we’ve modified how the analog deadband works, to make it smarter. This should give you more power to optimize how values are stored, and reduce the amount of data in the DB.

Let me know if you have any questions. I would try changing the index and see if that helps performance. If so, you can probably just leave it at 1 month. With proper indexes, there shouldn’t be a need to do a “table scan”, which in turn means that the size of the table shouldn’t make much of a difference.

Regards,

3

Nice, that’s good information. We don’t usually query anything greater than a week at a time (I would say 95% of the time is a week or less) so I’ll change our partition length to a week. I’ll check out the clustered index option as well and see if that makes a difference. Thanks, Colby!