My good friend @MichaelPerillo and I were recently discussing the possible cause for a SQL query to take an excessively long time, even though proper measures had been taken to ensure performance tuning had taken place. He made a suggestion that I believe is noteworthy, so I’m putting it here, also so I can reference it later.
A transactional SQL database performs each transaction in a queue, assigning each transaction a queue request. Queue requests are prioritized not only by how they come in, (traditional queue behavior) but also based on the account permissions of the originator of the query. Accounts with Read/Write permissions are not given as high of a priority as accounts with read only permissions. In my case, the account that was performing the query had DBO rights on the database. Meanwhile, service accounts with datareader access were blazing past it, while the account with DBO rights was still plugging along.
Now, I’m not saying my ability to write top-notch queries far surpasses all others, I’m sure there’s plenty I could be doing that would improve performance. But outside of the typical performance-tuning techniques and best practices, there is a difference in performance based on account permissions, where accounts without write access will be given higher priority in the queue.