This is my first post, so if you need clarification on some, just let me know.
The details of my server are as follows: - Windows 2008 Datacenter Edition
SQL 2008 Standard Edition (10.0.1600)
12GB RAM
Quad Core Single Processor Machine
Problem
I have a stored procedure that runs and when I have just started SQL, it takes around 1/10 degree for each other to run. After time, the same question takes about 3 seconds to run.
I originally assumed that they were indexes which were causing problems, but if I made the exact copy of sproc and ran that copy version, then only 1/10 Takes one second again and the original one still takes 3 seconds.
Now I am assuming that this sproc cached execution scheme is something with caching and this sproc is run again then it is messing up the execution plan.
I have tried so far
I currently have a maintenance plan which runs every 15 minutes - detects a small table and for some reason At the time of execution on my sprocs, they come back to normal level, but then again goes back again
It has made a copy of the sproc to test and that 1 / Runs on the 10th and the original one still takes a long time.
Run the "Update Statistics" sproc to make sure all stats are up to date
Run the SQL Query Profiler to see if it has any suggestions on other indices which have tables It should be on, it is ending some suggestions which increased my index and DB size more than 70 GB and performance enhancement was careless.
Other information to note
DB has spread across two DBS in the same example, it contains information about a product, the other includes customer information < / P>
One of the table joining is 130 million lines long
DB is an upgrade from 2005 to 2008.
It sounds like a parameter to sniff for me.
Your 15-minute re-indexing (you need it!?) Will cause the reason to recompile the dependent process. Sometimes when this happens, the value of the parameter value in the next execution will be the normal Sub for the case are optimal. You can use it to prevent it from happening.
Comments
Post a Comment