Your direct issue is that your queries are getting Clustered Index Scans, despite you only needing a small subset of the data (TOP 10). Even in cases where the user has half a million rows, that's relatively much smaller than the total cardinality of the table, 150 million rows, and should be resulting in index seeks. This is due to hitting the tipping point when the Engine reads the data for the dbo.transactDetails table.

This happens in your particular case based on the indexes you currently have in place. You're trying to select top 10 td.* ... order by td.transactdate desc but your only index is the default clustered index on transactid. This means the entire table / index needs to be sorted, as denoted in the Sort (TopN Sort) operator in your execution plans, so that the engine knows which top 10 rows to return.

If you created your clustered index on (transactid, transactdate) instead, then it will cover your query and persist the data sorted by transactdate (within the sorting of each transactid) already. This will likely eliminate your issue with the tipping point since the entire index won't need to be scanned anymore, and should even eliminate the Sort (TopN Sort) operator from your execution plan too. So one simple change may result in two performance benefits to your query.

Won't it make more sense to create a non-clustered index on (transactdate, transactid)?

I was torn on if I should mention this, but another issue with your code is the fact you're using SELECT * (really SELECT td.* but essentially the same thing), which is an anti-pattern for many reasons, one being performance. You should always explicitly list only the fields you need.

I am assuming since you consciously use the alias to say td.* that maybe you do need all fields from the transactDetails table. If that's true, then it would be silly to make a nonclustered index because it would require you to define it to include all of the fields from the transactDetails table, in order to be covering (since you're SELECTing all of the fields). This would essentially be a duplicate of your clustered index and double the size of your table. The clustered index is the table itself, sorted on the fields you define the index on, therefore it already includes all of the fields from the transactDetails table. This is why it would be easier and better just to modify the clustered index to be defined as (transactid, transactdate).

If you made a nonclustered index on just (transactdate, transactid) without including the other columns then the Engine would need to either do a bunch of key lookup operations on the clustered index anyway to find those other fields or it may decide that operation is too costly and you hit the tipping point again, which would result in a clustered index scan instead of the nonclustered index being used, bringing you back to square one.

If you do decide you don't need all of the fields from the transactDetails table, and only needed one or two more, then it may make more sense to create a separate nonclustered index on (transactdate, transactid) that also included those couple of additional fields.

Your direct issue is that your queries are getting Clustered Index Scans, despite you only needing a small subset of the data (TOP 10). Even in cases where the user has half a million rows, that's relatively much smaller than the total cardinality of the table, 150 million rows, and should be resulting in index seeks. This is due to hitting the tipping point when the Engine reads the data for the dbo.transactDetails table.

This happens in your particular case based on the indexes you currently have in place. You're trying to select top 10 td.* ... order by td.transactdate desc but your only index is the default clustered index on transactid. This means the entire table / index needs to be sorted, as denoted in the Sort (TopN Sort) operator in your execution plans, so that the engine knows which top 10 rows to return.

If you created your clustered index on (transactid, transactdate) instead, then it will cover your query and persist the data sorted by transactdate (within the sorting of each transactid) already. This will likely eliminate your issue with the tipping point since the entire index won't need to be scanned anymore, and should even eliminate the Sort (TopN Sort) operator from your execution plan too. So one simple change may result in two performance benefits to your query.

Won't it make more sense to create a non-clustered index on (transactdate, transactid)?

I was torn on if I should mention this, but another issue with your code is the fact you're using SELECT * (really SELECT td.* but essentially the same thing), which is an anti-pattern for many reasons, one being performance. You should always explicitly list only the fields you need.

I am assuming since you consciously use the alias to say td.* that maybe you do need all fields from the transactDetails table. If that's true, then it would be silly to make a nonclustered index because it would require you to define it to include all of the fields from the transactDetails table, in order to be covering (since you're SELECTing all of the fields). This would essentially be a duplicate of your clustered index and double the size of your table. The clustered index is the table itself, sorted on the fields you define the index on, therefore it already includes all of the fields from the transactDetails table. This is why it would be easier and better just to modify the clustered index to be defined as (transactid, transactdate).

If you made a nonclustered index on just (transactdate, transactid) without including the other columns then the Engine would need to either do a bunch of key lookup operations on the clustered index anyway to find those other fields or it may decide that operation is too costly and you hit the tipping point again, which would result in a clustered index scan instead of the nonclustered index being used, bringing you back to square one.

If you do decide you don't need all of the fields from the transactDetails table, and only needed one or two more, then it may make more sense to create a separate nonclustered index on (transactdate, transactid) that also included those couple of additional fields.

Your direct issue is that your queries are getting Clustered Index Scans, despite you only needing a small subset of the data (TOP 10). Even in cases where the user has half a million rows, that's relatively much smaller than the total cardinality of the table, 150 million rows, and should be resulting in index seeks. This is due to hitting the tipping point when the Engine reads the data for the dbo.transactDetails table.

This happens in your particular case based on the indexes you currently have in place. You're trying to select top 10 td.* ... order by td.transactdate desc but your only index is the default clustered index on transactid. This means the entire table / index needs to be sorted, as denoted in the Sort (TopN Sort) operator in your execution plans, so that the engine knows which top 10 rows to return.

If you created your clustered index on (transactid, transactdate) instead, then it will cover your query and persist the data sorted by transactdate (within the sorting of each transactid) already. This will likely eliminate your issue with the tipping point since the entire index won't need to be scanned anymore, and should even eliminate the Sort (TopN Sort) operator from your execution plan too. So one simple change may result in two performance benefits to your query.

Your direct issue is that your queries are getting Clustered Index Scans, despite you only needing a small subset of the data (TOP 10). Even in cases where the user has half a million rows, that's relatively much smaller than the total cardinality of the table, 150 million rows, and should be resulting in index seeks. This is due to hitting the tipping point when the Engine reads the data for the dbo.transactDetails table.

This happens in your particular case based on the indexes you currently have in place. You're trying to select top 10 td.* ... order by td.transactdate desc but your only index is the default clustered index on transactid. This means the entire table / index needs to be sorted, as denoted in the Sort (TopN Sort) operator in your execution plans, so that the engine knows which top 10 rows to return.

If you created your clustered index on (transactid, transactdate) instead, then it will cover your query and persist the data sorted by transactdate (within the sorting of each transactid) already. This will likely eliminate your issue with the tipping point since the entire index won't need to be scanned anymore, and should even eliminate the Sort (TopN Sort) operator from your execution plan too. So one simple change may result in two performance benefits to your query.

Won't it make more sense to create a non-clustered index on (transactdate, transactid)?

I was torn on if I should mention this, but another issue with your code is the fact you're using SELECT * (really SELECT td.* but essentially the same thing), which is an anti-pattern for many reasons, one being performance. You should always explicitly list only the fields you need.

I am assuming since you consciously use the alias to say td.* that maybe you do need all fields from the transactDetails table. If that's true, then it would be silly to make a nonclustered index because it would require you to define it to include all of the fields from the transactDetails table, in order to be covering (since you're SELECTing all of the fields). This would essentially be a duplicate of your clustered index and double the size of your table. The clustered index is the table itself, sorted on the fields you define the index on, therefore it already includes all of the fields from the transactDetails table. This is why it would be easier and better just to modify the clustered index to be defined as (transactid, transactdate).

If you made a nonclustered index on just (transactdate, transactid) without including the other columns then the Engine would need to either do a bunch of key lookup operations on the clustered index anyway to find those other fields or it may decide that operation is too costly and you hit the tipping point again, which would result in a clustered index scan instead of the nonclustered index being used, bringing you back to square one.

If you do decide you don't need all of the fields from the transactDetails table, and only needed one or two more, then it may make more sense to create a separate nonclustered index on (transactdate, transactid) that also included those couple of additional fields.