Index Usage
in [Navision]
|
From: JYX on 25 Aug 2008 09:54 Hi, I'm doing index usage analysis on Nav 5.0 database to try to identify unuseful indexes, therefore I can remove them to improve performance. There are few tables/indexes like this: Company_Name$99000852$1_hlp_idx Company_Name$99000852$0_hlp_idx Company_Name$5802$2_hlp_idx These ones have high user_updates but 0 user_seeks or user_scans. What are these tables/indexes for? and Can I drop them like "normal" name indexes? Thanks for you help!
From: JYX on 25 Aug 2008 11:02 Hey Daniel, I'm not drop indexes from SQL, but I analyze it from SQL. There are some good posts on MS team blog on how to check it from SQL (using DMV) for NAV. I dropped indexes from Nav by uncheck MaintainSQLIndex. I don't know these ones - the ones with numbers in the name, that's why I'm posting the question here. But thanks for cautioning me, and I'll leave them alone now that I know what they're for. Regards, JYX "Daniel Rimmelzwaan" wrote: > I've replied to you before to not manually drop anything directly on SQL > Server, because that can cause severe issues in the NAV object definitions. > Drop those indexes at your own risk. I'm not questioning your skills on SQL > Server, but I've seen issues where object definitions are screwed up beyond > repair because someone was modifying table definitions manually on SQL > Server. ALL table design tasks, with the exception of ADDING indexes, should > be done in the NAV table designer. > > The tables named 'companyname$tablenumber$SIFTIndexnumber' are there to > store SIFT bucket values. You can search www.mibuso.com for a ton of > articles about this topic. The indexes on those tables are automatically > generated by NAV, and should not be touched at all. > > "JYX" <JYX(a)discussions.microsoft.com> wrote in message > news:76765061-A494-4AC1-8B1A-8EE4DAF83E04(a)microsoft.com... > > Hi, > > I'm doing index usage analysis on Nav 5.0 database to try to identify > > unuseful indexes, therefore I can remove them to improve performance. > > There > > are few tables/indexes like this: > > Company_Name$99000852$1_hlp_idx > > Company_Name$99000852$0_hlp_idx > > Company_Name$5802$2_hlp_idx > > These ones have high user_updates but 0 user_seeks or user_scans. What are > > these tables/indexes for? and Can I drop them like "normal" name indexes? > > > > Thanks for you help! > > >
From: JYX on 25 Aug 2008 13:44 Hi Daniel, What about the MaintainSIFTIndex property? I've seen tons of posts/tips recommending remove them unless quick sum is top priority. My question is: is it safe to remove them? e.g. won't cause any AL code run time error. I know Nav 5.0sp1, SIFT implementation has changed from SIFT table to SIFT views (indexed view), and Nav is smart enough to issue the proper SQL statement based on whether this property is checked or not, so that the value will be retrieved either based on the SIFT view or the underlying table directly. But what about Nav 5.0, is it safe to just disable the property? Thanks! "Daniel Rimmelzwaan" wrote: > OK cool :). Sorry for being a bit persistent about this, but not everyone is > as cautious as you seem to be, and I've seen some pretty bad problems. Those > DMV's are great tools to get to index statistics aren't they. > > "JYX" <JYX(a)discussions.microsoft.com> wrote in message > news:3AECBF04-09B9-4306-AB21-448F649B5F98(a)microsoft.com... > > Hey Daniel, > > I'm not drop indexes from SQL, but I analyze it from SQL. There are some > > good posts on MS team blog on how to check it from SQL (using DMV) for > > NAV. I > > dropped indexes from Nav by uncheck MaintainSQLIndex. I don't know these > > ones > > - the ones with numbers in the name, that's why I'm posting the question > > here. But thanks for cautioning me, and I'll leave them alone now that I > > know > > what they're for. > > > > Regards, > > JYX > > > > "Daniel Rimmelzwaan" wrote: > > > >> I've replied to you before to not manually drop anything directly on SQL > >> Server, because that can cause severe issues in the NAV object > >> definitions. > >> Drop those indexes at your own risk. I'm not questioning your skills on > >> SQL > >> Server, but I've seen issues where object definitions are screwed up > >> beyond > >> repair because someone was modifying table definitions manually on SQL > >> Server. ALL table design tasks, with the exception of ADDING indexes, > >> should > >> be done in the NAV table designer. > >> > >> The tables named 'companyname$tablenumber$SIFTIndexnumber' are there to > >> store SIFT bucket values. You can search www.mibuso.com for a ton of > >> articles about this topic. The indexes on those tables are automatically > >> generated by NAV, and should not be touched at all. > >> > >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message > >> news:76765061-A494-4AC1-8B1A-8EE4DAF83E04(a)microsoft.com... > >> > Hi, > >> > I'm doing index usage analysis on Nav 5.0 database to try to identify > >> > unuseful indexes, therefore I can remove them to improve performance. > >> > There > >> > are few tables/indexes like this: > >> > Company_Name$99000852$1_hlp_idx > >> > Company_Name$99000852$0_hlp_idx > >> > Company_Name$5802$2_hlp_idx > >> > These ones have high user_updates but 0 user_seeks or user_scans. What > >> > are > >> > these tables/indexes for? and Can I drop them like "normal" name > >> > indexes? > >> > > >> > Thanks for you help! > >> > > >> >
From: Daniel Rimmelzwaan on 25 Aug 2008 15:20 I wouldn't just turn it off completely, but it is possible to tweak SIFT usage to only maintain the levels that are actually used, and tyo eliminate redundant SIFT levels. You can do this by enabling and disabling SIFT levels in the SIFTLevels property. The difference between enabling and disabling that property is that with it turned on, the system will retrieve SIFT totals from the SIFT table, and with it turned off, it will get it from the source table itself. Take a look atht e standard 5.0 table 32 (Item Ledger Entry). Keys number 5 and 6 are almost identical, and they both have SumIndexFields. If you look at the SIFTLevels for these two keys, you will see that some of them are completely duplicated. You can turn off either one of the duplicate SIFT levels without affecting the system's capability to total the sumindexfields, but it will become a little bit faster, because it won't have to maintain those SIFT levels more than once. You can investigate where each SIFT key is used, and determine which SIFT levels are not used. For instance, if you add a key for 5 key fields, and you add a sumindex field, the system will maintain 5 SIFT buckets, one for field1, one for field1 and field2, one for fields 1, 2, and 3, etcetera. If you can determine that the system always filters all 5 fields, then all 4 lower level SIFT levels will never be used, and it is wasted resource to maintain them. "JYX" <JYX(a)discussions.microsoft.com> wrote in message news:E2FF0B6D-F760-4826-B4A5-804A712D65F8(a)microsoft.com... > Hi Daniel, > What about the MaintainSIFTIndex property? I've seen tons of posts/tips > recommending remove them unless quick sum is top priority. > My question is: is it safe to remove them? e.g. won't cause any AL code > run > time error. I know Nav 5.0sp1, SIFT implementation has changed from SIFT > table to SIFT views (indexed view), and Nav is smart enough to issue the > proper SQL statement based on whether this property is checked or not, so > that the value will be retrieved either based on the SIFT view or the > underlying table directly. But what about Nav 5.0, is it safe to just > disable > the property? > > Thanks! > > "Daniel Rimmelzwaan" wrote: > >> OK cool :). Sorry for being a bit persistent about this, but not everyone >> is >> as cautious as you seem to be, and I've seen some pretty bad problems. >> Those >> DMV's are great tools to get to index statistics aren't they. >> >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message >> news:3AECBF04-09B9-4306-AB21-448F649B5F98(a)microsoft.com... >> > Hey Daniel, >> > I'm not drop indexes from SQL, but I analyze it from SQL. There are >> > some >> > good posts on MS team blog on how to check it from SQL (using DMV) for >> > NAV. I >> > dropped indexes from Nav by uncheck MaintainSQLIndex. I don't know >> > these >> > ones >> > - the ones with numbers in the name, that's why I'm posting the >> > question >> > here. But thanks for cautioning me, and I'll leave them alone now that >> > I >> > know >> > what they're for. >> > >> > Regards, >> > JYX >> > >> > "Daniel Rimmelzwaan" wrote: >> > >> >> I've replied to you before to not manually drop anything directly on >> >> SQL >> >> Server, because that can cause severe issues in the NAV object >> >> definitions. >> >> Drop those indexes at your own risk. I'm not questioning your skills >> >> on >> >> SQL >> >> Server, but I've seen issues where object definitions are screwed up >> >> beyond >> >> repair because someone was modifying table definitions manually on SQL >> >> Server. ALL table design tasks, with the exception of ADDING indexes, >> >> should >> >> be done in the NAV table designer. >> >> >> >> The tables named 'companyname$tablenumber$SIFTIndexnumber' are there >> >> to >> >> store SIFT bucket values. You can search www.mibuso.com for a ton of >> >> articles about this topic. The indexes on those tables are >> >> automatically >> >> generated by NAV, and should not be touched at all. >> >> >> >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message >> >> news:76765061-A494-4AC1-8B1A-8EE4DAF83E04(a)microsoft.com... >> >> > Hi, >> >> > I'm doing index usage analysis on Nav 5.0 database to try to >> >> > identify >> >> > unuseful indexes, therefore I can remove them to improve >> >> > performance. >> >> > There >> >> > are few tables/indexes like this: >> >> > Company_Name$99000852$1_hlp_idx >> >> > Company_Name$99000852$0_hlp_idx >> >> > Company_Name$5802$2_hlp_idx >> >> > These ones have high user_updates but 0 user_seeks or user_scans. >> >> > What >> >> > are >> >> > these tables/indexes for? and Can I drop them like "normal" name >> >> > indexes? >> >> > >> >> > Thanks for you help! >> >> > >> >> >>
From: JYX on 25 Aug 2008 15:46 Hi Daniel, Thanks for the quick response. Yes, I agree that one should not be blindly turn off all SIFT. I'm aware of the impact performance wise both ways, e.g. to the reads and writes with and without SIFT. But what I was not 100% sure that, put performance aside, if turning off SIFT could generate any AL side runtime errors? It looks like in Nav 5 sp1, it should not be an issue, but I want to make sure it's also safe in this regard in Nav 5.0 as well. Can you confirm this? Thanks! "Daniel Rimmelzwaan" wrote: > I wouldn't just turn it off completely, but it is possible to tweak SIFT > usage to only maintain the levels that are actually used, and tyo eliminate > redundant SIFT levels. You can do this by enabling and disabling SIFT levels > in the SIFTLevels property. The difference between enabling and disabling > that property is that with it turned on, the system will retrieve SIFT > totals from the SIFT table, and with it turned off, it will get it from the > source table itself. > > Take a look atht e standard 5.0 table 32 (Item Ledger Entry). Keys number 5 > and 6 are almost identical, and they both have SumIndexFields. If you look > at the SIFTLevels for these two keys, you will see that some of them are > completely duplicated. You can turn off either one of the duplicate SIFT > levels without affecting the system's capability to total the > sumindexfields, but it will become a little bit faster, because it won't > have to maintain those SIFT levels more than once. > > You can investigate where each SIFT key is used, and determine which SIFT > levels are not used. For instance, if you add a key for 5 key fields, and > you add a sumindex field, the system will maintain 5 SIFT buckets, one for > field1, one for field1 and field2, one for fields 1, 2, and 3, etcetera. If > you can determine that the system always filters all 5 fields, then all 4 > lower level SIFT levels will never be used, and it is wasted resource to > maintain them. > > "JYX" <JYX(a)discussions.microsoft.com> wrote in message > news:E2FF0B6D-F760-4826-B4A5-804A712D65F8(a)microsoft.com... > > Hi Daniel, > > What about the MaintainSIFTIndex property? I've seen tons of posts/tips > > recommending remove them unless quick sum is top priority. > > My question is: is it safe to remove them? e.g. won't cause any AL code > > run > > time error. I know Nav 5.0sp1, SIFT implementation has changed from SIFT > > table to SIFT views (indexed view), and Nav is smart enough to issue the > > proper SQL statement based on whether this property is checked or not, so > > that the value will be retrieved either based on the SIFT view or the > > underlying table directly. But what about Nav 5.0, is it safe to just > > disable > > the property? > > > > Thanks! > > > > "Daniel Rimmelzwaan" wrote: > > > >> OK cool :). Sorry for being a bit persistent about this, but not everyone > >> is > >> as cautious as you seem to be, and I've seen some pretty bad problems. > >> Those > >> DMV's are great tools to get to index statistics aren't they. > >> > >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message > >> news:3AECBF04-09B9-4306-AB21-448F649B5F98(a)microsoft.com... > >> > Hey Daniel, > >> > I'm not drop indexes from SQL, but I analyze it from SQL. There are > >> > some > >> > good posts on MS team blog on how to check it from SQL (using DMV) for > >> > NAV. I > >> > dropped indexes from Nav by uncheck MaintainSQLIndex. I don't know > >> > these > >> > ones > >> > - the ones with numbers in the name, that's why I'm posting the > >> > question > >> > here. But thanks for cautioning me, and I'll leave them alone now that > >> > I > >> > know > >> > what they're for. > >> > > >> > Regards, > >> > JYX > >> > > >> > "Daniel Rimmelzwaan" wrote: > >> > > >> >> I've replied to you before to not manually drop anything directly on > >> >> SQL > >> >> Server, because that can cause severe issues in the NAV object > >> >> definitions. > >> >> Drop those indexes at your own risk. I'm not questioning your skills > >> >> on > >> >> SQL > >> >> Server, but I've seen issues where object definitions are screwed up > >> >> beyond > >> >> repair because someone was modifying table definitions manually on SQL > >> >> Server. ALL table design tasks, with the exception of ADDING indexes, > >> >> should > >> >> be done in the NAV table designer. > >> >> > >> >> The tables named 'companyname$tablenumber$SIFTIndexnumber' are there > >> >> to > >> >> store SIFT bucket values. You can search www.mibuso.com for a ton of > >> >> articles about this topic. The indexes on those tables are > >> >> automatically > >> >> generated by NAV, and should not be touched at all. > >> >> > >> >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message > >> >> news:76765061-A494-4AC1-8B1A-8EE4DAF83E04(a)microsoft.com... > >> >> > Hi, > >> >> > I'm doing index usage analysis on Nav 5.0 database to try to > >> >> > identify > >> >> > unuseful indexes, therefore I can remove them to improve > >> >> > performance. > >> >> > There > >> >> > are few tables/indexes like this: > >> >> > Company_Name$99000852$1_hlp_idx > >> >> > Company_Name$99000852$0_hlp_idx > >> >> > Company_Name$5802$2_hlp_idx > >> >> > These ones have high user_updates but 0 user_seeks or user_scans. > >> >> > What > >> >> > are > >> >> > these tables/indexes for? and Can I drop them like "normal" name > >> >> > indexes? > >> >> > > >> >> > Thanks for you help! > >> >> > > >> >> > >> >
|
Next
|
Last
Pages: 1 2 Prev: ODBC-error when try to select database in nav-client Next: About Totals and TOTALSCAUSEDBY |