SQL Server – How to Measure I/O Contention After Adding Calculated Fields on Indexed Views

indexinsertperformancequery-performancesql serversql server 2014

I have successfully added several calculated fields to different tables, for example:

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST(
    (
        ISNULL(
        CASE 
            WHEN sintMarketID = 2 
                AND strType = 'CARD' 
                AND strTier1 LIKE 'GG%' THEN 1 
            ELSE 0 
        END
        ,0)
    ) AS BIT)
PERSISTED;

This doesn't seem to have had any impact on write performance on the involved table, in this case dbo.tblBGiftVoucherItem.

However, when I have added indexed views, especially those which touch more than one table with an INNER JOIN, I have noticed a considerable increase on the number of deadlocks directly related to the tables in question.

What DMVs can I use that will give me an indication of I/O pressure by database object?

For example the table dbo.tblBGiftVoucherItem from the example above.

Is there any way I can measure/monitor how long the storage engine is taking to deal with writing requests specifically related to this table?

If I knew this I would have a better idea of how many calculated fields I could add to the table, and the cost involved in each of them.

Same with the indexed views.

Best Answer

What DMVs can I use that will give me an indication of I/O pressure by database object?

Probably the closest is sys.dm_db_index_operational_stats. This gives low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

Adding persisted computed columns typically has little impact, though they will add the usual extra costs if indexed. This is just the normal trade-off for adding an index though, and not specific to the underlying column being computed.

Using indexed views will tend to increase the chance of deadlocks, because indexed view maintenance has to be performed internally at the serializable isolation level (which typically acquires transaction-length key-range locks) to guarantee consistency when more than one table is referenced in the view.

Related question: How, when and why does implicit changing of transaction isolation level happen?

Indexed views can also increase the risk of incompatible locks if the view contains aggregation, because this has the effect of concentrating data ranges in the underlying tables into fewer rows in the indexed view.

There is always a trade-off when adding indexes and indexed views. In many cases, the deadlock risks can be minimized through careful design and attention to detail with respect to the execution plans generated for statements that modify the additional indexes.

My other piece of advice would be to look at investing in a commercial performance monitoring tool. It is possible to write your own based on DMVs, but it is not always easy, and much of the hard work (including presentation and historical reporting) has already been done in these products.

Related Question