T-SQL & Development
Set Your Application Names Before You Wish You Had.
When you run monitoring queries like sp_BlitzWho and sp_WhoIsActive, you wanna see the program names that are running the queries. It’s super useful when you’ve got multiple apps running on the same servers, or when you’ve got apps scattered across end user computers.
If you don’t set the program names, you’ll either end up with empty strings, or your dev tool’s default name, like Core .Net SqlClient Data Provider, which doesn’t tell you jack.
It’s up to the developers to set the program name in code or in their connection string. Just tack it on to the end of your connection string like this:
|
1 |
server=MyServer;database=MyDatabase;Integrated Security=SSPI;Application Name=My Incredible Application; |
And then in your monitoring tools, you’ll be able to group sessions together by application.
One question that comes up a lot afterwards is, “I’m looking at the plan cache and Query Store – what application and what user ran this query?” Unfortunately SQL Server can’t/won’t store that since a query might conceivably be run thousands of times per second, all from different servers/apps/users. If you need to track down who’s running a query, your best bet will be to set up an Extended Events trace, or if it’s a long-running query, do sampling of sp_BlitzWho or sp_WhoIsActive to table periodically.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Completely agree. I mean, MsAccess is what mine always has…
Love the fact that Postgres can set that value per query.
It feels so obvious now but this is the thing I have been missing for YEARS!
I only wish our dev team would see it that way. I’ve made the request several times but there are still wonky bits like you mentioned.
Amen.
I have to remind/beg developers every time a new app or service is deployed.
Being doing that for more than a decade. On some apps we add the username at the end of app name.
It really helped debugging performance issues.
Our team forces a correctly formed application name or else login is denied via trigger (this is prod only, non-prod does not enforce this). We also have extensive auditing and the app names being set make it super easy to identify who/what is running everything.
I wish there were a trace flag that would allow you blacklist default application names from connecting. It always seems that the apps with the worst performance problems are the ones that don’t specify and app name and the owner won’t admit is theirs until you come back to them about the third time.
You can do that with a login trigger.
Put them in the corner by themselves with Resource Governor 🙂
Agree to this. Hint: you can use “App=…” in place of “Application Name=…”.
Also if you can, use dedicated logins for each application. IMHO must have.