We are not using any cursors but we have multiple insert/update statement
specified individually for each column in the table. Would combining
multiple update statements into one help ?? We are also using a lot of
temporary table. This tables are populated with upto 20k rows and then
modified; all this done within the stored procedure. Wrt to bottlenecks ,
our buffer hit ratio is greater then 95% and avg disk queue length is less
"Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
news:O9dvKyjRDHA.1972TK2MSFTNGP11.phx.gbl...93> I would work on seeing if you can't optimize the initial process first.see> seconds on a quad processor box is a lot of time. You must be using
> cursors and a lot of individual processing. You need to narrow down toconcurrently> where the bottle necks are first. I would find it hard to believe there
> isn't a lot of blocking when your running that with 7 users. But in any
> case are you cpu bound, high disk queues, short of memory etc.
> Andrew J. Kelly
> SQL Server MVP
> "Amol Kasbekar" <apkcbord.com> wrote in message
> news:%23ThK0NjRDHA.1220TK2MSFTNGP12.phx.gbl...> seconds.> > We have a process which one run by a only a single client takes 93> and> > I run this test from query yzer. The SQL Server is installed locally> > is running on Quad processor each of 1.5ghz with hyperthreading.
> > I next create 7 sessions in query yzer and run this testresults> > from 7 sessions to simulate 7 users. I am seeing some interestingagain> in> but> > this case. 5 clients finish within 180 seconds with fastest at 165 and
> > slowest amongst them at 180. The last 2 however finish at 272 seconds. I
> > have debugging messages put in that tell me that all the 7 process start> > then the last 2 processes kind of go into sleep state and start backwhich> occuring,> > only after the first 5 are finished. There is no apparent blocking> > I check continuously thru EM.
> > The process is pretty complex to put the complete DDL and code snippets
> > here, but heres the skinny. The process calls two stored procedures,> the> > work heavily with temporary tables. They do a lot of inserts/updates in> procedures.> > temp tables. The temporary tables are created outside the stored> The> > The second procedure was giving recompilation locks so we made it a temp
> > stored procedure.
> > I checked the thread count, and the thread count remains constant at 94.>> > server is configured to use the default 250 max worker threads.
> > Any ideas or comments about this would be highly welcome.
> > TIA, Amol.