Month: May 2017

Sql server 2017

~ S ~ Leave a comment

New in sql server 2017:

  • possibility to process graph . see here 

 

Graph data processing – this give us , the ability to create two new types of tables

– node  – holds the entities  and could have properties
– edge –  holds the relations between nodes/entities.

There are some hidden columns
    $node_id …

$edge_id
$from_id
$to_id

 
Is adding a new clause = MATCH =
Here is an example from microsoft blog:

SELECT Person2.Name
FROM Person Person1, Friends, Person Person2
WHERE MATCH(Person1-(Friends)->Person2)
AND Person1.Name = ‘John’;

see it here

 

Good stuff , microsoft!

 

S

Stats

~ S ~ Leave a comment

Statistics are one of the most important objects./aspect from SQL Server.

Today, some  small knowledge about it. For deeper and more information, go search Microsoft site for it.

Statistics are the base when optimizer will choose, when it will generate an execution plan.

Statistics can be created:

  •  automatically (if you have AUTO_CREATE_STATISTICS = ON) for an index or if optimizer think it will benefit. ( image a where clause : WHERE city = ‘XXX’ – no index on it, and optimizer will choose to create statistics on it)
  • manually –   CREATE STATISTICS nameStats

You can view this information  and different aspect with following commands:

  • DBCC Show_Statistics
    • WITH STATS HEADER ; WITH HISTROGRAM
  • sys.dm_db_stats_properties
  • sys.dm_db_stats_histrogram
  • sys.stats
  • sys.stats_columns
  • sys.dm_db_incremental_stats_properties
  • STATS_DATE  – this will present last date time when was updated

If you have the option AUTO_UPDATE_STATISTICS = ON, then there is a threshold that will trigger an update of your statistics:

  • regular tables:
    • 0 rows in it – any modification will trigger
    • (0 , 500]  rows –  threshold will be  when you have 500+ modification
    • (500, …) – 20% + 500
  • temp table #
    • [0,6) , threshold  will be 6
    • [6,500] , threshold  = 500
    • (500,…)  – 20% + 500
  • @ variable tables
    • no threshold for recompilation

To change this recompiled threshold, you can use Trace Flag 2371. This is included on , in sql server 2016, compatibility level 130.

So , next time when a query is executed (one that is using the statistics involved), it is checking if statistics are up-to-date. NOW , there are outdated and the compiled plan is removed from cache, then a recompilation is triggered and  recompilation will trigger the update of statistics.

Also, when a query is compiled for the first time, then statistics is also checked for up-do-date. if they are outdated, then also will trigger the auto Update.

To view the modifications in a table, you can use different approach:

  • modification_counter from sys.dm_db_stats_properties
  • rowmodctr from sys.sysindexes – this is an old approach  and could be removed in future
  • leaf_XXX_count columns from sys.dm_db_index_operational_stats

 

Starting with sql server 2014,  we have something new – Incremental Statistics – applying  at partition level.  This means that is scanning the partition to get/update statistics; including the 20% threshold.
SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = ON)  – setting at db level
or at index level –  WITH (STATISTICS_INCREMENTAL=ON)
You can check it with sys.stats – column is_incremental

 

UPDATE STATISTICS can be :

  • FULL SCAN
  • SAMPLE

Rebuilding an index will update statistics with full scan.

 

More information can be written about statistics. This is only my view about it.

ps: I hope this info are accurate.

 

 

S