Slower system performance on pulling up reports / slower database performance.

Slower system performance on pulling up reports / slower database performance.:
PROBLEM:
While pulling up connect reports/logging into connect central, system takes a lot time to display data.

REASON:
When connect application uses INSERT, UPDATE, and DELETE statements to modify table data, breeze database indexes can become fragmented. Index fragmentation can happen when the logical sequence of pages is disrupted or when an index page contains less than its maximum amount of data, creating a gap in the data page or index. As indexes become fragmented, connect app get inefficient data reads when accessing tables and slower database performance.
Performing routine database maintenance such as reindexing is important for keeping your breeze databases running at peak performance. 
If your connect server usage/web access is too high then Adobe recommends to re-index your connect database every weekend night to achieve optimal performance.
While searching through the debug.log files , You get the lines similar to  -:

[06-25 01:30:22] https-8443-1700 (INFO) SLOW QUERY: 109ms spid:393 com.macromedia.breeze.sco.DAL.getDetailedMeetingAttendance(meeting,”1046523363″,”1012293661″,Fri Jun 22 16:00:42 CDT 2012,Fri Jun 22 17:46:14 CDT 2012)

[06-25 01:30:24] https-8443-1700 (INFO) SLOW QUERY: 156ms spid:393 com.macromedia.breeze.sco.DAL.getDetailedMeetingAttendance(meeting,”1018351354″,”1012293661″,Fri Jun 22 11:28:15 CDT 2012,Fri Jun 22 12:14:15 CDT 2012)

[06-25 01:36:55] https-8443-1649 (INFO) SLOW QUERY: 437ms spid:926 com.macromedia.breeze.sco.Reports$HeaderQuizQuestionAnswerDistribution.execute(“1070892119″,slide-view,lesson-location,core,1,2,3)

[06-25 01:36:55] https-8443-1649 (INFO) SLOW QUERY: 594ms spid:393 com.macromedia.breeze.sco.Reports$ReportQuizQuestionAnswerDistribution.execute(“1070892119″,slide-view,lesson-location,core)

[06-25 01:41:29] https-8443-1641 (INFO) SLOW QUERY: 328ms spid:393 com.macromedia.breeze.sco.Reports$ReportMyMeetings.execute(“1013634615″,view,mini-host,host,”1012293661″,meeting,”1012293745″,”1013634618″)
Observe the query execution time in ms as highlighted in red fonts. If your server logs is having values equal/greater that the highlighted one then the database must be re-indexed.

SOLUTION:
1. Schedule downtime and stop the connect services.
2. If connect database is located within the connect server , Launch SQL Server Management Studio Express.Skip to point 5
3. If connect database is located on remote server (Typically called as central database server managed by DBA) , Ask your DBA to execute the database re-index command mentioned on point 6
4.By default connect database name is breeze, If you have specified any other database name , Navigate to connect console page which is accessible only in connect server on URL -: http://localhost:8510/console/application-settings/database-settings?account-id=7 Identify the database name and let DBA know about the database name.
5. Launch a new query editor window.
6.Select breeze database.
Execute the below database re-index command on query editor window.
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”

GO
EXEC sp_MSforeachtable @command1=” UPDATE STATISTICS ?”

GO

7.Please note that the re-index process will take sometime depending on your database size.
8. Start the connect service when the re-index process is completed.

You can also follow the link :- http://blogs.adobe.com/connectsupport/adobe-connect-database-maintenance-activity to perform database maintenance activity to wipe off unused table records created on connect database.



DIGITAL JUICE