Scurvy Posted June 23, 2020 Report Posted June 23, 2020 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. Quote
quickgun_murugun Posted June 23, 2020 Report Posted June 23, 2020 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 Quote
Scurvy Posted June 23, 2020 Author Report Posted June 23, 2020 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. Quote
BostonBullodu Posted June 23, 2020 Report Posted June 23, 2020 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 Quote
Manikyam Posted June 23, 2020 Report Posted June 23, 2020 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 Quote
andhrabullet Posted June 23, 2020 Report Posted June 23, 2020 use force seek option.. maku alane jarigindi... due to some refresh Quote
quickgun_murugun Posted June 23, 2020 Report Posted June 23, 2020 52 minutes ago, Scurvy said: Do not have access to run profiler. Company policy. Ask your admin to grant ALTER TRACE rights temporarily ... Quote
quickgun_murugun Posted June 23, 2020 Report Posted June 23, 2020 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. Quote
kathanayaka Posted June 23, 2020 Report Posted June 23, 2020 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. Quote
AndhraneedSCS Posted June 23, 2020 Report Posted June 23, 2020 2 possibilities: 1) DB blocking 2) your SP took a different execution plan. Compare the Execution plan between good and bad executions Quote
Gentle_Boy Posted June 24, 2020 Report Posted June 24, 2020 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 Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.