SS2019 Running Extremely Slow

Question

Post reply

SS2019 Running Extremely Slow

frederico_fonseca

SSCoach

Points: 16157
More actions
April 10, 2022 at 9:49 pm
Answer
#4013639

while looking into it - was there a reason for you to build a heap with columnstore indexes instead of making one of them a clustered columnstore index?

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

water490 SSC Eights! Points: 959 More actions · Answer 1

duplicate post

This reply was modified 2 years, 7 months ago by water490.

water490 SSC Eights! Points: 959 More actions · Answer 2

frederico_fonseca wrote:

while looking into it - was there a reason for you to build a heap with columnstore indexes instead of making one of them a clustered columnstore index?

This is a great question. I am rookie. I know basics on writing SQL but have no clue on anything else. If I made a mistake in setting up index please let me know how I can fix it. Sorry for the rookie mistakes.

water490 SSC Eights! Points: 959 More actions · Answer 3

frederico_fonseca wrote:

while looking into it - was there a reason for you to build a heap with columnstore indexes instead of making one of them a clustered columnstore index?

this worked! why was this not an issue on my old machine?

water490 SSC Eights! Points: 959 More actions · Answer 4

frederico_fonseca wrote:

while looking into it - was there a reason for you to build a heap with columnstore indexes instead of making one of them a clustered columnstore index?

the query i have created has multiple sub-components and those are put into temp tables. for each temp table i create, i also create an index for it. below is a typical one:

--Create indexes
CREATE NONCLUSTERED COLUMNSTORE INDEX [MyIndex] ON [dbo].[tblExpiration]
(
[SYMBOL1],
[DATE],
[EXPIRATION]
)WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0) ON [PRIMARY]

i am not super experienced with this. is this the correct way to do it? should i be using non-clustered? or should i be using clustered?

there are multiple columns i use throughout the script so i need those columns to be indexed. to my simple brain i think i need columnstore index but not sure whether it should be clustered or not.

water490 SSC Eights! Points: 959 More actions · Answer 5

another question...

the execution plan says to create a non-clustered index on main table with the suggested fields:

Missing INdex (Impact 41.8205): Create Nonclustered index [<Name of missing index, sysname,>] on dbo.MyDB ([Column1], [Column2]) include ([Column3)

i added this index with noted fields but it still says the same message. do you know why it is saying this?

This reply was modified 2 years, 7 months ago by water490.

frederico_fonseca SSCoach Points: 16157 More actions · Answer 6

instead of having all your tables as heap and with additional non clustered indexes (columnstore or not) try and make one of them a clustered columnstore (or a normal clustered index).

using columnstore vs normal clustered index depends on the usage given to the data - columnstore are good for aggregation queries - but bad for updates/deletes and some times also bad for queries that just do select the data without aggregating.

for the recommended indexes - many times they should be ignored - but they can be a pointer that some indexes may be required - not always and many times not worth it at all at least the way the adviser suggests.

water490 SSC Eights! Points: 959 More actions · Answer 7

frederico_fonseca wrote:

instead of having all your tables as heap and with additional non clustered indexes (columnstore or not) try and make one of them a clustered columnstore (or a normal clustered index).
using columnstore vs normal clustered index depends on the usage given to the data - columnstore are good for aggregation queries - but bad for updates/deletes and some times also bad for queries that just do select the data without aggregating.
for the recommended indexes - many times they should be ignored - but they can be a pointer that some indexes may be required - not always and many times not worth it at all at least the way the adviser suggests.

Most of my queries are a combination of select and group by so looks like columnstore is the way to go.

Thank you so much for your help on this! I appreciate it very much!!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Even with the columnstore thing, the original issue on this thread has never been solved. What made the old query suddenly perform so poorly when the database was moved to the new system? And what else is going to leave streak marks in the bed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

water490 SSC Eights! Points: 959 More actions · Answer 9

Jeff Moden wrote:

Even with the columnstore thing, the original issue on this thread has never been solved. What made the old query suddenly perform so poorly when the database was moved to the new system? And what else is going to leave streak marks in the bed?

agreed.

not sure what happened. i suspect (not certain) that the indexes got corrupted somehow when the move happened. as soon as the index was re-created the performance improved and got better. the most important thing for now is that the performance is significantly improved. why it degraded is the a thing of the past.