AT TIME ZONE way too slow
AT TIME ZONE shells out to Windows to perform time zone conversions. This process takes way too long when working with more than a few rows (see this blog post for details: http://sqlsoldier.net/wp/sqlserver/timezonesareadragseriously). The time zone info does not change frequently. The info from Windows required to make the conversions should be cached within SQL Server to speed up this processing.
Michael Barrett : please do upload your CLR example
Adam Gallagher commented
This seems like the same issue as https://feedback.azure.com/forums/908035-sql-server/suggestions/32898679-poor-performance-of-at-time-zone-for-large-rowsets. When I was investigating this, we ended up generating a custom CASE/WHEN statement based on the .NET timezone rules and were able to reduce the query time from about 50 minutes for a 100M-row table to about 30 seconds. It would be nice if the performance using the native syntax was acceptable, though.
Michael Barrett commented
I have tested this issue extensively, as I assumed that the native implementation of this functionality in SQL Server would be faster than my custom implementation in CLR functions. However, the native implementation proved to be around 5 times slower than my own implementation utilizing the .NET TimeZoneInfo class. I would expect the native implementation to be at least as fast as a custom implementation (that provides exactly the same functionality), and I am happy to share my implementation if interested.