Missing indexes in SQL

Index tuning in Dynamics NAV is often done by analyzing indexes and how they are used. There's a nice tool from the MS NAV Team that You can run from inside NAV to analyze You existing indexes. But what if You are missing indexes and are facing read problems rather than problems write performance?

Could I be missing some indexes?

If You use Performance monitor and look at "Avg. Disk Read Queue Length" and "Avg. Disk Write Queue Length" You can se how many transactions You have in queue for read/write from/to the physical disks.

If You experience performance problems and it's the read queue that's high Your database might be missing some indexes. It could be C/AL code that doesn't use keys/indexes optimally or users that often set filters not supproted by indexes. I'm assuming here that You have done Your homework and have Your existing indexes defragemented and a nice peace of hardware with a storage set up correctly.

SQL Server Dynamic Management Views (DMV)

The DMV's keeps information about what's going on inside Your sevrer (since the last start of the SQL Server service) and store that information for later use. This is one of the "new" features I like most in SQL2005. Every time the Query Optimizer get's a query and don't find an appropriate index it writes information about this to the DMV's.

Here is a simple script to query the DMV's about missing index statistics (kudos to Bart Duncan for his nice blog post about this).

I'm not saying that You should go ahead and create all those indexes in the result set of the query. But it can be valuable information when tuning Your database.

Adding an index allways comes with a cost of updating it, but if the index takes enough burden off the database engine it might be worth adding it. So be careful and work step by step monitoring with Performance Monitor and Profiler what's happening in Your database when doing changes to Your indexes. Use the missing indexes script as yet another source of information when tuning Your indexes.