mibuso.com
Home› SQL Performance
Options

table partition & filegroup partition

vuacoronavuacorona Member Posts: 23
edited 2007-07-17 in SQL Performance
Hello All:

I know Navision has one primary data file and one secondary data file. Is it possible if we can split to more secondary data file?

Another question: I have a large table containt few million of records, now if I partition this table to be multiple tables (horizontal partittion), then will Navision be able to access all these tables? How?

Sorry I am not a Navision geek, but just an experienced DBA.


vua.

Comments

  • Options
    SavatageSavatage Member Posts: 7,142
    What exactly are you trying to achieve?

    Do you want to split the database up to increase performance?
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • Options
    vuacoronavuacorona Member Posts: 23
    Yes.

    Would you have any advice?

    Savatage wrote:
    What exactly are you trying to achieve?

    Do you want to split the database up to increase performance?
  • Options
    SavatageSavatage Member Posts: 7,142
    it's all in the details: are you using Sql or Native Database. server size & specs, # of users, etc etc.

    Best thing is to search the forum for "performance" there are 100's of posts to read thru.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • Options
    colin_leversuch-robertscolin_leversuch-roberts Member Posts: 113
    yes you can use multiple filegroups - we have our db on 6 file groups.
    table partitioning - for sql 2005 - yes you should be able to do this - we're looking at this and I've implemented partitioned tables in other applications.
    You would want to make sure you test very very carefully with partitioned tables, there's quite a number of requirements and you'd also need to get the indexing absolutely right to avoid problems.
    Navision would not know the table was partitioned because all this happens "under the covers" - easier than partitioned views in sql 2000 - so implementation would be transparent.
  • Options
    vuacoronavuacorona Member Posts: 23
    colin leversuch-roberts wrote:
    yes you can use multiple filegroups - we have our db on 6 file groups.
    table partitioning - for sql 2005 - yes you should be able to do this - we're looking at this and I've implemented partitioned tables in other applications.
    You would want to make sure you test very very carefully with partitioned tables, there's quite a number of requirements and you'd also need to get the indexing absolutely right to avoid problems.
    Navision would not know the table was partitioned because all this happens "under the covers" - easier than partitioned views in sql 2000 - so implementation would be transparent.

    Sound great colin,

    Do you have some document or know anywhere for me to do some research?

    thanks
  • Options
    colin_leversuch-robertscolin_leversuch-roberts Member Posts: 113
    References:-
    Partitioned Tables and Indexes in SQL Server 2005
    By Kimberly L. Tripp
    http://msdn2.microsoft.com/en-us/library/ms345146.aspx

    I also have a document , unfinished, but i was trying to do much more with regard to the sliding window scenario. I'll have a think about files and filegroups and get back to you
  • Options
    vuacoronavuacorona Member Posts: 23
    colin leversuch-roberts wrote:
    References:-
    Partitioned Tables and Indexes in SQL Server 2005
    By Kimberly L. Tripp
    http://msdn2.microsoft.com/en-us/library/ms345146.aspx

    I also have a document , unfinished, but i was trying to do much more with regard to the sliding window scenario. I'll have a think about files and filegroups and get back to you

    Thank you very much Colin.

    Thanks everyone for reading the message.
  • Options
    fufikkfufikk Member Posts: 104
    As far as I know the more hdd disks you have on separate controllers the more efficient your db can be... If you divide your db into more filegroups backup can be perfomed on particular filegoups...
  • Options
    vuacoronavuacorona Member Posts: 23
    fufikk wrote:
    As far as I know the more hdd disks you have on separate controllers the more efficient your db can be... If you divide your db into more filegroups backup can be perfomed on particular filegoups...

    Absolutely.....

    But if we have some documents to study it would help.

    .
  • Options
    fufikkfufikk Member Posts: 104
    The only thing I know about dividing navi db is "Tuning Navision for better performance" - http://www.mibuso.com/dlinfo.asp?FileID=356

    As for backing up file groups I'd recommend SQL Books on line. So far my client's dbs are not that large so I haven't really used it, except for my own "testing" purposes
  • Options
    vuacoronavuacorona Member Posts: 23
    fufikk wrote:
    The only thing I know about dividing navi db is "Tuning Navision for better performance" - http://www.mibuso.com/dlinfo.asp?FileID=356

    As for backing up file groups I'd recommend SQL Books on line. So far my client's dbs are not that large so I haven't really used it, except for my own "testing" purposes

    Thank you for your input. Our SQL Server database is getting bigger and bigger... so far it reachs 100 GB, so I need to prepare for the worst case. I understand there are some limitation of Navision not like .NET, but we got to do what we got to do right?

    In addition I am in the process of upgrading our SQL 2K to be SQL 2005, then there are few advantage in SQL 2005 as Colin said above.
  • Options
    fufikkfufikk Member Posts: 104
    Hi,
    I came across a interesting set of atricles at MS site, maybe you'll find it helpfull
    http://technet.microsoft.com/en-us/sqlserver/bb331801.aspx

    and mabye a few articles from databasejournal.com - see section about sql high availability
    http://www.databasejournal.com/article.php/1503191
  • Options
    vuacoronavuacorona Member Posts: 23
    fufikk wrote:
    Hi,
    I came across a interesting set of atricles at MS site, maybe you'll find it helpfull
    http://technet.microsoft.com/en-us/sqlserver/bb331801.aspx

    and mabye a few articles from databasejournal.com - see section about sql high availability
    http://www.databasejournal.com/article.php/1503191

    Thanks fufikk, we never learn enough right... I actually subscribed to technet and keep up to date with SQL technology. Unfortunally, my knowledge in Navision is very limited.

    I already have cluster setup in our database. Once we upgrade to SQL 2005 I will addon database mirror.


    Thanks again everyone.
  • Options
    Scott_FrappierScott_Frappier Member Posts: 90
    vuacorona:

    1.) You can definitely add/change filegroups in Dynamics NAV. You cannot do it from the application itself, and you need to be careful if you are modifying the schema from within NAV, but I have implemented this functionality at customer sites without issues. You will need to use a "move script" that will move the indexes and data to the filegroup of your choice, but it can be done.

    2.) I played around with table partitioning in Dynamics NAV 4.0 SP3 on an Item Ledger Entry table...and after modifying the indexes and making it so that it set the "Entry No." as the partition separator, it worked wonderfully. I did not dig in very deeply, but I posted transactions and they worked without issues. I even did it with "Posting Date" but it required the keys to be updated, which I was a bit concerned with because the application sometimes needs to use an explicit key with reports.

    I used the document that Kim used as my basis for the partitioning...it seemed to work without issues, but I would definitely test it in a development environment before releasing...

    Feel free to email me at sfrappier@symtech.us if you have more questions. Also note - 4.0 SP3 Update 06 has some nice performance improvements with Dynamics NAV that will be out later this month (hopefully?). It now utilizes the TOP operator for queries, which can expedite queries (it also removes OPTION FAST on queries that are not forms if I recall correctly).

    - Scott
    Scott Frappier
    Vice President, Deployment Operations

    Symbiant Technologies, Inc.
    http://www.symbiantsolutions.com
  • Options
    vuacoronavuacorona Member Posts: 23
    Scott Frappier wrote:
    vuacorona:

    1.) You can definitely add/change filegroups in Dynamics NAV. You cannot do it from the application itself, and you need to be careful if you are modifying the schema from within NAV, but I have implemented this functionality at customer sites without issues. You will need to use a "move script" that will move the indexes and data to the filegroup of your choice, but it can be done.

    2.) I played around with table partitioning in Dynamics NAV 4.0 SP3 on an Item Ledger Entry table...and after modifying the indexes and making it so that it set the "Entry No." as the partition separator, it worked wonderfully. I did not dig in very deeply, but I posted transactions and they worked without issues. I even did it with "Posting Date" but it required the keys to be updated, which I was a bit concerned with because the application sometimes needs to use an explicit key with reports.

    I used the document that Kim used as my basis for the partitioning...it seemed to work without issues, but I would definitely test it in a development environment before releasing...

    Feel free to email me at sfrappier@symtech.us if you have more questions. Also note - 4.0 SP3 Update 06 has some nice performance improvements with Dynamics NAV that will be out later this month (hopefully?). It now utilizes the TOP operator for queries, which can expedite queries (it also removes OPTION FAST on queries that are not forms if I recall correctly).

    - Scott

    Scott:

    Thank you very much for your kindness, and especially posting your personal email here to help out other. That is wonderful, and I will follow your guide as well as keep your email into my "Navision Geek" black list... :)


    thanks again.
  • Options
    krikikriki Member, Moderator Posts: 9,090
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    colin_leversuch-robertscolin_leversuch-roberts Member Posts: 113
    the use of files and filegroups is mainly for performance or to allow for speed in backups and on-line recovery. ( most with Ent sql 2005 only )
    I would mention that on-line filegroup recovery requires that you do not use the PRIMARY partition for any user objects of data, I have no idea if Navision can support this - it's difficult for most developers to follow!
    The decision to make use of all these technologies is the based upon your application throughput and to a certain extent the database size. I'm not 100% certain on how much benefit filegroups will bring on a 100gb database, typically i might suggest a minimum of a 12 disk raid 10 per filegroup, would it actually go quicker on a 24 disk raid 10 ? You'll only know by testing. On a multi proc box, files per cpu per mdf/ndf will certainly aid write threads, but serialising transactions will still effectively place the database into single user mode - in this case partitioning may help - but then the whole environment becomes more complex to maintain, understand and support.
    I did some work on partitioning, my table resided in 8 filegroups each of which consisted of 8 files, so one table spanned 64 physical files, that makes for complex support - it did work well, BUT I was considering a table which could grow up to 25gb ( or more ) per day.
    There's no fit all answer I'm afraid - and finally putting lots of memory on your server negates physical storage anyway, so if in doubt increase memory.
  • Options
    vuacoronavuacorona Member Posts: 23
    colin leversuch-roberts wrote:
    the use of files and filegroups is mainly for performance or to allow for speed in backups and on-line recovery. ( most with Ent sql 2005 only )
    I would mention that on-line filegroup recovery requires that you do not use the PRIMARY partition for any user objects of data, I have no idea if Navision can support this - it's difficult for most developers to follow!
    The decision to make use of all these technologies is the based upon your application throughput and to a certain extent the database size. I'm not 100% certain on how much benefit filegroups will bring on a 100gb database, typically i might suggest a minimum of a 12 disk raid 10 per filegroup, would it actually go quicker on a 24 disk raid 10 ? You'll only know by testing. On a multi proc box, files per cpu per mdf/ndf will certainly aid write threads, but serialising transactions will still effectively place the database into single user mode - in this case partitioning may help - but then the whole environment becomes more complex to maintain, understand and support.
    I did some work on partitioning, my table resided in 8 filegroups each of which consisted of 8 files, so one table spanned 64 physical files, that makes for complex support - it did work well, BUT I was considering a table which could grow up to 25gb ( or more ) per day.
    There's no fit all answer I'm afraid - and finally putting lots of memory on your server negates physical storage anyway, so if in doubt increase memory.

    Hi Colin and all,

    Our backend hardware is SAN with two cluster servers each containt 16GB of RAM, and we are in the process of upgrading SQL2000 to SQL2005, then we will take the advantage of database mirroring.

    Currently everything is running all right, but as a proactive DBA, I am prepare for the worst case will come.

    Yes we have tables takes more than 10GB of data, and I am planning to break it down.

    Under SQL point of view, partition table, partition file group is not a problem for me. But I don't know if those Navision developers can be able to adapt this technology since I have very little of knowledge in Navision.
Sign In or Register to comment.