InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 05:54:50 GMT20http://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxI take it you didn't read the article I referenced, as it shows how to save and attach the graphical plan.Also, please post SQL 2008 questions in the SQL 2008 forums.Tue, 12 Feb 2013 06:54:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxHi, my issue got fixed with the help of excution plan. i was missing required non-clustered index on top of my table. ThanksTue, 12 Feb 2013 05:19:51 GMTp.panneerselvamhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspx[quote][b]p.panneerselvam (2/12/2013)[/b][hr]Execution plan Costselect 0%Parallelism(Gather Streams) 0%Sort(Distinct Sort) 0%Parallelism(Repartition Streams) 0%Hash Match(Partial Aggregate) 3%Compute Scalar 0%Clustered Index Scan[tblGLJnlEntrySum].tblGLJnlEntrySum_PK 97% Inaddition i am getting message like below/*Missing Index Details The Query Processor estimates that implementing the following index could improve the query cost by 97.4146%.*//*CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]ON [dbo].[tblGLJnlEntrySum] ([DivCode])INCLUDE ([glPeriodYear],[glPeriodMonth])*/so is that means that i have to create suc index mentioned above?[/quote]The suggested index should improve your query yes. At the moment, SQL Server is having to scan the entire table looking for records where [b]divCode = @vsDivCode[/b]. With the new index and included columns the scan should become an index seek on the new indexed column.Tue, 12 Feb 2013 05:06:30 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxExecution plan Costselect 0%Parallelism(Gather Streams) 0%Sort(Distinct Sort) 0%Parallelism(Repartition Streams) 0%Hash Match(Partial Aggregate) 3%Compute Scalar 0%Clustered Index Scan[tblGLJnlEntrySum].tblGLJnlEntrySum_PK 97% Inaddition i am getting message like below/*Missing Index Details The Query Processor estimates that implementing the following index could improve the query cost by 97.4146%.*//*CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]ON [dbo].[tblGLJnlEntrySum] ([DivCode])INCLUDE ([glPeriodYear],[glPeriodMonth])*/so is that means that i have to create suc index mentioned above?Tue, 12 Feb 2013 04:55:18 GMTp.panneerselvamhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxAlso what other indexes do you have on the tables asside from the Primary Keys?Tue, 12 Feb 2013 03:42:19 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxExecution plan please.Tue, 12 Feb 2013 03:13:45 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxTable Creation/****** Object: Table [dbo].[tblGLJnlEntrySum] Script Date: 02/12/2013 04:55:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tblGLJnlEntrySum]( [BatchID] [bigint] NOT NULL, [RecID] [bigint] NOT NULL, [GLBatchID] [bigint] NOT NULL, [GLRecID] [bigint] IDENTITY(1,1) NOT NULL, [DivCode] [varchar](3) NOT NULL, [DivName] [varchar](50) NOT NULL, [CurrencyID] [varchar](3) NOT NULL, [GLAcctNbrP1] [varchar](30) NOT NULL, [GLAcctNbrP2] [varchar](30) NULL, [GLAcctNbrP3] [varchar](30) NULL, [GLAcctNbrP4] [varchar](30) NULL, [GLAcctNbrP5] [varchar](30) NULL, [GrpGLAcctNbr] [varchar](255) NOT NULL, [GrpGLAcctType] [varchar](2) NULL, [AcctPeriod] [varchar](2) NOT NULL, [AcctYear] [varchar](4) NOT NULL, [AcctBalance] [money] NULL, [ProcessedDate] [datetime] NULL, [ProcessedFlag] [bit] NOT NULL, [GPProcessID] [bigint] NULL, [GPBatchID] [char](15) NULL, [GPErrorFlag] [int] NULL, [GPDatabase] [varchar](60) NULL, [CreatedDate] [datetime] NOT NULL, [CreatedUser] [varchar](255) NOT NULL, [UpdatedDate] [datetime] NULL, [UpdatedUser] [varchar](255) NULL, [GLAcctDescP1] [varchar](60) NULL, [GLAcctDescP2] [varchar](60) NULL, [GLAcctDescP3] [varchar](60) NULL, [GLAcctDescP4] [varchar](60) NULL, [GLAcctDescP5] [varchar](60) NULL, [GLAcctDesc] [varchar](255) NULL, [glPeriodYear] [int] NULL, [glPeriodMonth] [int] NULL, [glEntityCode] [varchar](50) NULL, [glEntityDesc] [varchar](100) NULL, [glAcctCode] [varchar](50) NULL, [glSubEntityCode] [varchar](50) NULL, [glSubEntityDesc] [varchar](100) NULL, [glAcctTypeCode] [varchar](50) NULL, [glAcctTypeDesc] [varchar](100) NULL, [flexCode01] [varchar](50) NULL, [flexDesc01] [varchar](100) NULL, [flexCode02] [varchar](50) NULL, [flexDesc02] [varchar](100) NULL, [flexCode03] [varchar](50) NULL, [flexDesc03] [varchar](100) NULL, [flexCode04] [varchar](50) NULL, [flexDesc04] [varchar](100) NULL, [flexCode05] [varchar](50) NULL, [flexDesc05] [varchar](100) NULL, [flexCode06] [varchar](50) NULL, [flexDesc06] [varchar](100) NULL, [flexCode07] [varchar](50) NULL, [flexDesc07] [varchar](100) NULL, [flexCode08] [varchar](50) NULL, [flexDesc08] [varchar](100) NULL, [flexCode09] [varchar](50) NULL, [flexDesc09] [varchar](100) NULL, [flexCode10] [varchar](50) NULL, [flexDesc10] [varchar](100) NULL, [glAcctYTDBalance] [money] NULL, [glAcctMonthDebitAmt] [money] NULL, [glAcctMonthCreditsAmt] [money] NULL, [glAcctMonthNetChangeAmt] [money] NULL, [CurrencyCode] [varchar](3) NULL, [FileCreateDate] [datetime] NULL, [grpEntityID] [varchar](2) NULL, [grpEntityErrorFlag] [varchar](1) NULL, [grpSubEntityID] [varchar](3) NULL, [grpSubEntityErrorFlag] [varchar](1) NULL, [grpGLAcctCode] [varchar](4) NULL, [grpGLAcctCodeErrorFlag] [varchar](1) NULL, [grpGLAcctNumberErrorFlag] [varchar](1) NULL, [recordErrorFlag] [varchar](1) NULL, [recordSource] [varchar](50) NULL, [createdBy] [varchar](50) NULL, [createTimeStamp] [datetime] NULL, [updatedBy] [varchar](50) NULL, [updateTimeStamp] [datetime] NULL, CONSTRAINT [tblGLJnlEntrySum_PK] PRIMARY KEY CLUSTERED ( [GLRecID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[tblGLJnlEntrySum] WITH NOCHECK ADD CONSTRAINT [FK_tblGLJnlEntrySum_tblDivision_DIM] FOREIGN KEY([DivCode])REFERENCES [dbo].[tblDivision_DIM] ([DivCode])GOALTER TABLE [dbo].[tblGLJnlEntrySum] CHECK CONSTRAINT [FK_tblGLJnlEntrySum_tblDivision_DIM]GOALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF_tblGLJnlEntrySum_CurrencyID] DEFAULT ('USD') FOR [CurrencyID]GOALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF__tblGLJnlE__Proce__703EA55A] DEFAULT ((0)) FOR [ProcessedFlag]GOALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF__tblGLJnlE__Creat__7132C993] DEFAULT (getdate()) FOR [CreatedDate]GOALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF__tblGLJnlE__Creat__7226EDCC] DEFAULT (suser_sname()) FOR [CreatedUser]GOALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF_tblGLJnlEntrySum_createdBy] DEFAULT (suser_sname()) FOR [createdBy]GOALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF_tblGLJnlEntrySum_createTimeStamp] DEFAULT (getdate()) FOR [createTimeStamp]GOTrigger used on top of table/****** Object: Trigger [dbo].[tblGLJnlEntrySum_TRU01] Script Date: 02/12/2013 04:56:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[tblGLJnlEntrySum_TRU01]ON [dbo].[tblGLJnlEntrySum]FOR UPDATE AS BEGIN update tblGLJnlEntrySum set UpdatedDate = getdate(), UpdatedUser = IsNull(deleted.UpdatedUser,suser_sname()) from deleted where tblGLJnlEntrySum.GLRecID = deleted.GLRecIDENDGOIndexes/****** Object: Index [IX_tblGLJnlEntrySum] Script Date: 02/12/2013 04:57:26 ******/CREATE NONCLUSTERED INDEX [IX_tblGLJnlEntrySum] ON [dbo].[tblGLJnlEntrySum] ( [DivCode] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GOUSE [dbSDUSAReporting]GO/****** Object: Index [tblGLJnlEntrySum_IX01] Script Date: 02/12/2013 04:58:33 ******/CREATE NONCLUSTERED INDEX [tblGLJnlEntrySum_IX01] ON [dbo].[tblGLJnlEntrySum] ( [DivCode] ASC, [GLAcctNbrP1] ASC, [AcctPeriod] ASC, [AcctYear] ASC, [AcctBalance] ASC)INCLUDE ( [GLAcctNbrP2],[GLAcctNbrP3],[GLAcctNbrP4],[GLAcctNbrP5]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GOUSE [dbSDUSAReporting]GO/****** Object: Index [tblGLJnlEntrySum_IX02] Script Date: 02/12/2013 04:58:56 ******/CREATE NONCLUSTERED INDEX [tblGLJnlEntrySum_IX02] ON [dbo].[tblGLJnlEntrySum] ( [DivCode] ASC, [AcctYear] ASC, [AcctPeriod] ASC, [GrpGLAcctNbr] ASC, [GLAcctNbrP1] ASC, [GLRecID] ASC, [CurrencyID] ASC, [AcctBalance] ASC)INCLUDE ( [GLAcctNbrP2],[GLAcctNbrP3],[GLAcctNbrP4],[GLAcctNbrP5],[GrpGLAcctType],[GLAcctDescP1],[GLAcctDescP2],[GLAcctDescP3],[GLAcctDescP4],[GLAcctDescP5]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GOUSE [dbSDUSAReporting]GO/****** Object: Index [tblGLJnlEntrySum_PK] Script Date: 02/12/2013 04:59:14 ******/ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [tblGLJnlEntrySum_PK] PRIMARY KEY CLUSTERED ( [GLRecID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GOTue, 12 Feb 2013 03:02:06 GMTp.panneerselvamhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxPlease post table definitions, index definitions and execution plan, as per [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Is the distinct really necessary?Tue, 12 Feb 2013 00:49:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1418763-65-1.aspxHi, I have 81 columns in my table. and record count=6690237. clustered index is been applied on PK. when i execute below select statement it is taking 3 min to process.select distinct divCode ,convert(varchar(4),glPeriodYear) + '/' + right('00' + convert(varchar(2),glPeriodMonth),2) + '/' + '01' as glPeriod from tblGLJnlEntrySum where divCode = @vsDivCode order by divCode,glPeriod desc Please tell me how i can speed up my query. need some suggestion on performance tuning.Mon, 11 Feb 2013 23:47:10 GMTp.panneerselvam