August 16, 2022 at 9:42 pm
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.
August 17, 2022 at 2:04 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2023 at 8:30 am
Can confirm that this works like a charm. We had the same issue and the fix took two seconds to implement. Query went from not finishing execution to a three second execution. User had a default DB set that had compatibility level of 150 and one of the DB's in the query had level 120. So make sure all involved DB's have the same compatibility!
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply