SQL Server 2012 EE - using NAV VSIFT Indexed View when that is slower than the Primary Key

CraigNeedham

Dear Experts

I have a very simple Query that selects records from a table where the timestamp (cast as a BigInt) is greater than a previously stored BigInt. It runs on SQL 2012 EE used to be quite fast (~100ms) and overnight slowed down (~5 Seconds). I looked at the execution plan and noticed it is ​using an Indexed View. I am not sure if it has always used the indexed view, but I suspect it swapped to using the indexed view and was using the primary key before. When I restructure the Query by swapping the cast, the query behaves differently on this server and uses the Primary Key and is quite fast.
There has been no Server configuration changes, Hardware or software changes and the data in the database is changing constantly.

My Question is: In what circumstances can SQL swap to using the Indexed View on EE when in fact the Primary key executes the query faster? The Query is very straight-forward:

SELECT DISTINCT cast(tab.[Document Type] as varchar)+','+tab.[Document No] AS RecID FROM [MyTableName] AS tab WHERE tab.[timestamp] > cast(cast(918273645 as bigint) as timestamp) AND (tab.[Document Type]=1)

I would really appreciate your insights into the inner workings of SQL Server 2012 Enterprise Edition, why would it all of a sudden execute the same code significantly slower than the day before...

Kind regards and thank you.

Slawek_Guzek

There is nothing weird here, this is just how query optimizer works.

SQL Server decides what index to used based on number of pages it needs to read (not number of records) to get the data. Even when number of records is the same, the structure size, in terms of page counts, varies between the base table and indexes defind on it for many reasons.

One day your table had just one page too many comparing to number of pages in your idexed view, and SQL Server decided that it will be more efficient to use the indexed view + the lookup operation rather than do full table scan.

Slawek_Guzek

SQL Server Enterprise decides whether to expand queried view and use the underlying table(s), or whether to use an index on a view based on query cost, which is calculated from table statistics and table and index sizes. The process is quite complicated, there are a few factors having considerable impact.

If you didn't define an index on timestamp column SQL Server will have to do the table scan operation to compare timestamp in all records to determine which one matches the criteria [timestamp] > CAST(CAST(918273645 as bigint) as timestamp). The table scan have to be executed when the the query optimizer will expand the view and use the table, but if it decides to use the indexed view it may think that it will save time by doing index seek / scan + lookup to the main table.

Since the indexed views usually have less, sometimes much less records than the table, it is easy for SQL Server to decide - lets scan the index on the view first - as it will return much less that the table), then lets lookup records from the main table and then decide whether the timestamp in retrieved recors match the criteria or not. The bigger ratio between number of records in the table vs number the records in the clustered index on the view the more likely SQL Server will decide on using the indexed view + lookup, rarher than simple full table scan

The description above is not fully correct (as in fact there are a bit different criteria used by SQL's query optimiser) but should be good enough to explain what's going on.

Fortunately there is a way to influence this behaviour. The best would be to add an index including all 3 fields: [Document Type], [timestamp], [Document No] (fields in that particular order). But adding indexes comes at the expense of write performace degradation - especialy when the [timestamp] column is included in it, as this column gets updated every modification made to the record, no matter what column gets modified.

There is also another solution which may help. There are two query hints, NOEXPAND and EXPAND VIEW which could force SQL Server to use the clustered index on a view, or to ignore it and always use the underlying table.

In your particular case I would try:

SELECT DISTINCT cast(tab.[Document Type] as varchar)+','+tab.[Document No] AS RecID 
FROM [MyTableName] AS tab
WHERE tab.[timestamp] > cast(cast(918273645 as bigint) as timestamp) AND (tab.[Document Type]=1)
OPTION (EXPAND VIEWS)

CraigNeedham

Thank you for your comprehensive reply Slawek, I appreciate it.


You are right that Option (Expand Views) will ensure the underlying table and it's primary key is used, rather than the indexed view. I have tried it and now it runs in about 200ms. I have solved this problem in another way before by swapping the cast on the timestamp from:

WHERE tab.[timestamp] > cast(cast(918273645 as bigint) as timestamp)

To:

WHERE cast(tab.[timestamp] as bigint) > 918273645

This also runs in 200ms and uses the Primary Key, not the Indexed View.

My question relates more to the behaviour of SQL and why on one day back in February, the query was fast and the next day it was slow. From your reply above, I deduce it may have something to do with statistics, although the statistics on the relevant table was rebuilt weekly. The ration between the number of records in the indexed view and the table is 1:1 and there are about 6.5 million records in the table and that particular indexed view.

What I don't understand is how one day SQL just decides to start making a less efficient decision when executing this query, the only thing that was changing was the number of records in the table (ever increasing), but the ratio would remain 1:1.


As you said, the SQL Server will favour the Indexed View over the table the larger the ratio. But for this 1:1 ratio I am trying to find out what would have tipped the balance?

Thank you once again for your time.

Slawek_Guzek

There is nothing weird here, this is just how query optimizer works.

SQL Server decides what index to used based on number of pages it needs to read (not number of records) to get the data. Even when number of records is the same, the structure size, in terms of page counts, varies between the base table and indexes defind on it for many reasons.

One day your table had just one page too many comparing to number of pages in your idexed view, and SQL Server decided that it will be more efficient to use the indexed view + the lookup operation rather than do full table scan.

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'SQL Performance' forum]