| Author |
Topic |
|
LaurieCox
158 Posts |
 Posted - 2015-03-10 : 10:50:06
|
| We have a nightly job that until last week averaged about 2 1/2 hours. Starting last week it is now averaging over 3 1/2 hours. I have identified the two steps in the job (each taking about 1/2 hour longer than they used to) that are causing the problem but don't know what to look for. The tables they reference have not changed, nor has there been an increase in the table sizes. Most of the tables they pull from are not indexed (besides primary keys) but that is not new.The job has been in production for a couple of years with a few tweaks here and there (though nothing big). The last change was a couple of months ago to add a new step but at the time it did not affect the average run time (the new step averages less than a minute) and is not one of the problem steps.There have been no new scheduled jobs added lately. I asked at our staff meeting if anybody knew of any changes that happened last week and nobody could think of anything.But obviously something has changed. So my question is what things should I look at to find out what changed and caused the job to start running longer?Note I am not a DBA. I spend most of my time writing queries and manipulating data.We are using version 2008 r2.Thanks,Laurie |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-10 : 10:57:11
|
| Have you tried rebuilding the indexes or even just updating the statistics? |
 |
|
|
LaurieCox
158 Posts |
Posted - 2015-03-10 : 11:38:41
|
| Thanks for your reply. The tables the job pulls from are rebuilt every day. In a job that runs just before this one we pull production data from the production server into a "data store". We drop and recreate all of the tables in the data store. Besides primary keys we do not pull/create indexes. The first step in the job than pulls data from the newly built tables in the data store and creates more tables. This step is not one of the offending steps and is not running any longer than usual. The offending steps then run and pull data both from the data store and from the tables built in the preceding step.I have nothing against "rebuilding the indexes or even just updating the statistics" but all of the tables are brand new so I don't really see how this would help. Would there be any benefit to rebuilding indexes or updating statistics on newly created tables? I would have to add this as step in the job. I am sure it would not help to do it before the tables are rebuilt.The change was so dramatic from one day to the next. For months the job has been taking about 2 1/2 hours to run. Then all of a sudden on March 3rd the job started taking over 3 1/2 hours to run. It seems that something must have happened/changed that day but I have no idea what to look for.I will discuss rebuilding indexes/updating statistics with my boss, but I still don't see how that could be the problem/solution.Laurie |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-10 : 11:48:58
|
| OK. You didn't mention initially that the tables are rebuilt every day. Now I wonder: since you only have a PK on the tables, are the rows being inserted in the PK order? If not, you may see fragmentation, which will affect the following queries. How did the data in those tables change on March 3rd? Also, you should consider appropriate indexes to support the "offending" steps. Examine the query plans for those steps. If you see table scans or clustered index scans (on the PK) that's a clue that you might need additional NCIs. |
|
|
|
LaurieCox
158 Posts |
Posted - 2015-03-10 : 12:23:57
|
quote:
Originally posted by gbritton
… How did the data in those tables change on March 3rd? …
That is an excellent question and first one I asked. The answer as far as I can see is that it didn't. I did not write the package that rebuilds the tables in the data store so I don't know exactly how it is done. But again that did not change between March 2nd and March 3rd. As far as examining the query plans for the offending steps that is not going to be easy. I did not write either of the stored procedures and they are long and complicated. They do a lot of data manipulation. They create a couple of tables for use in reports. There are multiple selects/inserts/updates in each procedure. Again none of this has changed.I do have another task that evolves actually figuring out what these procedures do. While I am doing that I will see if there is anything I can do to optimize them. But even if I do optimize them it still does not solve the mystery of why they all of a sudden started running long.Something must of changed/happened but I don't know enough to know what to look for. Thanks for giving me suggestions and I will look into that stuff for optimization.Laurie |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-10 : 12:35:48
|
| I would start by recompiling the stored procedures as it sounds like a bad plan. Regardless if things don't change, the data has changed and that can sway the optimizer to think a different plan is needed. If recompiling them doesn't work, update stats on the related tables. I know you say they are rebuilt each day, but you need statistics on them. I assume you have auto create stats enabled, but be sure to check that it's the case. It is the default.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
LaurieCox
158 Posts |
Posted - 2015-03-10 : 14:08:02
|
| Thanks for your reply,I had no idea if we had Auto Create Statistics on, but I poked around a bit and under database properties I found it. And on all the databases involved we do have Auto Create and Auto Update Statistics set to True.I will try recompiling one of the offending stored procedures and see if this makes a difference.I still do not like not having an answer to the mystery of what caused the sudden change in run time.Laurie |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-10 : 14:11:20
|
| The mystery can be solved, but it would required in-depth DBA knowledge plus having had certain processes in place to collect data (like WhoIsActive). Not really something we can step you through quickly.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
LaurieCox
158 Posts |
Posted - 2015-03-10 : 14:23:00
|
| I feared as much. I did a quick search on WhoIsActive and it is not something I want to (or have the time to) figure out at the moment (though it does look interesting). So the mystery will have to stay a mystery. I did do the sp_recompile on one of the offending SPs and will see what happens tonight.Thanks for your suggestions,Laurie |
|
|
|
LaurieCox
158 Posts |
Posted - 2015-03-12 : 11:19:06
|
Well I tried the sp_recompile on one of the offending Stored Procedures and the thing has got worse (a lot worse). Yesterday it run for 5 ½ hours. Today it is on its 6th hour and still running.The bol tells me that sp_recompile does:quote:
Causes stored procedures and triggers to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.
So the recompile does not actually happened until the thing is run. So I thought that was what was wrong yesterday and hoped that today would be better. No such luck.Can my recompiling be the thing that made things even worse? If so what can I do about it? I am obviously mucking about in stuff I don't understand. Our Network Admin is looking to see if anything changed last Tuesday. Laurie |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-12 : 13:19:52
|
| If it got worse due to the recompile, then you can try recompiling it again. I would update stats at this point on all affected tables.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Job running long, what to look for? It got worse!