-
|
I have a script for syncing logins in AG and ever since we migrated to a new environment for the servers the Script has gone from running in under 8 minutes to taking 11-12 hours. When I debugged the issue it was the Sync-DbaLoginPermission command that was slowly ticking off the logins one by one. No error messages are coming up is it just taking a long time. The environment is newer equipment and all servers are located in same datacenter. Where do I begin to find the speed issue? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 1 reply
-
|
Found the answer. After tracing the command it was narrowed down to the following statement: exec('use ' + @db_name + ' INSERT #loginmappings select suser_sname(u.sid), db_name(), u.name, null from sys.database_principals AS u where suser_sname(u.sid) is not null') Suser_Sname was performing poorly and was traced back to servers still pointing Domain Controllers to Azure. Once the DC was switched to local instance performance returned. |
Beta Was this translation helpful? Give feedback.
-
|
That is SUPER helpful info, thank you @JedWare |
Beta Was this translation helpful? Give feedback.
Found the answer. After tracing the command it was narrowed down to the following statement:
exec('use ' + @db_name + ' INSERT #loginmappings select suser_sname(u.sid), db_name(), u.name, null from sys.database_principals AS u where suser_sname(u.sid) is not null')
Suser_Sname was performing poorly and was traced back to servers still pointing Domain Controllers to Azure. Once the DC was switched to local instance performance returned.