Migliora le prestazioni delle query di SQL Server su tabelle di grandi dimensioni

Ho una tabella relativamente grande (attualmente 2 milioni di record) e vorrei sapere se è ansible migliorare le prestazioni per le query ad-hoc. La parola ad-hoc è la chiave qui. L’aggiunta di indici non è un’opzione (ci sono già indici sulle colonne che vengono interrogati più comunemente).

Esecuzione di una query semplice per restituire i 100 record aggiornati più recentemente:

select top 100 * from ER101_ACCT_ORDER_DTL order by er101_upd_date_iso desc 

Ci vogliono diversi minuti. Vedi il piano di esecuzione di seguito:

inserisci la descrizione dell'immagine qui

Ulteriori dettagli dalla scansione della tabella:

inserisci la descrizione dell'immagine qui

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

Il server è piuttosto potente (dalla memoria RAM da 48 GB, 24 processori core) con SQL Server 2008 R2 x64.

Aggiornare

Ho trovato questo codice per creare una tabella con 1.000.000 di record. Ho pensato di poter eseguire SELECT TOP 100 * FROM testEnvironment ORDER BY mailAddress DESC su alcuni server diversi per scoprire se le mie velocità di accesso al disco erano scadenti sul 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,aN,2)) + SUBSTRING(vowels,bN,1))) FROM Tally a CROSS JOIN Tally2 b CROSS APPLY (SELECT 'BCDFGHJKLMNPRSTVWZ SCSKKNSNSPSTBLCLFLGLPLSLBRCRDRFRGRPRTRVRSHSMGHCHPHRHWHBWCWSWTW') d(poss) CROSS APPLY (SELECT 'AEIOU') e(vowels)) SELECT IDENTITY(INT,1,1) AS ID, aN + bN 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 + bN + bN 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; 

Ma sui tre server di test la query ha funzionato quasi istantaneamente. Qualcuno può spiegarlo?

inserisci la descrizione dell'immagine qui

Aggiornamento 2

Grazie per i commenti, per favore, continuate a farli venire … mi hanno spinto a provare a cambiare l’indice della chiave primaria da non cluster a cluster con risultati piuttosto interessanti (e inaspettati?).

Non cluster:

inserisci la descrizione dell'immagine qui

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

clustered:

inserisci la descrizione dell'immagine qui

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

Com’è ansible? Senza un indice nella colonna er101_upd_date_iso come può essere utilizzato un indice di scansione clusterizzato?

Aggiornamento 3

Come richiesto, questo è lo script della tabella di creazione:

 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] 

Il tavolo ha una dimensione di 2,8 GB con dimensioni dell’indice pari a 3,9 GB.

Ci sono alcuni problemi con questa query (e questo vale per ogni query).

Mancanza di indice

La mancanza di indice sulla colonna er101_upd_date_iso è la cosa più importante come Oded ha già menzionato.

Senza l’indice corrispondente (che potrebbe causare la scansione della tabella) non è ansible eseguire query veloci su tabelle di grandi dimensioni.

Se non è ansible aggiungere indici (per vari motivi, tra cui non vi è alcun motivo per creare indici per una sola query ad hoc ), suggerirei alcune soluzioni alternative (che possono essere utilizzate per query ad-hoc):

1. Utilizzare tabelle temporanee

Crea una tabella temporanea su sottoinsieme (righe e colonne) di dati a cui sei interessato. La tabella temporanea dovrebbe essere molto più piccola della tabella di origine originale, può essere indicizzata facilmente (se necessario) e può memorizzare nella cache un sottoinsieme di dati a cui sei interessato.

Per creare una tabella temporanea puoi usare il codice (non testato) come:

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

Professionisti:

  • Facile da fare per qualsiasi sottoinsieme di dati.
  • Facile da gestire: è temporaneo e si tratta di un tavolo .
  • Non influisce sulle prestazioni generali del sistema come la view .
  • La tabella temporanea può essere indicizzata.
  • Non devi preoccupartene – è temporaneo :).

Contro:

  • È un’istantanea di dati, ma probabilmente è abbastanza buono per la maggior parte delle query ad hoc.

2. Espressione di tabella comune – CTE

Personalmente uso molto CTE con query ad-hoc: è molto utile build (e testare) un’interrogazione pezzo per pezzo.

Vedere l’esempio di seguito (la query che inizia con WITH ).

Professionisti:

  • Facile da build partendo da una grande vista e selezionando e filtrando ciò di cui hai veramente bisogno.
  • Facile da testare

Contro:

  • Alcune persone non amano le query CDE – CDE sembrano lunghe e difficili da capire.

3. Creare viste

Simile al precedente, ma crea viste anziché tabelle temporanee (se giochi spesso con le stesse query e hai la versione MS SQL che supporta le viste indicizzate.

Puoi creare viste o viste indicizzate sul sottoinsieme di dati che ti interessa ed eseguire query sulla vista, che dovrebbe contenere solo sottoinsiemi interessanti di dati molto più piccoli dell’intera tabella.

Professionisti:

  • Facile da fare.
  • È aggiornato con i dati di origine.

Contro:

  • Possibile solo per sottoinsieme definito di dati.
  • Potrebbe essere inefficiente per tabelle di grandi dimensioni con un alto tasso di aggiornamenti.
  • Non è così facile da gestire.
  • Può influire sulle prestazioni generali del sistema.
  • Non sono sicuro che le visualizzazioni indicizzate siano disponibili in ogni versione di MS SQL.

Selezione di tutte le colonne

L’esecuzione della query a stella ( SELECT * FROM ) sul grande tavolo non è una buona cosa …

Se hai colonne grandi (come le stringhe lunghe) ci vuole un sacco di tempo per leggerle da disco e passare per rete.

Proverei a sostituire * con i nomi delle colonne di cui hai veramente bisogno.

Oppure, se hai bisogno di tutte le colonne, prova a riscrivere la query a qualcosa di simile (usando l’espressione di dati comune ):

 ;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 

Letture sporche

L’ultima cosa che potrebbe velocizzare la query ad hoc è consentire letture sporche con suggerimento tabella WITH (NOLOCK) .

Invece di suggerimento è ansible impostare il livello di isolamento della transazione per leggere non limitato:

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

o impostare l’impostazione corretta di SQL Management Studio.

Suppongo che per le query ad hoc le letture sporche siano abbastanza buone.

Risposta semplice: NO. Non è ansible aiutare le query ad hoc su una tabella di 238 colonne con un fattore di riempimento del 50% sull’indice di cluster.

Risposta dettagliata:

Come ho affermato in altre risposte su questo argomento, il design dell’Indice è sia Arte che Scienza e ci sono così tanti fattori da considerare che ci sono poche, se non nessuna, regole dure e veloci. È necessario considerare: il volume delle operazioni DML rispetto a SELECTs, il sottosistema del disco, altri indici / trigger sulla tabella, la distribuzione dei dati all’interno della tabella, sono query che utilizzano le condizioni SARGable WHERE e molte altre cose che non ricordo nemmeno correttamente adesso.

Posso dire che non è ansible fornire aiuto per le domande su questo argomento senza una comprensione della tabella stessa, dei suoi indici, trigger ecc. Ora che hai pubblicato la definizione della tabella (ancora in attesa degli indici ma la definizione della tabella da sola punta a 99% del problema) Posso offrire alcuni suggerimenti.

Innanzitutto, se la definizione della tabella è accurata (238 colonne, fattore di riempimento del 50%), puoi praticamente ignorare il resto delle risposte / consigli qui ;-). Mi spiace essere poco politico qui, ma seriamente, è una caccia all’oca selvaggia senza conoscere le specifiche. E ora che vediamo la definizione della tabella diventa un po ‘più chiaro il motivo per cui una semplice query richiederebbe così tanto tempo, anche quando le query di test (Aggiornamento n. 1) sono state eseguite così rapidamente.

Il problema principale qui (e in molte situazioni di scarso rendimento) è la ctriggers modellizzazione dei dati. 238 colonne non è vietato, proprio come avere 999 indici non è proibito, ma generalmente non è nemmeno molto saggio.

raccomandazioni:

  1. Innanzitutto, questo tavolo ha davvero bisogno di essere rimodellato. Se questa è una tabella di data warehouse allora forse, ma in caso contrario, questi campi devono davvero essere suddivisi in più tabelle che possono avere tutti lo stesso PK. Avresti una tabella di record master e le tabelle figlio sono solo informazioni dipendenti basate su attributi comunemente associati e il PK di queste tabelle è lo stesso del PK della tabella master e quindi anche FK alla tabella principale. Ci sarà una relazione 1 a 1 tra il master e tutte le tabelle figlio.
  2. L’uso di ANSI_PADDING OFF è disturbante, per non dire incoerente all’interno della tabella a causa delle varie aggiunte di colonne nel tempo. Non sei sicuro di poterlo aggiustare ora, ma idealmente avresti sempre ANSI_PADDING ON , o per lo meno avere la stessa impostazione in tutte le istruzioni ALTER TABLE .
  3. Prendi in considerazione la possibilità di creare 2 ulteriori gruppi di file: tabelle e indici. È meglio non mettere le tue cose in PRIMARY dato che è dove SQL SERVER memorizza tutti i suoi dati e meta-dati sui tuoi oggetti. Crei il tuo indice di tabella e cluster (dato che questo è il dato per la tabella) su [Tables] e tutti gli indici non in cluster su [Indexes]
  4. Aumentare il fattore di riempimento dal 50%. Questo numero basso è probabilmente il motivo per cui lo spazio dell’indice è più grande del tuo spazio dati. La ricostruzione di un indice consente di ricreare le pagine di dati con un massimo di 4k (rispetto alla dimensione totale di 8 k) utilizzata per i dati, in modo che la tabella sia distribuita su un’ampia area.
  5. Se la maggior parte o tutte le query contengono “ER101_ORG_CODE” nella condizione WHERE , prendere in considerazione la possibilità di spostarlo nella colonna principale dell’indice cluster. Supponendo che sia usato più spesso di “ER101_ORD_NBR”. Se “ER101_ORD_NBR” viene usato più spesso, tienilo. Sembra, supponendo che i nomi dei campi significhi “OrganizationCode” e “OrderNumber”, che “OrgCode” sia un raggruppamento migliore che potrebbe avere più “OrderNumbers” al suo interno.
  6. Punto minore, ma se “ER101_ORG_CODE” è sempre di 2 caratteri, utilizzare CHAR(2) invece di VARCHAR(2) poiché salverà un byte nell’intestazione della riga che tiene traccia delle dimensioni variabili della larghezza e sum su milioni di righe.
  7. Come altri hanno menzionato, l’utilizzo di SELECT * danneggerà le prestazioni. Non solo perché richiede a SQL Server di restituire tutte le colonne e quindi è più probabile che esegua una scansione indice cluster indipendentemente dagli altri indici, ma richiede anche tempo a SQL Server per andare alla definizione della tabella e tradurre * in tutte le colonne nomi. Dovrebbe essere leggermente più veloce per specificare tutti i 238 nomi di colonna nell’elenco SELECT sebbene ciò non aiuti il ​​problema di Scansione. Ma hai mai davvero bisogno di tutte le 238 colonne allo stesso tempo?

In bocca al lupo!

AGGIORNARE
Per completezza alla domanda “come migliorare le prestazioni su una tabella di grandi dimensioni per query ad hoc”, è opportuno notare che, sebbene non sia di aiuto in questo caso specifico, SE qualcuno sta utilizzando SQL Server 2012 (o più recente quando quel momento arriva) e SE la tabella non viene aggiornata, quindi utilizzare Columnstore Indexes è un’opzione. Per maggiori dettagli su questa nuova funzionalità, guarda qui: http://msdn.microsoft.com/en-us/library/gg492088.aspx (credo che questi siano stati fatti per essere aggiornabili a partire da SQL Server 2014).

AGGIORNAMENTO 2
Ulteriori considerazioni sono:

  • Abilita la compressione sull’indice clusterizzato. Questa opzione è diventata disponibile in SQL Server 2008, ma come funzionalità esclusiva di Enterprise Edition. Tuttavia, a partire da SQL Server 2016 SP1 , la compressione dei dati è stata resa disponibile in tutte le edizioni ! Si prega di consultare la pagina MSDN per la compressione dei dati per i dettagli sulla compressione di righe e pagine.
  • Se non è ansible utilizzare la compressione dati o se non fornisce molti vantaggi per una determinata tabella, IF se si dispone di una colonna di tipo a lunghezza fissa ( INT , BIGINT , TINYINT , SMALLINT , CHAR , NCHAR , BINARY , DATETIME , SMALLDATETIME , MONEY , ecc. E ben oltre il 50% delle righe sono NULL , quindi prendere in considerazione l’triggerszione dell’opzione SPARSE che è diventata disponibile in SQL Server 2008. Per i dettagli vedere la pagina MSDN per Utilizzare le colonne sparse .

Stai ottenendo una scansione della tabella lì, nel senso che non hai un indice definito su er101_upd_date_iso , o se quella colonna fa parte di un indice esistente, l’indice non può essere utilizzato (probabilmente non è la colonna dell’indicatore primario).

L’aggiunta di indici mancanti aiuterà le prestazioni senza fine.

ci sono già indici sulle colonne che vengono interrogati più comunemente

Ciò non significa che siano usati in questa query (e probabilmente non lo sono).

Suggerisco di leggere Trovare le cause di scarse prestazioni in SQL Server di Gail Shaw, parte 1 e parte 2 .

La domanda specifica in particolare che le prestazioni devono essere migliorate per le query ad-hoc e che gli indici non possono essere aggiunti. Quindi, prendendo quello per il valore nominale, cosa si può fare per migliorare le prestazioni su qualsiasi tavolo?

Poiché stiamo prendendo in considerazione query ad hoc, la clausola WHERE e la clausola ORDER BY possono contenere qualsiasi combinazione di colonne. Ciò significa che quasi indipendentemente da quali indici sono posizionati sulla tabella ci saranno alcune query che richiedono una scansione della tabella, come visto sopra nel piano di query di una query con scarse prestazioni.

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.

modificare

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.

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?

Com’è ansible? 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.

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

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)

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 🙂