Parameter Sensitive Plan (PSP) Optimization, The Great Feature Coming with SQL Server 2022

The SQL Server 2022 is coming with fantastic new features. One of these features is Parameter Sensitive Plan Optimization.

The stored procedures are very useful and have a lot of advantages for the performance and the security.

You can see the differences between the ad hoc queries and the stored procedures below.

A query process completes with 6 steps in an ad hoc query. But it completes with 2 steps in stored procedures.

The first 4 steps run when the procedure first executed and then the execution plan stored in cache. The next time, we execute the stored procedure, we don’t need to run first 4 steps. Because it is still on cache. This situation is very useful and it increases the query performance.

But sometimes, it can cause a performance down. It depends on the first execution parameter.

The execution plan is created for the first parameter. And the execution plan contains the index for calling the stored procedure. If the next time, we call the stored procedure with a different parameter, need a different index, the cached plan chooses the wrong index and the query will be slower.

If we use the ad hoc query according to stored procedure, our query execution plan will choose the correct index. Because, when we call an ad hoc query, the execution plan is calculated for every query.

This is the problem.

The stored procedures, can be call with different parameters and every parameter may need different indexes, but the stored procedure has just one execution plan and one index.

And this is the solution!

The SQL Server 2022 has a new feature for this problem. We can store more than one execution plan for a stored procedure.

I will explain it with a dataset.

You can use the script below to create the sample database.

CREATE DATABASE SALESPSP
GO
USE SALESPSP 
GO 
CREATE TABLE ITEMS (ID INT IDENTITY(1,1) PRIMARY KEY,ITEMNAME VARCHAR(100),PRICE FLOAT)
DECLARE @I AS INT=1
DECLARE @PRICE AS FLOAT
DECLARE @ITEMNAME AS VARCHAR(100)
WHILE @I<=100
BEGIN
	 
	SET @PRICE =RAND()*10000
	SET @PRICE=ROUND(@PRICE,2)
	SET @ITEMNAME='ITEM'+REPLICATE('0',3-LEN(@I))+CONVERT(VARCHAR,@I)
	INSERT INTO ITEMS (ITEMNAME,PRICE) VALUES (@ITEMNAME,@PRICE )
SET @I=@I+1
END


SELECT * FROM ITEMS 


CREATE TABLE CUSTOMERS (ID INT IDENTITY(1,1) PRIMARY KEY,CUSTOMERNAME VARCHAR(100))
SET @I=1
DECLARE @CUSTOMERNAME AS VARCHAR(100)
WHILE @I<=100
BEGIN
	SET @CUSTOMERNAME='CUSTOMER'+REPLICATE('0',3-LEN(@I))+CONVERT(VARCHAR,@I)
	INSERT INTO CUSTOMERS (CUSTOMERNAME) VALUES (@CUSTOMERNAME)
SET @I=@I+1
END

CREATE TABLE SALES (ID INT IDENTITY(1,1) PRIMARY KEY,ITEMID INT,CUSTOMERID INT,DATE_ DATETIME,AMOUNT INT,PRICE FLOAT,TOTALPRICE FLOAT,DEFINITION_ BINARY(4000))
SET @I=0
DECLARE @ITEMID AS INT

DECLARE @AMOUNT AS INT
DECLARE @CUSTOMERID AS INT
DECLARE @DATE AS DATETIME 

WHILE @I<500000
BEGIN
	DECLARE @RAND AS FLOAT
	SET @RAND =RAND()

	IF @RAND<0.9
		SET @ITEMID =1
	ELSE 
		SET @ITEMID=(RAND()*99)+1

	SELECT @PRICE=PRICE FROM ITEMS WHERE ID=@ITEMID
	SET @CUSTOMERID=(RAND()*99)+1
	DECLARE @DAYCOUNT AS INT
	DECLARE @SECONDCOUNT AS INT
 
	SET @AMOUNT=(RAND()*19)+1
	SET @DAYCOUNT=RAND()*180
	SET @SECONDCOUNT=RAND()*24*60
	SET @DATE=DATEADD(DAY,@DAYCOUNT,GETDATE())
	SET @DATE=DATEADD(DAY,@SECONDCOUNT,@DATE )
	INSERT INTO SALES (ITEMID,CUSTOMERID,AMOUNT,PRICE,TOTALPRICE,DATE_)
	VALUES (@ITEMID,@CUSTOMERID,@AMOUNT,@PRICE,@PRICE*@AMOUNT,@DATE)
SET @I=@I+1
END
CREATE INDEX IX1 ON SALES (ITEMID)

We have 100 rows in the ITEMS table.

And we have 100 rows for the CUSTOMERS table.

Let’s look at the SALES table related with the ITEMS and the CUSTOMERS table.

We have 500.00 rows for the SALES table.

In this script, we are creating the rows by choosing a random ITEMID and random CUSTOMERID. But there is more probability to choose the 1 value for the ITEMID.

As we can see below, we have about 450.000 rows for the ITEMID=1 and 500-600 rows for the other ITEMIDs.

We have a primary key and clustered index contains the ID field and we have a nonclustered index in SALES table.

Let’s run the query.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES 
WHERE ITEMID=20
GROUP BY ITEMID

Now, Let’s look at the query statistics and learn how many page did the query read.

The query reads 1709 pages and it’s equal that 1709×8/1024=13 MB.

Now let’s look at the execution plan.

The server optimizes itself by choosing the IX1 nonclustered index.

If we look at the IX1 index, wee see the ITEMID column. It is normal. I send a parameter to the query as ITEMID and query choose the index contains ITEMID.

Well, what happens if the system does not find the correct index, but performs a clustered index scan over the primary key? Let’s see this by doing index forcing.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES WITH (INDEX=PK_SALES)
WHERE ITEMID=20
GROUP BY ITEMID

As you see, the read page count increased to two times. I think it’s not a big problem.

Well, Let’s call the query again with ITEMID=1.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES  
WHERE ITEMID=1
GROUP BY ITEMID

The read page count is the same and 3043.

Let’s look at the execuion plan again.

According to execution plan, the system prefer the clustered index instead of nonclustered index. We know that, our parameter is ITEMID and we have an nonclustered index contains the ITEMID but the system choose the clustered index.

Because if we use the ITEMID=1, using the clustered index is more effective. Because there are 450.000 rows with the ITEMID=1.

Ok. Let’s force to use nonclustered index.

SET STATISTICS IO ON 
SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
COUNT(*) ROWCOUNT_
FROM SALES  WITH (INDEX=IX1)
WHERE ITEMID=1
GROUP BY ITEMID 

As we can see, the system reads so much.

The SQL Server can find the correct index by using the statistics and compile the correct plan. But we have a problem with the stored procedures. It is created the first time the Execution plan stored procedure run.

According to this rule, we have a risk. If the stored procedure will execute first with the parameter ITEMID=1, the procedure always use the clustered index for all the parameters. Or the first execution parameter’s frequency is less then, for example ITEMID=20, the execution plan will be create for the non clustered index.

In SQL Server 2022, we have a fantastic feature. The feature name is PSP (Parameter Sensitive Plan) Optimization. This feature allows to store mutiple execution plan for a stored procedure.

So we can use the stored procedures like the ad hoc queries.

Let’s try it. I create a stored procedure, named GETSALES_BY_ITEMID.

CREATE PROC [dbo].[GETSALES_BY_ITEMID]
@ITEMID AS INT
AS
BEGIN
	SELECT ITEMID,SUM(AMOUNT) TOTALAMOUNT,
	COUNT(*) ROWCOUNT_
	FROM SALES   
	WHERE ITEMID=@ITEMID 
	GROUP BY ITEMID 
END

Right click on the SALESPSP database, click properties and change the compatibility level to SQL Server 2019.

Now, run the procedure with ITEM=20 parameter.

Let’s look at the active execution plan.

Let’s look at the active execution plan.

Let’s call the procedure with the parameter ITEMID=1, again the system uses the IX1 nonclustred index. But it is not correct index. The clustered index is better fort he ITEM=1.

Let’s change the compatibility level to 2022 again.

When we call the procedure with ITEMID=1, the system uses the clustered index.

When we call the procedure with ITEMID=20, the system uses the non-clustered index.

Conclusion

The stored procedures are absolutely so important for the performance. But the stored procedures have only one execution plan for the different type of parameters. Because of this situation, the proc can choose the wrong index and it is not good for the performance.

In the SQL Server 2022, the stored procedures can store multiple execution plan and they can use the correct index, according to parameters. This is a really great feature. Thanks to SQL Server engineers.

I hope, you liked this article. See you on another article.

Why is correct normalization so important in database systems?

Hi,

In this article, i will talk about, choosing right data types and normalization on database tables.

The “Database” is a basic word.   It contains “Data” and “Base” words. So it means “The Base of Data”.

So, in databases, the correct definition of the data is more important than we guess.

We define the variable as “string” while we are coding.

But in database systems, there are a lot of data types to define a string data.

Unfortunately, we generally use nchar for string values and use bigint for integer values.

In this article, I will explain, how this situation causes a big problem.

Now, let’s imagine!

We have a task. Our task is to design a citizenship database table for a country.

For example my country Turkey. The population is now about 85 million. Let’s assume that with the people who died and the family tree, we have about 200 million people in our database.

Let’s design the table.

First of all, we have an ID column. The column has an auto-increment value and it is also the primary key of the table.

And the other columns are here.

ID                                        :Auto increment primary key value

CITIZENNUMBER            :11 digits unique citizen number

CITYID                                :The id number of the city where the citizen was born

TOWNID                            :The id number of the town where the citizen currently lives

DISTRICTID                       :The id number of the district where the citizen currently lives

NAMESURNAME             :The citizen’s name surname info

I think these are enough for an easy understanding of the subject.

Actually, this is enough to understand the results of wrong datatypes.

Let’s start from the ID column. We think firstly, this column will address about 200 million rows. So this is a very big value. So we think that we must use the “bigint” data type.

The next column is CITIZENNUMBER. This has 11 digits number and we choose the bigint again.

And the other columns are also integer values. Then let’s choose the bigint for all of them too.

Now let’s calculate it. A bigint value uses 8 Byte in the storage. Even we use just 1 or 0 for the value.
If we use the bigint value for all these columns, we need about 7.6 GB of space for the data.
We have just one table and just 5 numeric columns. I think it is too much.
Well, how can we reduce this huge space? Of course, by using the correct data types.
The bigint data types addresses values between  -2^63 and  +2^63.
Ok, do we need this huge gap for 200 million rows?
Of course not.
We have also an integer data type. The integer data type addresses values between -2^32 ile +2^32. It means between about -2 Billion and +2 Billion.
So, for the ID column, the integer data type is enough and better than the bigint. Because the integer data type uses just 4 Bytes. It is two times better than the bigint. And we know that the population will never be more than 2 Billion.

CITIZENNUMBER: This  column has 11 digits number. So an 11 digits value needs bigint.

CITYID: This column identifies the city Id from the Cities table. In Turkey country, we have just 81 cities and we know that it will always be less than 255. So we can use the TinyInt data types for the CityID column. The Tinyint datatype addresses the values between 1 and 255 and it uses just 1 Byte for a value.

TOWNIND: In Turkey, there are about 1.000 towns. So we can’t use the tinyint because it is not enough. But, we don’t want to use the integer. Because 2 Billion is so much for a value between 1 and 1000. There is another data type Smallint. The Smallint  data type addresses the values between -32.000  and +32.000. So the Smallint is suitable for the TOWNID column.

DISTRICTID:
Turkey has about 55.000 districts. So I can’t use the Tinyint and Smallint. But the Bigint is so much for this type of value. Then the Integer is good for this column.

Now, let’s calculate again. We can see the total space. It is about 3.54 GB. It is about two times better than the first design.

Let’s look at a string column. NameSurname.
In the country, there are citizens from all over the World. So if we define a max character limit, we have to calculate the max character limit of the names from all over the World.
For example, I know that the Spanish people have the longest names. Because they give names to their children as father’s, grand father’s, grand grand father’s names. 😊
For example
“pablo diego josé francisco de paula juan nepomuceno maría de los remedios cipriano de la santísima trinidad ruiz y Picasso”
We just say “Pablo Picasso” 😊
If we want to keep this name-surname information on the database, we have to use 250 digits for namesurname.
This data type is a string. But in database systems, there are so many data types used for string values.
Char, nchar, varchar,nvarchar etc.
Let’s explain the differences between these types with a basic example.
Let’s create a table with the columns below and save it as TEST.
ID:Int
NAME1:char(50)
NAME2:nchar(50)
NAME3:varchar(50)
NAME4:nvarchar(50)

Let’s insert my name, “ÖMER” to the table.

Let’s query the table.

I copy the value from the NAME1 column and paste it into an empty place.

Did you see where the cursor is blinking?

It is blinking far from the end of the string value. The name value “ÖMER” has 4 digits length but the cursor is blinking at 50 digits length. It means, the char(50) uses 50 digits for any value less or equal to 50 digits. It is a very big disadvantage. Think! We have a lot of short names, for example, Jhon, Marry, May, etc. Their average length is just  4 characters and it is a really huge waste.

Let’s investigate the NAME2 column. Copy the NAME2 column and paste it into the text area.

The situation is the same. The cursor is blinking at the end of the 50th character. So, we can say that char and nchar may be the same.

Now let’s look at NAME3 and NAME4 columns. NAME3 is varchar and NAME4 is Nvarchar. Copy the NAME3 and paste into the free text space.

Can you see where the cursor is blinking? The cursor is blinking at the end of the string. The length of the string is 4 digits and the cursor is blinking here. So we can understand that the varchar data type uses space as the string value’s length not max length.

According to this scenario, the char data type uses 50 digits and varchar uses just 4 digits. I think it’s really better than the char or nchar.

We can see that the Nvarchar is the same as the varchar. We don’t know what the “N” means on Nchar or Nvarchar does yet.

The “N” character symbolizes Unicode support. Unicode means the international characters.

Let me show you with an example to understand what Unicode support is.

I use google translate and translate a word to into Chinese. The first word that comes to my mind is book. In Chinese, “书” word means “book”. So I copy this word and paste it into the table.

And I select the data from the table below.

You can see that in the NAME1 and NAME3 columns we can’t see the Chinese character. These columns are both char and varchar and do not support the Unicode characters. It is very easy to understand. Char and Varchar data types use 1 Byte for each letter or character. 1 Byte means 0-255 different numbers. For example, in alphabet, there are 26 characters. So we have 26 lowercase and 26 uppercase letters, 10 numbers, and a lot of punctuations. 255 is enough to address all these letters. But think about the Japanese alphabet. The Japanese alphabet has about 2.000 letters. So 1 byte is not enough for all these characters. We need more. Maybe 2 Bytes can be suitable for us. 2 bytes addresses about 32.000 different letters. Nchar and Nvarchar uses 2 bytes for a letter and can show the Unicode characters.

Let’s turn the scenario again. We have 200 million rows that include namesurname column with max 250  digits and with Unicode support.

Then we have to use nchar(250) or nvarchar(250).

Using nchar(250) is a wrong stuation. Because 250 is just a limit but we arrange 250×2=500 Bytes for all namesurnames.

Using nvarchar (250) is the best choice. Because it supports Unicode characters and also uses space as the namesurname’s length.

Let’s calculate again

As you see, in the wrong design, we use about 100 GB. But in the correct design, we use just 11 GB. It is 10 times smaller than the first one.

We are talking about just one table and just 6 columns. And even in this situation, we can see a huge difference.

In database systems, we have a lot of tables with a lot of columns.

In this article, we talked about the normalization and importance of choosing the right data type.

We talked about the size of the data. You can think that it is only about the size on the disk and you can ignore all these calculations in your mind. You can say “It is all about the money. I can use bigger storage.”  But it is a wrong approach. Because it is not about just the storage. Because SQL Server works on memory and uses the CPU.

If the data type is bigint, You use 8 Byte space on your ram for the for example, 15 number.

If the data type is tinyint, You use 1 Byte space on your ram for the 15 number.

And also if you use bigint, you can use your CPU 8 times more.

As a result,

The Normalization and the knowing to use the right data types are more important than we guess.

See you again in the next article.