Posts in Database Development

Availability Group Endpoint URL Firewall Gotcha

A few days ago I was working on changing the Endpoint URL and Port for a 2016 SQL Server Availability Group to use a private network interface. We wanted this so we could have the Synchronizing (or Mirroring) traffic have its own dedicated network, which would keep it away from the network traffic of client connections to the database server. This would help increase the throughput of data to and from the databases in the Availability Databases list.

Our setup is a straight forward Windows Cluster with two Nodes. We have two stand alone SQL Server Instances running on these Nodes. Each Instance has an Availability Group defined with one Node the Primary Replica and the other Node the Secondary Replica. We run the Replicas in Synchronous Commit Availability Mode with Automatic Failover. All connections are allowed to the Primary Role and the Readable Secondary is set to no.

Read More

FRESH orange juice will go with hot sauce, donuts, OR toast!

Logical processing order (a.k.a. binding order) of a SELECT query in Microsoft SQL Server T-SQL is fundamental.  This is because it has direct bearing on what results (data) you get, which is the whole reason for querying in the first place.  Unfortunately for us, it looks almost nothing like the order we have to remember to use when writing T-SQL syntax.  Keep reading…

What the Heck is Constant Folding?

Constant Folding is the term used when SQL Server query optimizer evaluates an expression or expressions BEFORE the query is compiled. For example, the following query would undergo constant folding by the optimizer before compilation:

SELECT COUNT(1) FROM dbo.SampleTable WHERE IDColumn = 5 + 7

The expression “5 + 7” is evaluated to “12” before the query is compiled. Constant folding happens for a limited number of expressions. Generally speaking, constant folding occurs for the following types of expressions:

1. Arithmetic expressions containing only constants (and no variables or parameters)

2. Logical expressions containing only constants, such as 1=1 and 1>0

3. Built-in deterministic functions whose inputs rely solely on provided values, without depending on any external context, such as database settings, encryption keys, environment variables, etc.

When possible, construct your T-SQL code in such a way that the optimizer can make use of constant folding. This will alleviate the need for the optimizer to repeatedly evaluate the expression at run time. It will also allow the cardinality estimator to more accurately predict the size of the result set.

For further reading, I highly suggest reading the TechNet article on Constant Folding. You can find some related information in the Forced Parameterization TechNet article here.

Happy coding!

We're Here To Help.

Database problem? Tell us a little bit about it.

Name*

Email*

Subject

Message*

captcha

* Required