01-18-2017 09:27 AM
I'm running the latest version of Sage Act! 2013 for Windows, I had a fairly complex SQL Query (Query with multiple sub-queries) I was running from MSSMS as ACTREADER user. The query was taking about 10 seconds to complete before DB Maintenance.
I hadn't ran the DB Maintenance (Repair and Re-index) in quite some time so I decided to run that last night and it completed fine.
But now today when I run my SQL query and it's been running for over 14 minutes and has not completed execution or returned any data yet.
Is there an indexing issue or something here that might cause this? I had read somewhere that reindexing and then updating table statistics (like act db does) can sometimes cause this type of issue.
Any one have any thoughts or experience with this type of issue?
Any help would be appreciated.
01-20-2017 06:33 AM
A clarification question: did you do the DB maintenance found in ACT or did you do something directly on the SQL Server?
I can't speak to what ACT does for the maintenance, but I would guess that it is running a Rebuild Index or Reorganize Index maintenance step on SQL Server. When run, these steps will make sure all of the indexes are up to date and can remove fragmentation in the actual storage. This can help query performance because the query optimizer can scan/seek through an index instead of a table, if there is an applicable index. However, having too many indexes on a table is a bad thing, and having too few can be a bad thing as well, when it comes to performance. Too many can result in the query optimizer having a hard time picking the best one. Too few can result in the optimizer just doing a table scan, which is usually much more expensive.
To your actual issue, I'm wondering if the index maintenance added a lot of data which is causing a long scan, or multiple scans. It's possible that it even removed an index that was being used previously and the optimizer now has to do a table scan instead. It's hard to say what the cause is, because I don't know what's been done in the background on SQL Server. I do know from experience that multiple sub-queries can become slow based on how the optimizer sees them. Sometimes re-writing the query (changing what tables are accessed in which level of the query) can help a lot. Other times it might not make a difference.
One other thing to possibly check for: memory pressure. Is SQL Server maxed out in its buffer pool such that it has to constantly read from disk instead of the cache for the query? Or possibly it's paging memory to the hard drive and has to read from there for the cache.
I hope this helps out, or at least gives you some ideas to go forward.
01-20-2017 06:54 AM
Yup, i ran the ACT DB Maintenance, I believe it Rebuilds the Indexes and then mentions Updating Table Statistics.
I have never done any direct alterations of the ACT SQL Instance or Database, only queries and most of the time I'm querying the Views not the Tables directly.
The only changes to the Act Database we've done are through Act or created Sub-Entities via the SDK, but the queries I'm doing are not on those Sub-Entities.
Do you have any suggestions on commands I could run to check the indexes? Or any other thoughts on things I could check?
I know my server is running at about 60% memory usage. The sqlserver.exe process is running about 1,560,640 K and it's an SQL Server 2008 R2 Express Edition if that makes any difference?
I'm certainly no DBA and still learning about SQL Server so there might be something obvious that I'm missing.
I certainly appreciate your response and I will see if re arranging the sub-queries to see if it speeds up the execution time.
If you have any other thoughts let me know!
01-20-2017 10:01 AM
If you have any questions while looking at the query, I can certainly attempt to help when I have time. I've gone through a number of optimizations like that, but there's usually a point where it's no longer worth taking it further. I've got some pretty ugly ones that take 10-15 seconds each, but when I think about what's all being done, a human would take hours or days to do the same thing. Then I don't care so much that they "take long".