Kelly. Thank you for sharing. Hats off to you. This would have to be the best post so far.
-Jian On Aug 12, 10:55 am, Kelly <[email protected]> wrote: > 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.
