uso usar tipos rapidas plan optimizar optimización mas ejemplos ejecución consultas consulta con sql sql-server sql-server-2008

usar - Mejore el rendimiento de consultas de SQL Server en tablas grandes



usar indices en consulta sql (9)

Tengo una tabla relativamente grande (actualmente 2 millones de registros) y me gustaría saber si es posible mejorar el rendimiento de las consultas ad-hoc. La palabra ad-hoc es la clave aquí. Agregar índices no es una opción (ya hay índices en las columnas que se consultan más comúnmente).

Ejecutando una consulta simple para devolver los 100 registros actualizados más recientemente:

select top 100 * from ER101_ACCT_ORDER_DTL order by er101_upd_date_iso desc

Toma varios minutos. Vea el plan de ejecución a continuación:

Detalles adicionales del escaneo de la tabla:

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

El servidor es bastante potente (desde la memoria RAM de 48GB, 24 procesadores centrales) ejecutando el servidor sql 2008 r2 x64.

Actualizar

Encontré este código para crear una tabla con 1,000,000 de registros. Pensé que podría ejecutar SELECT TOP 100 * FROM testEnvironment ORDER BY mailAddress DESC en algunos servidores diferentes para averiguar si las velocidades de acceso a mi disco eran malas en el servidor.

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;

Pero en los tres servidores de prueba la consulta se ejecutó casi instantáneamente. ¿Alguien puede explicar esto?

Actualización 2

Gracias por los comentarios, por favor, sigan presentándolos ... me llevaron a intentar cambiar el índice de clave principal de no agrupado a agrupado con resultados bastante interesantes (¿e inesperados?).

No agrupado:

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

Agrupado

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

¿Cómo es esto posible? Sin un índice en la columna er101_upd_date_iso, ¿cómo se puede usar un análisis de índice agrupado?

Actualización 3

Según lo solicitado, este es el script de crear tabla:

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 tabla tiene 2,8 GB de tamaño, con un tamaño de índice de 3,9 GB.

¿Cómo es esto posible? 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.

Hay algunos problemas con esta consulta (y esto se aplica a todas las consultas).

Falta de índice

La falta de índice en la columna er101_upd_date_iso es lo más importante que Oded ya ha mencionado.

Sin el índice coincidente (cuya ausencia podría causar escaneo de tabla) no hay posibilidad de ejecutar consultas rápidas en tablas grandes.

Si no puede agregar índices (por diversas razones, incluido el hecho de que no tiene sentido crear un índice para una sola consulta ad hoc ), sugeriría algunas soluciones (que se pueden usar para consultas ad-hoc):

1. Usa tablas temporales

Cree una tabla temporal en subconjuntos (filas y columnas) de los datos que le interesan. La tabla temporal debe ser mucho más pequeña que la tabla fuente original, puede indexarse ​​fácilmente (si es necesario) y puede almacenar en caché subconjuntos de datos que le interesan.

Para crear una tabla temporal puede usar código (no probado) como:

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

Pros:

  • Fácil de hacer para cualquier subconjunto de datos.
  • Fácil de administrar: es temporal y es una mesa .
  • No afecta el rendimiento general del sistema como la view .
  • La tabla temporal puede ser indexada.
  • No tiene que preocuparse por eso, es temporal :).

Contras:

  • Es una instantánea de los datos, pero probablemente esto sea lo suficientemente bueno para la mayoría de las consultas ad-hoc.

2. Expresión de tabla común - CTE

Personalmente utilizo CTE mucho con consultas ad-hoc - es de gran ayuda construir (y probar) una consulta pieza por pieza.

Vea el siguiente ejemplo (la consulta que comienza con WITH ).

Pros:

  • Fácil de construir a partir de una gran vista y luego seleccionar y filtrar lo que realmente necesita.
  • Fácil de probar

Contras:

  • Algunas personas no les gusta CDE - Las consultas de CDE parecen ser largas y difíciles de entender.

3. Crea vistas

Similar a la anterior, pero cree vistas en lugar de tablas temporales (si juega a menudo con las mismas consultas y tiene una versión de MS SQL que admite vistas indizadas).

Puede crear vistas o vistas indizadas en un subconjunto de datos que le interesan y ejecutar consultas en la vista, que solo debe contener un subconjunto de datos interesante, mucho más pequeño que toda la tabla.

Pros:

  • Fácil de hacer.
  • Está actualizado con los datos de origen.

Contras:

  • Posible solo para un subconjunto de datos definido.
  • Podría ser ineficiente para tablas grandes con alta tasa de actualizaciones.
  • No es tan fácil de administrar.
  • Puede afectar el rendimiento general del sistema.
  • No estoy seguro de que las vistas indizadas estén disponibles en todas las versiones de MS SQL.

Seleccionando todas las columnas

La consulta de estrella corriendo ( SELECT * FROM ) en la mesa grande no es algo bueno ...

Si tiene columnas grandes (como cadenas largas) lleva mucho tiempo leerlas desde el disco y pasar por la red.

Intentaría reemplazar * con los nombres de columna que realmente necesita.

O bien, si necesita todas las columnas, intente reescribir la consulta a algo similar (utilizando una expresión de datos común ):

;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

Sucias lecturas

Lo último que podría acelerar la consulta ad hoc es permitir lecturas sucias con la sugerencia de tabla WITH (NOLOCK) .

En lugar de una pista, puede configurar el nivel de aislamiento de transacción para leer sin compromiso:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

o establecer la configuración correcta de SQL Management Studio.

Supongo que para las consultas ad-hoc leer sucio es lo suficientemente bueno.

Obtendrá un examen de tabla allí, lo que significa que no tiene un índice definido en er101_upd_date_iso , o si esa columna es parte de un índice existente, no se puede usar el índice (posiblemente no sea la columna del indexador principal).

Agregar los índices faltantes ayudará al rendimiento sin fin.

ya hay índices en las columnas que se consultan más comúnmente

Eso no significa que se usen en esta consulta (y probablemente no lo estén).

Sugiero leer Find the Causes of Poor Performance en SQL Server por Gail Shaw, parte 1 y parte 2 .

Respuesta simple: NO. No puede ayudar con las consultas ad hoc en una tabla de columnas 238 con un factor de relleno del 50% en el índice agrupado.

Respuesta detallada:

Como ya lo indiqué en otras respuestas sobre este tema, el diseño del índice es tanto arte como ciencia y hay tantos factores para considerar que existen pocas, si las hay, reglas estrictas. Debe tener en cuenta: el volumen de operaciones DML frente a SELECT, el subsistema de disco, otros índices / disparadores en la tabla, la distribución de datos dentro de la tabla, son consultas usando condiciones SARWable WHERE, y varias otras cosas que ni siquiera puedo recordar bien ahora.

Puedo decir que no se puede dar ayuda para las preguntas sobre este tema sin una comprensión de la Tabla en sí, sus índices, desencadenantes, etc. Ahora que ha publicado la definición de la tabla (aún esperando en los Índices pero la definición de la Tabla solo apunta a 99% del problema) Puedo ofrecer algunas sugerencias.

Primero, si la definición de la tabla es precisa (238 columnas, 50% factor de relleno), entonces puede ignorar el resto de las respuestas aquí ;-). Perdón por ser menos que político aquí, pero en serio, es una persecución sin conocer los detalles. Y ahora que vemos la definición de tabla, se vuelve bastante más claro en cuanto a por qué una consulta simple llevaría tanto tiempo, incluso cuando las consultas de prueba (Actualización n. ° 1) se ejecutaron tan rápido.

El principal problema aquí (y en muchas situaciones de bajo rendimiento) es el mal modelado de datos. 238 columnas no están prohibidas, al igual que tener índices de 999 no está prohibido, pero tampoco suele ser muy inteligente.

Recomendaciones:

  1. Primero, esta mesa realmente necesita ser remodelada. Si esta es una tabla de almacén de datos, entonces tal vez, pero si no, estos campos realmente deben dividirse en varias tablas que pueden tener el mismo PK. Tendría una tabla de registro maestro y las tablas secundarias son solo información dependiente basada en atributos comúnmente asociados y la PK de esas tablas es la misma que la PK de la tabla maestra y, por lo tanto, también FK a la tabla maestra. Habrá una relación de 1 a 1 entre las tablas maestra y todas las secundarias.
  2. El uso de ANSI_PADDING OFF es inquietante, por no mencionar inconsistente dentro de la tabla debido a las diversas adiciones de columna a lo largo del tiempo. No estoy seguro de si puede solucionarlo ahora, pero lo ideal es que siempre tenga ANSI_PADDING ON o, al menos, tenga la misma configuración en todas las instrucciones ALTER TABLE .
  3. Considere la posibilidad de crear 2 grupos de archivos adicionales: tablas e índices. Lo mejor es no poner sus cosas en PRIMARY ya que es donde SQL SERVER almacena todos sus datos y metadatos sobre sus objetos. Crea su Tabla e Índice agrupado (como son los datos para la tabla) en [Tables] y todos los índices no agrupados en [Indexes]
  4. Aumenta el factor de relleno del 50%. Este número bajo es probable por qué su espacio de índice es más grande que su espacio de datos. Al hacer una reconstrucción de índice, se recrearán las páginas de datos con un máximo de 4k (del tamaño total de 8 k de página) utilizado para sus datos, de modo que su tabla se distribuya en un área amplia.
  5. Si la mayoría o todas las consultas tienen "ER101_ORG_CODE" en la condición WHERE , entonces considere mover eso a la columna principal del índice agrupado. Suponiendo que se usa con más frecuencia que "ER101_ORD_NBR". Si "ER101_ORD_NBR" se usa con más frecuencia, guárdalo. Parece que, suponiendo que los nombres de campo significan "Código de organización" y "Número de orden", ese "Código de organización" es una agrupación mejor que podría tener varios "Números de orden" dentro de ella.
  6. Punto menor, pero si "ER101_ORG_CODE" es siempre de 2 caracteres, entonces use CHAR(2) lugar de VARCHAR(2) ya que guardará un byte en el encabezado de la fila que rastrea los tamaños de ancho variable y suma más de millones de filas.
  7. Como han mencionado otros aquí, usar SELECT * perjudicará el rendimiento. No solo porque requiere que SQL Server devuelva todas las columnas y, por lo tanto, sea más probable que realice un Análisis de índice agrupado independientemente de sus otros índices, sino que también le lleva tiempo al SQL Server ir a la definición de tabla y traducir * a toda la columna nombres. Debería ser un poco más rápido especificar los 238 nombres de columna en la lista SELECT aunque eso no ayudará al problema de Escaneo. Pero, ¿de verdad necesitas las 238 columnas al mismo tiempo?

¡Buena suerte!

ACTUALIZAR
En aras de completar la pregunta "cómo mejorar el rendimiento en una tabla grande para consultas ad-hoc", se debe tener en cuenta que, si bien no será útil para este caso específico, SI alguien está usando SQL Server 2012 (o más nuevo cuando ese momento llega) y SI la tabla no se está actualizando, entonces usar Columnstore Indexes es una opción. Para obtener más detalles sobre esa nueva característica, mira aquí: http://msdn.microsoft.com/en-us/library/gg492088.aspx (creo que estos se hicieron para ser actualizable a partir de SQL Server 2014).

ACTUALIZACIÓN 2
Consideraciones adicionales son:

  • Habilite la compresión en el índice agrupado. Esta opción estuvo disponible en SQL Server 2008, pero como una función exclusiva de Enterprise Edition. Sin embargo, a partir de SQL Server 2016 SP1 , ¡Data Compression estuvo disponible en todas las ediciones ! Consulte la página de MSDN para Compresión de datos para obtener detalles sobre la Compresión de filas y páginas.
  • Si no puede usar Compresión de datos, o si no proporcionará muchos beneficios para una tabla en particular, entonces SI tiene una columna de tipo de longitud fija ( INT , BIGINT , SMALLINT , SMALLINT , CHAR , NCHAR , BINARY , DATETIME , SMALLDATETIME , MONEY , etc.) y más del 50% de las filas son NULL , luego considere habilitar la opción SPARSE que estuvo disponible en SQL Server 2008. Consulte la página de MSDN para Usar columnas dispersas para obtener más información.

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?

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

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)

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

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.

Editar

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.