Archive for the ‘Denali’ Category

SQL Server: Simple Parameterization

31/10/2011 Leave a comment

Simple parameterization is SQL Server feature which allow the optimizer to parameterize submitted queries. If submitted query has no parameters and has constant values plugged in, the optimizer can choose to treat constant values as parameters and automatically create parameterized query and execution plan for it. Simple parameterization is on by default and it’s designed to reduce cost of generating execution plans. SQL Server uses a simple parameterization for a relatively small set of queries where query plan doesn’t depend on particular constant values.
To see how simple parameterization works I will use AdventureWorks database. Let’s run some simple query and see what is written in SQL Server’s plan cache.

FROM Production.TransactionHistory
WHERE TransactionID = 213429
SELECT cp.objtype,
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
WHERE st.text LIKE '%TransactionHistory%'
AND st.text NOT LIKE '%sys.dm_exec_cached_plans%'

Here are the results from the plan cache query:

We can see that SQL Server created two entries in the cache. The first entry is submitted query with constant value and it’s cached as objtype adhoc. It is a shell query and it doesn’t contain execution plan. A shell query contains only the exact adhoc batch text and pointer to the parameterized (prepared) plan. The second entry is parameterized query and has objtype of prepared. Prepared query contains compiled execution plan and query text with a parameter in place of a constant value. We can also see that shell query use 16KB of cache while prepared plan use 32KB. If we open a query_plan of the shell query we can see that shell query contains only pointer to the prepared plan. From (1) we can see which parameterized plan is used with parameterized query text and in (2) we can see which statement is submitted.

Let’s run same type of the query but with different constant value (213430). If we observe SELECT operator in the actual execution plan of the query we can tell that query is parameterized.

(1) In “Parameter List” we can see “Parameter Compiled Value” and “Parameter Runtime Value”. (2) Shows us parameterized text and (3) gives us hash value of the query plan. We can use QueryPlanHash value to return query plan and execution count of the parameterized query.

SELECT st.text, qs.execution_count, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
WHERE qs.query_plan_hash = 0xAFB37EEDFFA28FE3

If we look at results of plan cache query again, we can see that the second shell query is created with the exact text of the second query and it points to a parameterized plan.

Why shell query is cached when it only points to parameterized query? It’s cached only to make it easier to find parameterized query if the exact same query with the same constant is re-executed later.

Now, I will show one interesting behavior of simple parameterization. Let’s run same type of the query but with constant value of 5. Here are the results from the plan cache query:

Two new entries are created. (1) One shell query and one (2) prepared but with parameter data type of tinyint. It’s strange that the optimizer created a new prepared query with tinyint data type parameter when there is a cached plan with int parameter value. It seems that the optimizer, during simple parameterization, use parameter with the lowest possible data type for the specified constant value of number data type. If we execute the query with varchar or nvarchar constant value, data type varchar(8000) and nvarchar(4000) is used as parameter.

As I mentioned at the begging of the post, relatively small set of queries will be simple parameterized. Only simple queries whose plan doesn’t depend on constant value will be considered “safe” for simple parameterization and will be cached. Also, there are a lot of statement types which SQL Server will not even attempt to auto-parameterize.  For complete list of these statement types you can check Apendix A in the article Plan Caching in SQL Server 2008.

Let’s take a look at these simple queries.

SELECT TransactionID, ProductID
FROM Production.TransactionHistory
WHERE ProductID = 725
FROM Production.TransactionHistory
WHERE ProductID = 725

If we execute those 2 queries and check results from plan cache query we can see that first query is parameterized and second one isn’t. Why?

For the first query, Query Optimizer thinks that nonclustered index seek is the best option for the query even when constant value change, because only columns we need are contained in the nonclustered index (clustered index is created on TransactionID column).
The second query is different, we want all columns from the table and a nonclustered index seek and key lookup might be a good choice when only a few rows are returned, but it might be a terrible choice when many rows are returned.  If we look at query_plan from the second query we can see that query is parameterized. It seems that query went through auto-parameterization process but query optimizer decided that it is “unsafe” and parameterized query isn’t cached.


SQL server will simple parameterize some simple queries whose plan doesn’t depend on different constant values. During simple parameterization, if the optimizer decide that parameterized query is “safe”, two entries are written to the plan cache: shell query and prepared query. Shell query doesn’t contain full plan, it only point to parameterized plan which contain full execution plan for the query. Shell and parameterized queries are cached to reduce cost of generating execution plans. If we run a lot of adhoc queries with different constant value, more parameterized queries for the same type of query but with different parameter data type and large number of shell queries will be written to the plan cache.

SQL Server: Find statistics information in database

13/10/2011 1 comment

Recently I worked on a performance tuning of a database and noticed a lot of user created statistics. I checked few of them and saw that lots of user created statistics are multi-column statistics and some of them have a filter defined. There was no documentation of created statistics and I wanted to find out all statistics with all defined columns and filter. That is easy with sys.stats, but for better visibility, I wanted to list all statistics columns and defined filter in a single concatenated string.
Below script will return all statistics with all columns and defined filter for all user tables and views in a database. Script works in SQL Server 2008 and Denali (SQL Server 2005 doesn’t have filtered statistics).

;WITH Stat(SchemaName, TableName, StatisticsName, StatisticsType, filter_definition, stats_column_id, name)
SELECT AS SchemaName , AS TableName, AS StatisticsName,
WHEN s.user_created = 1 THEN 'USER'
WHEN s.auto_created = 1 THEN 'AUTO'
WHEN s.auto_created = 0 AND s.user_created = 0 THEN 'INDEX'
END AS StatisticsType,
FROM sys.stats s
INNER JOIN sys.objects o
ON o.object_id = s.object_id
INNER JOIN sys.schemas sh
ON sh.schema_id = o.schema_id
INNER JOIN sys.stats_columns sc
ON sc.stats_id = s.stats_id
AND sc.object_id = s.object_id
INNER JOIN sys.columns c
ON c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE o.type in ('U','V') -- UserTable and View

SELECT s.SchemaName,
STUFF (( SELECT ', ' +
FROM Stat s1
WHERE s1.TableName=s.TableName
AND s1.StatisticsName=s.StatisticsName
ORDER BY s1.stats_column_id
), 1,1, null) + CASE
WHEN s.filter_definition IS NOT NULL THEN
' FILTER: ' + s.filter_definition
END AS StatisticsColumns
FROM Stat s
/* Filter statistics: USER - User-created, AUTO - automac-created, INDEX - index statistics */
-- WHERE s.StatisticsType in ('USER', 'AUTO')
GROUP BY s.SchemaName, s.TableName, s.StatisticsType, s.filter_definition, s.StatisticsName
ORDER BY s.SchemaName, s.TableName, s.StatisticsType

In the query we can filter for only automatically created and user created statistic if we want to exclude index statistics. Below is sample output for user and automatically created statistics in AdventureWorks database. (1) Show you the output for multi-column statistics with a filter defined.

If you have some suggestions for the script feel free to add comment.

SQL Server Denali: New warnings in Actual Execution Plan

29/09/2011 Leave a comment

I’ve played with SQL Server Denali and found some new features and improvements in Query Execution plan. My intention was to test Extended Events and new events: sort_warning and hash_warnig. In SQL Server 2008R2, we can create SQL Trace to capture sort and hash warnings but there is no easy way to find out which statement cause it. I hoped that with new XEvent in Denali we can do that easily.
I reduced maximum server memory for SQL Server, ran some queries with ORDER BY and here is the Actual Execution Plan for one query.

Do you see something new? There is a warning on Sort operator (1) and on SELECT (2).
If we look at the Sort operator and SELECT we can see more information about warnings.

In Denali we see Sort warnings in query execution plan. It seems that some waits are also visible. Is the MemoryGrant only wait type that will appears in query plan?
I don’t know what kinds of warnings are included in Denali actual execution query plan but I think that these two warnings are not the only one.

Categories: Denali, Performance

SQL Server Denali – FORMAT() string function

02/09/2011 Leave a comment

SQL Server Denali brings us new string function, FORMAT function. It will make formatting of date/time and number values easier.

Syntax: FORMAT (value, format [,culture])

Formatting Date/Time

Let’s see how it works:

DECLARE @a datetime = getdate()
-- If the culture argument is not provided,
-- then the language of the current session is used.
FORMAT(@a, N'dd') AS dd,
FORMAT(@a, N'ddd') AS ddd,
FORMAT(@a, N'dddd') AS dddd


FORMAT(@a, N'yy') AS yy,
FORMAT(@a, N'yyy') AS yyy

If you specify culture argument, you can easily format date to other languages. Next examples show you how to format date, weekday and month to Croatian language.

-- Formatting with Croatian language
SELECT FORMAT(@a, N'd', N'hr-HR') AS d,
FORMAT(@a, N'dd', N'hr-HR') AS dd,
FORMAT(@a, N'ddd', N'hr-HR') AS ddd,
FORMAT(@a, N'dddd', N'hr-HR') AS dddd

SELECT FORMAT(@a, 'M','hr-HR') AS M,
FORMAT(@a, 'MM','hr-HR') AS MM,
FORMAT(@a, 'MMM','hr-HR') AS MMM,

Examples with German and Italian languages.

-- Formatting with German language
SELECT FORMAT(@a, N'd', N'de-DE') AS d,
FORMAT(@a, N'dd', N'de-DE') AS dd,
FORMAT(@a, N'ddd', N'de-DE') AS ddd,
FORMAT(@a, N'dddd', N'de-DE') AS dddd

-- Formatting with Italian language
FORMAT(@a, N'MM', N'it-IT') AS MM,
FORMAT(@a, N'MMM', N'it-IT') AS MMM,

List of all available languages you can find in National Language Support (NLS) API Reference. With FORMAT function you can also use custom formatting. Let’s see how custom formatting works.

DECLARE @a datetime = getdate()

-- Custom formatting of date
SELECT FORMAT ( @a, N'dd/MM/yyyy') AS 'dd/MM/yyyy',
FORMAT ( @a, N'dd.MM.yyy')  AS 'dd.MM.yyy',
FORMAT ( @a, N'dd.MMMM.yy', 'hr-HR') AS 'dd.MMMM.yy Croatian',
FORMAT ( @a, N'dd.MMMM.yyy (dddd)', 'hr-HR') AS 'dd.MMMM.yyy (dddd) Croatian',
FORMAT ( @a, N'dddd, dd.MMM.yy', 'hr-HR') AS 'dddd, dd.MMM.yy Croatian',
FORMAT ( @a, N'dddd MMMM dd, yyyy', 'en-US') AS 'MMMM dd, yyyy (dddd) English'

-- Custom formatting of time
SELECT FORMAT ( @a, N'hh:mm:ss') AS 'hh:mm:ss',
FORMAT ( @a, N'hh:mm') AS 'hh:mm',
FORMAT ( @a, N'hh') AS 'hh',
FORMAT ( @a, N'mm') AS 'mm',
FORMAT ( @a, N'ss') AS 'ss'

Formatting Number

FORMAT function is very useful if you need to format currency value. Here are some examples for formatting currency value.

DECLARE @a numeric(10,2)
SET @a = '5132.45'
SELECT FORMAT(@a, N'c') AS SessionLanguage,
FORMAT(@a, N'c', 'hr-Hr') AS Croatian,
FORMAT(@a, N'c', 'de-DE') AS German,
FORMAT(@a, N'c', 'it-IT') AS Italian

-- You can also control number of digits after the decimal point
SELECT FORMAT(@a, N'c0', 'de-DE') AS German0,
FORMAT(@a, N'c1', 'de-DE') AS German1,
FORMAT(@a, N'c2', 'de-DE') AS German2,
FORMAT(@a, N'c3', 'de-DE') AS German3

With FORMAT function you can even format value to scientific and hexadecimal.

DECLARE @I int = 90
SELECT FORMAT(@I,'e') AS Scientific,
FORMAT(@I,'x') AS Hexa,
FORMAT(@I,'x4') AS Hexa1

Format function is nice addition to the T-SQL language and it will make formatting much easier. Various tricks with CAST and CONVERT functions will no longer be required.

Categories: Denali, String Functions, TSQL

SQL Server Denali – CONCAT() string function

28/08/2011 Leave a comment

CONCAT() is new string function in SQL Server Denali. It returns a string that is the result of concatenating two or more string values (or values that can be converted to string).
Syntax: CONCAT (string_value1, string_value2 [, string_valueN ] )

With CONCAT function, you can concatenate between 2 and 254 values. If you try to use CONCAT with only one value or with more than 254 values you will get an error.


Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.

Simple examples:

SELECT CONCAT('SQL', 'Server', 'Denali') AS Result1

DECLARE @a varchar(10) = 'SQL'
DECLARE @b varchar(10) = 'Server'
DECLARE @c varchar(10) = 'Denali'
SELECT CONCAT(@a, @b, @c) AS Result2

USE AdventureWorks2008R2
SELECT TOP 5 CONCAT(FirstName, LastName) AS FullName
FROM Person.Person

It’s easy to work with CONCAT function, but as you can see there is no string delimiter between values. To get proper FullName in the last example you will need to add space between FirstName and LastName.

USE AdventureWorks2008R2
SELECT TOP 5 CONCAT(FirstName, ' ', LastName) AS FullName
FROM Person.Person
Syed Abbas
Catherine Abel
Kim Abercrombie
Kim Abercrombie
Kim Abercrombie

CONCAT function has 2 advantages compared to the concatenating of strings using the “+” operator:
1. NULL values are implicitly converted to an empty string
2. All arguments are implicitly converted to string

CONCAT() and NULL arguments

CONCAT function automatically converts all NULL arguments to an empty string. It’s great for T-SQL developers. In next examples you can see the difference between CONCAT function and concatenation of strings using the “+” operator.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = NULL
DECLARE @c varchar(10) = 'Denali'
-- CONCAT implicitly convert NULL to an empty string
SELECT CONCAT(@a, @b, @c) AS Result1

-- If you use "+" operator with NULL result is NULL
SELECT @a + @b + @c AS Result2

-- To get the same result with "+" operator you need to deal with NULL values
SELECT @a + ISNULL(@b, '') + @c AS Result3
SQL ServerDenali
SQL ServerDenali

If all the arguments are null, then an empty string of type varchar(1) is returned.

CONCAT() and non-string arguments

CONCAT function implicitly converts all non-string arguments to string data types (if conversion is possible) and then concatenate values.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT CONCAT(@a, @b, @c, @d) AS Result1
SQL ServerDenali2011Aug 27 2011  9:42PM

As you can see, @c and @d are implicitly converted to string and you get result back. If you use same example with “+” operator you will get an error.
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value ‘SQL ServerDenali’ to data type int.

To get the same result with “+” operator you need to convert @c and ©d values to string data types.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT @a + @b + CONVERT(varchar(10),@c) + CONVERT(varchar(20),@d) AS Result2
SQL ServerDenali2011Aug 27 2011  9:42PM

As you can see, concatenating string is much easier with CONCAT function.  What you think?

Categories: Denali, TSQL

SQL Server Denali – IIF() and CHOOSE() functions

22/08/2011 Leave a comment

SQL Server Denali introduced 2 new logical functions: IIF and CHOOSE.

IIF function

IIF function returns one of two values, depending on whether Boolean expression evaluates to true or false.
Syntax:  IIF (boolean_expression, true_value, false_value)
IIF function is simple replacement for:
Case when <boolean_expression>=true  then <true_value> else <false_value> end

Let’s see how IIF function works.

-- Simple IIF
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;

In next select statement, I will use IIF function to retrieve PriceDescription value. If ListPrice is greater the 1000 then set Expensive for PriceDscription else it is Cheap.

If we look at the execution plan, we will see that Compute Scalar is used to support IIF function. On Compute Scalar property we can see that IIF function is converted to CASE expression. So, the same rules that apply to the CASE statement also apply to IIF function.

IIF function can be nested but only up to the level of 10. Next examples show IIF nesting to 10th level and error if we try to nest IIF up to the level of 11.

CHOOSE function

CHOOSE function returns a value from the given array based on the specified index position.

Syntax: CHOOSE (index, val_1, val_2 [, val_n ])

Let’s see how CHOOSE function works.

SELECT CHOOSE(1, 'a', 'b', 'c') AS First,
CHOOSE(2, 'a', 'b', 'c') AS Second;
First Second
----- ------
a     b
-- If the index value exceeds the bounds of the array of values,
-- then CHOOSE returns null.
SELECT CHOOSE(0, 'a', 'b', 'c') AS First,
CHOOSE(4, 'a', 'b', 'c') AS Second
First Second
----- ------
-- If the provided index value has a numeric data type other than int,
-- then the value is implicitly converted to an integer
SELECT CHOOSE(2.5, 'a', 'b', 'c') AS First,
CHOOSE(3.9, 'a', 'b', 'c') AS Second;
First Second
----- ------
b     c

In next query, I will show how CHOOSE function can be used in clauses such as select list, GROUP BY and ORDER BY.

SELECT p.Name,
SUM(s.OrderQty) SellQuantity,
CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th') AS Quarter
FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p
ON p.ProductID = s.ProductID
WHERE s.ModifiedDate >= '2006-01-01'
AND s.ModifiedDate < ' 2007-01-01'
GROUP BY p.Name,CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')
HAVING SUM(s.OrderQty) > 600
ORDER BY p.Name, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

If we look at the execution plan, we will see that Compute Scalar is used to support CHOOSE function. The engine has taken the CHOOSE function and converted it into a CASE.

IIF and CHOOSE functions are nice additions to the language and it’s good to know how they work.

Categories: Denali, TSQL

XEVENT Provider in Denali

16/08/2011 Leave a comment

XEVENT provider is new sqlps provider in Denali CTP3. To admit, I was surprised when I saw it. In SQL Server 2008R2 we don’t have any GUI for XEVENT (Extended Events) written by SQL Team and now, in Denali CTP3, we have nice GUI through SSMS and sqlps provider. Nice work.

XEVENT provider is built on SMO Library (Microsoft.SqlServer.Management.XEvent Namespace). I will try to show how to work with XEVENT provider from the beginning. To start to work with XEVENT objects in sqlps, we first need to access XEVENT provider in sqlps. From PS SQLSERVER:\ type CD XEVENT. Then you need to navigate to server instance typing <server name>\<instance name>. When you are at instance level, you can see that XEVENT provider have 2 subfolders: Packages and Sessions.

From Packages subfolder we can access to metadata of all objects that are available on the instance. To be precise, all public metadata, private objects are not visible with XEVENT provider. From Sessions subfolder we can see all available sessions on the system, manage it and create new sessions. We can even generate SQL DDL script for CREATE, DROP and ALTER session.

Let’s start with Packages subfolder. (1)From Packages subfolders we can see all public packages on the system. (2)To access some package ModuleId and Name need to be specified. (3) From the Package we can get all metadata that are available in the package: events, actions, targets…

From there, to list all Events in the package we can type: dir .\EventInfoSet
The same pattern can be used for any object in the package: ActionInfo for Actions, TargetInfo for target etc.
To see Event Fields for some specific Event we can use the code:

# Get Event "sqlserver.scan_started" in $event variable
$event = dir .\EventInfoSet | Where-Object {$ -eq "scan_started"}
# Return ReadOnly Event Fields
# Return Data Event Fields
# Return Customizable Event Fields

It’s nice to have possibility to see Extended Events metadata with Powershell but I prefer to use T-SQL script or new GUI in SSMS.

For me, sessions subfolder is much more useful. (1) From Sessions subfolder we can see all available session on that instance, manage it or create new session. (2) On my server, 3 sessions are available. System_health is running and Perf1 and Perf2 are stopped. (3) We can navigate to system_health session and from there (4) we can see all events and targets that are associated to the session.

If you want to see all relevant information (actions, predicates and event fields) for specific event in the system_health session you can use:

dir .\Events | Where-Object {$ -eq "sqlserver.error_reported"} | Format-List Name, Actions,PredicateExpression, EventFields


$ev = dir .\Events | Where-Object {$ -eq "sqlserver.error_reported"}

From sessions subfolder, session can be started and stopped.

# Find system_health session
$session = Get-ChildItem | Where-Object {$ -eq "system_health"}
# Stop session
# Check is session running

If you want to create T-SQL DDL for CREATE EVENT SESSION and DROP EVENT SESSION you can use next code:


In this post I showed some basics of using XEVENT provider in Denali sqlps. I hope it will help someone.

Categories: Denali, Extended Events, SQLPS