SQL Server Performance

slow insert

Discussion in 'Performance Tuning for DBAs' started by spo, Jun 21, 2006.

  1. spo New Member

    Hello,<br /><br />I have a server win2003 (no sp1) 8 processors and sqlserver EE 2000 sp3a.<br />The application server is a win2003 8 proc accessing to sqlserver over ODBC TCP/IP.<br />There is a dedicated 1gbs network link between the 2 servers.<br />CPU, memory, network and disks seem to be ok (not overloaded).<br /><br />I am monitoring long queries using the MS sqlserver profiler and found out that the following insert is spending more than 1,3-1,7 second frequently.<br />The table HLMVTGP is storing 7.000.000 lines (the description of the table is supplied after the query).<br /><br />Statistics are automatically updated and are launched once at night.<br />Shrinkfile is done every day.<br /><br /><br />My questions :<br />- is there any way to improve that query ?<br />- any advice ?<br /><br />declare @P1 int<br />set @P1=4625132<br />exec sp_prepexec @P1 output, N'@P1 varchar(8000),@P2 varchar(8000),@P3 int,@P4 int,@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000),@P8 varchar(8000),@P9 varchar(8000),@P10 varchar(8000),@P11 varchar(8000),@P12 varchar(8000),@P13 int,@P14 int,@P15 varchar(8000),@P16 varchar(8000),@P17 varchar(8000),@P18 varchar(8000),@P19 varchar(8000),@P20 varchar(8000),@P21 varchar(8000),@P22 int,@P23 decimal(38,3),@P24 decimal(38,3),@P25 int,@P26 decimal(38,3),@P27 decimal(38,3),@P28 decimal(38,3),@P29 decimal(38,2),@P30 varchar(8000),@P31 varchar(8000),@P32 varchar(8000),@P33 varchar(8000),@P34 varchar(8000),@P35 varchar(8000),@P36 varchar(8000),@P37 varchar(8000),@P38 int,@P39 int,@P40 varchar(8000),@P41 varchar(8000),@P42 varchar(8000),@P43 int,@P44 int,@P45 int,@P46 int,@P47 int,@P48 int,@P49 int,@P50 int,@P51 int,@P52 int,@P53 int,@P54 int,@P55 int,@P56 int,@P57 int,@P58 int,@P59 int,@P60 int,@P61 int,@P62 int,@P63 int,@P64 int,@P65 int,@P66 int,@P67 int,@P68 varchar(8000),@P69 varchar(8000),@P70 varchar(8000),@P71 varchar(8000),@P72 int,@P73 int,@P74 int,@P75 int,@P76 int,@P77 varchar(8000),@P78 int,@P79 int,@P80 int,@P81 int,@P82 int,@P83 varchar(8000),@P84 varchar(8000),@P85 varchar(8000),@P86 varchar(8000),@P87 varchar(8000),@P88 varchar(8000),@P89 int,@P90 int,@P91 int,@P92 decimal(38,0),@P93 int,@P94 int,@P95 int,@P96 int,@P97 int,@P98 int,@P99 varchar(8000),@P100 varchar(8000)', N'INSERT INTO REFLEX.HLMVTGP VALUES( @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26, @P27, @P28, @P29, @P30, @P31, @P32, @P33, @P34, @P35, @P36, @P37, @P38, @P39, @P40, @P41, @P42, @P43, @P44, @P45, @P46, @P47, @P48, @P49, @P50, @P51, @P52, @P53, @P54, @P55, @P56, @P57, @P58, @P59, @P60, @P61, @P62, @P63, @P64, @P65, @P66, @P67, @P68, @P69, @P70, @P71, @P72, @P73, @P74, @P75, @P76, @P77, @P78, @P79, @P80, @P81, @P82, @P83, @P84, @P85, @P86, @P87, @P88, @P89, @P90, @P91, @P92, @P93, @P94, @P95, @P96, @P97, @P98, @P99, @P100 )', 'P04', 'PGC', 2, 1215086, '-', '200', '440', '', '', '', '', '', 0, 0, '003020000004388617', '003020000000289567', '', '105394', '02', 'COP', 'STD', 21, 0.000, 6.925, 483, 0.000, 159.285, 0.000, 0.00, '', '', '', '', '', '', '0000100042', 'P04', 2, 6684, '', '', '', 0, 0, 0, 0, 0, 20, 6, 5, 18, 20, 7, 2, 28, 0, 0, 0, 0, 0, 0, 0, 0, 20, 7, 2, 28, '1', '0', '', '0', 0, 0, 0, 0, 0, '0', 0, 0, 0, 0, 0, '0', '0', '0', '0', '1', 'P04', 2, 6684, 2200, 0, 0, 20, 6, 6, 15, 92352, 'HFMI5D', 'DELAIDE-LL'<br />select @P1<br /><br />CREATE TABLE [reflex].[HLMVTGP] (<br />[VGCDPO] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGCACT] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGNAMG] [numeric](2, 0) NOT NULL ,<br />[VGNMVG] [numeric](9, 0) NOT NULL ,<br />[VGSMVG] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGCTST] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGCTVG] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGCTMS] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGCDOM] [varchar] (13) COLLATE French_CI_AS NOT NULL ,<br />[VGRMVS] [varchar] (20) COLLATE French_CI_AS NOT NULL ,<br />[VGCMES] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGCDPT] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGAOMG] [numeric](2, 0) NOT NULL ,<br />[VGNOMG] [numeric](9, 0) NOT NULL ,<br />[VGNGEI] [varchar] (1<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE French_CI_AS NOT NULL ,<br />[VGNSUP] [varchar] (1<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE French_CI_AS NOT NULL ,<br />[VGNCOL] [varchar] (1<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE French_CI_AS NOT NULL ,<br />[VGCART] [varchar] (16) COLLATE French_CI_AS NOT NULL ,<br />[VGCVLA] [varchar] (2) COLLATE French_CI_AS NOT NULL ,<br />[VGCPRP] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGCQAL] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGQMVG] [numeric](9, 0) NOT NULL ,<br />[VGPNMG] [numeric](9, 3) NOT NULL ,<br />[VGPBMG] [numeric](9, 3) NOT NULL ,<br />[VGQGAM] [numeric](9, 0) NOT NULL ,<br />[VGPNAV] [numeric](9, 3) NOT NULL ,<br />[VGPBAV] [numeric](9, 3) NOT NULL ,<br />[VGPRGE] [numeric](11, 3) NOT NULL ,<br />[VGMTGE] [numeric](13, 2) NOT NULL ,<br />[VGCDES] [varchar] (13) COLLATE French_CI_AS NOT NULL ,<br />[VGCFDS] [varchar] (15) COLLATE French_CI_AS NOT NULL ,<br />[VGRRSO] [varchar] (20) COLLATE French_CI_AS NOT NULL ,<br />[VGCDOR] [varchar] (13) COLLATE French_CI_AS NOT NULL ,<br />[VGRODP] [varchar] (20) COLLATE French_CI_AS NOT NULL ,<br />[VGTDJG] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGCFOU] [varchar] (13) COLLATE French_CI_AS NOT NULL ,<br />[VGCDPR] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGNANN] [numeric](2, 0) NOT NULL ,<br />[VGNREC] [numeric](9, 0) NOT NULL ,<br />[VGLOTF] [varchar] (20) COLLATE French_CI_AS NOT NULL ,<br />[VGLOT2] [varchar] (20) COLLATE French_CI_AS NOT NULL ,<br />[VGLOT3] [varchar] (20) COLLATE French_CI_AS NOT NULL ,<br />[VGSFAB] [numeric](2, 0) NOT NULL ,<br />[VGAFAB] [numeric](2, 0) NOT NULL ,<br />[VGMFAB] [numeric](2, 0) NOT NULL ,<br />[VGJFAB] [numeric](2, 0) NOT NULL ,<br />[VGHFAB] [numeric](4, 0) NOT NULL ,<br />[VGSREG] [numeric](2, 0) NOT NULL ,<br />[VGAREG] [numeric](2, 0) NOT NULL ,<br />[VGMREG] [numeric](2, 0) NOT NULL ,<br />[VGJREG] [numeric](2, 0) NOT NULL ,<br />[VGSDLU] [numeric](2, 0) NOT NULL ,<br />[VGADLU] [numeric](2, 0) NOT NULL ,<br />[VGMDLU] [numeric](2, 0) NOT NULL ,<br />[VGJDLU] [numeric](2, 0) NOT NULL ,<br />[VGSDLV] [numeric](2, 0) NOT NULL ,<br />[VGADLV] [numeric](2, 0) NOT NULL ,<br />[VGMDLV] [numeric](2, 0) NOT NULL ,<br />[VGJDLV] [numeric](2, 0) NOT NULL ,<br />[VGSDLC] [numeric](2, 0) NOT NULL ,<br />[VGADLC] [numeric](2, 0) NOT NULL ,<br />[VGMDLC] [numeric](2, 0) NOT NULL ,<br />[VGJDLC] [numeric](2, 0) NOT NULL ,<br />[VGSDOR] [numeric](2, 0) NOT NULL ,<br />[VGADOR] [numeric](2, 0) NOT NULL ,<br />[VGMDOR] [numeric](2, 0) NOT NULL ,<br />[VGJDOR] [numeric](2, 0) NOT NULL ,<br />[VGTGDI] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGTGBL] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGCMBG] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGTBDO] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGSDOU] [numeric](2, 0) NOT NULL ,<br />[VGADOU] [numeric](2, 0) NOT NULL ,<br />[VGMDOU] [numeric](2, 0) NOT NULL ,<br />[VGJDOU] [numeric](2, 0) NOT NULL ,<br />[VGHDOU] [numeric](4, 0) NOT NULL ,<br />[VGTBST] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGSSTA] [numeric](2, 0) NOT NULL ,<br />[VGASTA] [numeric](2, 0) NOT NULL ,<br />[VGMSTA] [numeric](2, 0) NOT NULL ,<br />[VGJSTA] [numeric](2, 0) NOT NULL ,<br />[VGHSTA] [numeric](4, 0) NOT NULL ,<br />[VGTBCT] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGTBRC] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGTBEM] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGTGIN] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGTPKG] [varchar] (1) COLLATE French_CI_AS NOT NULL ,<br />[VGCDP1] [varchar] (3) COLLATE French_CI_AS NOT NULL ,<br />[VGNANR] [numeric](2, 0) NOT NULL ,<br />[VGNUMR] [numeric](9, 0) NOT NULL ,<br />[VGNLIR] [numeric](6, 0) NOT NULL ,<br />[VGNOBJ] [numeric](15, 0) NOT NULL ,<br />[VGNCOM] [numeric](9, 0) NOT NULL ,<br />[VGSCRE] [numeric](2, 0) NOT NULL ,<br />[VGACRE] [numeric](2, 0) NOT NULL ,<br />[VGMCRE] [numeric](2, 0) NOT NULL ,<br />[VGJCRE] [numeric](2, 0) NOT NULL ,<br />[VGHCRE] [numeric](6, 0) NOT NULL ,<br />[VGCPCR] [varchar] (10) COLLATE French_CI_AS NOT NULL ,<br />[VGCUCR] [varchar] (10) COLLATE French_CI_AS NOT NULL <br />) ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL2] ON [reflex].[HLMVTGP]([VGCDPO], [VGCACT], [VGNAMG], [VGNOMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL3] ON [reflex].[HLMVTGP]([VGCDPO], [VGCTST], [VGCACT], [VGCART], [VGCVLA], [VGCPRP], [VGCQAL], [VGSCRE], [VGACRE], [VGMCRE], [VGJCRE], [VGHCRE], [VGNAMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL4] ON [reflex].[HLMVTGP]([VGCACT], [VGCART], [VGCVLA], [VGCPRP], [VGCQAL], [VGSCRE], [VGACRE], [VGMCRE], [VGJCRE], [VGHCRE], [VGNAMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL5] ON [reflex].[HLMVTGP]([VGCACT], [VGCART], [VGCVLA], [VGCPRP], [VGCQAL], [VGLOTF], [VGSCRE], [VGACRE], [VGMCRE], [VGJCRE], [VGHCRE], [VGNAMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL6] ON [reflex].[HLMVTGP]([VGNSUP], [VGCDPO], [VGCACT], [VGNAMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL8] ON [reflex].[HLMVTGP]([VGCDPO], [VGCACT], [VGNGEI], [VGSCRE], [VGACRE], [VGMCRE], [VGJCRE], [VGHCRE], [VGNAMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL9] ON [reflex].[HLMVTGP]([VGRMVS], [VGCACT], [VGCDPO], [VGNAMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGLA] ON [reflex].[HLMVTGP]([VGCACT], [VGCTST], [VGCART], [VGCVLA], [VGLOTF], [VGCDPO], [VGCPRP], [VGCQAL], [VGSCRE], [VGACRE], [VGMCRE], [VGJCRE], [VGHCRE], [VGNAMG], [VGNMVG]) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGL7] ON [reflex].[HLMVTGP]([VGCDPO], [VGCACT], [VGNGEI], [VGCTMS], [VGNAMG] DESC , [VGNMVG] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY]<br />GO<br /><br /> CREATE INDEX [HLMVTGLB] ON [reflex].[HLMVTGP]([VGCDPO], [VGCACT], [VGSCRE], [VGACRE], [VGMCRE], [VGJCRE], [VGNAMG], [VGNMVG]) ON [PRIMARY]<br />GO<br /><br /><br />
  2. Adriaan New Member

    No primary key, no clustered index, and a lot of non-clustered indexes. Not forgetting 7 million rows.

    Anyway, 1.3 to 1.7 seconds is not excessive for an insert, I would think.

    Does the table have triggers for inserts?
  3. Adriaan New Member

    ... anyway, is it really an insert? I must confess got lost among the parameter list.
  4. spo New Member

    Yes it is an INSERT...the texte is directly extracted withou modification from MS profiler.

    regarding the primary key, there is one clustered primary key (I forgot to include it into the generated script). No trigger are used.
    See the complementary informations :

    ALTER TABLE [reflex].[HLMVTGP] WITH NOCHECK ADD
    PRIMARY KEY CLUSTERED
    (
    [VGCDPO],
    [VGCACT],
    [VGNAMG],
    [VGNMVG]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    ALTER TABLE [reflex].[HLMVTGP] ADD
    CONSTRAINT [DF__HLMVTGP__VGCDPO__73FFE8D0] DEFAULT ('') FOR [VGCDPO],
    CONSTRAINT [DF__HLMVTGP__VGCACT__74F40D09] DEFAULT ('') FOR [VGCACT],
    CONSTRAINT [DF__HLMVTGP__VGNAMG__75E83142] DEFAULT (0.0) FOR [VGNAMG],
    CONSTRAINT [DF__HLMVTGP__VGNMVG__76DC557B] DEFAULT (0.0) FOR [VGNMVG],
    CONSTRAINT [DF__HLMVTGP__VGSMVG__77D079B4] DEFAULT ('') FOR [VGSMVG],
    CONSTRAINT [DF__HLMVTGP__VGCTST__78C49DED] DEFAULT ('') FOR [VGCTST],
    CONSTRAINT [DF__HLMVTGP__VGCTVG__79B8C226] DEFAULT ('') FOR [VGCTVG],
    CONSTRAINT [DF__HLMVTGP__VGCTMS__7AACE65F] DEFAULT ('') FOR [VGCTMS],
    CONSTRAINT [DF__HLMVTGP__VGCDOM__7BA10A98] DEFAULT ('') FOR [VGCDOM],
    CONSTRAINT [DF__HLMVTGP__VGRMVS__7C952ED1] DEFAULT ('') FOR [VGRMVS],
    CONSTRAINT [DF__HLMVTGP__VGCMES__7D89530A] DEFAULT ('') FOR [VGCMES],
    CONSTRAINT [DF__HLMVTGP__VGCDPT__7E7D7743] DEFAULT ('') FOR [VGCDPT],
    CONSTRAINT [DF__HLMVTGP__VGAOMG__7F719B7C] DEFAULT (0.0) FOR [VGAOMG],
    CONSTRAINT [DF__HLMVTGP__VGNOMG__0065BFB5] DEFAULT (0.0) FOR [VGNOMG],
    CONSTRAINT [DF__HLMVTGP__VGNGEI__0159E3EE] DEFAULT ('') FOR [VGNGEI],
    CONSTRAINT [DF__HLMVTGP__VGNSUP__024E0827] DEFAULT ('') FOR [VGNSUP],
    CONSTRAINT [DF__HLMVTGP__VGNCOL__03422C60] DEFAULT ('') FOR [VGNCOL],
    CONSTRAINT [DF__HLMVTGP__VGCART__04365099] DEFAULT ('') FOR [VGCART],
    CONSTRAINT [DF__HLMVTGP__VGCVLA__052A74D2] DEFAULT ('') FOR [VGCVLA],
    CONSTRAINT [DF__HLMVTGP__VGCPRP__061E990B] DEFAULT ('') FOR [VGCPRP],
    CONSTRAINT [DF__HLMVTGP__VGCQAL__0712BD44] DEFAULT ('') FOR [VGCQAL],
    CONSTRAINT [DF__HLMVTGP__VGQMVG__0806E17D] DEFAULT (0.0) FOR [VGQMVG],
    CONSTRAINT [DF__HLMVTGP__VGPNMG__08FB05B6] DEFAULT (0.0) FOR [VGPNMG],
    CONSTRAINT [DF__HLMVTGP__VGPBMG__09EF29EF] DEFAULT (0.0) FOR [VGPBMG],
    CONSTRAINT [DF__HLMVTGP__VGQGAM__0AE34E28] DEFAULT (0.0) FOR [VGQGAM],
    CONSTRAINT [DF__HLMVTGP__VGPNAV__0BD77261] DEFAULT (0.0) FOR [VGPNAV],
    CONSTRAINT [DF__HLMVTGP__VGPBAV__0CCB969A] DEFAULT (0.0) FOR [VGPBAV],
    CONSTRAINT [DF__HLMVTGP__VGPRGE__0DBFBAD3] DEFAULT (0.0) FOR [VGPRGE],
    CONSTRAINT [DF__HLMVTGP__VGMTGE__0EB3DF0C] DEFAULT (0.0) FOR [VGMTGE],
    CONSTRAINT [DF__HLMVTGP__VGCDES__0FA80345] DEFAULT ('') FOR [VGCDES],
    CONSTRAINT [DF__HLMVTGP__VGCFDS__109C277E] DEFAULT ('') FOR [VGCFDS],
    CONSTRAINT [DF__HLMVTGP__VGRRSO__11904BB7] DEFAULT ('') FOR [VGRRSO],
    CONSTRAINT [DF__HLMVTGP__VGCDOR__12846FF0] DEFAULT ('') FOR [VGCDOR],
    CONSTRAINT [DF__HLMVTGP__VGRODP__13789429] DEFAULT ('') FOR [VGRODP],
    CONSTRAINT [DF__HLMVTGP__VGTDJG__146CB862] DEFAULT ('') FOR [VGTDJG],
    CONSTRAINT [DF__HLMVTGP__VGCFOU__1560DC9B] DEFAULT ('') FOR [VGCFOU],
    CONSTRAINT [DF__HLMVTGP__VGCDPR__165500D4] DEFAULT ('') FOR [VGCDPR],
    CONSTRAINT [DF__HLMVTGP__VGNANN__1749250D] DEFAULT (0.0) FOR [VGNANN],
    CONSTRAINT [DF__HLMVTGP__VGNREC__183D4946] DEFAULT (0.0) FOR [VGNREC],
    CONSTRAINT [DF__HLMVTGP__VGLOTF__19316D7F] DEFAULT ('') FOR [VGLOTF],
    CONSTRAINT [DF__HLMVTGP__VGLOT2__1A2591B8] DEFAULT ('') FOR [VGLOT2],
    CONSTRAINT [DF__HLMVTGP__VGLOT3__1B19B5F1] DEFAULT ('') FOR [VGLOT3],
    CONSTRAINT [DF__HLMVTGP__VGSFAB__1C0DDA2A] DEFAULT (0.0) FOR [VGSFAB],
    CONSTRAINT [DF__HLMVTGP__VGAFAB__1D01FE63] DEFAULT (0.0) FOR [VGAFAB],
    CONSTRAINT [DF__HLMVTGP__VGMFAB__1DF6229C] DEFAULT (0.0) FOR [VGMFAB],
    CONSTRAINT [DF__HLMVTGP__VGJFAB__1EEA46D5] DEFAULT (0.0) FOR [VGJFAB],
    CONSTRAINT [DF__HLMVTGP__VGHFAB__1FDE6B0E] DEFAULT (0.0) FOR [VGHFAB],
    CONSTRAINT [DF__HLMVTGP__VGSREG__20D28F47] DEFAULT (0.0) FOR [VGSREG],
    CONSTRAINT [DF__HLMVTGP__VGAREG__21C6B380] DEFAULT (0.0) FOR [VGAREG],
    CONSTRAINT [DF__HLMVTGP__VGMREG__22BAD7B9] DEFAULT (0.0) FOR [VGMREG],
    CONSTRAINT [DF__HLMVTGP__VGJREG__23AEFBF2] DEFAULT (0.0) FOR [VGJREG],
    CONSTRAINT [DF__HLMVTGP__VGSDLU__24A3202B] DEFAULT (0.0) FOR [VGSDLU],
    CONSTRAINT [DF__HLMVTGP__VGADLU__25974464] DEFAULT (0.0) FOR [VGADLU],
    CONSTRAINT [DF__HLMVTGP__VGMDLU__268B689D] DEFAULT (0.0) FOR [VGMDLU],
    CONSTRAINT [DF__HLMVTGP__VGJDLU__277F8CD6] DEFAULT (0.0) FOR [VGJDLU],
    CONSTRAINT [DF__HLMVTGP__VGSDLV__2873B10F] DEFAULT (0.0) FOR [VGSDLV],
    CONSTRAINT [DF__HLMVTGP__VGADLV__2967D548] DEFAULT (0.0) FOR [VGADLV],
    CONSTRAINT [DF__HLMVTGP__VGMDLV__2A5BF981] DEFAULT (0.0) FOR [VGMDLV],
    CONSTRAINT [DF__HLMVTGP__VGJDLV__2B501DBA] DEFAULT (0.0) FOR [VGJDLV],
    CONSTRAINT [DF__HLMVTGP__VGSDLC__2C4441F3] DEFAULT (0.0) FOR [VGSDLC],
    CONSTRAINT [DF__HLMVTGP__VGADLC__2D38662C] DEFAULT (0.0) FOR [VGADLC],
    CONSTRAINT [DF__HLMVTGP__VGMDLC__2E2C8A65] DEFAULT (0.0) FOR [VGMDLC],
    CONSTRAINT [DF__HLMVTGP__VGJDLC__2F20AE9E] DEFAULT (0.0) FOR [VGJDLC],
    CONSTRAINT [DF__HLMVTGP__VGSDOR__3014D2D7] DEFAULT (0.0) FOR [VGSDOR],
    CONSTRAINT [DF__HLMVTGP__VGADOR__3108F710] DEFAULT (0.0) FOR [VGADOR],
    CONSTRAINT [DF__HLMVTGP__VGMDOR__31FD1B49] DEFAULT (0.0) FOR [VGMDOR],
    CONSTRAINT [DF__HLMVTGP__VGJDOR__32F13F82] DEFAULT (0.0) FOR [VGJDOR],
    CONSTRAINT [DF__HLMVTGP__VGTGDI__33E563BB] DEFAULT ('') FOR [VGTGDI],
    CONSTRAINT [DF__HLMVTGP__VGTGBL__34D987F4] DEFAULT ('') FOR [VGTGBL],
    CONSTRAINT [DF__HLMVTGP__VGCMBG__35CDAC2D] DEFAULT ('') FOR [VGCMBG],
    CONSTRAINT [DF__HLMVTGP__VGTBDO__36C1D066] DEFAULT ('') FOR [VGTBDO],
    CONSTRAINT [DF__HLMVTGP__VGSDOU__37B5F49F] DEFAULT (0.0) FOR [VGSDOU],
    CONSTRAINT [DF__HLMVTGP__VGADOU__38AA18D8] DEFAULT (0.0) FOR [VGADOU],
    CONSTRAINT [DF__HLMVTGP__VGMDOU__399E3D11] DEFAULT (0.0) FOR [VGMDOU],
    CONSTRAINT [DF__HLMVTGP__VGJDOU__3A92614A] DEFAULT (0.0) FOR [VGJDOU],
    CONSTRAINT [DF__HLMVTGP__VGHDOU__3B868583] DEFAULT (0.0) FOR [VGHDOU],
    CONSTRAINT [DF__HLMVTGP__VGTBST__3C7AA9BC] DEFAULT ('') FOR [VGTBST],
    CONSTRAINT [DF__HLMVTGP__VGSSTA__3D6ECDF5] DEFAULT (0.0) FOR [VGSSTA],
    CONSTRAINT [DF__HLMVTGP__VGASTA__3E62F22E] DEFAULT (0.0) FOR [VGASTA],
    CONSTRAINT [DF__HLMVTGP__VGMSTA__3F571667] DEFAULT (0.0) FOR [VGMSTA],
    CONSTRAINT [DF__HLMVTGP__VGJSTA__404B3AA0] DEFAULT (0.0) FOR [VGJSTA],
    CONSTRAINT [DF__HLMVTGP__VGHSTA__413F5ED9] DEFAULT (0.0) FOR [VGHSTA],
    CONSTRAINT [DF__HLMVTGP__VGTBCT__42338312] DEFAULT ('') FOR [VGTBCT],
    CONSTRAINT [DF__HLMVTGP__VGTBRC__4327A74B] DEFAULT ('') FOR [VGTBRC],
    CONSTRAINT [DF__HLMVTGP__VGTBEM__441BCB84] DEFAULT ('') FOR [VGTBEM],
    CONSTRAINT [DF__HLMVTGP__VGTGIN__450FEFBD] DEFAULT ('') FOR [VGTGIN],
    CONSTRAINT [DF__HLMVTGP__VGTPKG__460413F6] DEFAULT ('') FOR [VGTPKG],
    CONSTRAINT [DF__HLMVTGP__VGCDP1__46F8382F] DEFAULT ('') FOR [VGCDP1],
    CONSTRAINT [DF__HLMVTGP__VGNANR__47EC5C68] DEFAULT (0.0) FOR [VGNANR],
    CONSTRAINT [DF__HLMVTGP__VGNUMR__48E080A1] DEFAULT (0.0) FOR [VGNUMR],
    CONSTRAINT [DF__HLMVTGP__VGNLIR__49D4A4DA] DEFAULT (0.0) FOR [VGNLIR],
    CONSTRAINT [DF__HLMVTGP__VGNOBJ__4AC8C913] DEFAULT (0.0) FOR [VGNOBJ],
    CONSTRAINT [DF__HLMVTGP__VGNCOM__4BBCED4C] DEFAULT (0.0) FOR [VGNCOM],
    CONSTRAINT [DF__HLMVTGP__VGSCRE__4CB11185] DEFAULT (0.0) FOR [VGSCRE],
    CONSTRAINT [DF__HLMVTGP__VGACRE__4DA535BE] DEFAULT (0.0) FOR [VGACRE],
    CONSTRAINT [DF__HLMVTGP__VGMCRE__4E9959F7] DEFAULT (0.0) FOR [VGMCRE],
    CONSTRAINT [DF__HLMVTGP__VGJCRE__4F8D7E30] DEFAULT (0.0) FOR [VGJCRE],
    CONSTRAINT [DF__HLMVTGP__VGHCRE__5081A269] DEFAULT (0.0) FOR [VGHCRE],
    CONSTRAINT [DF__HLMVTGP__VGCPCR__5175C6A2] DEFAULT ('') FOR [VGCPCR],
    CONSTRAINT [DF__HLMVTGP__VGCUCR__5269EADB] DEFAULT ('') FOR [VGCUCR]
    GO
  5. joechang New Member

    1 sec+ is excessive even for so many columns, execessive unnecessary use of numeric

    but what can you control?
    this looks like a one those ERP/CRM applications designed to work with many databases vendors,
    such that runs horribly

    no sane person would have designed a table like this,
    varchar(3) should just be char(3)
    any numeric(x,0) should just be int, smallint or tinyint as appropriate
    sp_prepexec is an antiquated syntax, should be changed to a stored procedure

    its possible that the cost of parsing this is very high,
    also, the peak times are not important, what is the average time & cpu for this insert?
    it could be the long one are for index page splits etc
  6. Adriaan New Member

    He mentioned the client app is connecting through ODBC, which is probably the reason for the antiquated syntax on the trace.
  7. joechang New Member

    its possible to build a good odbc app,
    but sp_prepexec for this is stupid
    if this is executed frequently, they could have do a prepare, then execute repeatedly

    but still the xproc is dangerous with 32-bit systems today, this should have been converted to a stored proc, and not declared variables to varchar(8000)
  8. spo New Member

    You have guess right : the application is designed using a 4GL product and should work with Sqlserver/oracle/db2, that is probably why the db objects are generic ones.

    I would forward your comments to the responsible of the db driver.

    How can I measure the average time & cpu for 1 insert ?
    The profiler without any filter elements could not be launched on the production system...
  9. joechang New Member

    run perfmon to get the following values averaged over 10-20min during a busy period:<br /> Processor -&gt; % Processor Time <br /> SQLServer<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL Statistics -&gt; Batch Requests/sec<br /><br />do you really have 8 processor cores? <br /> if so is it 4 sockets & dual core, or 8 sockets (ie, a NUMA system)<br />or is it a 4 processor with HT
  10. spo New Member

    It is really a 8 processor, hyper threading is not activated.
  11. joechang New Member

    what about the other items
  12. spo New Member

    Sorry but I do not really understand your question, I am not enougth familiar with hardware tips. The server is an HP one with 8 proc Xeon TM (MP) 2,7 Ghz, 8go Ram, HT not activated but it is possible to activate it.

    My app has to use odbc, as it is built for any db vendor.
    Our 4GL tool is not abble to generate stored procedure.

    The varchar(8000) has been change (column size required by the api driver).
    It was the default max size for varchar.
    The driver now uses the real column size.
    Unit tests shows not fonctional impact and no performance impact.
    Do you know the effects this change would have on production sqlserver with multiple users (avg 400 connections simultaneoulsy).

    Thanks
  13. joechang New Member

    thats why i need the items i mentioned earlier to make a reasonable assessment of whats going on<br />it can be seriously misleading to base decisions on a select few extreme cases<br /><br />run perfmon to get the following values averaged over 10-20min during a busy period:<br />Processor -&gt; % Processor Time <br />SQLServer<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL Statistics -&gt; Batch Requests/sec<br />
  14. spo New Member

    The average CPU %time is 45% from 8am to 11am which is peak period.
    The SQLserver measures are not available within perfmon.
    One thing I forgot, we are using MS Cluster.
  15. joechang New Member

    as 2nd choice substitute for the Batch Requests/sec
    what is the Network Interface Packets received/sec and sent /sec for the active network card?

    also, use task manager to display cpu for all 8 proc, use the options to show kernel time too,
    is one or two procs consistently high, 90%+, with very high kernel time, while the others have low kernel time?
  16. spo New Member

    I would check tomorrow morning (it is 10pm in France).
    Thanks for your help.

    I have other queries that need investigations...but I have to analyse them before posting them here.

    Sebastien
  17. joechang New Member

    sounds like normal dba working hours to me
  18. spo New Member

    Here is the figures you asked for :

    From 7.30am to 10am

    On app server :
    avg max
    packet recv/s 6800 9420
    packet sent/s 6781 9268

    Proc %time avg 63 95
    Proc %priv avg 39 63

    Proc 0 %time 69 97
    Proc 1 %time 59 95
    Proc 2 %time 62 95
    Proc 3 %time 61 95
    Proc 4 %time 62 94
    Proc 5 %time 61 95
    Proc 6 %time 62 96
    Proc 7 %time 66 96

    Proc 0 %priv 52 75
    Proc 1 %priv 38 67
    Proc 2 %priv 38 64
    Proc 3 %priv 36 60
    Proc 4 %priv 36 60
    Proc 5 %priv 36 59
    Proc 6 %priv 37 63
    Proc 7 %priv 39 63

    On sql server :
    avg max
    packet recv/s 6500 8926
    packet sent/s 6200 8300

    Proc %time avg 28 54
    Proc %priv avg 8 15

    Proc 0 %time 27 53
    Proc 1 %time 18 43
    Proc 2 %time 28 77
    Proc 3 %time 24 57
    Proc 4 %time 30 57
    Proc 5 %time 31 60
    Proc 6 %time 32 68
    Proc 7 %time 34 60

    Proc 0 %priv 16 25
    Proc 1 %priv 8 16
    Proc 2 %priv 7 18
    Proc 3 %priv 6 14
    Proc 4 %priv 7 16
    Proc 5 %priv 8 13
    Proc 6 %priv 7 13
    Proc 7 %priv 8 16

    One another point, we are not doing any dbcc dbreindex.
    do you thing that this could have any consequences on performances ???
  19. joechang New Member

    App Server
    i did not ask for the app server, but since you provided it, i will comment on it.

    is it my understanding that you have one 8 proc app server and one 8 proc db server?
    your app server is seriously struggling, note the the high priveleged times.

    i would suggest you put up a 2 proc app server to see what load it can carrry relative to your 8 proc. i suspect the different will not be large,
    then see if you can load balance across two 2 proc app servers.

    database
    assuming the large majority of the network round trips are from SQL rpc calls, (this is why i would have preferred the sql batch request/sec)

    my suspicion is that you have a high call problem with NUMA systems (see my article on high call volume app)
    however, for this, i would have expected one cpu to be much higher, mostly privileged, than the others for w2k3, w2k would exhibit the counters you are showing.
    since you have an HP system, did anyone from HP come out to do special tuning?
    is there setting in boot.ini (MAXCLUSTER or something like that)
    was intfilter used?

    anyways, without special tricks, apps that make a high call volume (10K/sec, you are close enough) actually perform better on 4-way non-NUMA systems than 8+ NUMA systems

    look into why you are making so many rpc calls,
    it could be your use of odbc, generating sp_xx calls that get 1 row at a time
    this is your primary problem
  20. joechang New Member

    also, if you run profiler over the network, the extra network traffic immediately degrades your sql performance, because sql server is essentially saturated on network round-trip handling capability (not network bandwidth)

    i do suspect that you could run a profiler trace directly from the server console (not a remote conn) if you are careful to save results to a file on an unsed disk
  21. spo New Member

    Hello Joe,

    First of all, thanks for your time and advices.

    Yes, we have 2 servers (app + sql) that have 8 proc each.
    i do not know if HP has done special tuning on these servers, they had a problem with usb board ... that board has been deconnected i presume (i would check).

    The contents of sql boot.ini is :

    [boot loader]
    timeout=30
    default=multi(0)disk(0)rdisk(0)partition(1)WINDOWS
    [operating systems]
    multi(0)disk(0)rdisk(0)partition(1)WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE

    3GB has not been set because with the cluster, the application can run on the same node and the appl needs a lot of memory.

    I would have a look to all your advices but i do not understand why my "insert" is long whereas the sql cpu is not overloaded. The execution time of the insert should not rely on the network either. Is this correct ?
  22. joechang New Member

    there could any number of reasons why individual calls might take much longer than expected.

    even though your cpu does not appear to be overloaded, in fact your system is heavily loaded for the type of processing you are trying to do, ie, high call volume
    i suspect you will not get much more than 10-14K network round trips per sec on your database,
    but your app server is even more heavily loaded, so 10K/s may be the most your app can drive

    it has to do with how sql server handles network round-trips, this is why you cannot run profiler without impacting your system
    there might be chance the delays are more due to the app server being slow to response to network calls, note your very high peak cpus

    was the long inserts due to both high cpu and duration or just duration?

    try running profiler unfiltered on the server console itself saving results to a file (do not save to a table under any circumstance) for a 5-10min period, then see what the mix of calls are,
    use PSSDIAG from the MS site
  23. alzdba Member

    Just my 2ct <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />- What's the cluster ratio of your clustering index ?<br />- does it preform many page-splits ?<br /><br />- you can start sql-profiler-traces wich save to a local drive like this :<br /> (Check the script, adjust the drive you want to save to)<br />-- show Traces info<br /> SELECT * FROM :: fn_trace_getinfo(default) <br /><br />goto finish -- to be commented to do something<br /><br /><br />declare @StartStopEnd varchar(5)<br />declare @TraceID int<br />declare @RunDays smallint<br />declare @RunHours smallint<br />declare @RunMinutes smallint<br />declare @PlanNY char(1)<br />-- standaard voor 1u15 minuten ingesteld<br />Select @RunDays = 0<br />, @RunHours = 1<br />, @RunMinutes = 15<br /><br />Select @StartStopEnd = 'Start', @TraceID = 0, @PlanNY = 'N'<br />-- Select @StartStopEnd = 'Stop', @TraceID = xxx<br />-- Select @StartStopEnd = 'End' --, @TraceID = xxx<br /><br /><br />if upper(@StartStopEnd )= 'START' goto StartTrace<br />else <br /> begin<br />if upper(@StartStopEnd )= 'STOP' goto StopTrace<br />else<br /> begin<br />if upper(@StartStopEnd )= 'END' goto CleanupTrace<br /> else <br /> begin<br />Print 'Ongeldige @StartStopEnd kode [' + @StartStopEnd + ']'<br />goto finish<br /> end<br /> end<br /> end <br />goto finish<br /><br />StartTrace:<br />-- Create a Queue<br />declare @rc int<br />-- declare @TraceID int<br />declare @maxfilesize bigint<br />declare @DateTime datetime<br /><br />set @DateTime = dateadd(dd,@RunDays,dateadd(hh,@RunHours,dateadd(mi,@RunMinutes,getdate())))<br /><br />-- MB<br />set @maxfilesize = 5000 -- Mb<br /><br />Declare @TraceFileName nvarchar(300)<br />set @TraceFileName = 'C:ALZDBA_SQL_Trace' + '_' + replace(@@servername,'','_') + '_' + replace(replace(replace(convert(char(16),getdate(),121),'-',''),' ','_'),':','') -- + '.trc' wordt automatisch toegevoegd<br /><br />if datalength(@TraceFileName) &gt; 299 <br /> begin<br />print 'Filename to long ! [' + cast( datalength(@TraceFileName) as varchar(5)) +'] - [' + cast(@TraceFileName as varchar(300)) +']'<br /><br />goto finish<br /><br /> end<br /><br />print '-- Saving tracedata to : ' + cast(@TraceFileName as varchar(300)) + ' --'<br />print '-- **********************' + REPLICATE('*', datalength(@TraceFileName)/2) + ' --'<br />print ' '<br />print '-- Trace will end at ' + convert(varchar(23),@DateTime,121) + '. --'<br /><br />-- Please replace the text InsertFileNameHere, with an appropriate<br />-- filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension<br />-- will be appended to the filename automatically. If you are writing from<br />-- remote server to local drive, please use UNC path and make sure server has<br />-- write access to your network share<br /><br />exec @rc = sp_trace_create @TraceID output, 0, @TraceFileName, @maxfilesize, @Datetime <br />if (@rc &lt;&gt; 0) goto error<br /><br />-- Client side File and Table cannot be scripted<br /><br />-- Set the events<br />declare @on bit<br />set @on = 1<br />-- 10 = RPC:Completed<br />exec sp_trace_setevent @TraceID, 10, 1, @on -- textdata<br />exec sp_trace_setevent @TraceID, 10, 3, @on -- DbId<br />exec sp_trace_setevent @TraceID, 10, 6, @on -- NTUsername<br />exec sp_trace_setevent @TraceID, 10, 7, @on -- NTDomainname<br />exec sp_trace_setevent @TraceID, 10, 8, @on -- ClientHostname<br />exec sp_trace_setevent @TraceID, 10, 10, @on -- Applicationname<br />exec sp_trace_setevent @TraceID, 10, 11, @on -- SQLSecurityLoginName<br />exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID<br />exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration<br />exec sp_trace_setevent @TraceID, 10, 14, @on -- Starttime<br />exec sp_trace_setevent @TraceID, 10, 15, @on -- Endtime<br />exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads<br />exec sp_trace_setevent @TraceID, 10, 17, @on -- Writes<br />exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU<br />exec sp_trace_setevent @TraceID, 10, 26, @on -- Servername<br />exec sp_trace_setevent @TraceID, 10, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 10, 40, @on -- DatabaseUsername<br />-- 11 = RPC<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarting<br />exec sp_trace_setevent @TraceID, 11, 1, @on -- textdata<br />exec sp_trace_setevent @TraceID, 11, 3, @on -- DbId<br />exec sp_trace_setevent @TraceID, 11, 6, @on -- NTUsername<br />exec sp_trace_setevent @TraceID, 11, 7, @on -- NTDomainname<br />exec sp_trace_setevent @TraceID, 11, 8, @on -- ClientHostname<br />exec sp_trace_setevent @TraceID, 11, 10, @on -- Applicationname<br />exec sp_trace_setevent @TraceID, 11, 11, @on -- SQLSecurityLoginName<br />exec sp_trace_setevent @TraceID, 11, 12, @on -- SPID<br />exec sp_trace_setevent @TraceID, 11, 13, @on -- Duration<br />exec sp_trace_setevent @TraceID, 11, 14, @on -- Starttime<br />exec sp_trace_setevent @TraceID, 11, 15, @on -- Endtime<br />exec sp_trace_setevent @TraceID, 11, 16, @on -- Reads<br />exec sp_trace_setevent @TraceID, 11, 17, @on -- Writes<br />exec sp_trace_setevent @TraceID, 11, 18, @on -- CPU<br />exec sp_trace_setevent @TraceID, 11, 26, @on -- Servername<br />exec sp_trace_setevent @TraceID, 11, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 11, 40, @on -- DatabaseUsername<br />-- 12 = SQL:BatchCompleeted<br />exec sp_trace_setevent @TraceID, 12, 1, @on<br />exec sp_trace_setevent @TraceID, 12, 3, @on<br />exec sp_trace_setevent @TraceID, 12, 6, @on<br />exec sp_trace_setevent @TraceID, 12, 7, @on<br />exec sp_trace_setevent @TraceID, 12, 8, @on<br />exec sp_trace_setevent @TraceID, 12, 10, @on<br />exec sp_trace_setevent @TraceID, 12, 11, @on <br />exec sp_trace_setevent @TraceID, 12, 12, @on<br />exec sp_trace_setevent @TraceID, 12, 13, @on<br />exec sp_trace_setevent @TraceID, 12, 14, @on<br />exec sp_trace_setevent @TraceID, 12, 15, @on<br />exec sp_trace_setevent @TraceID, 12, 16, @on<br />exec sp_trace_setevent @TraceID, 12, 17, @on<br />exec sp_trace_setevent @TraceID, 12, 18, @on<br />exec sp_trace_setevent @TraceID, 12, 26, @on<br />exec sp_trace_setevent @TraceID, 12, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 12, 40, @on<br />-- 13 = SQL:BatchStarting<br />exec sp_trace_setevent @TraceID, 13, 1, @on<br />exec sp_trace_setevent @TraceID, 13, 3, @on<br />exec sp_trace_setevent @TraceID, 13, 6, @on<br />exec sp_trace_setevent @TraceID, 13, 7, @on<br />exec sp_trace_setevent @TraceID, 13, 8, @on<br />exec sp_trace_setevent @TraceID, 13, 10, @on<br />exec sp_trace_setevent @TraceID, 13, 11, @on <br />exec sp_trace_setevent @TraceID, 13, 12, @on<br />exec sp_trace_setevent @TraceID, 13, 13, @on<br />exec sp_trace_setevent @TraceID, 13, 14, @on<br />exec sp_trace_setevent @TraceID, 13, 15, @on<br />exec sp_trace_setevent @TraceID, 13, 16, @on<br />exec sp_trace_setevent @TraceID, 13, 17, @on<br />exec sp_trace_setevent @TraceID, 13, 18, @on<br />exec sp_trace_setevent @TraceID, 13, 26, @on<br />exec sp_trace_setevent @TraceID, 13, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 13, 40, @on<br />-- 14 = Login<br />exec sp_trace_setevent @TraceID, 14, 1, @on<br />exec sp_trace_setevent @TraceID, 14, 3, @on<br />exec sp_trace_setevent @TraceID, 14, 6, @on<br />exec sp_trace_setevent @TraceID, 14, 7, @on<br />exec sp_trace_setevent @TraceID, 14, 8, @on<br />exec sp_trace_setevent @TraceID, 14, 10, @on<br />exec sp_trace_setevent @TraceID, 14, 11, @on <br />exec sp_trace_setevent @TraceID, 14, 12, @on<br />exec sp_trace_setevent @TraceID, 14, 13, @on<br />exec sp_trace_setevent @TraceID, 14, 14, @on<br />exec sp_trace_setevent @TraceID, 14, 15, @on<br />exec sp_trace_setevent @TraceID, 14, 16, @on<br />exec sp_trace_setevent @TraceID, 14, 17, @on<br />exec sp_trace_setevent @TraceID, 14, 18, @on<br />exec sp_trace_setevent @TraceID, 14, 26, @on<br />exec sp_trace_setevent @TraceID, 14, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 14, 40, @on<br />-- 15 = Logout<br />exec sp_trace_setevent @TraceID, 15, 1, @on<br />exec sp_trace_setevent @TraceID, 15, 3, @on<br />exec sp_trace_setevent @TraceID, 15, 6, @on<br />exec sp_trace_setevent @TraceID, 15, 7, @on<br />exec sp_trace_setevent @TraceID, 15, 8, @on<br />exec sp_trace_setevent @TraceID, 15, 10, @on<br />exec sp_trace_setevent @TraceID, 15, 11, @on <br />exec sp_trace_setevent @TraceID, 15, 12, @on<br />exec sp_trace_setevent @TraceID, 15, 13, @on<br />exec sp_trace_setevent @TraceID, 15, 14, @on<br />exec sp_trace_setevent @TraceID, 15, 15, @on<br />exec sp_trace_setevent @TraceID, 15, 16, @on<br />exec sp_trace_setevent @TraceID, 15, 17, @on<br />exec sp_trace_setevent @TraceID, 15, 18, @on<br />exec sp_trace_setevent @TraceID, 15, 26, @on<br />exec sp_trace_setevent @TraceID, 15, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 15, 40, @on<br />-- 17 = ExistingConnection<br />exec sp_trace_setevent @TraceID, 17, 1, @on<br />exec sp_trace_setevent @TraceID, 17, 3, @on<br />exec sp_trace_setevent @TraceID, 17, 6, @on<br />exec sp_trace_setevent @TraceID, 17, 7, @on<br />exec sp_trace_setevent @TraceID, 17, 8, @on<br />exec sp_trace_setevent @TraceID, 17, 10, @on<br />exec sp_trace_setevent @TraceID, 17, 11, @on <br />exec sp_trace_setevent @TraceID, 17, 12, @on<br />exec sp_trace_setevent @TraceID, 17, 13, @on<br />exec sp_trace_setevent @TraceID, 17, 14, @on<br />exec sp_trace_setevent @TraceID, 17, 15, @on<br />exec sp_trace_setevent @TraceID, 17, 16, @on<br />exec sp_trace_setevent @TraceID, 17, 17, @on<br />exec sp_trace_setevent @TraceID, 17, 18, @on<br />exec sp_trace_setevent @TraceID, 17, 26, @on<br />exec sp_trace_setevent @TraceID, 17, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 17, 40, @on<br /><br /><br />if @PlanNY = 'Y'<br /> Begin<br />-- 68 = obtain the execution plans<br /><br />exec sp_trace_setevent @traceid, 68, 1, @on --Execution Plan, TextData<br />-- exec sp_trace_setevent @traceid, 68, 2, @on --Execution Plan, BinaryData<br />exec sp_trace_setevent @traceid, 68, 3, @on --Execution Plan, DatabaseID<br />-- exec sp_trace_setevent @traceid, 68, 4, @on --Execution Plan, TransactionID<br />exec sp_trace_setevent @traceid, 68, 6, @on --Execution Plan, NTUserName<br />exec sp_trace_setevent @traceid, 68, 7, @on --Execution Plan, NTDomainName<br />exec sp_trace_setevent @traceid, 68, 8, @on --Execution Plan, ClientHostName<br />-- exec sp_trace_setevent @traceid, 68, 9, @on --Execution Plan, ClientProcessID<br />exec sp_trace_setevent @traceid, 68, 10, @on --Execution Plan, ApplicationName<br />exec sp_trace_setevent @traceid, 68, 11, @on --Execution Plan, SQLSecurityLoginName<br />exec sp_trace_setevent @traceid, 68, 12, @on --Execution Plan, SPID<br />exec sp_trace_setevent @traceid, 68, 13, @on --Execution Plan, Duration<br />exec sp_trace_setevent @traceid, 68, 14, @on --Execution Plan, StartTime<br />exec sp_trace_setevent @traceid, 68, 15, @on --Execution Plan, EndTime<br />exec sp_trace_setevent @traceid, 68, 16, @on --Execution Plan, Reads<br />exec sp_trace_setevent @traceid, 68, 17, @on --Execution Plan, Writes<br />exec sp_trace_setevent @traceid, 68, 18, @on --Execution Plan, CPU<br />-- exec sp_trace_setevent @traceid, 68, 21, @on --Execution Plan, EventSubClass<br />-- exec sp_trace_setevent @traceid, 68, 22, @on --Execution Plan, ObjectID<br />-- exec sp_trace_setevent @traceid, 68, 25, @on --Execution Plan, IntegerData<br />-- exec sp_trace_setevent @traceid, 68, 27, @on --Execution Plan, Eventclass<br />exec sp_trace_setevent @TraceID, 68, 26, @on<br />exec sp_trace_setevent @TraceID, 68, 31, @on -- Error <br />exec sp_trace_setevent @TraceID, 68, 40, @on<br /> END<br /><br />-- Set the Filters<br />declare @intfilter int<br />declare @bigintfilter bigint<br /><br />exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'<br /><br /><br />-- Set the trace status to start<br />exec sp_trace_setstatus @TraceID, @status = 1<br /><br />-- display trace id for future references<br />-- select TraceID=@TraceID<br />print '-- '<br />Print '-- TraceID for this trace [' + cast(@TraceID as varchar(10)) + '] --'<br />Print '-- ************************' + REPLICATE('*', datalength(cast(@TraceID as varchar(10)))) + '* --'<br /><br />-- show Traces info<br />SELECT * FROM :: fn_trace_getinfo(default) <br /><br />goto finish<br /><br />StopTrace:<br />-- Set the trace status to stop <br /> exec sp_trace_setstatus @TraceID, @status = 0<br /> print 'Trace Stopped.'<br /><br />goto finish<br /><br />CleanupTrace:<br />-- Set the trace status to cleanup <br />exec sp_trace_setstatus @TraceID, @status = 2<br /> print 'Trace Cleaned up.'<br /><br />goto finish<br /><br /><br />error: <br />select ErrorCode=@rc<br /><br />finish: <br />go<br /><br />
  24. spo New Member

    Hello,

    These are the informations given by show contig regarding the clustered index :
    count pages = 435 747
    extent switches = 77 062
    scan density = 71
    best count = 54 469
    actual count = 77 063
    logical frag = 3

    i do not understand how i can use your script into the profiler (i never did a such thing, i would search, thanks)

Share This Page