Is there a way to tell SQL Server what data structure/algorithm to use for it's indexing?
In other words, can you tell it to use hashes, b-trees or a different base value for a b-tree to make it less memory intensive?
My problem, is that I have a 12TB database that wants to use 1TB of RAM for indexing, but the box only has 192GB of RAM in it. SQL Server does some memory management and the database eventually thrashes out, disk I/O queue gets way to high, etc. probably due to swapping. I'd like to use indexing, but I believe the default btree indexing is too memory heavy. Based on responses, I might also post a followup question. Thank you.
I reckon I stared at this question for a while trying to understand what you're asking. Until it dawned on me: you have large tables without proper indexes and your queries always trigger table scans. This causes a high IO load.
I recommend you follow the Waits and Queues methodology to identify the bottlenecks. Is a proven methodology that is used by the SQL Customer Advisory Team (SQLCAT) in its investigations. Once the bottlenecks are identified, ask here about how to overcome them.
But first you will need to get your understanding of SQL Server and general database concepts a little bit higher then what transpires from your your question. I highly recommend you read these topics on MSDN before going any further: