Where do all these statistics come from?

Business Central/NAV Technical Forum
1

Hello!

I found a lot of statistics in a customers database that I don’t recognize. It’s over 38K (!) rows in sysindexes which all have field names as the name of the stats. The name of these stats never contain “$” and they are not auto created. I guess someone hase been using the database tuning advisor or something similar.

So what’s the deal here? Should I just remove all these statistics so I just have those containing an “$” in the name left? I think I should, but I thought I could collect some input first since there’s over 38K of them in the database…

Best x-mas wishes from a really snowy and cold Sweden!

/Lars

2

Hi Lars,

is it really statistics or indexes? Could you give an example?

3

The id for the sales line table in one of the companies are 1269761218.

If I run this: “select * from sysindexes where id = 1269761218”

I will get this (I hope it’s readable):

id status first indid root minlen keycnt groupid dpages reserved used rowcnt rowmodctr reserved3 reserved4 maxlen maxirow OrigFillFactor StatVersion reserved2 FirstIAM impid lockflags pgmodctr keys name statblob maxlen rows
1269761218 2066 0x58CE77000300 1 0x9ACE77000300 956 3 2 20717 26957 26888 82843 1226 0 0 2048 39 50 0 0 0xFA1602000300 0 0 0 NULL MyCompanyName$Sales Line$0 NULL 8000 82843
1269761218 2 0xC05678000300 2 0x225978000300 22 12 2 1121 1139 1133 82843 1226 0 0 126 98 50 0 0 0x291702000300 0 0 0 NULL $1 NULL 8000 82843
1269761218 2 0x887478000300 3 0xCA7478000300 9 8 2 674 691 681 82843 1226 0 0 101 73 50 0 0 0x3B1702000300 0 0 0 NULL $2 NULL 8000 82843
1269761218 2 0x887778000300 4 0xCA7778000300 13 10 2 857 873 865 82843 1226 0 0 127 99 50 0 0 0x4B1702000300 0 0 0 NULL $10 NULL 8000 82843
1269761218 96 NULL 5 NULL 0 1 0 0 0 0 0 1226 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Document No_ NULL 8000 0
1269761218 2 0xB07B78000300 6 0xF27B78000300 9 7 2 678 694 684 82843 1226 0 0 79 51 50 0 0 0x531702000300 0 0 0 NULL $5 NULL 8000 82843
1269761218 96 NULL 7 NULL 0 1 0 0 0 0 0 1226 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Line No_ NULL 8000 0
1269761218 2 0xB87E78000300 8 0xFA7E78000300 22 12 2 1121 1139 1133 82843 1226 0 0 126 98 50 0 0 0x631702000300 0 0 0 NULL $7 NULL 8000 82843
1269761218 96 NULL 9 NULL 0 1 0 0 0 0 0 1402 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL No_ NULL 8000 0
1269761218 96 NULL 10 NULL 0 1 0 0 0 0 0 1402 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Location Code NULL 8000 0
1269761218 96 NULL 11 NULL 0 1 0 0 0 0 0 1401 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Shipment Date NULL 8000 0
1269761218 96 NULL 12 NULL 0 1 0 0 0 0 0 1408 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Shortcut Dimension 1 Code NULL 8000 0
1269761218 96 NULL 13 NULL 0 1 0 0 0 0 0 1401 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Bill-to Customer No_ NULL 8000 0
1269761218 96 NULL 14 NULL 0 1 0 0 0 0 0 1226 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Drop Shipment NULL 8000 0
1269761218 96 NULL 15 NULL 0 1 0 0 0 0 0 1226 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Currency Code NULL 8000 0
1269761218 96 NULL 16 NULL 0 1 0 0 0 0 0 1227 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Variant Code NULL 8000 0
1269761218 96 NULL 17 NULL 0 1 0 0 0 0 0 1226 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Product Group Code NULL 8000 0
1269761218 2 0xB88378000300 18 0xFA8378000300 9 7 2 633 643 639 82843 1226 0 0 89 61 50 0 0 0xB31702000300 0 0 0 NULL $8 NULL 8000 82843
1269761218 96 NULL 19 NULL 0 1 0 0 0 0 0 1401 0 0 0 NULL 0 0 0 NULL 0 0 0 NULL Sell-to Customer No_ NULL 8000 0
1269761218 2 0xB88678000300 20 0xFA8678000300 22 11 2 942 953 951 82843 1226 0 0 114 86 50 0 0 0x111802000300 0 0 0 NULL $3 NULL 8000 82843

All these stats have status 96. Auto stats have been turned off and all created auto stats (WA_Sys) has been removed. I such a case there’s normally only statiscs with the same names as the indexes in the databas, but in this case there’s a lot of other statistics with field names as name.

4

It wasn’t that readable [:$]

Here’s some fewer colums

status indid name rows
2066 1
 MyCompanyName$Sales Line$0 82843
2 2
 $1 82843
2 3 $2 82843
2 4 $10 82843
96 5 Document No_ 0
2 6 $5 82843
96 7 Line No_ 0
2 8 $7 82843
96 9 No_ 0
96 10 Location Code 0
96 11 Shipment Date 0
96 12 Shortcut Dimension 1 Code 0
96 13 Bill-to Customer No_ 0
96 14 Drop Shipment 0
96 15 Currency Code 0
96 16 Variant Code 0
96 17 Product Group Code 0
2 18 $8 82843
96 19 Sell-to Customer No_ 0
2 20 $3 82843
5

Hmmm … sorry, I have no idea …

6

Me neither. So I better get rid of it then :slight_smile:

Can You agree with me that there should only be stats with the same name as the indexes?

7

That looks to me like someone who doesn’t know what they are doing ran some sort of ‘create missing indexes’ script on that database.

If you don’t KNOW what you are doing, then I don’t think you should be doing anything to fix it either. Don’t just start removing indexes because “their names don’t look right”. At the very least create a new database and import those objects into another location so that you can verify which indexes are supposed to be there according to the object design.

8

These are not indexdes. It’s statistics. And they don’t belong there as I see it. At least not from a NAV perspective.

I think I need to ask my client if they know of any “optimization” activity that have created these statistics.

9

I didn’t know that statistics are stored in a table called ‘sysindexes’. I use an external tool to look at that information.

If it is indeed statistics then probably someone ran sp_createstats. If you can verify that the fields are all index columns then they ran sp_createstats ‘indexonly’, and you will want to leave those stats in there. As long as your database does not have autostats, and you have a daily job to update statistics, those stats actually help SQL Server.

10

Lars, do a simple and no-risk test by manually collecting statistics for one table:

  1. Isolate your query for a single table.
  2. In a separate query window, manually collect statistics for that table and its associated objects. For example: “update statistics dbo.mytable with fullscan, all”
  3. Run your query again for that table.

A few notes on the specified command:

  • It does NOT specify the NORECOMPUTE option, such that if AUTO_STATISTICS_UPDATE was disabled for the table then the command above will reenable it.
  • It collects statistics for everything including even non-indexed columns. This simply gives the cost-based optimizer more information when creating an execution plan for a SQL statement. I’ve never had a problem doing this. It just simply make take more elapsed time.
  • It does a full scan, which is equivalent to a sample size of 100%. Again, I’ve never had a problem with this - just takes more elapsed time.

If this gives the desired result, you can run a script to perform this command for every table in the database(s), or you can simple run the Update Statistics task in a Maintenance Plan.