Improve SQL Server query performance on large tables

Question

I have a relatively large table  currently 2 million records  and would like to know if it s possible to improve performance for ad-hoc queries  The word ad-hoc being key here  Adding indexs is not an option  there are already indexs on the columns which are queried most commonly    Running a simple query to return the 100 most recently updated records   select top 100   from ER101 ACCT ORDER DTL order by er101 upd date iso desc   Takes several minutes  See execution plan below     Additional detail from the table scan     SQL Server Execution Times    CPU time   3945 ms   elapsed time   148524 ms    The server is pretty powerful  from memory 48GB ram  24 core processor  running sql server 2008 r2 x64   Update  I found this code to create a table with 1 000 000 records   I thought i could then run SELECT TOP 100   FROM testEnvironment ORDER BY mailAddress DESC on a few different servers to find out if my disk access speeds were poor on the server   WITH t1 N  AS  SELECT 1 UNION ALL SELECT 1   t2 N  AS  SELECT 1 FROM t1 x  t1 y   t3 N  AS  SELECT 1 FROM t2 x  t2 y   Tally N  AS  SELECT TOP 98 ROW NUMBER   OVER  ORDER BY  SELECT NULL   FROM t3 x  t3 y   Tally2 N  AS  SELECT TOP 5 ROW NUMBER   OVER  ORDER BY  SELECT NULL   FROM t3 x  t3 y   Combinations N  AS  SELECT DISTINCT LTRIM RTRIM RTRIM SUBSTRING poss a N 2     SUBSTRING vowels b N 1                        FROM Tally a                     CROSS JOIN Tally2 b                     CROSS APPLY  SELECT  B C D F G H J K L M N P R S T V W Z SCSKKNSNSPSTBLCLFLGLPLSLBRCRDRFRGRPRTRVRSHSMGHCHPHRHWHBWCWSWTW   d poss                      CROSS APPLY  SELECT  AEIOU   e vowels   SELECT IDENTITY INT 1 1  AS ID  a N   b N AS N INTO  testNames FROM Combinations a  CROSS JOIN Combinations b   SELECT IDENTITY INT 1 1  AS ID  firstName  secondName INTO  testNames2 FROM  SELECT firstName  secondName       FROM  SELECT TOP 1000 --1000   1000   1 000 000 rows             N AS firstName             FROM  testNames             ORDER BY NEWID    a       CROSS JOIN  SELECT TOP 1000 --1000   1000   1 000 000 rows                   N AS secondName                   FROM  testNames                   ORDER BY NEWID    b  innerQ   SELECT firstName  secondName  firstName         secondName     fake com  AS eMail  CAST  ABS CHECKSUM NEWID       250    1 AS VARCHAR 3         AS mailAddress   ABS CHECKSUM NEWID       152100    1 AS jID  IDENTITY INT 1 1  AS ID INTO  testNames3 FROM  testNames2  SELECT IDENTITY INT 1 1  AS ID  firstName  secondName  eMail   mailAddress   b N   b N AS mailAddress INTO testEnvironment FROM  testNames3 a INNER JOIN  testNames b ON a jID   b ID   --CLEAN UP USELESS TABLES DROP TABLE  testNames  DROP TABLE  testNames2  DROP TABLE  testNames3    But on the three test servers the query ran almost instantaneously  Can anyone explain this     Update 2  Thank you for the comments- please keep them coming    they led me to try changing the primary key index from non-clustered to clustered with rather interesting  and unexpected   results   Non-clustered     SQL Server Execution Times    CPU time   3634 ms   elapsed time   154179 ms    Clustered     SQL Server Execution Times    CPU time   2650 ms   elapsed time   52177 ms    How is this possible  Without an index on the er101 upd date iso column how can a clustered index scan be used   Update 3  As requested- this is the create table script   CREATE TABLE  dbo   ER101 ACCT ORDER DTL        ER101 ORG CODE   varchar  2  NOT NULL       ER101 ORD NBR   int  NOT NULL       ER101 ORD LINE   int  NOT NULL       ER101 EVT ID   int  NULL       ER101 FUNC ID   int  NULL       ER101 STATUS CDE   varchar  2  NULL       ER101 SETUP ID   varchar  8  NULL       ER101 DEPT   varchar  6  NULL       ER101 ORD TYPE   varchar  2  NULL       ER101 STATUS   char  1  NULL       ER101 PRT STS   char  1  NULL       ER101 STS AT PRT   char  1  NULL       ER101 CHG COMMENT   varchar  255  NULL       ER101 ENT DATE ISO   datetime  NULL       ER101 ENT USER ID   varchar  10  NULL       ER101 UPD DATE ISO   datetime  NULL       ER101 UPD USER ID   varchar  10  NULL       ER101 LIN NBR   int  NULL       ER101 PHASE   char  1  NULL       ER101 RES CLASS   char  1  NULL       ER101 NEW RES TYPE   varchar  6  NULL       ER101 RES CODE   varchar  12  NULL       ER101 RES QTY   numeric  11  2  NULL       ER101 UNIT CHRG   numeric  13  4  NULL       ER101 UNIT COST   numeric  13  4  NULL       ER101 EXT COST   numeric  11  2  NULL       ER101 EXT CHRG   numeric  11  2  NULL       ER101 UOM   varchar  3  NULL       ER101 MIN CHRG   numeric  11  2  NULL       ER101 PER UOM   varchar  3  NULL       ER101 MAX CHRG   numeric  11  2  NULL       ER101 BILLABLE   char  1  NULL       ER101 OVERRIDE FLAG   char  1  NULL       ER101 RES TEXT YN   char  1  NULL       ER101 DB CR FLAG   char  1  NULL       ER101 INTERNAL   char  1  NULL       ER101 REF FIELD   varchar  255  NULL       ER101 SERIAL NBR   varchar  50  NULL       ER101 RES PER UNITS   int  NULL       ER101 SETUP BILLABLE   char  1  NULL       ER101 START DATE ISO   datetime  NULL       ER101 END DATE ISO   datetime  NULL       ER101 START TIME ISO   datetime  NULL       ER101 END TIME ISO   datetime  NULL       ER101 COMPL STS   char  1  NULL       ER101 CANCEL DATE ISO   datetime  NULL       ER101 BLOCK CODE   varchar  6  NULL       ER101 PROP CODE   varchar  8  NULL       ER101 RM TYPE   varchar  12  NULL       ER101 WO COMPL DATE   datetime  NULL       ER101 WO BATCH ID   varchar  10  NULL       ER101 WO SCHED DATE ISO   datetime  NULL       ER101 GL REF TRANS   char  1  NULL       ER101 GL COS TRANS   char  1  NULL       ER101 INVOICE NBR   int  NULL       ER101 RES CLOSED   char  1  NULL       ER101 LEAD DAYS   int  NULL       ER101 LEAD HHMM   int  NULL       ER101 STRIKE DAYS   int  NULL       ER101 STRIKE HHMM   int  NULL       ER101 LEAD FLAG   char  1  NULL       ER101 STRIKE FLAG   char  1  NULL       ER101 RANGE FLAG   char  1  NULL       ER101 REQ LEAD STDATE   datetime  NULL       ER101 REQ LEAD ENDATE   datetime  NULL       ER101 REQ STRK STDATE   datetime  NULL       ER101 REQ STRK ENDATE   datetime  NULL       ER101 LEAD STDATE   datetime  NULL       ER101 LEAD ENDATE   datetime  NULL       ER101 STRK STDATE   datetime  NULL       ER101 STRK ENDATE   datetime  NULL       ER101 DEL MARK   char  1  NULL       ER101 USER FLD1 02X   varchar  2  NULL       ER101 USER FLD1 04X   varchar  4  NULL       ER101 USER FLD1 06X   varchar  6  NULL       ER101 USER NBR 060P   int  NULL       ER101 USER NBR 092P   numeric  9  2  NULL       ER101 PR LIST DTL   numeric  11  2  NULL       ER101 EXT ACCT CODE   varchar  8  NULL       ER101 AO STS 1   char  1  NULL       ER101 PLAN PHASE   char  1  NULL       ER101 PLAN SEQ   int  NULL       ER101 ACT PHASE   char  1  NULL       ER101 ACT SEQ   int  NULL       ER101 REV PHASE   char  1  NULL       ER101 REV SEQ   int  NULL       ER101 FORE PHASE   char  1  NULL       ER101 FORE SEQ   int  NULL       ER101 EXTRA1 PHASE   char  1  NULL       ER101 EXTRA1 SEQ   int  NULL       ER101 EXTRA2 PHASE   char  1  NULL       ER101 EXTRA2 SEQ   int  NULL       ER101 SETUP MSTR SEQ   int  NULL       ER101 SETUP ALTERED   char  1  NULL       ER101 RES LOCKED   char  1  NULL       ER101 PRICE LIST   varchar  10  NULL       ER101 SO SEARCH   varchar  9  NULL       ER101 SSB NBR   int  NULL       ER101 MIN QTY   numeric  11  2  NULL       ER101 MAX QTY   numeric  11  2  NULL       ER101 START SIGN   char  1  NULL       ER101 END SIGN   char  1  NULL       ER101 START DAYS   int  NULL       ER101 END DAYS   int  NULL       ER101 TEMPLATE   char  1  NULL       ER101 TIME OFFSET   char  1  NULL       ER101 ASSIGN CODE   varchar  10  NULL       ER101 FC UNIT CHRG   numeric  13  4  NULL       ER101 FC EXT CHRG   numeric  11  2  NULL       ER101 CURRENCY   varchar  3  NULL       ER101 FC RATE   numeric  12  5  NULL       ER101 FC DATE   datetime  NULL       ER101 FC MIN CHRG   numeric  11  2  NULL       ER101 FC MAX CHRG   numeric  11  2  NULL       ER101 FC FOREIGN   numeric  12  5  NULL       ER101 STAT ORD NBR   int  NULL       ER101 STAT ORD LINE   int  NULL       ER101 DESC   varchar  255  NULL   ON  PRIMARY  SET ANSI PADDING OFF ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PRT SEQ 1   varchar  12  NULL SET ANSI PADDING ON ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PRT SEQ 2   varchar  120  NULL SET ANSI PADDING OFF ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TAX BASIS   char  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 RES CATEGORY   char  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 DECIMALS   char  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TAX SEQ   varchar  7  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MANUAL   char  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR LC RATE   numeric  12  5  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR FC RATE   numeric  12  5  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR PL RATE   numeric  12  5  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR DIFF   char  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR UNIT CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR EXT CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR MIN CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TR MAX CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PL UNIT CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PL EXT CHRG   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PL MIN CHRG   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PL MAX CHRG   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TAX RATE TYPE   char  1  NULL SET ANSI PADDING ON ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ORDER FORM   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 FACTOR   int  NULL SET ANSI PADDING OFF ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MGMT RPT CODE   varchar  6  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ROUND CHRG   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 WHOLE QTY   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SET QTY   numeric  15  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SET UNITS   numeric  15  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SET ROUNDING   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SET SUB   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TIME QTY   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 GL DISTR PCT   numeric  7  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 REG SEQ   int  NULL SET ANSI PADDING ON ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ALT DESC   varchar  255  NULL SET ANSI PADDING OFF ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 REG ACCT   varchar  8  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 DAILY   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 AVG UNIT CHRG   varchar  1  NULL SET ANSI PADDING ON ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ALT DESC2   varchar  255  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 CONTRACT SEQ   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ORIG RATE   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 DISC PCT   decimal  17  10  NULL SET ANSI PADDING OFF ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 DTL EXIST   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ORDERED ONLY   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHOW STDATE   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHOW STTIME   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHOW ENDATE   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHOW ENTIME   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHOW RATE   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHOW UNITS   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 BASE RATE   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 COMMIT QTY   numeric  11  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MM QTY USED   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MM CHRG USED   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM TEXT 1   varchar  50  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM NBR 1   numeric  13  3  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM NBR 2   numeric  13  3  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM NBR 3   numeric  13  3  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PL BASE RATE   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 REV DIST   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 COVER   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 RATE TYPE   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 USE SEASONAL   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TAX EI   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TAXES   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 FC TAXES   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PL TAXES   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 FC QTY   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 LEAD HRS   numeric  6  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 STRIKE HRS   numeric  6  2  NULL SET ANSI PADDING ON ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 CANCEL USER ID   varchar  10  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ST OFFSET HRS   numeric  7  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 EN OFFSET HRS   numeric  7  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MEMO FLAG   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MEMO EXT CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MEMO EXT CHRG PL   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MEMO EXT CHRG TR   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MEMO EXT CHRG FC   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TIME QTY EDIT   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SURCHARGE PCT   decimal  17  10  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 INCL EXT CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 INCL EXT CHRG FC   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 CARRIER   varchar  6  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SETUP ID2   varchar  8  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHIPPABLE   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 CHARGEABLE   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM NBR ALLOW   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM NBR START   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM NBR END   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM SUPPLIER   varchar  8  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 TRACK ID   varchar  40  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 REF INV NBR   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 NEW ITEM STS   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MSTR REG ACCT CODE   varchar  8  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ALT DESC3   varchar  255  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ALT DESC4   varchar  255  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ALT DESC5   varchar  255  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SETUP ROLLUP   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MM COST USED   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 AUTO SHIP RCD   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM FIXED   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ITEM EST TBD   varchar  3  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ROLLUP PL UNIT CHRG   numeric  13  4  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ROLLUP PL EXT CHRG   numeric  13  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 GL ORD REV TRANS   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 DISCOUNT FLAG   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SETUP RES TYPE   varchar  6  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SETUP RES CODE   varchar  12  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PERS SCHED FLAG   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PRINT STAMP   datetime  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SHOW EXT CHRG   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PRINT SEQ NBR   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 PAY LOCATION   varchar  3  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 MAX RM NIGHTS   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 USE TIER COST   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 UNITS SCHEME CODE   varchar  6  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 ROUND TIME   varchar  2  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 LEVEL   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 SETUP PARENT ORD LINE   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 BADGE PRT STS   varchar  1  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 EVT PROMO SEQ   int  NULL ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  ER101 REG TYPE   varchar  12  NULL         Object   Index  PK  ER101 ACCT ORDER     Script Date  04 15 2012 20 24 37         ALTER TABLE  dbo   ER101 ACCT ORDER DTL  ADD  CONSTRAINT  PK  ER101 ACCT ORDER  PRIMARY KEY CLUSTERED         ER101 ORD NBR  ASC       ER101 ORD LINE  ASC       ER101 ORG CODE  ASC  WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  FILLFACTOR   50  ON  PRIMARY    The table is 2 8 GB in size with index size standing at 3 9 GB

User · Answer

Even if you have indexes on some columns that are used in some queries, the fact that your 'ad-hoc' query causes a table scan shows that you don't have sufficient indexes to allow this query to complete efficiently.

For date ranges in particular it is difficult to add good indexes.

Just looking at your query, the db has to sort all the records by the selected column to be able to return the first n records.

Does the db also do a full table scan without the order by clause? Does the table have a primary key - without a PK, the db will have to work harder to perform the sort?

User · Answer

I know it s been quite a time since the beginning    There is a lot of wisdom in all these answers  Good indexing is the first thing when trying to improve a query  Well  almost the first  The most-first  so to speak  is making changes to code so that it s efficient  So  after all s been said and done  if one has a query with no WHERE  or when the WHERE-condition is not selective enough  there is only one way to get the data  TABLE SCAN  INDEX SCAN   If one needs all the columns from a table  then TABLE SCAN will be used - no question about it  This might be a heap scan or clustered index scan  depending on the type of data organization  The only last way to speed things up  if at all possible   is to make sure that as many cores are used as possible to do the scan  OPTION  MAXDOP 0   I m ignoring the subject of storage  of course  but one should make sure that one has unlimited RAM  which goes without saying

User · Answer

I know that you said that adding indexes is not an option but that would be the only option to eliminate the table scan you have. When you do a scan, SQL Server reads all 2 million rows on the table to fulfill your query.

this article provides more info but remember: Seek = good, Scan = bad.

Second, can't you eliminate the select * and select only the columns you need? Third, no "where" clause? Even if you have a index, since you are reading everything the best you will get is a index scan (which is better than a table scan, but it is not a seek, which is what you should aim for)

User · Answer

Simple Answer  NO  You cannot help ad hoc queries on a 238 column table with a 50  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 can t 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 99  of the issue  I can offer some suggestions   First  if the table definition is accurate  238 columns  50  Fill Factor  then you can pretty much ignore the rest of the answers   advice here  -   Sorry to be less-than-political here  but seriously  it s 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  238 columns is not prohibited just like having 999 indexes is not prohibited  but it is also generally not very wise   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 ANSI PADDING 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 ANSI PADDING 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  Indexes  Increase the Fill Factor from 50   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  ER101 ORG CODE  in the WHERE condition  then consider moving that to the leading column of the clustered index  Assuming that it is used more often than  ER101 ORD NBR   If  ER101 ORD NBR  is used more often then keep it  It just seems  assuming that the field names mean  OrganizationCode  and  OrderNumber   that  OrgCode  is a better grouping that might have multiple  OrderNumbers  within it  Minor point  but if  ER101 ORG CODE  is always 2 characters  then use CHAR 2  instead of VARCHAR 2  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 238 column names in the SELECT list though that won t help the Scan issue  But do you ever really need all 238 columns at the same time anyway    Good luck   UPDATE 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 2012  or newer when that time comes  and IF the table is not being updated  then using Columnstore Indexes is an option  For more details on that new feature  look here  http   msdn microsoft com en-us library gg492088 aspx  I believe these were made to be updateable starting in SQL Server 2014    UPDATE 2 Additional considerations are    Enable compression on the Clustered Index  This option became available in SQL Server 2008  but as an Enterprise Edition-only feature  However  as of SQL Server 2016 SP1  Data Compression was made available in all editions  Please see the MSDN page for Data Compression for details on Row and Page Compression  If you cannot use Data Compression  or if it won t 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 50  of the rows are NULL  then consider enabling the SPARSE option which became available in SQL Server 2008  Please see the MSDN page for Use Sparse Columns for details

User · Answer

One of the reasons your 1M test ran quicker is likely because the temp tables are entirely in memory and would only go to disk if your server experiences memory pressure   You can either re-craft your query to remove the order by  add a good clustered index and covering index es  as previously mentioned  or query the DMV to check for IO pressure to see if hardware related   -- From Glen Barry -- Clear Wait Stats  consider clearing and running wait stats query again after a few minutes  -- DBCC SQLPERF  sys dm os wait stats   CLEAR    -- Check Task Counts to get an initial idea what the problem might be  -- Avg Current Tasks Count  Avg Runnable Tasks Count  Avg Pending Disk IO Count across all schedulers -- Run several times in quick succession SELECT AVG current tasks count  AS  Avg Task Count           AVG runnable tasks count  AS  Avg Runnable Task Count          AVG pending disk io count  AS  Avg Pending DiskIO Count  FROM sys dm os schedulers WITH  NOLOCK  WHERE scheduler id  lt  255 OPTION  RECOMPILE    -- Sustained values above 10 suggest further investigation in that area -- High current tasks count is often an indication of locking blocking problems -- High runnable tasks count is a good indication of CPU pressure -- High pending disk io count is an indication of I O pressure

User · Answer

There are a few issues with this query  and this apply to every query    Lack of index  Lack of index on er101 upd date iso column is most important thing as Oded has already mentioned   Without matching index  which lack of could cause table scan  there is no chance to run fast queries on big tables   If you cannot add indexes  for various reasons including there is no point in creating index for just one ad-hoc query  I would suggest a few workarounds  which can be used for ad-hoc queries    1  Use temporary tables  Create temporary table on subset  rows and columns  of data you are interested in  Temporary table should be much smaller that original source table  can be indexed easily  if needed  and can cached subset of data which you are interested in   To create temporary table you can use code  not tested  like   -- copy records from last month to temporary table INSERT INTO     my temporary table SELECT       FROM     er101 acct order dtl WITH  NOLOCK  WHERE      er101 upd date iso  gt  DATEADD month  -1  GETDATE     -- you can add any index you need on temp table CREATE INDEX idx er101 upd date iso ON  my temporary table er101 upd date iso   -- run other queries on temporary table  which can be indexed  SELECT TOP 100        FROM       my temporary table  ORDER BY      er101 upd date iso DESC   Pros    Easy to do for any subset of data  Easy to manage -- it s temporary and it s table  Doesn t affect overall system performance like view  Temporary table can be indexed  You don t have to care about it -- it s temporary       Cons    It s snapshot of data -- but probably this is good enough for most ad-hoc queries    2  Common table expression -- CTE  Personally I use CTE a lot with ad-hoc queries -- it s help a lot with building  and testing  a query piece by piece   See example below  the query starting with WITH    Pros    Easy to build starting from big view and then selecting and filtering what really you need  Easy to test    Cons    Some people dislike CDE -- CDE queries seem to be long and difficult to understand    3  Create views  Similar to above  but create views instead of temporary tables  if you play often with the same queries and you have MS SQL version which supports indexed views   You can create views or indexed views on subset of data you are interested in and run queries on view -- which should contain only interesting subset of data much smaller than the whole table   Pros    Easy to do  It s up to date with source data    Cons    Possible only for defined subset of data  Could be inefficient for large tables with high rate of updates  Not so easy to manage  Can affect overall system performance  I am not sure indexed views are available in every version of MS SQL    Selecting all columns  Running star query  SELECT   FROM  on big table is not good thing     If you have large columns  like long strings  it takes a lot of time to read them from disk and pass by network   I would try to replace   with column names which you really need   Or  if you need all columns try to rewrite query to something like  using common data expression     WITH recs AS       SELECT TOP 100          id as rec id -- select primary key only     FROM          er101 acct order dtl      ORDER BY          er101 upd date iso DESC   SELECT     er101 acct order dtl   FROM     recs     JOIN       er101 acct order dtl     ON       er101 acct order dtl id   recs rec id ORDER BY      er101 upd date iso DESC    Dirty reads  Last thing which could speed up the ad-hoc query is allowing dirty reads with table hint WITH  NOLOCK    Instead of hint you can set transaction isolation level to read uncommited   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   or set proper SQL Management Studio setting   I assume for ad-hoc queries dirty reads is good enough

User · Answer

You are getting a table scan there  meaning that you do not have an index defined on er101 upd date iso  or if that column is part of an existing index  the index can t be used  possibly it is not the primary indexer column    Adding missing indexes will help performance no end      there are already indexs on the columns which are queried most commonly   That does not mean they are used in this query  and they probably are not    I suggest reading Finding the Causes of Poor Performance in SQL Server by Gail Shaw  part 1 and part 2

User · Answer

The question specifically states the performance needs to be improved for ad-hoc queries, and that indexes can't be added. So taking that at face value, what can be done to improve performance on any table?

Since we're considering ad-hoc queries, the WHERE clause and the ORDER BY clause can contain any combination of columns. This means that almost regardless of what indexes are placed on the table there will be some queries that require a table scan, as seen above in query plan of a poorly performing query.

Taking this into account, let's assume there are no indexes at all on the table apart from a clustered index on the primary key. Now let's consider what options we have to maximize performance.

Defragment the table

As long as we have a clustered index then we can defragment the table using DBCC INDEXDEFRAG (deprecated) or preferably ALTER INDEX. This will minimize the number of disk reads required to scan the table and will improve speed.
Use the fastest disks possible. You don't say what disks you're using but if you can use SSDs.
Optimize tempdb. Put tempdb on the fastest disks possible, again SSDs. See this SO Article and this RedGate article.
As stated in other answers, using a more selective query will return less data, and should be therefore be faster.

Now let's consider what we can do if we are allowed to add indexes.

If we weren't talking about ad-hoc queries, then we would add indexes specifically for the limited set of queries being run against the table. Since we are discussing ad-hoc queries, what can be done to improve speed most of the time?

Add a single column index to each column. This should give SQL Server at least something to work with to improve the speed for the majority of queries, but won't be optimal.
Add specific indexes for the most common queries so they are optimized.
Add additional specific indexes as required by monitoring for poorly performing queries.

Edit

I've run some tests on a 'large' table of 22 million rows. My table only has six columns but does contain 4GB of data. My machine is a respectable desktop with 8Gb RAM and a quad core CPU and has a single Agility 3 SSD.

I removed all indexes apart from the primary key on the Id column.

A similar query to the problem one given in the question takes 5 seconds if SQL server is restarted first and 3 seconds subsequently. The database tuning advisor obviously recommends adding an index to improve this query, with an estimated improvement of > 99%. Adding an index results in a query time of effectively zero.

What's also interesting is that my query plan is identical to yours (with the clustered index scan), but the index scan accounts for 9% of the query cost and the sort the remaining 91%. I can only assume your table contains an enormous amount of data and/or your disks are very slow or located over a very slow network connection.

User · Answer

How is this possible  Without an index on the er101 upd date iso column how can a clustered index scan be used       An index is a B-Tree where each leaf node is pointing to a  bunch of rows  called  a  Page  in SQL internal terminology   That is when the index is a non-clustered index    Clustered index is a special case  in which the leaf nodes has the  bunch of rows   rather than pointing to them   that is why     1  There can be only one clustered index on the table    this also means the whole table is stored as the clustered index  that is why you started seeing index scan rather than a table scan   2  An operation that utilizes clustered index is generally faster than a non-clustered index  Read more at http   msdn microsoft com en-us library ms177443 aspx  For the problem you have  you should really consider adding this column to a index  as you said adding a new index  or a column to an existing index  increases INSERT UPDATE costs  But it might be possible to remove some underutilized index  or a column from an existing index  to replace with  er101 upd date iso    If index changes are not possible  i recommend adding a statistics on the column  it can fasten things up when the columns have some correlation with indexed columns  http   msdn microsoft com en-us library ms188038 aspx  BTW  You will get much more help if you can post the table schema of ER101 ACCT ORDER DTL  and the existing indices too     probably the query could be re-written to use some of them

[sql] Improve SQL Server query performance on large tables

The answer is

Lack of index

1. Use temporary tables

2. Common table expression -- CTE

3. Create views

Selecting all columns

Dirty reads

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Tags