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.
AT TIME ZONE is slow enough that I just avoid it and use .NET TimeZoneInfo methods to convert at the client application.
If SQL could deliver the performance at least equal to client applications using TimeZoneInfo I'd be included to use it frequently.
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.