1

Currently, I'm trying to run the following example query:

SELECT [DATA1], [DATA2] FROM TABLE WHERE
    [DIMENSION0] IN (1, 5, ... (possibly 10s of numbers)) AND
    [DIMENSION1] IN (5) AND
    [DIMENSION2] IN (10) AND
    [DIMENSION3] IN (48) AND
    [DIMENSION4] IN (1) AND
    [DIMENSION5] IN (1) AND
    [DIMENSION6] IN (8) AND
    [DIMENSION7] IN (1) AND
    [DIMENSION8] IN (52) AND
    [DIMENSION9] IN (1, 10, ... (possibly 100s of numbers)) AND
    [DIMENSION10] IN (1, 235, ... (possibly 1000s of numbers)) AND
    [DIMENSION11] IN (1)

The table looks like the following;

D = Dimension

[D0] [D1] [D2] [D3] [D4] [D5] [D6] [D7] [D8] [D9] [D10] [D11] [DATA1] [DATA2]

Which contains a clustered index along all the dimensions, and could contain millions of records.

When I run this query through SSMS, I get the following query plan:

enter image description here

Here, it wildly overestimates the number of records it is looking for, which I believe is the reason for it running so slowly.

I've updated the statistics, but that wasn't the problem, so I'm left with it being an issue with the query.

I've also been able to improve the speed of the query by forcing SQL to run in parallel by using:

OPTION(QUERYTRACEON 8649)

This produces the following execution plan:

enter image description here

This is faster, but it is still overestimating the number of rows.

I was hoping that someone might be able to help me understand why this estimation is so high, and how I could possibly reduce it.

Clustered index definition:

/****** Object:  Index [ClusteredIndex-20180726-083210]    Script Date: 
26/07/2018 09:47:58 ******/
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20180726-083210] ON 
[dbo].[TABLE]
(
[DIMENSION0] ASC,
[DIMENSION1] ASC,
[DIMENSION2] ASC,
[DIMENSION3] ASC,
[DIMENSION4] ASC,
[DIMENSION5] ASC,
[DIMENSION6] ASC,
[DIMENSION7] ASC,
[DIMENSION8] ASC,
[DIMENSION9] ASC,
[DIMENSION10] ASC,
[DIMENSION11] ASC,
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = 
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I perform insert, updates and deletes to the table.

I can't use a Clustered Columnstore Index, as the Data2 column is varbinary(max). I tried using a non-clustered version, but the query plan just used the clustered index.

I did update statistics with FULLSCAN. There are some Dimensions that are hit more than others. I have previously experimented with the order of the Dimensions in the Clustered Index, but it still overestimates the number of rows.

Full query with plan: https://www.brentozar.com/pastetheplan/?id=HyLHtXDVX

  • @andy, is the values in IN clause is static? – Biju jose Jul 26 '18 at 8:48
  • I mean, which dimensions have the most unique values? – Stijn Wynants Jul 26 '18 at 9:44
  • This can vary, but with current table, Dimensions 3,4,5 and 11 all have a single unique value. – Andy Jul 26 '18 at 10:27
  • What happens if you remove columns 3,4,5 and 11 from the index and/or the query? – Lennart Jul 26 '18 at 10:59
  • @Lennart - Just tried it, and it reduced the percentage from 300% -> 292%, but not much of a large difference was made. – Andy Jul 26 '18 at 11:08
1

The way I see it - the problem is bad SQL, Terribly bad.

Starts with:

[DIMENSION3] IN (48) AND

Generate a Where, not an IN, if you ahve one element.

But worse:

[DIMENSION10] IN (1, 235, ... (possibly 1000s of numbers)) AND

There is no statistics on IN, so it works best with smallish selections. In this case, it is better to make a temp table WITH STATISTICS and load the values in there, then replace the IN with some sort of subselect. This way the query optimizer actually has an idea what he is facing (in terms of selectivity) and may decide to approach in a different way.

Otherwise there are 2 things for you ;)

  • Get decent hardware ;) Then possibly in memory tables?
  • Realize there is a reason for Analysis Service.

Queries like that really stretch SQL Server. While it made progress, this is exactly what Analysis Server cubes are made for.

  • Thanks for the comment! Turns out, this is the answer to why the statistics are so bad. I've tried your recommendations, but it seems to have little impact on performance (bar the hardware, which hasn't been changed, but will make it a lot faster!). I'm going to look at improving the uniqueness of the where criteria and continue to monitor performance. – Andy Jul 30 '18 at 7:39
1

The issue may not be with improving statistics, but rather a problematic indexing strategy.

Indexing all (or many) columns is usually not effective. Indexes were intended to be built from a small number of high-cardinality columns using small data types (int, bigints, small varchars).

You may consider hashing any huge columns like the varbinary(max) as part of the insert or ETL process and then indexing them:

How to handle uniqueness of many large columns in SQL Server?

How many rows in your main table? What percent of those rows will be returned by your query?

I ask because if your query returns ~20% of the data then nonclustered indexes will probably be eliminated as a retrieval path because scanning NC indexes can result in hitting the same datapage at different times during the scan to retrieve multiple rows on that page.

  • Interesting, so if I take all my dimensions, and combine them into a varchar(100), I might get better performance as I would have a unique column that SQL Server can use as an index? (I'll give this a go). Currently the table contains 3 Million+ rows, and is returning 38795. The 3 million will eventually rise into the 10's of millions. – Andy Jul 27 '18 at 14:51
  • So? Here is a hint: last time I did that we had to keep 10 years data and where loading 400 million rows or so every day, 5 days a week. An we wanted that done in less than half an hour, btw. 10s of millions is pocket change for a data warehouse. It is not even mid size - it is something you can run on a smallish VM (smallish for database servers). – TomTom Jul 27 '18 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.