Tag Archives: PERFORMANCE

SQL Server Problem Tombola: Giant Reads

A SQL Server is as fast as you tune your workload, one of the things I try to do is tune the workload aka the queries executing on my SQL server in order to get a better performing server. I will start to look for problems with certain queries who do too many reads to retrieve a small result set. A problem however is when working with vendor applications, you might not be able to tune the applications queries. This limits your options but there are a lot of thing you can still do to optimize your workload.

Today we will do some testing with a giant read query and tune it in different ways by using real life scenarios

First of all, we are going to create a database and create two tables on it. We will be executing the scripts on SQL Server 2016 so some syntax might have to be changed when working with lower versions.

DROP
DATABASE
IF
EXISTS BigRead

GO

CREATE
DATABASE BigRead

GO

USE BigRead

GO

CREATE
TABLE dbo.BigReadTable_1

(

    BID int
identity (-2147483648 ,1),

    TheDate datetime,

    TheCategory varchar(20),

    TheLink int

)

GO

CREATE
TABLE dbo.BigReadTable_2

(

    BID_2 int
identity(-2147483648,1),

    TheDate datetime,

    TheCategory varchar(20),

    TheValue decimal(18,0)

)

GO

Once we have these table we will insert some data into it

— INSERT 1000 ROWS

SET
NOCOUNT
ON

insert
into dbo.BigReadTable_2

select
DATEADD(day,ABS(Checksum(NewID())
% 10),getdate()),
‘Category ‘+cast(ABS(Checksum(NewID())
% 10)
as
varchar),
cast((rand()*100000)
as
decimal(18,0))

GO 1000

 

insert
into dbo.BigReadTable_1

select
DATEADD(day,ABS(Checksum(NewID())
% 10),getdate()),‘Category ‘+cast(ABS(Checksum(NewID())
% 10)
as
varchar),(select
top 1 BID_2 from dbo.BigReadTable_2 order
by
newid())

GO 1000

 

insert
into dbo.BigReadTable_1

select TheDate,TheCategory,TheLink from dbo.BigReadTable_1

GO 9

 

insert
into dbo.BigReadTable_2

select [TheDate],[TheCategory],[TheValue] FROM dbo.BigReadTable_2

GO 9

After inserting the rows we will add a clustered index for each table

CREATE
CLUSTERED
INDEX CI_1 ON dbo.BIGREADTABLE_2

(

    BID_2

)

 

GO

 

CREATE
CLUSTERED
INDEX CI_2 ON dbo.BIGREADTABLE_1

(

    TheLink

)

Now we are all set up to start our testing. The query we are going to use is the following

select
*
from dbo.BigReadTable_2 C

inner
join

(

    select
AVG(TheValue) Average,TheCategory from dbo.BigReadTable_2

    group
by TheCategory

)B on C.TheCategory = B.TheCategory

inner
join

(

    select
SUM(TheValue) Summer, TheCategory from dbo.BigReadTable_2

    GROUP
BY TheCategory

)A on A.TheCategory = B.TheCategory

inner
join

    (select
CAST(AVG(CAST(TheDate AS
FLOAT))
AS
DATETIME) AVGDate,TheLink

    FROM dbo.BigReadTable_1

    Group
By TheLink) D

on D.TheLink = C.BID_2

OPTION(RECOMPILE)

This is the plan we get with this query at this moment

And these are the client statistics

Now what are the different levels of tuning possible for this query?

  1. We are able to edit code
  2. We are not able to edit code
  3. We are not able to edit code & cannot add indexes

We are able to edit code

As we can see this query is not the most optimal in filtering out data. In this case our tuning might be quite simple. If you can discuss with your business users that you can only return the fields they will use you will already see a performance increase. Let’s say for this example we only need the data for the BigReadTable_2

—LEAVE OUT SOME FIELDS

select B.*,A.*,D.*,C.TheCategory from dbo.BigReadTable_2 C

inner
join

(

    select
AVG(TheValue) Average,TheCategory from dbo.BigReadTable_2

    group
by TheCategory

)B on C.TheCategory = B.TheCategory

inner
join

(

    select
SUM(TheValue) Summer, TheCategory from dbo.BigReadTable_2

    GROUP
BY TheCategory

)A on A.TheCategory = B.TheCategory

inner
join

    (select
CAST(AVG(CAST(TheDate AS
FLOAT))
AS
DATETIME) AVGDate,TheLink

    FROM dbo.BigReadTable_1

    Group
By TheLink) D

on D.TheLink = C.BID_2

OPTION(RECOMPILE)

Here are the result for the client statistics ( the plan will stay the same )

We see that we are reducing the resources that we used when selecting all columns, so without adding additional indexes or filtering data we have tuned the amount of bytes received by 25%. This shows you how important it is to just select the fields you need instead of selecting everything.

There are many different ways of altering the code, but we will not cover all of these.

We are not able to edit the code

Now things are getting a little trickier, you no longer have control over your query. Good news though, we can still add indexes. So what can we do for our query

In SQL Server 2016 I’m using a new cool trick which can be used even in the express edition. The trick is adding an empty non-clustered columnstore index to the table to benefit from batch mode (for more information check out Niko’s blog on http://www.nikoport.com/ or check this post which explains the trick more thoroughly (http://sqlmag.com/sql-server/what-you-need-know-about-batch-mode-window-aggregate-operator-sql-server-2016-part-1 )

Let’s add 2 non-clustered columnstores

— No Possible Index NO Code edit

DROP
INDEX
IF
EXISTS NCCI_PERF ON dbo.BigReadTable_2

GO

CREATE
NONCLUSTERED
COLUMNSTORE
INDEX NCCI_PERF ON d    bo.BigReadTable_2

(

BID_2, TheDate, TheCategory, TheValue

)

WHERE BID_2 =
1 and BID_2 =
2

GO

DROP
INDEX
IF
EXISTS NCCI_PERF2 ON dbo.BigReadTable_1

GO

CREATE
NONCLUSTERED
COLUMNSTORE
INDEX NCCI_PERF2 ON dbo.BigReadTable_1

(

BID, TheDate, TheCategory, TheLink

)

WHERE BID =
1 and BID =
2

GO

Here is what we get as a result for our client statistics

We get the same plan, but we are using batch mode which means processing 1000 rows at a time instead of 1 row at a time.

By adding these 2 empty indexes we are reducing our execution time by 50%! Great result I feel for adding something empty!

No Code Edit and No Additional Indexes allowed

This is just annoying, almost all tools have been taken from you! But you can still tune the query! Mostly these queries would have the highest cost on your server. So by altering your cost threshold for parallelism you might just force those queries to go into parallel execution speeding them up!

Let’s check the cost of the plan:

The plan has a cost of 15, so let’s lower the value for my cost threshold for parallelism to 14 (current value = 50) and see what result we get.

As you can see we lowered the total execution time of our query by 30-40% by altering a server setting! If we look at the plan

you can see we get the benefit from the parallel execution this specific query benefits from the parallel execution! This however can also have some downsides!

Conclusion

When dealing with Big read queries you got a few tricks up your sleeve when working with SQL Server. In most cases you have 3 different scenario’s:
1. You can change the code
2. You can’t change the code but can add indexes
3. You can’t change the code and can’t add indexes

For the first scenario’s things are “easy”, you have multiple ways of tuning your query and only one was shown here. For the second scenario things get more complicated, you might be able to add an index to gain a seek, but a golden bullet for big reads does not exist. The COLUMNSTORE trick does help in most cases, but you will have to evaluate this for yourself. Then in the last scenario things are the trickiest, you can alter your server settings, but this might come at a price. In the example given today, the parallel plan was beneficial for this query, but it might slow down other processes or even overload your processor slowing everything down.

Thank you for reading & stay tuned!

 

 

Tagged , , , , , ,

SQL Server Problem Tombola: Too Many Indexes

When auditing SQL Server databases one of the more common problems I see is the fact that people are adding too many indexes on their tables. This has a significant performance impact on your SQL Server, first off your data inserts will be slower because for every update/delete/insert because we will have to maintain all indexes added for that specific table. But this also has an impact on your read performance, as we all now SQL Server is pretty fast when it comes to reading data out of memory. However, when you have a lot of indexes the data for these indexes also needs to be stored inside your memory, which will increase the amount of memory needed to have this full table in memory. If the data has to be fetched from disk, we will see a performance impact. While indexes are created to increase read performance, one might hurt their read performance when adding indexes while their system is in memory pressure.

To show you what the actual effects of having too many indexes on your table is I have created a few tests.

  1. We are going to test how fast we can insert/Update/Delete data while having different amount of indexes to our table (1 index, 2 indexes, 4 indexes)
    The indexes will be the biggest kind AKA Covering indexes
  2. We are going to test how much memory we are using extra for each index we add.

 

Inserts, Updates and Deletes

I have created a script which is going to insert 100 000 rows into a table

CREATE
TABLE IndexMania

(

IndexManiaID int
identity (1,1),

IndexColumn1 datetime,

IndexColumn2 char(1000),

IndexColumn3 char(500),

IndexColumn4 decimal (18,2),

IndexColumn5 int

)

You can see that I added a char(    1000) & char (500) field to make sure I get a good amount of data to be inserted. If my data inserts are too small SQL Server will make use of the lazy writer and not show any writes with the insert statement. By increasing the size of one row & importing 100 000 rows I will bypass this issue. I am also clearing the SQL Server memory before every statement.

The indexes I created are the following

— CLUSTERED INDEX ON ID

CREATE
UNIQUE
CLUSTERED
INDEX [CI_IndexMania1] ON [dbo].[IndexMania]

(

    [IndexManiaID] ASC

)

— CREATE COVERING DATE INDEX

CREATE
NONCLUSTERED
INDEX [NCI_1] ON [dbo].[IndexMania]

(

    [IndexColumn1] ASC

)

INCLUDE (     [IndexManiaID],

    [IndexColumn2],

    [IndexColumn3],

    [IndexColumn4],

    [IndexColumn5])

GO

— 2 Additional NCI

CREATE
NONCLUSTERED
INDEX [NCI_2] ON [dbo].[IndexMania]

(

    [IndexColumn2] ASC,

    [IndexColumn3] ASC

)

INCLUDE (     [IndexManiaID],

    [IndexColumn1],

    [IndexColumn4],

    [IndexColumn5])

CREATE
NONCLUSTERED
INDEX [NCI_3] ON [dbo].[IndexMania]

(

    [IndexColumn4] ASC,

    [IndexColumn3] ASC

)

INCLUDE (     [IndexManiaID],

    [IndexColumn1],

    [IndexColumn2],

    [IndexColumn5])

 

The results

HEAP TABLE
Amount of writes (about the same for Delete insert and update)

total_logical_writes

20020

Memory Usage

Object

Type

Index

Index_Type

buffer_pages

buffer_mb

IndexMania

USER_TABLE

 

HEAP

20001

156

Time statistics

INSERT

SQL Server Execution Times:

CPU time = 688 ms, elapsed time = 1299 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 19 ms.

UPDATE

SQL Server Execution Times:

CPU time = 407 ms, elapsed time = 623 ms.

SQL Server parse and compile time:

CPU time = 15 ms, elapsed time = 20 ms.
DELETE

SQL Server Execution Times:

CPU time = 468 ms, elapsed time = 1129 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Clustered Index Table
Amount of writes (about the same for Delete insert and update)

total_logical_writes

20040

Memory Usage

Object

Type

Index

Index_Type

buffer_pages

buffer_mb

IndexMania

USER_TABLE

CI_IndexMania1

CLUSTERED

20082

156

INSERT

SQL Server Execution Times:

CPU time = 1171 ms, elapsed time = 2745 ms.

SQL Server parse and compile time:

CPU time = 16 ms, elapsed time = 25 ms.
UPDATE

SQL Server Execution Times:

CPU time = 297 ms, elapsed time = 550 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 20 ms.
DELETE

SQL Server Execution Times:

CPU time = 734 ms, elapsed time = 3201 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Clustered Index & 1 Covering index

Amount of writes (about the same for Delete insert and update)

total_logical_writes

40694

Memory Usage

Object

Type

Index

Index_Type

buffer_pages

buffer_mb

IndexMania

USER_TABLE

NCI_1

NONCLUSTERED

20131

157

IndexMania

USER_TABLE

CI_IndexMania1

CLUSTERED

20083

156

TOTAL

     

40214

313

INSERT

SQL Server Execution Times:

CPU time = 1953 ms, elapsed time = 4575 ms.

SQL Server parse and compile time:

CPU time = 16 ms, elapsed time = 32 ms.
UPDATE

SQL Server Execution Times:

CPU time = 2375 ms, elapsed time = 5780 ms.

SQL Server parse and compile time:

CPU time = 16 ms, elapsed time = 23 ms.
DELETE

SQL Server Execution Times:

CPU time = 1718 ms, elapsed time = 7761 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Clustered Index & 3 Covering Indexes

Amount of writes

Insert Writes

Update Writes

Delete Writes

124154

182542

125677

Memory Usage

Object

Type

Index

Index_Type

buffer_pages

buffer_mb

IndexMania

USER_TABLE

NCI_2

NONCLUSTERED

40275

314

IndexMania

USER_TABLE

NCI_3

NONCLUSTERED

23331

182

IndexMania

USER_TABLE

NCI_1

NONCLUSTERED

20145

157

IndexMania

USER_TABLE

CI_IndexMania1

CLUSTERED

20083

156

Total

     

103834

809

INSERT

SQL Server Execution Times:

CPU time = 42468 ms, elapsed time = 47094 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 21 ms.

UPDATE

SQL Server Execution Times:

CPU time = 108063 ms, elapsed time = 125766 ms.

SQL Server parse and compile time:

CPU time = 10 ms, elapsed time = 10 ms.

DELETE

SQL Server Execution Times:

CPU time = 54922 ms, elapsed time = 73162 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Conclusion

As seen in our test, we can conclude that each added index has an impact on our write performance & on the amount of data stored in memory to facilitate the index.

Amount of writes

When inserting data to a heap table we needed about 20020 writes to insert 100 000 records, there was almost no difference with the amount of writes while writing to a clustered index. When adding a covering index, the amount of writes doubled, which is normal because we will keep the data 2 times. When adding another two covering indexes we are doing about 4 times the original amount of writes. There are some discrepancies in the amount of writes, but this is due to the fact that to execute some of the queries we needed to spill to TEMPDB, which gave a larger amount of writes as the amount of indexes increased. But after multiple tests we can see that we will need 4 times the amount of writes purely for the indexes. (DELETE UPDATE & INSERT)

Time consumed

When inserting data we have seen that while inserting into a heap table we have an average of about 1 second, when adding a clustered index this increased to an average of 2-3 seconds. When adding 1 covering index this increased to 4-6 seconds, and with two additional covering indexes the amount of time exploded to a whopping 45 – 50 seconds to insert the same workload.

Memory Used by object

After inserting 100 000 rows into the heap table we have seen that we use 156MB of the memory to facilitate this table, when adding the clustered index this amount stays exactly the same. When adding 1 covering index this doubled, which means we are now keeping 2 copies of the data inside the memory. When adding the 2 additional covering indexes we see that we are keeping the same data 4 times inside our memory.

General Conclusion

We should take care when adding additional indexes, this does not only have an impact on our insert/update/delete performance but this also impacts the amount of memory used for that specific table. We want to be able to get all items out of our cache which will be in nanoseconds instead of milliseconds when retrieving data from disk, so we have to keep in mind that every index we add might take additional space inside your cache, removing other objects out of your cache & slowing them down.

In the example of today we were using covering indexes which is actually saying double my data. Keep in mind however that this post is not a pledge to stop using indexes even covering indexes. Indexes will help your performance incredibly if used in the correct way. As a general recommendation I would say try to keep your indexes as small as possible & only include the fields you need to reduce the overhead of your index.

Thank you for reading and stay tuned!

 

 

 

Tagged , , , , , ,