Improve SQL Server query performance on large tables. Simple Answer NO. You cannot help ad hoc queries on a 2. Fill Factor on the Clustered Index. Detailed Answer As I have stated in other answers on this topic, Index design is both Art and Science and there are so many factors to consider that there are few, if any, hard and fast rules. You need to consider the volume of DML operations vs SELECTs, disk subsystem, other indexes triggers on the table, distribution of data within the table, are queries using SARGable WHERE conditions, and several other things that I cant even remember right now. I can say that no help can be given for questions on this topic without an understanding of the Table itself, its indexes, triggers, etc. Now that you have posted the table definition still waiting on the Indexes but the Table definition alone points to 9. I can offer some suggestions. First, if the table definition is accurate 2. If you need to optimise SQL Server performance, it pays to understand SQL Server Statistics. Grant Fritchey answers some frequentlyasked questions about SQL Server. Is there any difference between updating the statistics of a table using spupdatestats with out resample and updating stats of table using UPDATE STATISTICS without. Fill Factor then you can pretty much ignore the rest of the answers advice here. Sorry to be less than political here, but seriously, its a wild goose chase without knowing the specifics. And now that we see the table definition it becomes quite a bit clearer as to why a simple query would take so long, even when the test queries Update 1 ran so quickly. The main problem here and in many poor performance situations is bad data modeling. Recommendations First, this table really needs to be remodeled. If this is a data warehouse table then maybe, but if not then these fields really need to be broken up into several tables which can all have the same PK. You would have a master record table and the child tables are just dependent info based on commonly associated attributes and the PK of those tables is the same as the PK of the master table and hence also FK to the master table. There will be a 1 to 1 relationship between master and all child tables. The use of ANSIPADDING OFF is disturbing, not to mention inconsistent within the table due to the various column additions over time. Not sure if you can fix that now, but ideally you would always have ANSIPADDING ON, or at the very least have the same setting across all ALTER TABLE statements. Consider creating 2 additional File Groups Tables and Indexes. It is best not to put your stuff in PRIMARY as that is where SQL SERVER stores all of its data and meta data about your objects. You create your Table and Clustered Index as that is the data for the table on Tables and all Non. Clustered indexes on IndexesIncrease the Fill Factor from 5. This low number is likely why your index space is larger than your data space. Doing an Index Rebuild will recreate the data pages with a max of 4k out of the total 8k page size used for your data so your table is spread out over a wide area. If most or all queries have ER1. ORGCODE in the WHERE condition, then consider moving that to the leading column of the clustered index. Assuming that it is used more often than ER1. ORDNBR. If ER1. 01ORDNBR is used more often then keep it. It just seems, assuming that the field names mean Organization. Code and Order. Number, that Org. Code is a better grouping that might have multiple Order. Numbers within it. Minor point, but if ER1. It can be very useful to update statistics in SQL Server. Learn how to avoid common missteps that lead to longrunning maintenance jobs and wasted IO. I am currently comparing performance of PostgreSQL with several other SQL systems. I am aware of the timing option to turn on timing queries. BoostingUpd4.png' alt='Sql Server Update Statistics Full Scan All Tables In Sql' title='Sql Server Update Statistics Full Scan All Tables In Sql' />ORGCODE is always 2 characters, then use CHAR2 instead of VARCHAR2 as it will save a byte in the row header which tracks variable width sizes and adds up over millions of rows. As others here have mentioned, using SELECT will hurt performance. Not only due to it requiring SQL Server to return all columns and hence be more likely to do a Clustered Index Scan regardless of your other indexes, but it also takes SQL Server time to go to the table definition and translate into all of the column names. It should be slightly faster to specify all 2. SELECT list though that wont help the Scan issue. But do you ever really need all 2. Good luck Update. Sql Server Update Statistics Full Scan All Tables In Sql' title='Sql Server Update Statistics Full Scan All Tables In Sql' />For the sake of completeness to the question how to improve performance on a large table for ad hoc queries, it should be noted that while it will not help for this specific case, IF someone is using SQL Server 2. IF the table is not being updated, then using Columnstore Indexes is an option. For more details on that new feature, look here. I believe these were made to be updateable starting in SQL Server 2. In that results set, we can see that for document with identifier 14536, there are 3 occurrences of keyword. This allows us to tell that a FullText. Update. 2Additional considerations are Enable compression on the Clustered Index. This option became available in SQL Server 2. Enterprise Edition only. Please see the MSDN page for Data Compression for details on Row and Page Compression. If you cannot use Data Compression, or if it wont provide much benefit for a particular table, then IF you have a column of a fixed length type INT, BIGINT, TINYINT, SMALLINT, CHAR, NCHAR, BINARY, DATETIME, SMALLDATETIME, MONEY, etc and well over 5. NULL, then consider enabling the SPARSE option which became available in SQL Server 2. Please see the MSDN page for Use Sparse Columns for details. I Went Walking Sequencing Activity Preschool.