Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mike45
Starting Member
2 Posts |
 Posted - 2011-08-27 : 13:12:49
|
| Hi!I have a question regarding performance issues on a SQL 2008 Express 64-bit database.The database in question is serving a Visual Dataflex application to 10 simultaneous users.The first 2 weeks the performance was good(which was the reason why we migrated the VDF app. This wasearlier using the embedded database in VDF).The users now start to complain about the speed(especially when they start the app, and print out a preview of a report to the screen).The latter one use a query to retreive the data.First I suspect there were fragmented indexes. A report showed that there were some of these(one index showed 94 percent fragm.).Tried then to rebuild these indexes that have more than 30 percent fragm. Some of these could'nt get lower than 50 percent after rebuild.My first question: Can these rebuild's have a negative impact on the performance??Second: What diagnoses(tools) should I use, and after that, how should I resolve the problem?I have found very good documentation around the concept on the Internet, but I am a little bit lost in all this info ;-)I am grateful to any suggestions on how I can break down this in a step by step fashion. Best RegardsMikael OlssonSweden |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-27 : 13:29:04
|
| You should also UPDATE STATISTICS on all the tables, it's possible that your statistics are out of date and the cached query plans are no longer optimal. (this is likely since the performance dropped over a period of time)SQL Express has limits on CPU and memory usage compared to the other editions: http://msdn.microsoft.com/en-us/library/cc645993.aspx#ScalabilityThese will hamper performance regardless of index and statistics.If you can, run DBCC FREEPROCCACHE and see if the performance improves. This clears the procedure cache which could help if the cached plan was inefficient. The new compiled plans may have better performance.If all else fails, you can try running the Database Tuning Wizard and see if it has any recommendations for indexes or statistics. Be very careful applying any changes though, you should test them all before deploying them to production. If you have access to the SQL code, you can try hand-optimizing with index or join hints, but this is usually a last resort, and again should be tested thoroughly. You should contact the vendor's support and have them evaluate whether the code can be improved. It's possible they don't support the product on SQL Express, and you should consider upgrading to another edition. |
 |
|
|
Mike45
Starting Member
2 Posts |
Posted - 2011-08-27 : 13:34:27
|
| Hi!Thank you so much for your answer.I try these suggestions Monday morning when I'm back to work.Best RegardsMikael OlssonSweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-27 : 17:00:48
|
| "Visual Dataflex application"This is using SQL Server presumably? (Given your subject title). I used to use DataFlex many years ago - back in the days of DOS. Is it the same animal, upgraded to use SQL Server for the database part perhaps?The part that is bothering me is that you have only been running for 2 weeks ?? and in that time performance has fallen off. Unless you have large numbers of users, or record changes, that seems improbable to me.Definitely worth rebuilding indexes and statistics ... but it might well be something else given only 2 weeks running and assuming not a lot of users / record-changes. If there an application component running on the server (that is running SQL)? Some database "back end" perhaps? If so I wonder if it is trashing memory or somesuch ... |
|
|
|
|
|
|
|
|
Performance degrade SQL Server 2008 Express