I have a record table that receives records of several applications, several inserts per second, that approximate ~ 1M of rows per day. The table has a dozen columns (mostly nvarchar), for example. gravity, name of the application and message, and it also has a clustered cluster ID and some computed columns. A "real" column is JSON data, and a couple of the calculated columns use the JSON functions to extract pieces of this. The other calculated column consists of a "prefix" of the timestamp column (YYYYMMDDHH). It seemed to me that an index in a datetimeoffset (7) would be too granular. All these are persisted. The only indexes are the ID, the time stamp prefix and the name of the application, which I think would exist in almost all queries (3 separate indexes).
The query was terribly slow until I had the calculated columns persist (not surprisingly), which seemed to help, but still very slow, even with the indices that are used. Enlargement has not helped much. It is also sporadic (sometimes fast, usually slow), which made me think that it was due to a heavy and sporadic load of inserts. I spent a long time (days) trying to figure out what was slow, but I really could not identify anything. Now I am investigating if the partition would help. It could certainly help with aging / deleting old data.
My initial idea was to try to expand things so that queries can reach multiple sources in parallel and reduce critical points, but from what I have read, it is the opposite of what I should do (group logical data to reduce the number of partitions used). I hope that all queries use the timestamp column or the date prefix (based on the timestamp) to limit their results, and that would help eliminate old records, so the partition based on that seems to make sense. Other good candidates seem to be the name of the application and possibly the severity, which would help to group the things that will be consulted together.
In addition to deleting old records, would the partition help improve the query time in this scenario and, if so, does my reasoning make sense for the options in the partition column? I have read that it can and can not, so I am not sure if I should follow this route …