sqlsql-serverindexingazure-sql-databasedatabase-tuning

Slow performance on table with just over 1 million records: Are the indexes wrong?

I've been asked to look into a performance issue for an app that is becoming progressively slower. Pretty quickly, I was able to narrow down the problem to a single database table. Poorly structured C# code I'm ok at optimizing. But with SQL tables, I'm less confident. So I'm here hoping for some help!

The table in question stores multi-lingual translations for certain keywords used across the app. It's a growth table. And as it grows, the performance on basic SELECTs and JOINs is starting to degrade sharply. Right now there are just over 1 million records in the table, which isn't really all that much.

For example:

SELECT * FROM PE_TranslationPhrase WHERE Phrase = 'ABC-123'

That can take anywhere from 8 to 32 seconds to complete.

The table is hosted on Azure SQL. Here's a look at it in SSMS:

enter image description here

So it's not that complex of a table. The Primary Key structure isn't just your normal auto-incremented integer. TranslationId and CultureName together make up the primary key (which is fine).

When dealing with performance issues, the first place to look, of course, is the indexes. This is what's on the table now:

CLUSTERED:
 - [TranslationId] ASC,
 - [CultureName] ASC

STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF

And

NON-CLUSTERED:
 - [CultureName] ASC,
 - INCLUDE ([Phrase])

STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF

The reason for the NON-CLUSTERED index with Phrase and CultureName is because those columns are used all the time for filters and joins. Example:

LEFT JOIN 
    PE_TranslationPhrase TP 
    ON A.Description COLLATE Latin1_General_CS_AS = TP.Phrase COLLATE Latin1_General_CS_AS 
    AND A.CULTURENAME = TP.CultureName

WHAT I'VE TRIED, AND QUESTIONS:

I tried to rebuild the indexes:

ALTER INDEX ALL ON dbo.PE_TranslationPhrase REBUILD

That didn't seem to have a measurable impact on performance.

My question is: Is it bad to have CultureName as part of both indexes? How could I / should I change these indexes?

Thanks!!

Solution

  • For this query:

    SELECT * FROM PE_TranslationPhrase WHERE Phrase = 'ABC-123'

    You want an index where Phrase is the first key in the index.

    None of your indexes have Phrase as the first column, so the database needs to scan the entire table.