0

Thanks in advance guys, quick inquiry below:

I have a query, hitting a 10 columns- 14,000,000 row table with a clustered index on Id, and a non-clustered index on f_date.

I also do 2 left joins to really small tables… and I have a where clause:

WHERE F_date >= '20131212'

(the statement shall return around 4 million rows)

The statement takes hours to run… I moved the tables over to another box, and I found out that without the clustered index and with just the non-clustered index runs in seconds, how is this possible?

Furthermore in the new box I created the clustered key, and it takes hours again, I delete it and runs in seconds! Why?!

One thing I saw is that in sys.dm_exec_query_memory_grants, when it runs slow is when it gets assigned 20MB… when it runs in seconds it gets 5GB granted…

So, no clustered index = lots of memory granted = runs in seconds. With clustered index = 20MB = runs in hours. ?!?!

ps: the join also throws a warning because I am joining varchar to nvarchar.

Chicago1988
  • 970
  • 3
  • 14
  • 35
  • Better post that whole query, along with an execution plan, table definition plus any indexes on them. Without that we would be guessing blindly as to the cause of bad performance. – TT. Jan 28 '16 at 17:00
  • Definitely would like to see the execution plan. – SQLChao Jan 28 '16 at 17:22
  • Thanks for your promt reply... Well I never get to the actual execution plan for the one that takes hours, because after 1 hour and seeing tha it only got granted 5MB I just terminate it... But I do see lazy spool on the estimated one... Will update this shortly; but as of now, I see a lot similarities with this guy http://stackoverflow.com/questions/33948706/order-by-slows-query-down-massively – Chicago1988 Jan 28 '16 at 17:50
  • Hi guys, thanks for all your support... Finally the issue was that I had 3 joins, and the joins were joining varchar(10) to nvarchar (255)... That messed up the engine... and it made the engine sometimes assign 10mb for the statement and sometimes 4gb... Now I changed the datatypes to match, and the engine always gives the statement 9GB for the execution, and runs super fast. Makes sense for you guys? Anyone had a similar issue? – Chicago1988 Jan 29 '16 at 11:04

0 Answers0