I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements.
I have an automated build system that runs a series of SQL scripts for database changes – mostly alter table, alter procedure, with relevant grant statements.
While trying to change some things in my build process (including running under a different user account), I noticed that the current account running the scripts executes them up to 100x faster than any other user – even users with exactly the same permissions, and belonging to the same Active Directory group. I've tested SQL logins and Windows Authentication, with both results being basically identical.
The build process is run through a C# script in all cases. The script properly opens the connection only once, executes all the statements, then closes. This happens the same for all users. The script itself is called by a batch file, which in turn is called by a CI tool.
The average execution time for the current build user running this script is 12ms per statement, while the average execution time for any other user is 275ms per statement.
What possible causes could be contributing to this performance difference?
Best Answer
TL;DR; Check database triggers
It turns our this was a very narrow, unique issue - but one that others might experience in a similar setup.
The performance issue was being caused by a database trigger that was called on
grant,create, andalterstatements, and based on the user, executed additional (sometimes heavy) logic.These triggers were running extra logic for the accounts I was testing with, but not the original account.