Posts in Performance

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!

Partition Alignment in a Windows-Based Environment Using the DiskPart Utility

Alignment of disk partitions can have a huge performance impact, particularly for database servers. Incorrect alignment from an out-of-the-box installation could tax a system’s I/O performance as much as 35%, depending on the type of activity (1). Partitioning, and therefore partition alignment, is one of the first things to happen when a server is being built. A mistake in that early step can negatively affect the performance of the I/O subsystem for the life of a production database server. And yet it was not long ago that it seemed nobody in the DBA community had ever heard of partition alignment.

Keep reading…

We're Here To Help.

Database problem? Tell us a little bit about it.

Name*

Email*

Subject

Message*

captcha

* Required