Long running procedure

Question

Long running procedure

Thamizh

Old Hand

Points: 359
More actions
March 2, 2010 at 4:58 am

#211756

Hi all,
Any one give some word about this,If i run the procedure in test server it performs good.The same implement in the production server running very slow .I checked all windows in the production server but its exactly same whatever in the production server including row count.If anyone know the solution Please post me those steps.

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

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

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 1

Thamizh (3/2/2010)
Hi all,
Any one give some word about this,If i run the procedure in test server it performs good.The same implement in the production server running very slow .I checked all windows in the production server but its exactly same whatever in the production server including row count.If anyone know the solution Please post me those steps.

You shouldn't compare the performance of a Test server with Production Server, there are many things which may affect this delay (actually you'll surely face the delay in the execution of any SQL Command on production if you compare that with test server)

because:

1. User Load on Production

2. diff in Data in Tables

3. Buffer\ Page life expectancy

4. disk IO

5. Index Fragmentation

the list goes on and on

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

Lowell SSC Guru Points: 323507 More actions · Answer 2

To continue with Sarab's points, two issues I always check for:

update statistics on production; this can make a big difference.

does your procedure have "default" values for any of it's parameters? if it does, it's probably sufferenign from parameter sniffing. search SSC for more info.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Thamizh (3/2/2010)
Hi all,
Any one give some word about this,If i run the procedure in test server it performs good.The same implement in the production server running very slow .I checked all windows in the production server but its exactly same whatever in the production server including row count.If anyone know the solution Please post me those steps.

Read the Gail's article added in my signature.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

kalyan.nimbala Mr or Mrs. 500 Points: 524 More actions · Answer 4

Run the query part ot the SP on production server and see the execution plan, this way you can see which part of your query is taking so much amount of time....another issue can be that your tables might be in Testing and you're running your query in the production server there by making linked server querying where Testing server has limited bandwidth.

Thamizh Old Hand Points: 359 More actions · Answer 5

presently what happen is that took long time on its first execution after that its running normally.But once its modified again running long on its first execution....Any idea...?

kalyan.nimbala Mr or Mrs. 500 Points: 524 More actions · Answer 6

What do you mean by modifying? Changing the columns you select or chang the logic, you can see the execution steps in execution plan along wid the time it takes to execute each select statement in your bunch of SQL stmnts.

Thamizh Old Hand Points: 359 More actions · Answer 7

Thamizh

Old Hand

Points: 359

March 4, 2010 at 7:51 pm

#1128927

OK.I do

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 8

Thamizh (3/4/2010)
presently what happen is that took long time on its first execution after that its running normally.But once its modified again running long on its first execution....Any idea...?

The time taken by query\SP on its first execution is the real time your query\Sp is taking, the next time you execute SQL takes the benifit of buffer and do logical read.

refer msdn for buffer pages, execution plans and page life expectancy ratio

also if you want to test & fine tune the execution timings you should try it on a test machine with cleaning its buffer cache.

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

To add to the list

1. missing indexes

2. amount of data

3. disk configuration

4. log file fragmentation (VLF)

There are a lot of things to look for when trying to tune a query.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mike 84768 Right there with Babe Points: 792 More actions · Answer 10

This could be the result of a very light workload and very small number of queries executing on the test server...not enough memory pressure to force the query plan and data pages to be flushed from the respective buffers...probably the opposite of the production server.