Category Archives: SQL2014

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!

 

 

Advertisements
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 , , , , , ,

SQL Server Problem Tombola: MAXDOP 1

As you all may now many vendors force you to put the MAXDOP of your SQL Server to 1 (SAP, SHAREPOINT, AX). This is by design for these application because another MAXDOP setting might interfere with the otherwise “performant” workload of the application. Today I would like to show you what the impact is of setting your MAXDOP to 1 on your server actually is.

To start off I would like to give you a small introduction on what MAXDOP actually does. The MAXDOP setting limits the amount of Cores used by one query in your SQL server instance (On SQL Server 2016 you can do this on database level THANK GOD! J ), for example if you put your MD to 2 then the query receives the option to create a parallel plan with 2 cores to execute a query. The MD setting works in parallel with another setting on instance level: Cost Threshold for Parallelism (default 5). The CTFP is the value you set for your instance to start evaluating the possibility to use a parallel plan if you have MD 2/4/8/…. For example, if your CTFP is set to 60 and your query plan only has a cost of 10 your query will not be evaluated to run as parallel, if then however you have another query which has a cost of 80 the query optimizer will check if it is possible to run this query in parallel (depending on your MD setting).

To show you what the impact to your system is I am going to create an OLTP workload & OLAP workload to my Virtual Machine running on my laptop. The OLTP workload will do update inserts and deletes, the OLAP workload will do some read intensive queries onto the system. The OLTP workload will be small queries with a low cost, the OLAP workload will have more impact and calculations which will make them have a higher cost.

The virtual machine will be configured with 4 cores

To run the workload I will be using SQLQueryStress which can be found at ( https://github.com/ErikEJ/SqlQueryStress ) This will use 1 thread and will do 100 iterations for the two workloads.

I will be running all my test against in memory tables to reduce any disk overhead, and just purely look to the CPU usage.

I will be running 5 tests with following setup.

  1. MAXDOP 1 OLAP & OLTP together
  2. MAXDOP 2 COST THRESHOLD FOR PARALLELISM 1 OLAP & OLTP together
  3. MAXDOP 4 COST THRESHOLD FOR PARALLELISM 1 OLAP & OLTP together
  4. MAXDOP 2 COST THRESHOLD FOR PARALLELISM 6 OLAP & OLTP together
  5. MAXDOP 2 COST THRESHOLD FOR PARALLELISM 6 OLAP & OLTP together

I have altered my CTFP setting to 6 because then only my OLAP queries will be eligible for a parallel plan since they have a plan cost of 7.

Here are the results

Workload

MAXDOP

COST THRESHOLD

CPU/ITERATION

SECONDS/ITERATION

ELAPSED TIME

Client Seconds

LogicalReads

OLAP

1

NONE

0,6654

1,0881

22,305

0,2113

21,4595

OLTP

1

NONE

0,2831

0,5072

27,17

0,2522

NONE

OLAP

2

1

0,6595

0,4862

25,263

0,2301

19,4194

OLTP

2

1

0,2898

0,2495

19,1247

0,1479

NONE

OLAP

4

1

0,5921

0,4178

17,8526

0,1658

25,3333

OLTP

4

1

0,1635

0,1648

13,1542

0,1046

NONE

OLAP

2

6

0,4739

0,3581

17,9613

0,1618

31,7011

OLTP

2

6

0,1929

0,1869

12,7859

0,102

NONE

OLAP

4

6

0,4576

0,3861

21,2969

0,1918

32,5532

OLTP

4

6

0,3335

0,3118

19,9687

0,174

NONE

 

When looking at the full elapsed time we can see that following configuration were the fastest.

Shortest OLTP RUN

12,7859 Seconds

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

6

Shortest OLAP RUN

17,8526 Seconds

MAXDOP 4

COST THRESHOLD FOR PARALLELISM

1

Shortest Full run

30,7472 Seconds

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

6

 

When Looking at the CPU user for every iteration we can see that following configurations used the least amount of CPU

Smallest OLTP CPU/Iteration

0,1929

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

6

Smallest OLAP CPU/Iteration

0,4739

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

6

Smallest FULL CPU/Iteration

0,6668

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

6

 

When looking at the amount of seconds used by the client for every iteration we see the following test to take the least amount of time

Shortest OLTP Client/Iteration

0,1020 Seconds

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

6

Shortest OLAP Client/Iteration

0,1618 Seconds

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

6

Shortest FULL Client/Iteration

0,2638 Seconds

MAXDOP2

COST THRESHOLD FOR PARALLELISM

6

 

When looking at the full elapsed time following configuration were the slowest

Slowest OLTP RUN

27,1700 Seconds

MAXDOP 1

COST THRESHOLD FOR PARALLELISM

NONE

Slowest OLAP RUN

25,2630 Seconds

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

1

Slowest Full run

49,4750 Seconds

MAXDOP 1

COST THRESHOLD FOR PARALLELISM

NONE

 

When looking at the CPU usage for every iteration we can see that following configuration uses the most of the CPU

Largest OLTP CPU/Iteration

0,3335

MAXDOP 4

COST THRESHOLD FOR PARALLELISM

6

Largest OLAP CPU/Iteration

0,6654

MAXDOP 1

COST THRESHOLD FOR PARALLELISM

NONE

Largest FULL CPU/Iteration

0,9485

MAXDOP 1

COST THRESHOLD FOR PARALLELISM

NONE

 

When looking at the amount of seconds used by every client for every iteration we see that following test were the slowest.

Slowest OLTP Client/Iteration

0,2522 Seconds

MAXDOP 1

COST THRESHOLD FOR PARALLELISM

NONE

Slowest OLAP Client/Iteration

0,2301 Seconds

MAXDOP 2

COST THRESHOLD FOR PARALLELISM

1

Slowest FULL Client/Iteration

0,4635 Seconds

MAXDOP 1

COST THRESHOLD FOR PARALLELISM

NONE

 

Conclusion OLTP & OLAP

 

As we can see in the results the MAXDOP 1 setting is the slowest in comparison to all other tests in full. This means that in most cases you will limit the throughput of your SQL Server if you set your MAXDOP to one.

The fastest configuration for my laptop has been proven to be MAXDOP 2 with COST THRESHOLD FOR PARALLELISM 6 for this workload with this hardware configuration. This is however not an exact science; this has to be tested for every system.

The MAXDOP 1 was the slowest because we are limiting our Read intensive workload to use one core for every query it is executing. Which increases the time for every read query in total, this makes that the workload was the slowest.

The fastest configuration is when we allow the Read intensive workload to use 2 cores and limit the write/update/delete workload to 1 core. Because we are running both workloads together with 1 thread, we are letting our read intensive workload use 2 cores and leaving the 2 other cores available for our OLTP workload. Which means that we are getting an ideal throughput for our workload as seen to our hardware.

What with Data warehouses?

Our data warehouse will primarily have a read intensive aka OLAP workload, with one or more daily loads (INSERT/UPDATES/DELETES) happening. This means that we should get the most throughput on our read queries. I have been to some clients who have set their MAXDOP to 1 for their data warehouse this will have a negative effect on the speed with which your queries will execute. I will show you that now by test doing just the OLAP workload without the OLTP workload interfering.

For this I will do 3 tests

  1. 100 Iterations 1 thread OLAP workload MAXDOP 1
  2. 100 Iterations 1 thread OLAP workload MAXDOP 2
  3. 100 Iterations 1 thread OLAP workload MAXDOP 4

These are the results on my laptop

Workload

MAXDOP

CPU ITERATION

Seconds Iteration

Elapsed Time

OLAP

1

0,5297

0,5353

27,5624

OLAP

2

0,3703

0,2058

10,9843

OLAP

4

0,3117

0,1769

9,3593

 

As you can see in the table above the fastest way of doing read queries is by giving the workload as much cores as possible to increase the amount of reads which can be done in the least amount of time.

MAXDOP 1 is the slowest run, with MAXDOP 2 being 17 SECONDS faster. There is not a lot of difference between the run of MAXDOP 4 and MAXDOP 2 because of hardware limitations.

Conclusion OLAP

Because of the Read intended workload of a Data warehouse we should not put our MAXDOP to 1. As seen in the test above the more cores you have the more read throughput you will have which will make your queries in your DWH go faster. In these test we were 60% faster when using multiple cores for our workload instead of one core.

What with pure write workload?

When you are just writing data (UPDATE INSERT DELETE ) I would also like to know what MAXDOP setting would be the best. To test this, I have created 3 test scenarios

  1. 100 Iterations 1 thread OLTP workload MAXDOP 1
  2. 100 Iterations 1 thread OLTP workload MAXDOP 2
  3. 100 Iterations 1 thread OLTP workload MAXDOP 4

These are the results on my laptop

MAXDOP

CPU SECONDS

ACTUAL SECONDS

ELAPSED TIME

CLIENT SECONDS

1

0,0013

0,0048

1,1576

0,0467

2

0,0037

0,0183

1,3125

0,0568

4

0,006

0,0305

1,2988

0,0579

 

Conclusion OLTP

When inserting/updating/Deleting data we will be the fastest when running these queries in in a serial way. When running these processes in parallel we will increase the amount of CPU needed for the query and this will increase the time elapsed. When writing to in memory tables as we are doing in this test we see that writing data in amount of CPU time is 50% faster on MAXDOP 1. This means that going in a serial way will be faster than doing this in a parallel way.

Overall Conclusion

We should use caution when setting our MAXDOP setting, we can see that for the two different workloads we have different needs. The read intensive workload will in most cases benefit from a parallel execution, while the write intensive workload will benefit of a serial execution. Because most systems are not READ or WRITE only, I generally think the way to go is try to filter out those read-intensive high cost plans and allow these plans to make use of parallel execution. Thus limiting the amount of queries to be executed in parallel but not removing the option by default. This way we will not hold back our OLTP queries and we will be able to speed up those queries who can really benefit of the parallel execution. This means you should always use the MAXDOP setting in combination with your COST THRESHOLD FOR PARALLELISM SETTING. There is however no golden bullet for these settings and they will always need to be tested out to cope with the workload of your system.

Thank you for reading and stay tuned!

Tagged , , , , , , ,

SQL Saturday #602 Iceland

This week I  got the great news that I will be presenting 2 session on the SQL Saturday #602 in Iceland!

On Saturday the 18th of march in Reykjavik I will be talking on Operational analytics & will be presenting a whole new session on SQL Server problems!

The session on Operational analytics will be an full hour showing you how to combine your OLTP & OLAP workload in one database!

The tombola is a whole new concept! I will be bringing a tombola machine stacked full with SQL Server issues I’ve seen in the field throughout the years. An impartial observer will pick a ball from the bowl which contains an issue. This issue will then be explained & we will show the impact it has on the system. After this we will show you how to fix it on long term & short term. We will keep fishing issues out of the bowl the hour is finished.

All information concerning the SQL Saturday can be found on http://www.sqlsaturday.com/602/EventHome.aspx

I would like to thank the SQL Saturday team of Iceland for giving me the opportunity to do present on their SQL Saturday!

Hope to see a lot of you there!

Stay tuned!

Natively Stored Procs & In memory Tables: NextGen loading!

Today I was creating a dummy database and wanted to fill this with a certain amount of data. Since I was going to use this database for demo purposes, I created them with foreign keys & Primary keys, and some indexes. (For my next blog on real-time analytics) But during the creation of this database I found that the way I was using to load my data was quite slow, so I decided to load the data into my database using Natively Stored Procedures and in memory tables since this should be the fastest way. The result really baffled me!

Some background information: I’m using the DELL Inspirion 5220 (http://i.dell.com/sites/doccontent/shared-content/data-sheets/en/Documents/Dell_Precision_15_5000_Series_5510_Spec_Sheet.pdf) with 16GB of ram. My virtual machine has access to all cores but is capped to 90%, and my virtual machine has 4096MB of memory & 3584 MB of memory is allocated to SQL Server. The VM is a server core with SQL server 2016. My VM is running on SSD’s.

The First Scenario which I used was loading my data into SQL Server without the usage of In memory & Natively stored procedures. ( A lot of code will follow but this way you can test it yourself)

The following script was used.

— CREATE DATABASE PART

ALTER DATABASE REALTIMEANALYTICS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE MASTER
GO
DROP DATABASE IF EXISTS RealtimeAnalytics
GO
CREATE DATABASE [RealtimeAnalytics] CONTAINMENT NONE
ON PRIMARY
( NAME N’RealtimeAnalytics_1′FILENAME N’G:\DATA\RealtimeAnalytics_1.mdf’, SIZE = 131072KB , FILEGROWTH = 131072KB ),
( NAME N’RealtimeAnalytics_2′FILENAME N’G:\DATA\RealtimeAnalytics_2.ndf’, SIZE = 131072KB , FILEGROWTH = 131072KB ),
( NAME N’RealtimeAnalytics_3′FILENAME N’G:\DATA\RealtimeAnalytics_3.ndf’, SIZE = 131072KB , FILEGROWTH = 131072KB ),
( NAME N’RealtimeAnalytics_4′FILENAME N’G:\DATA\RealtimeAnalytics_4.ndf’, SIZE = 131072KB , FILEGROWTH = 131072KB )
LOG ON
( NAME N’RealtimeAnalytics_log’FILENAME N’C:\Data\LOG\RealtimeAnalytics_log.ldf’, SIZE = 131072KB , FILEGROWTH = 131072KB )
GO

I created a Database with 4 files called RealtimeAnalytics. Afterwards I’m adding 4 tables with keys to each other.

— CREATE TABLE PART

CREATE TABLE dbo.Clients
(
ClientID int identity(-2147483648,1)PRIMARY KEY NOT NULL,
ClientName varchar(200),
ClientCreditcard varchar(200)
)
GO
CREATE TABLE dbo.SalesPersons
(
SalesPersonID int identity(-2147483648,1)PRIMARY KEY NOT NULL,
SalesPersonName varchar(300),
SalesPersonDiscount int
)
GO
CREATE TABLE dbo.Item
(
ItemID int identity(-2147483648,1)PRIMARY KEY NOT NULL,
ItemName varchar(300),
ItemPrice numeric(15,2)
)
GO
CREATE TABLE dbo.Production
(
ProductionID int identity (-2147483648,1)PRIMARY KEY NOT NULL,
ClientID int FOREIGN KEY REFERENCES CLIENTS(ClientID)NOT NULL,
SalesPersonID int FOREIGN KEY REFERENCES SalesPersons(SalesPersonID)NOT NULL,
AmountOfItemsSold int,
ItemID int FOREIGN KEY REFERENCES Item(ItemID)NOT NULL,
DateOfSale datetime not null DEFAULT(Getdate())
)
GO

After the creation of the tables I’m going to insert data into the tables in a while loop (not the best solution but hey I will do the same thing with the natively)

—- FILL THE TABLES WITH DATA
DECLARE @Count int = 1
–FILL CLIENTS
WHILE @Count <= 1000
BEGIN
INSERT INTO dbo.Clients
select ‘Name’ +cast(@Count as varchar),‘1111-1111-111’+cast(@Count as varchar)
SET @Count = @Count +1
END
SET @Count = 1
–FILL SalesPersons
WHILE @Count <= 10
BEGIN
INSERT INTO dbo.SalesPersons
select ‘Name’ cast(@Count as varchar), FLOOR(RAND()*(501)+1)
SET @Count = @Count +1
END
SET @Count = 1
–FILL Items
WHILE @Count <= 800
BEGIN
INSERT INTO dbo.Item
select ‘Item’+cast(@Count as varchar),cast(RAND()*(2000001)as numeric(15,2))
SET @Count = @Count +1
END
SET @Count = 1
DECLARE @ClientID int
DECLARE @SalesPersonID int
DECLARE @ItemID int
–FILL Production
WHILE @Count <= 2000000
BEGIN
SET @ClientID =(select ClientID from dbo.Clients where ClientName ‘Name’+CAST(FLOOR(RAND()*(10001)+1)AS VARCHAR))
SET @SalesPersonID =(select SalesPersonID from dbo.SalesPersons where SalesPersonName ‘Name’+CAST(FLOOR(RAND()*(101)+1)AS VARCHAR))
SET @ItemID =(select ItemID from dbo.Item where ItemName =‘Item’+CAST(FLOOR(RAND()*(8001)+1)as varchar))
INSERT INTO dbo.Production
select @ClientID,@SalesPersonID,FLOOR(RAND()*(1001)+1),@ItemID,dateadd(second,floor(rand()*(6048001)+1),getdate())
SET @Count = @Count +1
END

When we run this script we see following workload on the machine, my C drive (SSD is getting hammered, but cpu usage is quite fine)

I decided to stop the script after about an hour and a half, because this was taking way to long

When I check how many records where inserted into the Production Table I see following result

During all that time only about 1 000 000 rows were inserted, this is unacceptable. So I thought let’s use that improved Inmemory table and natively stored procedures and see what the difference is. So I edited the script with following code

On the Create database I made it Memory Optimized enabled

use master
go
alter database RealtimeAnalytics add filegroup RealtimeAnalytics_mod contains memory_optimized_data
go
— adapt filename as needed
alter database RealtimeAnalytics add file (name=‘RealtimeAnalytics_mod’filename=‘G:\data\RealtimeAnalytics_mod’)
to filegroup RealtimeAnalytics_mod
go

I then created the same table structure but inmemory.

–MEMORY OPTIMIZED
use RealtimeAnalytics
go
CREATE TABLE dbo.Clients_MON
(
ClientID int identity(1,1)PRIMARY KEY NONCLUSTERED NOT NULL,
ClientName varchar(200),
ClientCreditcard varchar(200)
)
with (memory_optimized=on)
GO
CREATE TABLE dbo.SalesPersons_MON
(
SalesPersonID int identity(1,1)PRIMARY KEY NONCLUSTERED NOT NULL,
SalesPersonName varchar(300),
SalesPersonDiscount int
)
with (memory_optimized=on)
GO
CREATE TABLE dbo.Item_MON
(
ItemID int identity(1,1)PRIMARY KEY NONCLUSTERED NOT NULL,
ItemName varchar(300),
ItemPrice numeric(15,2)
)
with (memory_optimized=on)
GO
CREATE TABLE dbo.Production_MON
(
ProductionID int identity (1,1PRIMARY KEY NONCLUSTERED NOT NULL,
ClientID int FOREIGN KEY REFERENCES CLIENTS_MON(ClientID)NOT NULL,
SalesPersonID int FOREIGN KEY REFERENCES SalesPersons_MON(SalesPersonID)NOT NULL,
AmountOfItemsSold int,
ItemID int FOREIGN KEY REFERENCES Item_MON(ItemID)NOT NULL,
DateOfSale datetime not null DEFAULT(Getdate())
)
with (memory_optimized=on)

Notice that I can’t set my ID to -2147483648, more info on that here : (https://msdn.microsoft.com/en-us/library/dn247640.aspx). Why would they do that?

But this is out of scope and I’m going to continue by giving you the stored proc that I created. This is exactly the same thing as I did in the previous test, only now I made a native stored proc out of it.

USE RealtimeAnalytics
GO
create procedure dbo.FillDatabase with native_compilationschemabindingexecute as owner
as
begin
atomic 
with (transaction isolation level=snapshotlanguage=N’us_english’)
DECLARE @Count int = 1
–FILL CLIENTS
WHILE @Count <= 1000
BEGIN
INSERT INTO dbo.Clients_MON
select ‘Name’ cast(@Count as varchar),‘1111-1111-111’+cast(@Count as varchar)
SET @Count = @Count +1
END
SET @Count = 1
–FILL SalesPersons
WHILE @Count <= 10
BEGIN
INSERT INTO dbo.SalesPersons_MON
select ‘Name’ +cast(@Count as varchar), FLOOR(RAND()*(501)+1)
SET @Count = @Count +1
END
SET @Count = 1
–FILL Items
WHILE @Count <= 800
BEGIN
INSERT INTO dbo.Item_MON
select ‘Item’ cast(@Count as varchar),cast(RAND()*(2000001as numeric(15,2))
SET @Count = @Count +1
END
SET @Count = 1
DECLARE @ClientID int
DECLARE @SalesPersonID int
DECLARE @ItemID int
–FILL Items
WHILE @Count <= 2000000
BEGIN
SET @ClientID (select ClientID from dbo.Clients_MON where ClientName ‘Name’+CAST(FLOOR(RAND()*(10001)+1AS VARCHAR))
SET @SalesPersonID (select SalesPersonID from dbo.SalesPersons_MON where SalesPersonName ‘Name’+CAST(FLOOR(RAND()*(101)+1)AS VARCHAR))
SET @ItemID (select ItemID from dbo.Item_MON where ItemName ‘Item’+CAST(FLOOR(RAND()*(8001)+1)as varchar))
INSERT INTO dbo.Production_MON
select @ClientID,@SalesPersonID,FLOOR(RAND()*(1001)+1),@ItemID,dateadd(secondfloor(rand()*(6048001)+1),getdate())
SET @Count = @Count +1
END
end

After this was created I created following statement to load the data to inmemory and then switch it to my normal tables using insert into select

EXEC dbo.FillDatabase
GO
SET IDENTITY_INSERT dbo.Clients ON
insert into dbo.Clients(ClientID,ClientName,ClientCreditcard)
select ClientID,ClientName,ClientCreditcard from Clients_MON
SET IDENTITY_INSERT dbo.Clients OFF
GO
SET IDENTITY_INSERT dbo.SalesPersons ON
insert into dbo.SalesPersons (SalesPersonID,SalesPersonName,SalesPersonDiscount)
select SalesPersonID,SalesPersonName,SalesPersonDiscount from SalesPersons_MON
SET IDENTITY_INSERT dbo.SalesPersons OFF
GO
SET IDENTITY_INSERT dbo.Item ON
insert into dbo.Item(ItemID,ItemName,ItemPrice)
select ItemID,ItemName,ItemPrice from Item_MON
SET IDENTITY_INSERT dbo.Item OFF
GO
SET IDENTITY_INSERT dbo.Production ON
insert into dbo.Production (ProductionID,ClientID,SalesPersonID,AmountOfItemsSold,ItemID,DateOfSale)
select ProductionID,ClientID,SalesPersonID,AmountOfItemsSold,ItemID,DateOfSale from Production_MON
SET IDENTITY_INSERT dbo.Production OFF

While executing the last statement we can see that the workload has changed. We no longer see our disk getting hammered (Normal because we are hammering our Memory) and more CPU usage!

After about 8 minutes I got a ping that my query had executed. 8 MINUTES!!!!

So for doing exactly the same thing but first loading them into memory I got my result in my table in 8 minutes and 48 seconds, while with the other way (No In memory tables & native procs) I only had inserted 1 000 000 rows after 1 hour and 30 minutes. This is an excellent performance gain if you ask me! To conclude, let’s just say that In memory optimized tables & natively stored procs are fast, very fast, extremely fast! If you have a high insert rate on your table and the table can’t follow I would suggest trying to implement this scenario because it might be the case that you can greatly benefit from it.

Thanks for reading and stay tuned!

TEMPDB: The Ghost of VersionStore

Close to the 30th of October we find that some SQL Servers start acting spooky, as if they want to add to the sentiment of Halloween. This week I had one SQL Server doing exactly that. The server was running without any problems for the past months but today his TempdDB was starting to fill up. Of course this required some investigation, and I found that Version Store was the problem for my unexpected TempDB growth. The strange thing was that one database was taking up almost all of my tempdb space in version store, but it did not have any open transactions. Spooky stuff indeed!

So the ghost I was investigating today was the fact that version store data of one database would not be removed from tempdb after the transaction was committed. To show you how the ghost worked and how you can solve it, I’ll continue with code examples of my ghost.

Since I am working on a consolidated server I start off with creating two databases which have nothing to do with each other except the fact that they are on the same server instance and share the resources.

CREATE
DATABASE TESTDB_1

GO

CREATE
DATABASE TESTDB_2

GO

 

Next I’m going to create a table in each database

USE [TESTDB_1]

GO

CREATE
TABLE TestTable
(TTID int

identity(-2147483648,1),c1 varchar(200))

GO

USE [TESTDB_2]

GO

CREATE
TABLE TestTable
(TTID int

identity(-2147483648,1),c1 varchar(200))

GO

 

And insert some data into it

USE [TESTDB_1]

DECLARE @Insert int

SET @Insert = 1

WHILE @Insert <= 10000

BEGIN

INSERT
INTO dbo.TestTable

VALUES (‘TEST’+cast(@Insert as
varchar))

SET @Insert = @Insert + 1

END

GO

 

select
top 10000 *
from dbo.TestTable

GO

USE [TESTDB_2]

GO

DECLARE @Insert int

SET @Insert = 1

WHILE @Insert <= 10000

BEGIN

INSERT
INTO dbo.TestTable

VALUES (‘TEST’+cast(@Insert as
varchar))

SET @Insert = @Insert + 1

END

GO

 

select
top 10000 *
from dbo.TestTable

GO

 

Now I’m going to allow my databases to use snapshot so that we will be able to use the versionstore

ALTER
DATABASE TESTDB_1 SET
allow_snapshot_isolation
ON

GO

ALTER
DATABASE TESTDB_2 SET
allow_snapshot_isolation
ON

GO

 

I’m now going to execute an update on 1 of the tables in a committed transaction

 

SET
TRANSACTION
ISOLATION
LEVEL
READ
COMMITTED

BEGIN
TRAN

USE TESTDB_1

update dbo.TestTable

SET c1 =
‘UpdatedValue’

COMMIT

 

If I now go on and check the version store

SELECT
count(*)
as
amountofrec,d.name DBName from
sys.dm_tran_version_store tvs

inner
join
sys.databases d on tvs.database_id = d.database_id

group
by d.name

 

We see that there are some values in version store

If we now wait for the Tempdb garbage collector to kick in(this will take about a minute) and execute the query from above we can see that our version store is empty again.

This is the normal behavior. But today, I was not having the normal behavior, I was having spooky behaviour, I had this really small transaction which stayed open for about 2 hours on one of my databases, and a lot of update action on another database which I will demonstrate in the following code.

This is the open small transaction

SET
TRANSACTION
ISOLATION
LEVEL
READ
COMMITTED

BEGIN
TRAN

USE TESTDB_1

update dbo.TestTable

SET c1 =
‘SmallTransactionUpdateNotCommitted’

where TTID =
2147483648

 

When we now check the versionstore we see our one row

If we look for an OPEN transaction we can see that the transaction is still open on the database

Because it is an open transaction this row will not be deleted from our versionstore.

Now I will start mass updating on the other table in the other database. TESTDB_2

SET
TRANSACTION
ISOLATION
LEVEL
READ
COMMITTED

BEGIN
TRAN

USE TESTDB_2

update dbo.TestTable

SET c1 =
‘MultipleUpdates’

COMMIT

GO 10

 

As we can see this updated all the values 10 times and committed the transaction

There are no open transactions left on the second database

If we go check our version store we will see that the rows are now in the version store

If we wait a minute to let our garbage collector kick in, we can see that …

The rows are still in version store!!!

So not only the row of the uncommitted transaction of 1 database will stay in version store, also all the other rows of transaction who are committed after the first transaction was opened! This means that one open transaction can cause your tempdb to fill up with data from other databases!

To prove my point, I will now commit the open transaction and see if our version store gets cleared.

If we wait about a minute and go check our versionstore again we can see that it has been cleared!

 

A spooky problem indeed! Because this means that even the smallest open transaction which uses version store in your consolidated database environment can haunt all of your databases!

The solution for this problem is quite straightforward, commit the open transaction or if you cannot commit it, kill it. Then after a minute your version store will be empty again.

 

Thank you for reading, and stay tuned!

Tagged , , , , , , ,

Clustered Columnstore Index TupleMove Magic

Today we will be exploring a new functionality in SQL SERVER 2014 Enterprise edition. This new feature is called the Clustered Columnstore index. In SQL 2012 we already got a “preview” with the NON-clustered columnstore index. But the concept has drastically changed with the new Clustered columnstore index.

In this post I will be talking about the difference with the old columnstore index, the internals of the Clustered columnstore index & when to use the clustered columnstoreindex.

  1. What’s the difference with Non-clustered Columnstore Index?

The most annoying thing with the old NON-clustered columnstore index was that it could not be updated. This major issue has been solved with the new Clustered columnstore index which makes it updatable. Also schema modifications are now available.

With the 2012 version there were only a limited number of data types supported. This has been fixed in the 2014 version.
Only the following datatypes are not yet supported with the clustered columnstore index: Text, Timestamp, HierachyID, SQL_Variant, XML, (n)Varchar(MAX), Geography, Geometry.

Even though the compression of the non-clustered columnstore index was impressive this has also been improved on the SQL 2014 version, with the archival compression.

  1. What’s going on under the hood of the SQL SERVER when working with Clustered Columnsstore index?(TUPLE)

As we know the 2012 columnstore index was not updatable, how did they fix this in the 2014 version?
Well actually they did not make the Columnstore table updatable, they just store the newly updated data into a delta store, which is a normal rowstore segment. After this they mark the old row in the columnstore for deletion. When this delta store reaches a threshold (1048576 rows), it becomes a closed segment which is marked to be inserted into the clustered columnstore index, after this a new delta store is created for new inserted/updated rows.
However if a row is currently stored into a DeltaStore and it is then updated, a normal update of the deltastore will occur.

When does this insertion occur?
This is handled by a mechanism called the tuple mover which is a background process which runs every 5 minutes or it can be triggered manually by rebuilding the table. The tuple mover will look for closed segments of the deltastore and then insert these values into the clustered columnstore index, it will also delete the rows which are marked for deletion and it will remove updated rows from the clustered columnstore which were stored in the delta store. As said before the tuple mover will look for closed segments which are delta stores which have reached the size of 1048576 rows.
This is the scenario for when we do normal inserts.However when we do a bulk insert, the segment handling becomes a different story.

When you do a bulk insert the threshold for the deltastore is only 100 000 rows. Afterwards the delta store will be marked as closed and will wait for the tuple mover to insert them into the Clustered columnstore table.
The tuple mover will not block any read scans of the data but it will block deletes and updates which are running at the moment that the tuple move comes into action. But for a datawarehouse system this should not present major blocking issues, and that is the system for which the clustered columnstore index is intended but more on that later in this post.

I will now continue with a small presentation of the delta store/tuple mover in action.

First I will create a new table and insert 1 200 000 rows.

—Create a test table

Create
table TestTuple

(

c1_ID int
not
null,

c1_Product varchar(100)
not
null

)

 

—Create the clustered columnstore index

 

create
clustered
columnstore
index ix_test on testtuple

 

—Insert 1 200 000 rows

 

DECLARE @teller int

SET @teller = 1

 

while @teller <= 1200000

begin

insert
into TestTuple
(c1_ID,c1_Product)

values(@teller,
‘Productje’)

set @teller = @teller + 1

end

 

—Check the tuple move

 

select
*

        from
sys.column_store_dictionaries;

select
*

        from
sys.column_store_segments;

select
*

        from
sys.column_store_row_groups;

    

After this when we check the DMV we can see that in the table there is a Delta store that is marked as closed which will get updated by the tuple mover to the table and 2 delta stores which are open in which newly inserted data will be put

We can see when we rebuild the table, the deltastore becomes inserted into the columnstore

    —Rebuild the table

 

Alter
table testtuple rebuild

 

—rerun dmv the tuple move

 

select
*

        from
sys.column_store_dictionaries;

select
*

        from
sys.column_store_segments;

select
*

        from
sys.column_store_row_groups;

        

 

What happens when we delete data from the table?

What happens if we delete about 120 000 records

—Delete data from table

 

delete
from testtuple

where c1_id < 40000

 

  • I got the data three times in my table that’s why I have 40000

 

As you can see no data has actually been deleted. But we have rows which are marked for deletion.

 

—Select data where id < 40000

 

select
*
from testtuple where c1_id < 40000

 

We can’t query this data because the rows that are marked for deletion are stored in a bitmap which is checked before any query is executed.

 

Now let’s see what happens when we update the data?

—Update data where id

 

update testtuple

set [c1_Product] =
‘ProductjeV2’

where c1_id > 40000 and c1_id < 80000

 

—rerun dmv the tuple move

 

select
*

        from
sys.column_store_dictionaries;

select
*

        from
sys.column_store_segments;

select
*

        from
sys.column_store_row_groups;

 

—Select updated data

 

select
*
from testtuple where c1_id > 40000 and c1_id < 80000

 

 

 

As we can see the rows have not been updated, but they are just added to a new deltastore which is open because we have not yet reached our threshold.

If we update the same rows again we will see another behavior.

 

—Update data where id

 

update testtuple

set [c1_Product] =
‘ProductjeV3’

where c1_id > 40000 and c1_id < 80000

 

 

As we can see the amount of rows in the deltastore stays the same. We continue by selecting the updated data

—Select updated data

 

select
*
from testtuple where c1_id > 40000 and c1_id < 80000

 

So only when we are updating newly inserted/updated rows we are actually updating the rows, otherwise the current rows just get marked for deletion and will be skipped in the query and only the latest updated value is shown.It is the same case for deleting rows, we will not see the deleted rows but they are still stored in the table. Only after the tuple move comes into action or if we rebuild the table/index the rows are inserted or deleted in the CCI.

 

 

  1. What table should I use for the clustered columnstore index?

     

So for which systems should we use the clustered columnstore index? This answer is quite straightforward: Datawarehouse systems. Clustered columnstore index is perfect for big fact tables which are used for aggregating lots of values. Also with the new compression the size of your fact tables can be drastically lowered. For an OLTP system the clustered columnstore index is not a good thing because the tuple mover might cause serious blocking and the CCI will actually perform worse when seeking single rows. But more on that in a future post.

Thanks for reading, in the next posts I will be covering the performance of the new CCI and other new features in SQL SERVER 2014 like the in-memory tables. Stay tuned!

Tagged , , , ,