increase CPU time

Hi All,
I've a problem regarding increased CPU time.....
I've a stored procedure which joins approx 6
table and i'm using table variable to hold data. Stored procedure is working
fine and absolutely OK but when it runs on LIVE server through Web
Application, It increase CPU time and increase READS Per runs.
i added SET NOCOUNT ON and SET NOCOUNT OFF
as well as i added WITH RECOMPILE option, but no use it is still giving too
much reads per run.
is anybody having solution of this problem? Please
reply me ASAP...
Sword is hanging on my neck ';' help me
Manish SukhijaManish
What reads? Logical?
How about indexes defined on the tables? Do you see the optimizer is
available to create an efficient execution plan , i mean it uses the
indexes?
Actually if want more accurate answer please post DDL+ Sample data
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is
> working
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving
> too
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhija|||Hi Uri,
as i defined that i've stored procedure which joins approximately 6
table and when it runs om live server and when it is being checked by hosted
party in Profiler it shows
database is producing a high amount of cpu usage as well as page
reads in a perticular Stored procedure...
then i put a index on a column of one of joined table,
it reduce some time, now i want to know that is this the only solution to
decrease CPU time or is there any other way around, if this is only way then
on which table and ofcourse on which column should i put index. As you know
Stored procedure joins 6 table and they are having so may columns.
so should i put index on all columns of 6
table but as fas as i know it's not best practise to put index on all column
s
it can make adverse afftect on performance.
make me right if i'm wrong... or is there any other
way to decrease CPU time in that Stored procedure.
If you want code of that Stored procedure
i'll send it to you...
Manish
"Uri Dimant" wrote:

> Manish
> What reads? Logical?
> How about indexes defined on the tables? Do you see the optimizer is
> available to create an efficient execution plan , i mean it uses the
> indexes?
> Actually if want more accurate answer please post DDL+ Sample data
>
>
>
> "Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in messag
e
> news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
>
>|||Manish
Do you have FOREIGN KEY constraints? Does the key of referncing table have
an index?
http://www.sql-server-performance.c...or_counters.asp
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:1A433AA4-D9A0-47E2-82A4-1EEB092DD27B@.microsoft.com...
> Hi Uri,
> as i defined that i've stored procedure which joins approximately
> 6
> table and when it runs om live server and when it is being checked by
> hosted
> party in Profiler it shows
> database is producing a high amount of cpu usage as well as page
> reads in a perticular Stored procedure...
> then i put a index on a column of one of joined table,
> it reduce some time, now i want to know that is this the only solution to
> decrease CPU time or is there any other way around, if this is only way
> then
> on which table and ofcourse on which column should i put index. As you
> know
> Stored procedure joins 6 table and they are having so may columns.
> so should i put index on all columns of 6
> table but as fas as i know it's not best practise to put index on all
> columns
> it can make adverse afftect on performance.
> make me right if i'm wrong... or is there any other
> way to decrease CPU time in that Stored procedure.
> If you want code of that Stored procedure
> i'll send it to you...
> Manish
>
> "Uri Dimant" wrote:
>|||You may want to look at DBCC SHOWCONTIG if you aren't already running some
kind of defrag maintenence on your indexes.
--
Regards,
Jamie
"Manish Sukhija" wrote:

> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is worki
ng
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving to
o
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhijasql