June 25, 2015 at 12:03 am
I am trying to figure out an issue, hoping experts here can provide some direction.
We have a fairly powerful back-end system. here are the specs
SQL2008R2 SP1 CUM13 RCSI enabled database
100% SSD storage
300GB RAM allocated to SQL 325 total RAM on system
Application: 1st tier ERP system
From the ERP, there are interactive applications. A particular application, when launched, a few days ago, took around 30secs to open and load up data. This is the best detail I can provide so please bear with me on this.
Two weeks later, the same app is now taking 1+ min to open and I am trying to figure out why. The data that the app goes after has not increased. The indexes the app sees have not changed. As a matter of fact, all the tables the app goes after, have been very recently rebuilt.
As of late, I have been rebuilding indexes on some very huge tables (400 million+) records. Has that process taken up some RAM buffers or something along those lines which is causing this delay.
I am planning on restarting the SQL services, and if after that, the issue is resolved, what is that tell me ?
Any feedback is greatly appreciated.
Thank you
SQLJay
June 25, 2015 at 3:40 am
Could be numerous things. A baseline springs to mind as a comparison, but that's a little late now.
Have you traced the start up to see what it is actually doing? It could be some specific query/stored procedure. That could narrow down your search. How many databases? How many applications? If there are other applications hitting the database server, have they experienced any slow down during too?
Any jobs running while starting up - either on the server or some remote job (data feed for reporting / DW, for example).
Could the application configuration have changed which would result in a different start up pattern?
We'll need to narrow down the search a little first.
June 25, 2015 at 4:54 am
There's not enough information to do more than make a few guesses.
The first thing you need to do is capture the query metrics for the queries that are called when the app starts up. Understand which queries are called, how long they take, and what they're execution plans are doing. If the performance degrades over time, capture those metrics again when the queries are slow.
We need to know what's going wrong, not just take stabs at a solution. It could be statistics going out of date. It could be a bad plan in cache caused by bad parameter sniffing (which also goes back around to statistics). It could be resource contention. It could be... Without the data, we're just guessing, so any solution proposed is unlikely to be workable.
To capture the query metrics, I suggest using extended events. They're more lightweight than trace, but, there's no GUI in 2008.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 25, 2015 at 7:53 pm
Grant, Clive,
Thank you for taking the time to provide your feedback.
I thought to go back to the basics in tackling this issue. I identified all the tables associated with this application, then checked when the last time they were rebuilt or check for fragmentation as well as their statistics details.
While the indexes were recently attended to, the update stats looked old so I manually updated stats for each of the five tables. Grant, your reply definitely pointed that out as a possible reason.
In updating the stats, the application now opens again within 30 secs, as it did a couple of weeks ago.
I plan on reading/learning more on this topic and creating a better maintenance plan that will help monitor the stats, and update them, as necessary.
Thanks again,
SQLJay
June 26, 2015 at 4:16 am
Glad to hear it worked out. Sorry I couldn't be specific. To help troubleshoot this in the future, capture the execution plan now while the query is running fast. Then, when you see it run slow again, capture the execution plan before you try to update the statistics. That will tell you a lot about how your statistics and the data changes within them affect your queries and the plans generated by the optimizer.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply