Share via

Azure SQL database - Query slow without db_owner role

Nuwan Kaluwila 0 Reputation points
2023-08-14T23:42:07.7966667+00:00

Hello

I have an extraordinary unexplainable issue.

We have an Azure SQL Database.

Pricing Tire: Standard S1: 20 DTUs

When I connect the database using an account with db_owner privilege and run a query (a view with nested loops), it gives me the results in less than a second.

However, If I connect the database using an account with "db_datareader & db_datawriter privileges and run the same query, it takes more than 50 seconds to get the results.

Is there any reason for this behaviour?

Can someone share some thoughts on this issue, please?

Thank you

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sedat SALMAN 14,455 Reputation points MVP
    2023-08-15T10:41:49.3666667+00:00

    Compare the execution plans for both the db_owner and the db_datareader roles. You can do this by enabling the "Include Actual Execution Plan" option in SSMS or using the SET STATISTICS XML ON command. If it's a non-production environment or you can afford to do it, try clearing the plan cache and see if that levels the performance between the two roles.

    DBCC FREEPROCCACHE

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.