Calling SQL experts

[Scurvy]

There is Sp taking long time to execute. I tried to do: rebuild indexes, re organize indexes worked fine and brought execution time to very low.

After a week later when i ran the same SP it is again taking longer. This time i did apply re organize ,rebuild ,  parameter sniffing  Option recompile in Sp but nothing works. Any tips/suggestions to optimize which you use.

[Scada]

😳

[reality]

Collect stats

[quickgun_murugun]

22 minutes ago, Scurvy said:

There is Sp taking long time to execute. I tried to do: rebuild indexes, re organize indexes worked fine and brought execution time to very low.

After a week later when i ran the same SP it is again taking longer. This time i did apply re organize ,rebuild ,  parameter sniffing  Option recompile in Sp but nothing works. Any tips/suggestions to optimize which you use.

run the profiler and see whats causing the resources .. disk I/O kuda impact undochu

[Scurvy]

11 minutes ago, quickgun_murugun said:

run the profiler and see whats causing the resources .. disk I/O kuda impact undochu

Do not have access to run profiler. Company policy.

[BostonBullodu]

Run the sp at different times during day and see 

lot more you can check like any string function being used in where clause etc can you drop and recreate the indexes after you load data 

[Manikyam]

13 minutes ago, Scurvy said:

Do not have access to run profiler. Company policy.

don't rebuild but drop and recreate this time

you are dealing with large volumes and continuous fragmentation

[andhrabullet]

use force seek option.. maku alane jarigindi... due to some refresh

[quickgun_murugun]

52 minutes ago, Scurvy said:

Do not have access to run profiler. Company policy.

Ask your admin to grant ALTER TRACE rights temporarily ... 

[quickgun_murugun]

55 minutes ago, Scurvy said:

Do not have access to run profiler. Company policy.

Joins case statements check cheyyu procedure definition lo ... 

Sometimes the disk I/O would slow down the process .

Check if any full backups or system scans are running during that time. 

 

[kathanayaka]

Stop tughlak troubleshooting and move to RDS MariaDB Aurora ASAP

1 hour ago, Scurvy said:

There is Sp taking long time to execute. I tried to do: rebuild indexes, re organize indexes worked fine and brought execution time to very low.

After a week later when i ran the same SP it is again taking longer. This time i did apply re organize ,rebuild ,  parameter sniffing  Option recompile in Sp but nothing works. Any tips/suggestions to optimize which you use.

 

[AndhraneedSCS]

2 possibilities:

1) DB blocking

2) your SP took a different execution plan. Compare the Execution plan between good and bad executions 

[Gentle_Boy]

Print date time....

Like

Select datetime()

at  code line 10

At code Line 30 

At line 50 

At line 100

At line 200

So you'll know what part of the stored proc...its taking longer time.

Then you can optimize and fine tune it