February 4, 2010 at 5:51 am
I have a fairly large log table, 17M rows. It contains three months' activity, after which the data is deleted (it is retained in the DW).
As part of (long overdue) tuning, I recently added a new index to speed up Customer Service queries against the table. The old index looked like this:
CREATE INDEX IX_Created_CustId ON LogTable (Created, CustomerId)
This is a hugely inefficient index, of course, when CS asks for all customer logins within a date range, so I dropped the existing and created a new index that also sorts the data appropriately:
CREATE INDEX IX_CustId_Created ON LogTable(CustomerId, Created DESC)
Much to my bewilderment, I just found out that the query/view does not use the new index, i.e. it performs an index scan instead of a seek. However, when I extract the select from the view and run it in query analyzer, the optimizer correctly performs a seek on the new index.
The view (index scan) reads 80000+ pages, the standalone query (seek) reads a little less than 200 pages. Yes, that's still a bit much, but I'd rather have a bookmark lookup on the main log table than the additional index maintenance overhead of including more fields in the index.
I've executed sp_refreshView against the view, and sp_recompile against the table, but that doesn't seem to impress the view in question much.
It's a 2005 server, but the databas in question is running in 2000 mode. Could this be part of the problem? Are there some other tricks I can try? I'd rather not modify the actual code in the view if I can avoid it, since this is an internet banking app, and there's quite a system that has to be set in motion for even minute code changes.
February 4, 2010 at 5:54 pm
When was the last time that statistics were updated?
Joie Andrew
"Since 1982"
February 8, 2010 at 2:22 am
Duh! Found it! The CustomerId is a varchar() field, but the application passes the parameter as an nvarchar, which makes SQL Server do an implicit conversion. When the parameter is passed as a varchar() the index works as expected.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply