wrote:How fix SQL performance issues? Start by checking your wait stats
2-Make sure you're running index maintenance frequently.
Everything Jeff said.
But, one exception, columnstore indexes. Reorganization and rebuild do things for columnstore beyond fragmentation. So consider using them because of that.
For those interested, it's the internals of a columnstore index. The delta store, where changes are stored, is a non-compressed, non-pivoted, b-tree index. As you exceed 100,000 rows in the delta store, you'll get that moved to compressed & pivoted, but there's all sorts of caveats around that because it's not a part of a straight data load. Again, details, details, details, in this. However, reorganize actually moves stuff out of the delta store. It helps. Rebuild, does the whole 9 yards with the data (at a much higher cost, of course). So, for columnstore, I do recommend index maintenance.
Aye! Thanks for the correction on the ColumnStore indexes concerning index maintenance. I always forget about that and agree that Reorganize on those is not the same monster that it can be for RowStore indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.