Updating sql data cube in sql2016 australian single dating
To overcome this issue, trace flag 2371 was introduced in SQL Server 2008 R2 SP1 that overrides the default threshold of the auto update statistics feature for tables with more than 25,000 rows.
If you turn on this trace flag, the new threshold to update the statistics will be lower for tables with a high number of rows.
For instance, if a field is defined as CHAR(10) and you update the value ‘Kris’ into this column, then it will be padded with six spaces.
In our case, the table has 60,000 rows, so more than 12,000 records should be modified to update the statistics, but using SQL Server 2016 the database engine will override this rule for large tables.
If we query the compatibility_level property of our database from the sys.databases system table we can see the current compatibility level: Although we are using SQL Server 2016 version, we need to make sure the compatibility level of the database is (130) which is the default compatibility level for newly created databases in SQL Server 2016.
In the case of large tables, such as a table with 1 million rows, more than 200,000 records need to be updated in order to update the table statistics.
This can lead to performance problems due to bad query plans created by the query optimizer that still uses the old statistics.
I have an Analysis Service database which I want to process it's cube regularly using sql database engine Job Agent, I scripted my cube process and paste it as step for job and continue it's configuration exactly as written in here https://msdn.microsoft.com/en-us/library/ff929186when I execute this job it gives me this error I Use SQL Server 2016 the cube exist because when I run the commands that pasted as job step on SSAS it works fine but it's problematic from SSMS Job Agent, I searched the issue and as written here https://msdn.microsoft.com/en-us/library/ms175426I created a role in my SSAS database and gave it all of the permissions see picture below And in Membership tab I specified following users hoping the issue resolved see picture below but it didn't work and still same error show up when I execute my job I even added NT SERVICE\SQLAgent$SQL2016 in security tab of Analysis Service Properties see picture below but still the same what else can I do to fix this???