I have a problem with index pages on a varchar column in my database.
The varchar field is an Identifier which is not unique. The field length is 15
DataId varchar (15)
I have an index on that column. When we are inserting data in the table, we
do lot of transactions per second (~2000), after a while since we begin
inserting the data, the dirty pages for the index on this varchar column is
much more(~2000) than anything else. That is driving the checkpoint to take
longer and therefore reducing our systems capacity(tps).
What is the best way to handle this situation? I need to reduce the dirty
pages so that the check point takes less time flushing the index pages(along
with other pages). The data length we typically insert averages about 3-5
chars. But sometimes we do insert data with 15 characters. Is varchar the
right data type or the char or some other? Tried with fixed length char(15)
also but the dirty pages remain the same.
Looking for some suggestions/opinions. Your input is greatly appreciated.
Thanks in advance.