Enabling performance enhancements for an index table
To start with, I have three tables, all with a primary key and some other data. These tables are combined into a ~ 70 million row join table: Table_1, Table_2, Table_3.
This junction table has a primary key in Table_3_Id, Table_1_Id, Table_2_Id
(in that order). There is also a nonclustered index above Table_1_Id, Table_2_Id, Table_3_Id
(in that order) with a fill index of 95.
The data is filtered with Table_1_Id (I have a preset of 100 of these IDs) and (via a connection) with a property from Table_3 (which is why it uses Table_3_Id). Then the values of Table_1_Id and Table_2_Id are returned. All this is done in one request in the Entity Framework.
This is the request:
var items = dataContext.TablesJoin.AsNoTracking()
.Join(dataContext.Table_3.AsNoTracking(), x => x.Table_3_Id, x => x.Id, (combi, scan) => new { combi, scan })
.Where(x => possibleIds.Contains(x.combi.Table_1_Id) && otherIds.Contains(x.scan.Other_Id))
.Select(x => new { FirstId = x.combi.Table_1_Id, SecondId = x.combi.DeviceInformationDevices_Id })
ToList();
Since this setup is done on SQL Server Express, I run into some space issues (10GB is the maximum). The data is approximately 2 GB, but the primary key and index are 5 GB in total. Since there is more data in the database as well, I am interested in reducing the size of the index while maintaining performance.
After looking at everything, I got some doubts as to what exactly is being used. Due to the join, I'm not entirely sure how useful it is to include Table_3_Id in a nonclustered index. Removing this column from the index saves about 1 GB of space.
In the beginning I had this table as a clustered index (into safe space), but due to the fact that there were quite a lot of inserts in the table (1000 / hour), it was very slow due to all the disk access as it had to constantly change 10 GB of data. Would it help if the fill factor was set lower (like 70) to get around this? Of course, that would also mean more wasted space, but if it could save a lot on the index, might it be worth it?
This table is used a lot and an index is needed for performance. Starting it without an index takes a few minutes, and with an index it takes 2 seconds.
Xml execution plan: http://pastebin.com/raw.php?i=tfUxgYrK
source to share
You don't need a primary key to be unique. Your NCI already provides this uniqueness. You can get rid of one of these indices. This should save some space.
You can save space that another index is using by making it clustered. You are noticing performance issues due to seemingly randomly placed inserts. This is believable. Consider changing the order of the index columns so that inserts only occur in one or more locations. This way all affected pages will be cached. The working set required by the DML will be low.
DML related issues may not be pagination related. This leads to more CPU utilization and fragmentation. Perfection problems are likely due to random pages being read from disk.
1000 inserts per hour is not a lot. Consider accumulating records in a delta table that is small and fully cached. Move rows to the main table in the background. This way the DML latency is off the critical path. The choices will need to either carry the attenuation or the UNION ALL
delta table.
source to share