1) If you are using Enterprise Edition, I would go with Partitioned Tables/Indexes, which would provide you an array of benefits.

2) If not, I would used partitioned views.  Benefits, but...

Since you are using SQL 2005, there is also an OPTION you can use with the query statement.  See here in BOL:  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/66fb1520-dcdf-4aab-9ff1-7de8f79e5b2d.htm

1) you don't need AWE on a 4GB box.  Investigate /3GB switch for you boot.ini in BOL.

2) If the database doesn't have lots of free space, index maint will not be...

This sounds like a parameter sniffing issue.  Assuming this is a sproc, use the WITH RECOMPILE option. 

Investigate parameter sniffing.  Almost certainly to blame here.  If this is in a sproc, consider the WITH RECOMPILE option.

1) WROX Press - Transact SQL for Beginners

2) Cursors are bad from a performance perspective, as has already been stated.  REALLY REALLY bad. 

3)...

Are you sure you don't mean to use Performance Monitor??  This is the tool to use for I/O investigation I believe.  There are some DMVs you can use on SQL...

IMHO, only one thing will likely provide any benefit here, and it is a very slim hope.  With 99% of your data being IsActive = 1, pruning inactive records is...

This is not correct.  Review DBCC SHOW_STATISTICS.  You will see average stuff as well as distribution stats.  The latter are used for specific value queries like you present and would...

you can do (@a = 0 and a is null) to capture the case where input is 0 and actual value is null

1) Parameter sniffing could be at issue here.  Perhaps the same query was called with a range of invoice numbers that was 50% of the entire table, in which case...

Sorry Will, I did my post after reading the first (and I thought without checking) only page of posts for the initial post. 

Had...

Joe Chang is the man for all of the math related to optimizer choices.  Pretty complex stuff that basically boils down to "if using the index costs less then use...

1) varchars only save you space if the average DATA length is several chars < length of char column.

2) Create new table with varchars.  Insert data to this table while...

Typical Answer:  It depends. 

Seriously though, if your query patterns normally restrict the data such that only smaller pieces of the table will be...