June 17, 2014 at 7:35 am
All,
Recently one of the Devs brought to my attention that one of his SQL Agent jobs (which executes a fairly simple SSIS package) was taking almost twice as long as it used too. The package simply truncates several tables in the QA version of the DB, then copies the current production data over into those tables. No indexes are disabled / dropped / created during this process.
Now, nothing has changed on the SQL or OS side on either of these servers around the time the job started taking longer. The *only* thing that I've noticed is that about the day the Dev reported as the start of the problem, the DB had run out of room in it's filegroup. It is set to a limited growth, and had reached that limit.
I did resolve that issue (bumped up the max size by a good bit, it still isn't close to it,) and the package has been working. Indexes have been rebuilt, statistics have been updated, but for some reason since then it's be taking twice as long to run.
Now, admitedly, going from a 3-3.5 minute run to 7-10 minutes isn't an end-of-the-world sort of thing, but...
Any thoughts?
I've asked the Dev to modify the SSRS package to log to a text file, so maybe tomorrow I'll find something there.
Thanks,
Jason
June 17, 2014 at 7:45 am
Maybe your production database got slower 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 8:02 am
Sounds like the amount of data has increased...
June 17, 2014 at 8:03 am
as the database ran out of space sounds like the database grew so there is more data for the SSIS package to process, so probably just a scaling issue.
check how much the database grew by from the backup history.
---------------------------------------------------------------------
June 17, 2014 at 8:28 am
Maybe the statistics are out of date on the QA server leading to different execution plans? I know, truncating tables & all, but... Or, maybe statistics have changed sufficiently in prod that you're getting different query plans?
Just guessing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 17, 2014 at 8:49 am
Koen - You mean 'DBCC TimeWarp(SlowTime)' doesn't speed up response by slowing time for the end-user? Drat...:-D
I don't think Prod has slowed down, as none of the other applications using DBs on this server are having problems. Not ruling it out, though.
Hanshi, George - That's a possibility, especially after taking a look at the backup sizes. The fulls haven't really changed, but there've been some Diffs that are as big or bigger than the Full they'd be based off. (Sun, Wed Fulls, M/Tu/Th/Fri Diffs, TLogs every 2hrs) I've contacted the customer to see if there might be something there...
Grant - Checking the last time Statistics were updated with this:
SELECT
o.name AS TableName
, i.name AS IndexName
, i.type_desc AS IndexType
, STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
FROM
sys.indexes i
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE
o.type = 'U'
AND i.name IS NOT NULL
ORDER BY
o.name, i.type
it shows them as having been updated this morning @ 5:05am. The job fires at 5:00am. Ditto the QA DB. I also have a maintenance plan setup to do a Full Scan update of Stats every Sunday (most all the DBs are primarily read, with few inserts / updates / deletes.) Indexes in Prod get checked every week or two, and if needed I'll manually set up a job to rebuild / reorg as needed. Currently indexes on the DB in question (QA and Prod) are in good shape...
Thanks guys!
June 17, 2014 at 9:00 am
Are those stats updated automatically, or by a maintenance routine? If I read that correctly, the stats are updated AFTER the job starts. What might be happening is that the job starts, using old stats, and realizes that the stats are ready for an update. Query plans are generated using the old stats, and the stats are then auto-updated in parallel.
June 17, 2014 at 9:04 am
A batch job in dev that runs at 5 a.m. for an extra 4 mins - this developer would have to be one of the good ones.........:-)
Its the sizes of the fulls I would be looking at for overall data growth, unless a lot of data is added before the refresh and deleted after. The good old SSMS reports will tell you when file growths happened (disk usage), and disk usage by top table will tell you how big the table involved are, all designed to give you an idea of historical data growth.
---------------------------------------------------------------------
June 17, 2014 at 9:18 am
So, according to the customer, the application usage is right on-line with their historical rates, so it's not had a lot of churn, comparitively. The full backups have been fairly consistent in size as well, and the DB itself hasn't grown.
But (ah, now the "but") they've added some indexes, and the SSIS package has a rebuild index task at the end of it for both the QA and Prod DBs...
So tomorrow, hopefully, with the logging from the SSIS package we should be able to see where the slowdown has come from. If it's the indexing steps, I've already suggested to the Dev we remove those steps, see if that fixes the problem, and if so, monitor the fragmentation to see just how frequently we might need to defrag the indexes, and which indexes. Then I'll work on tweaking Ola Hellengrens' script so we can stick it in the SSIS job as a T-SQL step...
😀
June 17, 2014 at 9:22 am
Aaaah, a classic 'nothing has changed - honest guv' case.
---------------------------------------------------------------------
June 17, 2014 at 10:00 am
You could be seeing something of what you're talking about, but as the data changes, if you have auto update stats turned on, you should see the stats change, although, not necessarily as fast as the data changes. You might need to incorporate a manual update of the statistics into the process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2014 at 6:56 am
Well, the log results are in, and the cause of the time increase?
The Index rebuilds. They're around 1/2 (maybe a bit more) of the total time for the SSIS package execution.
At which point the Dev sends me an e-mail saying "we can live with it, just wanted to know why it started taking longer..."
Ah well, it was fun digging out the cause of it.
June 18, 2014 at 6:58 am
jasona.work (6/18/2014)
Well, the log results are in, and the cause of the time increase?The Index rebuilds. They're around 1/2 (maybe a bit more) of the total time for the SSIS package execution.
At which point the Dev sends me an e-mail saying "we can live with it, just wanted to know why it started taking longer..."
Ah well, it was fun digging out the cause of it.
And a round of applause for including logging in the SSIS packages 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 18, 2014 at 7:34 am
Koen Verbeeck (6/18/2014)
And a round of applause for including logging in the SSIS packages 😀
Amen!!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply