2

I my company we are facing performance issues on a big Sql server DB (running on Azure, total size > 150GB).

We have one big table (1,5GB, 3 millions records) that has several quite slow "select".

Those records have an "insert date" and I know that it is easy to make partitions based on date.

But I am exploring a different idea.

I have a "Status" column that can have different values, one of them is "Closed" and different other values that I'll call "Open". My idea is to add a calculated persisted column with a value that can be "Open" or "Closed", i'll call it OpenClosedStatus.

"Closed tickets" continue to be accumulate and are rarely (if ever) read again, instead "open" tickets are normally less than 100 so having a dedicated partition should allow extremely fast read of their values. Please note: the record is inserted once, selected potentially several hundred times and finally updated once (the update sets the "closed" status).

It is very complex to make proper indexes to speed up the several selects on the "Open" tickets because we have hundreds of instances of different programs that read "open" tickets accordingly with different criteria, therefore I am considering to partition them accordingly with the OpenClosedStatus.

This idea implies that a ticket is always inserted in the "Open" partition, then it should be transferred to the "Closed" partition when the status is updated as closed. The "OpenClosedStatus" will always be used in the WHERE clause of our queries, at least when searching for "open" tickets (therefore going on the small partition).

I do not find a reference of this "dynamic change" of partition on update of the record, I am therefore looking for advice for or against this atypical usage or partitions.

Improve this question
1

1 Answer 1

Reset to default
3

partitioning <> performance

Partitioning would likely be overkill for this. You can get the same net effect much more easily with filtered indexes, e.g.

CREATE INDEX super_cool ON dbo.YourTable (really important columns) INCLUDE (status) WHERE (status = 'Open');

A lot of people confuse partitioning with a performance enhancing feature. It can have some benefit with clustered column store indexes, but that's not likely your scenario with only 3 million rows and frequent updates.

Partitioning could be a lot cooler, but Microsoft sadly stopped investing development time on it aside from bug fixes.

point being

Both partitioning and filtered indexes will dynamically manage which rows are in play and where in the index. It would not be a very desirable arrangement for you to manage that manually every time data changed, or to risk incorrect query results until you did.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.