Index Usage
in [Navision]
|
From: JYX on 27 Aug 2008 12:44 Hi Daniel, One more question on this: Use your example, if a KEY has 5 fields (F1.. F5), but some of them are not selective, say F3 and F5. Should the SIFT levels be defined as: F1; F1, F2; F1, F2, F4; To maintain less levels (unnecessary levels)? Thanks! "Daniel Rimmelzwaan" wrote: > C/AL will figure out where to get the values, as long as you leave the key > enabled. NAV needs to find the sumindexfield tied to the key is all that's > required. If the key or the SIFT level is not in SIFT tables, it will sum > the values straight from the tables themselves. You can see this yourself by > either running the SQL Profiler or by catching the SQL Statements in the > client monitor. With MaintainSIFT turned on the SQL looks like SUM(s4,s5) > FROM Company$32$1, and with it turned off it will look like > SUM(Quantity,Amount) FROM Company$ItemLedgerEntry. > > Try it in a test database, you'll figure out how it works. by the way, you > can see how the buckets are maintained in the SQL Server tables, that is all > done by INSERT, UPDATE, DELETE triggers on the source tables. Don't modify > any of the T-SQL though :). > > "JYX" <JYX(a)discussions.microsoft.com> wrote in message > news:86DEEAED-8F23-42BC-B524-B8ADC1F3AD45(a)microsoft.com... > > 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! > >> >> >> > > >> >> >> > >> >> > >> >
From: Daniel Rimmelzwaan on 30 Aug 2008 13:03 No you don't disable SIFT levels because they are not selective. You disnable SIFT levels because they are not used. So if you have a key with fields 1, 2, 3, 4, 5, and you always set filters on all 5 fields when calculating flowfields, then the only SIFT level you need is the one for all 5 fields, and you can disable the ones for 1, 1-2, 1-2-3 and 1-2-3-4. "JYX" <JYX(a)discussions.microsoft.com> wrote in message news:F3974054-9772-4615-8F27-191D3997A77B(a)microsoft.com... > Hi Daniel, > One more question on this: > Use your example, if a KEY has 5 fields (F1.. F5), but some of them are > not > selective, say F3 and F5. > Should the SIFT levels be defined as: > F1; F1, F2; F1, F2, F4; > To maintain less levels (unnecessary levels)? > > Thanks! > > "Daniel Rimmelzwaan" wrote: > >> C/AL will figure out where to get the values, as long as you leave the >> key >> enabled. NAV needs to find the sumindexfield tied to the key is all >> that's >> required. If the key or the SIFT level is not in SIFT tables, it will sum >> the values straight from the tables themselves. You can see this yourself >> by >> either running the SQL Profiler or by catching the SQL Statements in the >> client monitor. With MaintainSIFT turned on the SQL looks like SUM(s4,s5) >> FROM Company$32$1, and with it turned off it will look like >> SUM(Quantity,Amount) FROM Company$ItemLedgerEntry. >> >> Try it in a test database, you'll figure out how it works. by the way, >> you >> can see how the buckets are maintained in the SQL Server tables, that is >> all >> done by INSERT, UPDATE, DELETE triggers on the source tables. Don't >> modify >> any of the T-SQL though :). >> >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message >> news:86DEEAED-8F23-42BC-B524-B8ADC1F3AD45(a)microsoft.com... >> > 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! >> >> >> >> > >> >> >> >> >> >> >> >> >> >>
From: JYX on 2 Sep 2008 00:21 Hey Daniel, Nice to see your comments again! Yes, I agree with you. I've been reading a lot on the SIFT thing lately. Correct me if I'm wrong, if I have to eliminate a level(s), then it should be the less granular one(s), because they can be figured out by Nav from the more detailed level aggregation values, right? Maybe it's less optimal in terms of calculation, but it's better than going back to the source table, while it costs less to maintain when the source table is updated. The SIFT table is like a OLAP cube, while f-columns are dimensions and s-columns are measures. JYX "Daniel Rimmelzwaan" wrote: > No you don't disable SIFT levels because they are not selective. You > disnable SIFT levels because they are not used. So if you have a key with > fields 1, 2, 3, 4, 5, and you always set filters on all 5 fields when > calculating flowfields, then the only SIFT level you need is the one for all > 5 fields, and you can disable the ones for 1, 1-2, 1-2-3 and 1-2-3-4. > > "JYX" <JYX(a)discussions.microsoft.com> wrote in message > news:F3974054-9772-4615-8F27-191D3997A77B(a)microsoft.com... > > Hi Daniel, > > One more question on this: > > Use your example, if a KEY has 5 fields (F1.. F5), but some of them are > > not > > selective, say F3 and F5. > > Should the SIFT levels be defined as: > > F1; F1, F2; F1, F2, F4; > > To maintain less levels (unnecessary levels)? > > > > Thanks! > > > > "Daniel Rimmelzwaan" wrote: > > > >> C/AL will figure out where to get the values, as long as you leave the > >> key > >> enabled. NAV needs to find the sumindexfield tied to the key is all > >> that's > >> required. If the key or the SIFT level is not in SIFT tables, it will sum > >> the values straight from the tables themselves. You can see this yourself > >> by > >> either running the SQL Profiler or by catching the SQL Statements in the > >> client monitor. With MaintainSIFT turned on the SQL looks like SUM(s4,s5) > >> FROM Company$32$1, and with it turned off it will look like > >> SUM(Quantity,Amount) FROM Company$ItemLedgerEntry. > >> > >> Try it in a test database, you'll figure out how it works. by the way, > >> you > >> can see how the buckets are maintained in the SQL Server tables, that is > >> all > >> done by INSERT, UPDATE, DELETE triggers on the source tables. Don't > >> modify > >> any of the T-SQL though :). > >> > >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message > >> news:86DEEAED-8F23-42BC-B524-B8ADC1F3AD45(a)microsoft.com... > >> > 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! > >> >> >> >> > > >> >> >> >> > >> >> >> > >> >> > >> >
From: Daniel Rimmelzwaan on 2 Sep 2008 06:38 Kind of, yes, but not completely. Don't try to look at SIFT with BI glasses though, it's not completely the same. There are a few things to remember about less granular SIFT levels: 1: The less granular the SIFT level, the more likely it will be that the SIFT table is locked. If two people are entering a sales invoice for the same Item at the same time, and you only keep totals for Items in the ILE table, then the second person will have to wait until the SIFT is done maintaining. If the Item level is turned off, and the SIFT is maintained for Item and Customer, then they won't be locked out. It's a bit counter intuitive, but it will be faster for SQL to calculate a total from a granular level out of the SIFT table than out of the source table. 2: There's a balance there as well of course, with the number of sumindexfields per SIFT table. You'll have noticed that when NAV issues a SQL command for a SIFT total, it does so for all 's' fields in the key. It doesn't know how to total just one field. What's more, it's not certain which SIFT key will be used for a certain flowfield or calcsums operation, NAV just grabs the first one that has the right 's' and 'f' fields. So it also matters in what order the SIFT keys are listed in the keys form. If you have a SIFT key with just Item, and just one sumindexfield, and NAV seems to always use the one with 10 sumindexfields and 10 key fields, try moving your SIFT index to the top of the list. Not the very top of course, because that is the primary key field. This is where you'll get down to trial and error. Measure performance, make the change, measure performance again, repeat until you're satisfied. "JYX" <JYX(a)discussions.microsoft.com> wrote in message news:C3A46F62-04CF-4412-8D45-7CFB0C1F9753(a)microsoft.com... > Hey Daniel, > Nice to see your comments again! Yes, I agree with you. > I've been reading a lot on the SIFT thing lately. Correct me if I'm wrong, > if I have to eliminate a level(s), then it should be the less granular > one(s), because they can be figured out by Nav from the more detailed > level > aggregation values, right? Maybe it's less optimal in terms of > calculation, > but it's better than going back to the source table, while it costs less > to > maintain when the source table is updated. The SIFT table is like a OLAP > cube, while f-columns are dimensions and s-columns are measures. > > JYX > > "Daniel Rimmelzwaan" wrote: > >> No you don't disable SIFT levels because they are not selective. You >> disnable SIFT levels because they are not used. So if you have a key with >> fields 1, 2, 3, 4, 5, and you always set filters on all 5 fields when >> calculating flowfields, then the only SIFT level you need is the one for >> all >> 5 fields, and you can disable the ones for 1, 1-2, 1-2-3 and 1-2-3-4. >> >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message >> news:F3974054-9772-4615-8F27-191D3997A77B(a)microsoft.com... >> > Hi Daniel, >> > One more question on this: >> > Use your example, if a KEY has 5 fields (F1.. F5), but some of them are >> > not >> > selective, say F3 and F5. >> > Should the SIFT levels be defined as: >> > F1; F1, F2; F1, F2, F4; >> > To maintain less levels (unnecessary levels)? >> > >> > Thanks! >> > >> > "Daniel Rimmelzwaan" wrote: >> > >> >> C/AL will figure out where to get the values, as long as you leave the >> >> key >> >> enabled. NAV needs to find the sumindexfield tied to the key is all >> >> that's >> >> required. If the key or the SIFT level is not in SIFT tables, it will >> >> sum >> >> the values straight from the tables themselves. You can see this >> >> yourself >> >> by >> >> either running the SQL Profiler or by catching the SQL Statements in >> >> the >> >> client monitor. With MaintainSIFT turned on the SQL looks like >> >> SUM(s4,s5) >> >> FROM Company$32$1, and with it turned off it will look like >> >> SUM(Quantity,Amount) FROM Company$ItemLedgerEntry. >> >> >> >> Try it in a test database, you'll figure out how it works. by the way, >> >> you >> >> can see how the buckets are maintained in the SQL Server tables, that >> >> is >> >> all >> >> done by INSERT, UPDATE, DELETE triggers on the source tables. Don't >> >> modify >> >> any of the T-SQL though :). >> >> >> >> "JYX" <JYX(a)discussions.microsoft.com> wrote in message >> >> news:86DEEAED-8F23-42BC-B524-B8ADC1F3AD45(a)microsoft.com... >> >> > 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! >> >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>
First
|
Prev
|
Pages: 1 2 Prev: ODBC-error when try to select database in nav-client Next: About Totals and TOTALSCAUSEDBY |