Index Usage DMV behavior updated

Cross post with https://aka.ms/sqlserverteam Up until SQL Server 2008 R2, using  index usage stats entries in sys.dm_db_index_usage_stats to make some assumptions over index design and workload patterns was common. However, in SQL Server 2012 and higher versions, entries in this DMV were reset with every index rebuild, so this was no longer a viable way…


When do conversions generate conversion warnings? And why are these bad?

Hello, A few days ago a colleague asked me why was he seeing implicit conversions in the execution plan, but no warnings of such sort were being issued. Warnings are seen in the plan if the conditions they affect occur, and then are surfaced in the <Warnings> element of SHOWPLAN, either as affecting Cardinality Estimates…

0

SQL Swiss Army Knife #15 – Handling duplicate indexes

Hello all, Part of my job is to help customers find and fix performance issues, and this post comes after one such engagement. A customer was using another of the SQL Swiss Army Knife series scripts to find info on indexes on all the databases in a given instance – the one referenced in the…

0

A practical example on missing a filtered index

Hello all, Last week I did a brief presentation on SQLPort about hinting the database engine. What’s related here is that during the presentation, when I was discussing index hints, an attendee asked me about the usefulness of Filtered Indexes in certain scenarios, and how the database engine would behave differently in SQL Server 2008R2…


SQL Swiss Army Knife #12 – Index information galore!

Download scripts here: view_IndexInformation.sql (for all DBs at once), view_IndexInformation_CurrentDB.sql (for a DB in scope) and view_IndexCreation.sql (for relevant missing indexes) Hello all, Here is another post on SQL scripts that may help DBAs, following the series “SQL Swiss Army Knife”. I’ve been using and tweaking this script for years now, and with a recent update for SQL…


A case of seeks and scans

Hello all, A couple weeks ago while I was onsite, I was looking at some queries that had performance issues. To the customer, one in particular had become a conundrum that could not be easily answered with the usual approach of good indexing, simply because there was already a good covering index for that highly…

2

Adaptive Index Defrag

Latest update: 1/20/2017 (Check change log) Download on GitHub NOTE: if using SQL Server 2012 or SQL Server 2014, mind there is a bug where the online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows. If using @onlineRebuild option, to work around this,…