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.