Stored Procedure Performance

Question

Post reply

Stored Procedure Performance

Abhijeet Chavan

SSCommitted

Points: 1511
More actions
June 14, 2010 at 3:03 am

#225600

I have a stored procedure which gets called continuously. when checked in profiler it shows duration ranging from 0 to 185 milliseconds. The strange thing is that all the time i get a consistent duration of 0-1 but after every 25th or 26th execution it shoots to more than 150 milliseconds.
the stored procedure have 2 simple update or insert queries. Even if i delete all the rows from the tables, it still give me those strange readings.
This readings were taken on my test machine where no one else connects the sql instance.
Please tell me if u have any suggestions or solutions.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

can we get the table structure and the procedure.

Please check the index of the table and procedure exection plan

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Sounds like it could be recompiling , check with profiler

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Could be recompiles, could be there's some system process that's running at the same time either in SQL or windows.

Does the proc exhibit the same performance characteristics on the production server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 4

Thanks for the reply. (Dave Ballantyne and GilaMonster)

I checked in the profiler, there was no Recompile event.

Thank you very much for ur efforts.

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 5

Thanks Sharath

I checked the Execution plan.

There is an clustered index seek with a warning saying "columns with now statics" and then 1 column name and same for one more table there it gave 3 column have no statistic.

Is this causing the problem????

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

If the exec plan is warning about missing statistics, you could easily be having sub-optimal performance. Is auto_create statistics enabled at the database level?

You didn't answer my question about whether or not this behaviour is also present on the production server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 7

Hi Gail Shaw

Thanks for the reply.

About your first question : No i haven't checked this on production server, all i was testing was on my machine and couple of my colleagues machines with db backup from the production server.

And checked the Database properties.

Auto Create Statistics False

Auto Update Statistics False

So now my question is:

1 If i enable these settings will it solve the problem?

2 And what will be the impact on overall performance? (Will it slow down other transactions.)

Thanks.

Paul White SSC Guru Points: 150468 More actions · Answer 8

Another possibility is that the query is waiting while existing statistics are being re-sampled.

More information

edit: no, not that since auto update statistics is off (why did you change that from the default?)

Second guess: delay caused while a new extent is being allocated.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Why are auto-create and auto-update statistics disabled? Are you manually managing your statistics?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 10

Thank you Gail for the quick replies.

this database exist since a long time, and i have joined this comp 2 weeks back.

SO i am not aware why these settings are disabled.

And i don't think that anybody is manually managing the statistics.

Thanks