January 19, 2015 at 8:53 pm
I am a software developer and have adopted the role of DBA here so please don’t shoot me if I write something too silly :hehe:
We have a database that is used for a high volume of transactions. The core table my problem relates to has over 60.5 million rows and grows at a rate of roughly 50k per day. Each day a .NET task runs over this table and finds all transactions belonging to a particular date and device and performs a summation of these. This query has been running flawlessly (or so I thought) until about 3 weeks ago at which point it began timing out in .net (30 seconds). The query uses inline SQL from the .NET application.
What I am noticing is that it starts timing out again each Monday morning after our maintenance tasks have run and then slowly gets better over the coming days. Next Monday morning, BAM, timeouts again. The maintenance task performs an index rebuild or reorganise depending on fragmentation (reorganise between 5-30%, rebuild over 30%) and then goes on to update statistics based on their last updated date (> 2 days old). This maintenance occurs very early Monday morning before any processing begins and finishes without error.
After some investigation I found that for each time the query is run successfully, it puts a new plan in the cache. I guess this explains why it gets better as the week progresses. What I think is happening is that when the maintenance task runs, the query plans are being removed. The non-clustered index that the query is using is only being reorganised, not rebuilt as the growth causing fragmentation each week is not enough to trigger the rebuild. The stats for this index are being updated ok.
My questions are these:
If posting the query or an example of one of the plans helps, please let me know.
January 19, 2015 at 11:52 pm
sashby78 (1/19/2015)
My questions are these:- Do query plans using an index that have its stats updated get trashed when the stats are updated?
Yes
- Why in a query that uses a date/time and guid in the where clause is it creating a plan for each (well most of them at least) query? Note that it’s not using parametrisation, its inline SQL. It looks as if it’s storing the datetime and guid in the query plan.
Yup, it's not parameterised therefore the values for the datetime and guid are part of the query text and hence used to locate matching plans. When SQL's trying to find a matching plan for a ad-hoc query, it's a text match (white-space sensitive) between the incoming query and the queries stored with the plans in cache. Different guid = different query = different plan.
This is (one reason) why parameterising queries is a good idea. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2015 at 4:53 pm
Thanks for your response Gail!
We have moved it from inline SQL to a stored proc which seems to be doing the job. Would you recommend referring to the passing in parameters directly in the query or transferring them to local variables then use them in the query? From past experience I've had similar issues with values stored in plans when using the parameters passed in directly.
January 22, 2015 at 1:27 am
A stored procedure has a single plan, parameters will not cause a procedure to have multiple plans.
Write queries and procedures in the simplest way, only if testing shows a problem should you consider more complicated alternatives.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply