• You're getting a scan on the tblProductSizeWidthColor table, from the name alone, I think you've violated some design principals, which is reading, if I counted the digits correctly 332 million rows which gets filtered down to 600 for the result set. I suspect that is one of the largest problems you're dealing with.

    You're also getting a scan on tblRegularItems.

    But the biggest problem, I think, is statistics. The estimated number of rows for tblRegularItems is 1,but the actual is 127000+. That's a huge disparity. There's also an index seek against tblRegularItems that is showing estimated 19 rows, but actual 2.5 million. I'd suggest updating your stats on all the tables, and I'd suggest using FULL SCAN to do it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning