Category Archives for "Indexes"

Replication Slow at Subscriber Due to Missing Indexes

By Lori Brown | Helpful Scripts , Indexes , Replication , SQL Server

Problem Have you ever had transactional replication mysteriously start showing significant latency at a subscriber server?  If so, check to see if the primary keys from the publisher database are missing on the subscriber database! Replication was showing long latency while the publisher and subscriber servers were not heavily utilized.  Microsoft generated stored procedures that […]

Continue reading

Adding a Missing Index Killed Performance

By Jeffry Schwartz | Indexes , Missing Indexes , Performance Tuning , Query Tuning , SQL Server

SQL Server 2017 suggested an index to improve performance, so we added it in a development environment. The “improvement” made the query run almost 50 TIMES longer than the original! See how we fixed this issue.

Continue reading

Index REORGANIZE Generates Out of Space Messages

By Lori Brown | Indexes , SQL Administration , SQL Maintenance , SQL Server

I was doing some performance analysis at a client where we captured a traditional SQL trace.  Our client has SQL 2016 Enterprise that is fully patched up and uses Ola Hallengren’s IndexOptimize (https://ola.hallengren.com/) stored proc to do nightly index maintenance.  While the index maintenance ran, we caught over a hundred thousand Error 1105, Severity 17, […]

Continue reading

Rx for Demystifying Index Tuning Decisions – Part 6

By Jeffry Schwartz | Indexes , Missing Indexes , Performance Tuning , Query Tuning , SQL Development , SQL Server

Review In Parts 1 through 5 of this blog series the author discussed various aspects of SQL Server indices such as: Types of indices and their variations, e.g., clustered, nonclustered, filtered, and covering Physical versus logical index access methods, e.g., seeks, scans, key/RID lookups, singleton lookups, and range scans Why index tuning should begin with […]

Continue reading