Wow, thanks Jian and Tiffany! You two have far more confidence in me than I do!!! LOL :)
On Aug 12, 10:40 am, tiffany portman <[email protected]> wrote: > "RedDot for Dummies" by Kelly Burns > > working on it right? > :) > > On Fri, Aug 12, 2011 at 10:15 AM, Jian Huang <[email protected]>wrote: > > > > > > > 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. > > -- > 312-515-0480www.tiffanyportman.com > [email protected] Hide quoted text - > > - Show quoted text - -- 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.
