SP running slow

Question

SP running slow

imran karbhari-401826

SSC Veteran

Points: 250
More actions
May 25, 2008 at 7:18 am

#380673

Hi,
we have 1 sp which takes 16sec in dev environment while taks almost 4min in production environment. In the SP we are updating 15000 rows while searching from other table where there are 20000 rows. Though these tables have exctaly defination in both environment. I even restored DB from dev to prod but it takes exctly same time. I have checked indexs after even taking help from profiler but no diffrence. Also rebuilt all the indexes before ruuning. Please let me know what am i missing.

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Please post the sql code from the stored procedures and the XML qeury plans for your DEV and production environments.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

A few things you can check. I know you said you rebuilt your indexes, but there are a few other things that you can try to resolve this issue. Update statistics(full), recompile sp sp_recompile MySPName, make sure your data types are the same in both tables. A lot of times developers change table data types and forget to change the stored procedure, thus the optimizer cannot generate a good query plan.

Please post the execution plans beceause I am willing to bet you are getting seeks on one instance and scans on the other.

My blog: http://jahaines.blogspot.com

Avishkar Meshram SSC-Addicted Points: 414 More actions · Answer 3

Pl check the hardware configuration of both Dev and production server.

If CPU is bottleneck, then you should study and configure settings for parallelism, Hyperthreading.

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Well, there have been some good suggetions for you here. Care to give a little feedback on where you're at with this one?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Andrea Allred SSC Veteran Points: 231 More actions · Answer 5

I am a fan of updating statistics too. EXEC SP_UPDATESTATS does the whole database or Update statistics [tablename] does one table.

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Theare are lot of reasons. so as some of our forum member suggested, do that one.

But before doing all those things , just take a look on Query plan.

Set showplan on

As some forum members suggested, you also take a look on UPDATE STATISTICS.

run the below query, you should know the last date of UPDATE STATISTICS command ran for the particular table.

select moddate from sysstatistics where id = object_id("your_table_name")

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Note that, this may produce a list of dates. This is because, sysstatistics table maintains one or more entries for each of the index columns of the table. It may contain entries for non-indexed columns as well.

Make sure, you run update statistics on tables regularly, specially the tables whose volume changes considerably, over the time.

karthik