ricardo_chicas (8/19/2010)
For several reports we need to extract about 5 millions records from a table of 124 million ( 173 gb)I am able to use the clustered index ( with a "like" statement) and the only other parameter is a column with a state so it is not helping much, the thing is that the resultant table is really big ( 5 gb ) and is taking forever to load and it is also consuming lots of resources from the server ( we usually get the error there is not enough system memory to run this query... ) That said, I need to find a way to obtain the same data in a faster way or at least not consuming that much resources, maybe in chunks but I am not sure how to do it...
We have a server with 16 processors and 42 gb of ram, sql server 2006 sp3
Do you have any indexes to actually serve this query?
Retrieving 5M out of 124M rows is a case where a very selective index would certainly help.
On the other hand, a "like" predicate pretty much forces optimizer to do a full table scan - have you traced the query to see what is doing?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.