I should probably give the SQL Queries as well fora anyone without a DBA on hand :) This is for SQL 2005 db:
To check index size: -go to your project database -right click on IO_PGE table -choose Properties -choose Storage -look for "Row Count" and "Index Space" (our 'Row Count' was 1.5 million rows and our 'Index Space' was at 500MB, to give you some frame of reference) To check fragmentation on your table: --select your project database, right click it, "new query" --the query is: DBCC SHOWCONTIG (IO_PGE) --execute (our "Scan Density" came back at almost 0% and our 'Logical Scan Fragmentation' came back at 99%, for reference. Ideally you'd want these numbers reversed.....ScanDensity should be near 100 and Fragmentation should be near 0). To rebuild IO_PGE table index while online (i.e.- not affecting your production users): -go to your project database -right click on project database name -choose New Query -enter: ALTER INDEX ALL ON IO_PGE REBUILD WITH(ONLINE = ON) DBCC SHOWCONTIG (IO_PGE) Now it will have reindexed and show your *new* fragmentation info. You want Logical Scan Fragmentation to be as close as possible to 0. You want Scan Density [Best Count:Actual Count]. as close as possible to 100. Check the index size again. Row size won't change but index size should be somewhat reduced. Ours went from 500+ MB to around 350MB. Also when you do DBCC SHOWCONTIG (IO_PGE) you should see siginificant difference. After reindexing our IO_PGE, our 'Scan Density' went from about 0.3% to 97.5%. Our 'Logican Scan Fragmentation' went from 99% to 0.36%. Be sure to repeat the above process for each of these frequently used tables: --IO_PAG (pages table) --IO_PAG_<LGV> (Language-dependent pages table) --IO_PGE (Page elements table) --IO_VAL_<LGV> (Page element content, language-dependent table) --IO_VER_<LGV> (Page versions, language-dependent table) --IO_REL (Links table) Hope that helps, Kelly Alz.org On Aug 12, 9:21 am, Kelly <[email protected]> wrote: > If so, you may be benefitted by the below information - it definitely > resolved the issues for us. > > Also, although very helpful most of the time, RedDot Support never > mentioned this to us - and it would have saved me a great deal of time > and frustration if they had. I'm posting it here in case someone else > has this same issue. > > Environment: CMS 7.5 on Windows Server with SQL 2005 database > > Problem: > RD export kept running and dying without finishing. I would start it > at 4pm and the following morning it was still trying to run (17 hours) > and kept our project "locked up" so that editors could not get in. > Checked RD Export log. It ran until it reached IO_PGE. At this > point, it stopped. > > Checked table IO_PGE in our production project - this is a table that > maintains all of your Element information. Itt was using 500 MB > *just* for indexing that table. The table itself showed 99% > fragmentation. Not good. Learned from the web page given below that > IO_PGE Is a very frequently used table in RedDot and therefore it > should be getting RE INDEXED frequently to prevent this very problem > from happening. Our project has been running since 2006 without any > reindexing so clearly that was the issue. We were of course never > instructed to do this by the vendor or our consulting firm as a > necessary part of maintenance - so we had to 'learn the hard way'. > > Resolution: > We reindexed the 6 most frequently used table in our most heavily used > RedDot project so that they don’t become enormous and slow down > performance on the whole system. This took about 30 minutes or less > and did not lock out any users nor did it stop the system. Apparently > with SQL 2005, you can do an "online reindex" - reindexing while your > users are actively using the tables/database. > > Also, our DBA put a script in place that checks all the databases on > this server WEEKLY to ensure none go over a specific index size and > none become highly fragmented again. This should be done regularly by > everyone who runs a SQL Server in order to maximize performance and as > part of general housekeeping tasks for RedDot. > > Result: > After the reindexing, our export ran quickly and was successfully > completed in a matter of minutes. > > Specific Instructions: > > The below is copied from an IT department page at South East Missouri > State University (thank you, SEMO.edu for putting this great resource > online!) There is a TON of great information on this website - very > helpful! > > From:http://cmsserver.semo.edu/cms/Help/wsms-h-igd/en/html/jsframe.htm?aid057 > > 2.5.5 – Changing the Frequency of Management Server Tables > > If Management Server slows down over time, you may find it helpful to > delete the indexes for certain tables and then create them again. This > procedure is only recommended for tables whose content changes often, > such as for the project tables. The tables of the IOAdministration > administration database change rarely, while the tables in the > IOApplication application database do not change at all. > Which project tables change can vary widely from project to project > and depends on whether certain features are used, such as permissions, > workflows, translation workflows, versioning, or asset versioning. You > have to assume that all the tables will change when you set up a new > project, however. > Data is added or changed extremely frequently in the following project > tables: > IO_PAG Pages > > IO_PAG_<LGV> Language-dependent pages > > IO_PGE Page elements > > IO_VAL_<LGV> Page element content, language-dependent > > IO_VER_<LGV> Page versions, language-dependent > > IO_REL Links -- You received this message because you are subscribed to the Google Groups "RedDot CMS Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/reddot-cms-users?hl=en.
