August 13, 2012 at 7:24 am
I am having an issue with our ERP software. Our db platform is MS SQL Sever 2008 64 bit unicode.
One of the sessions in the software that is critical to our manufacturing process has intermittent issues. This process opens a production order and relieves inventory for every item on the part’s bill of material. It is quite intense, and it would not be uncommon for the transaction to take a few minutes to complete. However after a restart of MS SQL Server, the transactions slow down dramatically. Depending on the item, one transaction could take 10 minutes, 30 minutes, maybe even an hour. This continues for some time – days, maybe weeks, and then at some point things speed up. We can’t attribute it to anything in particular, and the software vendor appears to be stumped. The database server does not appear to be taxed, and has plenty of memory - 128gb.
We rebuild indexes once a week, along with updating statistics. Auto-update of statistics is on, as well as auto-create. We don't have a SQL Server Admin in house, so we are at a bit of a loss for what to do here. I realize this isn't a lot of info to go on, and ERP systems are very complex, but given things slow down with a restart of SQL server, we are focusing on that right now.
Any suggestion of things we should check would be great.
Thanks.
August 13, 2012 at 8:22 am
If the massive slowdown was just for the first few hours, I would assume it had something to do with caching. But since you get slow behavior for up to a week or more, then it switches, I'm more inclined to go with something along the lines of bad parameter sniffing. However, based on the amount of information, that's just a wild guess.
To know more, I'd be interested in what the most common wait stats are when it's slow versus when it's fast. Also, I'd be curious if all the performance increases or just certain operations. If it's certain operations, then focus on what queries are called by those operations. If it's the whole system, we need a lot more information for even a wild guess.
"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
August 13, 2012 at 4:31 pm
This does sound like a combination of clearing the procedure cache with the restart and also poorly optimized sql for the actual process itself. You said that you have 128 gb of memory available for this instance, how big are the databases on that instance? Have you looked at the execution plan for the sql being executed?
[font="Courier New"]Michael Connolly
[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply