Same query, two users, different performance

  • I was just troubleshooting a similar issue to find the difference was the default database assigned to the user accounts.  After getting query execution plans for both logins (which were vastly different) I compared them to find cardinality estimate issues.  The default DB for the user that was experiencing slowness was a DB with compatibility level 100.  The user that didn't have issues had their default DB at compatibility level 150.  The query was fully qualified for all tables so changing default DB was no problem here and for the problematic user the query went from 35 minutes to about 12-20 seconds on average.

    I figured this may seem obvious, but not something I had considered until I saw the difference in the execution plans and wanted to share here.

  • JasonLewis_DBA wrote:

    I was just troubleshooting a similar issue to find the difference was the default database assigned to the user accounts.  After getting query execution plans for both logins (which were vastly different) I compared them to find cardinality estimate issues.  The default DB for the user that was experiencing slowness was a DB with compatibility level 100.  The user that didn't have issues had their default DB at compatibility level 150.  The query was fully qualified for all tables so changing default DB was no problem here and for the problematic user the query went from 35 minutes to about 12-20 seconds on average.

    I figured this may seem obvious, but not something I had considered until I saw the difference in the execution plans and wanted to share here.

    Good catch. Thanks for sharing.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 2 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply