SQL Server 2008 query hint: Optimize for Unknown

A new query hint for SQL Server 2008 is: Optimize for Unknown

The query hint ‘Option (Optimize for (@Par = value))’ can also be used for SQL Server 2005.

‘Optimize for Unknown instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.’ Source: Query Hints

Basicly the query optimizer will ignore the parameter value during query optimization and will use the same query execution plan for each parameter value.

‘Optimize for Unknown’ is the new problem solving solution for ‘Parameter Sniffing’
(A good example of Parameter Sniffing)

Note: ‘If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.’

Data + Windows blue screen!

Just a funny post! 😀

Slow initial response time of SSRS

I’ve noticed that the SSRS service went in some kind of sleep mode after [x] minutes or at startup.
It took SSRS 1 to 2 minutes to startup/wake-up!
Well, I started Googleing and found out that I’m not the first one 🙂

Apparently the SSRS Service shuts down after [x] minutes of idleness (IIS option):

The solution is to increase the timer or disable the Idle timeout feature of IIS:

Step 1: Go to IIS Manager
Under the ‘Application Pools’ node, right click ‘DefaultAppPool’ (or ‘ReportServer’) in which the Reporting Server work process is running
Step 2: Select Properties and on the “Performance” tab, you will see that (by default) the worker processes will shut down after being idle for 20 min or so. You can increase the timer to 480min, so that the worker processes will not shut down for a regular working day.

However, the first report reader of the day will still wake of the service/worker processes, so you may schedule a report at beginning of a work day for this.

‘All’-entity within a Multi-value parameter

In addition to my previous post, an ‘All’-entity is very handy when it comes to some reports.
In my example, I’ll continue using the Sales example from my previous post.

Normally when you have a parameter with just one value, it’s easy: just use a NULL-value (click).
But when it comes to Multi-value parameters, it’s a bit more tricky.

I’ve added an ‘(All)’-entity to my selection of the SalesTerritory table:

In combination with my MultiValue function, these SQL statements will do it:

SQL code: TotalSales example.doc

Results:

SET @TerritoryList = ‘-1’ will return:

Note: The ‘(All)’-entity cannot be used in combination with other entities.
‘-1,2,4’ will not work, because TerritoryID ‘-1’ is not an existing ID.

Multi-value parameter function

Some reports use Multi-value parameters in combination with the Report Item ‘List’.
For example, in order to print a sales overview per location/territory.

If you’re using a parameter called TerritoryID and you wish to use a Multi-value list, you need some function that splits the parameter value (for example: ‘2,4,6,8’) into separate ID’s.

Well, there is an easy function for it:

SQL code: fn_MultiValue.doc

A simple example:

The results:

SQL Server 2008 R2 Express Advanced Services (with SSRS!)

Just found out that the new ‘SQL Server 2008 R2 Express with Advanced Services’ comes with SSRS! Click click

Ideal for all those webshop owners!
SSRS functionality comparison (link):

Other nice features:
– Spatial Indexes
– Geodetic Data Type