ARITHABORT Settings, Query Speed and Applications Running Queries

in #sqlserver3 years ago

Check out the demo and discussion in the video TSQL: ARITHABORT ON and Query Speed. While it may seem like a harmless setting, this setting may affect our queries. We should be aware of how the application runs this setting. Also, when we test this setting on and off, we see different results. This is not incorrect, as depending on what we want the definition of various data measures to be, we may want those different results.

Keep these questions in mind when watching this video and also pay close attention to the demo in the video and the results:

  • How does this setting handle different mathematical operations?
  • What do we see the result of this setting on and off in the demo?
  • What is something that developers can miss about management studio (SSMS)?
It's worth reviewing the latter part of the video to consider how this would possibly impact transactions. For an example, what would happen to our transaction if we had a 1,000,000 row batch, but within it had this issue with the setting on and off? What would the result be? What is the result that we want? Consider that for different environments and companies, those answers may be completely different.

Automating ETL
Check out the highest-rated Automating ETL course on Udemy, if you're interested in data.

If our transforms may involve data like these, we may want to consider these settings. As I point out in the video, I've seen this setting cause major conflict among developers and DBAs because each tend to blame the other for a query being slow. Wrong. Understanding this setting and how it affect queries may be the underlying issue. These debates also forget one very important point about management studio (SSMS) - it is an application. If we miss this, we miss the fact that it comes with its own settings. We can't compare a query being run in SSMS with our application because the settings may differ (not just this setting either).

Part of why I spend the first few minutes of this video cautioning about pointless debates is because it takes less than 5 minutes to determine if this is the issue (unless we don't have a test environment). Technical people can sometimes argue pointlessly when the issue can be discovered in minutes without any finger pointing. This isn't a moment to be smart; this is a moment to get results by solving the problem quickly and moving on to the next problem. In the grand scheme of architecture, this is a minor issue. Don't let it become a bigger issue than it is.

YouTube | Automating ETL | T-SQL In 2 Hours | Consumer Guide To Digital Security