Améliorer les performances des requêtes SQL Server sur les grandes tables

Question

J'ai une table relativement grande (actuellement 2 millions d'enregistrements) et j'aimerais savoir s'il est possible d'améliorer les performances pour les requêtes ad-hoc. Le mot ad-hoc étant la clé ici. L'ajout d'index n'est pas une option (il existe déjà des index sur les colonnes qui sont le plus souvent interrogées).

Exécution d'une simple requête pour renvoyer les 100 enregistrements les plus récemment mis à jour :

select top 100 * from ER101_ACCT_ORDER_DTL order by er101_upd_date_iso desc

Cela prend plusieurs minutes. Voir le plan d'exécution ci-dessous :

Détail supplémentaire du scan de la table :

SQL Server Execution Times:
  CPU time = 3945 ms,  elapsed time = 148524 ms.

Le serveur est assez puissant (de mémoire, 48 Go de RAM, processeur 24 cœurs) et exécute sql server 2008 r2 x64.

Mise à jour

J'ai trouvé ce code pour créer une table avec 1 000 000 d'enregistrements. J'ai pensé que je pourrais ensuite exécuter SELECT TOP 100 * FROM testEnvironment ORDER BY mailAddress DESC sur quelques serveurs différents pour savoir si mes vitesses d'accès au disque étaient mauvaises sur le serveur.

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;

Mais sur les trois serveurs de test, la requête s'est exécutée presque instantanément. Quelqu'un peut-il expliquer ce phénomène ?

Mise à jour 2

Merci pour les commentaires - continuez à les envoyer... ils m'ont conduit à essayer de changer l'index de la clé primaire de non-classé à classé avec des résultats plutôt intéressants (et inattendus ?).

Non groupé :

SQL Server Execution Times:
  CPU time = 3634 ms,  elapsed time = 154179 ms.

En grappe :

SQL Server Execution Times:
  CPU time = 2650 ms,  elapsed time = 52177 ms.

Comment cela est-il possible ? Sans un index sur la colonne er101_upd_date_iso, comment peut-on utiliser un scan d'index clusterisé ?

Mise à jour 3

Comme demandé - c'est la table de création 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]

La taille de la table est de 2,8 Go et celle de l'index est de 3,9 Go.

Answer 1

Réponse simple : NON. Vous ne pouvez pas aider les requêtes ad hoc sur une table à 238 colonnes avec un facteur de remplissage de 50 % sur l'index en grappe.

Réponse détaillée :

Comme je l'ai dit dans d'autres réponses à ce sujet, la conception d'un index est à la fois un art et une science et il y a tellement de facteurs à prendre en compte qu'il y a peu, voire aucune, règle absolue. Vous devez prendre en compte : le volume des opérations DML par rapport aux SELECT, le sous-système de disque, les autres index / déclencheurs sur la table, la distribution des données dans la table, les requêtes utilisant des conditions WHERE SARGable, et plusieurs autres choses dont je ne peux même pas me souvenir maintenant.

Je peux dire qu'aucune aide ne peut être apportée aux questions sur ce sujet sans une compréhension de la table elle-même, de ses index, de ses déclencheurs, etc. Maintenant que vous avez affiché la définition de la table (j'attends toujours les index, mais la définition de la table à elle seule indique 99 % du problème), je peux vous faire quelques suggestions.

Tout d'abord, si la définition du tableau est exacte (238 colonnes, facteur de remplissage de 50 %), vous pouvez ignorer le reste des réponses/conseils ici ;-). Désolé d'être moins que politique ici, mais sérieusement, c'est une chasse à l'oie sauvage sans connaître les détails. Et maintenant que nous voyons la définition de la table, nous comprenons mieux pourquoi une simple requête prend autant de temps, même si les requêtes de test (mise à jour n° 1) s'exécutent rapidement.

Le principal problème ici (et dans de nombreuses situations de mauvaises performances) est une mauvaise modélisation des données. 238 colonnes n'est pas interdit tout comme avoir 999 index n'est pas interdit, mais ce n'est généralement pas très judicieux.

Recommandations :

1. D'abord, cette table a vraiment besoin d'être remodelée. S'il s'agit d'une table d'entrepôt de données, alors peut-être, mais si ce n'est pas le cas, ces champs doivent vraiment être divisés en plusieurs tables qui peuvent toutes avoir le même PK. Vous auriez une table d'enregistrement maître et les tables enfants sont juste des informations dépendantes basées sur des attributs communément associés et le PK de ces tables est le même que le PK de la table maître et donc aussi FK à la table maître. Il y aura une relation 1 à 1 entre la table principale et toutes les tables enfant.
2. L'utilisation de ANSI_PADDING OFF est dérangeant, sans parler des incohérences au sein de la table en raison des divers ajouts de colonnes au fil du temps. Je ne sais pas si vous pouvez corriger cela maintenant, mais idéalement, vous devriez toujours avoir ANSI_PADDING ON ou, à tout le moins, avoir le même paramètre dans tous les pays de l'UE. ALTER TABLE déclarations.
3. Envisagez de créer 2 groupes de fichiers supplémentaires : Tables et Index. Il est préférable de ne pas mettre vos affaires dans PRIMARY car c'est là que SQL SERVER stocke toutes ses données et méta-données sur vos objets. Vous créez votre table et votre index clusterisé (car il s'agit des données de la table) sur le site de [Tables] et tous les index Non-Clustered sur [Indexes]
4. Augmentez le facteur de remplissage de 50%. Ce chiffre faible est probablement la raison pour laquelle votre espace d'index est plus grand que votre espace de données. La reconstruction de l'index recréera les pages de données avec un maximum de 4k (sur les 8k de la taille totale de la page) utilisées pour vos données, de sorte que votre table est répartie sur une large zone.
5. Si la plupart ou toutes les requêtes ont "ER101_ORG_CODE" dans le champ WHERE envisagez alors de la déplacer vers la première colonne de l'index groupé. En supposant qu'elle soit utilisée plus souvent que "ER101_ORD_NBR". Si "ER101_ORD_NBR" est utilisé plus souvent, gardez-le. Il semble simplement, en supposant que les noms de champ signifient "OrganizationCode" et "OrderNumber", que "OrgCode" est un meilleur regroupement qui pourrait avoir plusieurs "OrderNumbers" en son sein.
6. Un point mineur, mais si "ER101_ORG_CODE" est toujours composé de 2 caractères, alors utilisez CHAR(2) au lieu de VARCHAR(2) car cela permet d'économiser un octet dans l'en-tête de la ligne, ce qui permet de suivre des tailles de largeur variables et de les additionner sur des millions de lignes.
7. Comme d'autres l'ont mentionné ici, utiliser SELECT * va nuire aux performances. Non seulement parce qu'il faut que SQL Server renvoie toutes les colonnes et qu'il est donc plus susceptible d'effectuer un Clustered Index Scan sans tenir compte de vos autres index, mais aussi parce que SQL Server prend du temps pour aller dans la définition de la table et traduire * dans tous les noms de colonnes. Cela devrait être légèrement plus rapide pour spécifier l'ensemble des 238 noms de colonnes dans le fichier SELECT mais cela n'aidera pas le problème du scanner. Mais avez-vous jamais vraiment besoin des 238 colonnes en même temps ?

Bonne chance !

UPDATE
Pour compléter la question "comment améliorer les performances d'une grande table pour les requêtes ad hoc", il convient de noter que, bien que cela ne soit pas utile dans ce cas précis, SI quelqu'un utilise SQL Server 2012 (ou une version plus récente le moment venu) et SI la table n'est pas mise à jour, l'utilisation des Columnstore Indexes est une option. Pour plus de détails sur cette nouvelle fonctionnalité, voir ici : http://msdn.microsoft.com/en-us/library/gg492088.aspx (Je crois qu'ils ont été conçus pour être mis à jour à partir de SQL Server 2014).

MISE À JOUR 2
Des considérations supplémentaires sont à prendre en compte :

• Activez la compression sur l'index en grappe. Cette option est disponible depuis SQL Server 2008, mais uniquement dans l'édition Enterprise. Cependant, à partir de SQL Server 2016 SP1 La compression des données est désormais disponible. dans toutes les éditions ! Veuillez consulter la page MSDN pour Compression des données pour plus de détails sur la compression des rangées et des pages.
• Si vous ne pouvez pas utiliser la compression de données, ou si elle ne présente pas beaucoup d'avantages pour une table particulière, alors SI vous avez une colonne d'un type de longueur fixe ( INT , BIGINT , TINYINT , SMALLINT , CHAR , NCHAR , BINARY , DATETIME , SMALLDATETIME , MONEY etc.) et plus de 50 % des lignes sont NULL alors envisagez d'activer l'option SPARSE qui est devenue disponible dans SQL Server 2008. Veuillez consulter la page MSDN pour Utiliser des colonnes éparses pour les détails.

Answer 2

Il y a quelques problèmes avec cette requête (et cela s'applique à toutes les requêtes).

Manque d'indice

Absence d'indice sur er101_upd_date_iso La colonne est la chose la plus importante car Oded a déjà mentionné.

Sans index correspondant (dont l'absence pourrait entraîner un balayage de la table), il n'y a aucune chance d'exécuter des requêtes rapides sur de grandes tables.

Si vous ne pouvez pas ajouter d'index (pour diverses raisons, dont les suivantes il est inutile de créer un index pour une seule requête ad-hoc ) Je vous propose quelques solutions de contournement (qui peuvent être utilisées pour des requêtes ad-hoc) :

1. Utiliser des tables temporaires

Créez une table temporaire sur un sous-ensemble (lignes et colonnes) des données qui vous intéressent. La table temporaire doit être beaucoup plus petite que la table source originale, peut être indexée facilement (si nécessaire) et peut en cachette sous-ensemble de données qui vous intéresse.

Pour créer une table temporaire vous pouvez utiliser un code (non testé) comme :

-- 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 > 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

Pour :

• Facile à faire pour tout sous-ensemble de données.
• Facile à gérer - c'est temporaire et c'est tableau .
• N'affecte pas les performances globales du système comme view .
• La table temporaire peut être indexée.
• Vous n'avez pas à vous en soucier, c'est temporaire :).

Cons :

• Il s'agit d'un instantané des données, mais c'est probablement suffisant pour la plupart des requêtes ad hoc.

2. Expression de table commune -- CTE

Personnellement, j'utilise CTE beaucoup avec les requêtes ad-hoc -- cela aide beaucoup à construire (et tester) une requête pièce par pièce.

Voir l'exemple ci-dessous (la requête commençant par WITH ).

Pour :

• Facile à construire à partir de grande vue puis de sélectionner et de filtrer ce dont vous avez vraiment besoin.
• Facile à tester.

Cons :

• Certaines personnes n'aiment pas le CDE. Les requêtes CDE semblent longues et difficiles à comprendre.

3. Créer des vues

Similaire à ce qui précède, mais créez des vues au lieu de tables temporaires (si vous jouez souvent avec les mêmes requêtes et que vous avez une version de MS SQL qui supporte les vues indexées.

Vous pouvez créer des vues ou vues indexées sur un sous-ensemble de données qui vous intéressent et exécuter des requêtes sur la vue -- qui ne devrait contenir qu'un sous-ensemble intéressant de données, beaucoup plus petit que la table entière.

Pour :

• Facile à faire.
• Il est à jour avec les données sources.

Cons :

• Possible uniquement pour un sous-ensemble défini de données.
• Pourrait être inefficace pour les grandes tables avec un taux élevé de mises à jour.
• Pas si facile à gérer.
• Peut affecter les performances globales du système.
• Je ne suis pas sûr que les vues indexées soient disponibles dans toutes les versions de MS SQL.

Sélection de toutes les colonnes

Running recherche d'étoiles ( SELECT * FROM ) sur une grande table n'est pas une bonne chose...

Si vous avez de grandes colonnes (comme de longues chaînes de caractères), il faut beaucoup de temps pour les lire sur le disque et les transmettre par le réseau. et les faire passer par le réseau.

J'essaierais de remplacer * avec des noms de colonnes dont vous avez vraiment besoin.

Ou, si vous avez besoin de toutes les colonnes, essayez de réécrire la requête en quelque chose comme (en utilisant expression commune des données ) :

;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 

Des lectures sales

La dernière chose qui pourrait accélérer l'interrogation ad hoc est de permettre livres sales avec indice de table WITH (NOLOCK) .

Au lieu de l'indice, vous pouvez définir le niveau d'isolement des transactions à lire sans engagement :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

ou définir les paramètres appropriés de SQL Management Studio.

Je suppose que pour les requêtes ad-hoc livres sales est suffisant.

Answer 3

Vous recevez un balayage des tables là, ce qui signifie que vous n'ont pas d'index défini sur er101_upd_date_iso ou si cette colonne fait partie d'un index existant, l'index ne peut pas être utilisé (il se peut que ce ne soit pas la colonne de l'indexeur primaire).

L'ajout des index manquants améliorera considérablement les performances.

il y a déjà des index sur les colonnes qui sont interrogées le plus souvent.

Cela ne signifie pas qu'ils sont utilisés dans cette requête (et ils ne le sont probablement pas).

Je vous suggère de lire Finding the Causes of Poor Performance in SQL Server de Gail Shaw, partie 1 et partie 2 .

Answer 4

La question précise que les performances doivent être améliorées pour ad-hoc et que les index ne peuvent pas être ajoutés. Si l'on prend cela au pied de la lettre, que peut-on faire pour améliorer les performances d'une table ?

Comme nous envisageons des requêtes ad hoc, la clause WHERE et la clause ORDER BY peuvent contenir n'importe quelle combinaison de colonnes. Cela signifie que, quels que soient les index placés sur la table, certaines requêtes nécessiteront un balayage de la table, comme on le voit ci-dessus dans le plan de requête d'une requête peu performante.

En tenant compte de cela, supposons qu'il n'y a aucun index sur la table, à part un index clusterisé sur la clé primaire. Voyons maintenant quelles sont les options dont nous disposons pour maximiser les performances.

• Défragmenter la table

  Tant que nous avons un index clusterisé, nous pouvons défragmenter la table en utilisant DBCC INDEXDEFRAG (déprécié) ou de préférence ALTER INDEX . Cela minimisera le nombre de lectures de disque nécessaires pour analyser la table et améliorera la vitesse.

• Utilisez les disques les plus rapides possibles. Vous ne dites pas quels disques vous utilisez mais si vous pouvez utiliser des SSD.

• Optimiser tempdb. Mettez tempdb sur les disques les plus rapides possibles, encore une fois des SSD. Voir ceci Article SO et ceci Article de RedGate .

• Comme indiqué dans d'autres réponses, l'utilisation d'une requête plus sélective renverra moins de données, et devrait donc être plus rapide.

Voyons maintenant ce que nous pouvons faire si nous sommes autorisés à ajouter des index.

Si nous n'étaient pas Si nous parlons de requêtes ad-hoc, nous ajouterons des index spécifiquement pour l'ensemble limité de requêtes exécutées sur la table. Puisque nous parlons de ad-hoc Qu'est-ce qui peut être fait pour améliorer la rapidité des requêtes ? le plus du temps ?

• Ajoutez un index de colonne unique à chaque colonne. Cela devrait donner au moins quelque chose à SQL Server pour améliorer la vitesse pour la majorité des requêtes, mais ce ne sera pas optimal.
• Ajoutez des index spécifiques pour les requêtes les plus courantes afin qu'elles soient optimisées.
• Ajoutez des index spécifiques supplémentaires si nécessaire en surveillant les requêtes peu performantes.

Modifier

J'ai effectué quelques tests sur une "grande" table de 22 millions de lignes. Mon tableau ne comporte que six colonnes mais contient 4 Go de données. Ma machine est un ordinateur de bureau respectable doté de 8 Go de RAM, d'un processeur quadri-cœur et d'un seul SSD Agility 3.

J'ai supprimé tous les index, sauf la clé primaire de la colonne Id.

Une requête similaire au problème donné dans la question prend 5 secondes si le serveur SQL est redémarré en premier et 3 secondes ensuite. Le conseiller en optimisation de base de données recommande évidemment l'ajout d'un index pour améliorer cette requête, avec une amélioration estimée à > 99%. L'ajout d'un index se traduit par un temps de requête effectivement nul.

Ce qui est également intéressant, c'est que mon plan de requête est identique au vôtre (avec le balayage d'index en grappe), mais le balayage d'index représente 9 % du coût de la requête et le tri les 91 % restants. Je ne peux que supposer que votre table contient une énorme quantité de données et/ou que vos disques sont très lents ou situés sur une connexion réseau très lente.

Answer 5

Même si vous avez des index sur certaines colonnes qui sont utilisées dans certaines requêtes, le fait que votre requête "ad hoc" provoque un balayage de table montre que vous n'avez pas suffisamment d'index pour permettre à cette requête de se dérouler efficacement.

Pour les plages de dates en particulier, il est difficile d'ajouter de bons index.

En regardant votre requête, la base de données doit trier tous les enregistrements par la colonne sélectionnée pour pouvoir retourner les n premiers enregistrements.

La base de données effectue-t-elle également un balayage complet de la table sans la clause order by ? La table dispose-t-elle d'une clé primaire ? Sans clé primaire, la base de données devra travailler davantage pour effectuer le tri ?