Update Stats for Table

 SET NOCOUNT  ON 

 DECLARE  @SQLcommand NVARCHAR(512), 

          @Table      SYSNAME 

 DECLARE CurAllTables CURSOR  FOR 

  SELECT table_schema + '.' + table_name 

FROM information_schema.tables T 

       INNER JOIN sys.sysindexes SSI 

ON t.TABLE_NAME = object_name(ssi.id) 

WHERE SSI.rowcnt > 500

    AND SSI.NAME LIKE '%_EN'

 OPEN CurAllTables 

FETCH NEXT FROM CurAllTables 

INTO @Table 

WHILE (@@FETCH_STATUS = 0) 

  BEGIN 

    PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table 

    SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN' 

    EXEC sp_executesql @SQLcommand 

    FETCH NEXT FROM CurAllTables 

    INTO @Table 

  END 

CLOSE CurAllTables 

DEALLOCATE CurAllTables 

SET NOCOUNT  OFF 

GO

Find Index Fragmentation in Table

 SELECT 

S.name as 'Schema_Name',

T.name as 'Table_Name',

I.name as 'Index_Name',

F.Avg_Fragmentation_in_Percent,

F.Page_Count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as F

INNER JOIN sys.tables  as T    ON T.object_id = F.object_id

INNER JOIN sys.schemas as S ON T.schema_id = S.schema_id

INNER JOIN sys.indexes as I   ON I.object_id = F.object_id

AND F.index_id = I.index_id

WHERE F.database_id = DB_ID()

AND I.name IS NOT NULL

AND F.avg_fragmentation_in_percent > 0

ORDER BY F.avg_fragmentation_in_percent DESC

Azure SQL Indexes and Statistics

Maintaining index with low fragmentation is significant performance improvement in any SQL databases.

You can check and download scripts from Azure Database Blog from author  Yochanan Rachamim.

How to maintain Azure SQL Indexes and Statistics - Microsoft Community Hub

How to sort months chronologically in Power BI

It is simple to  get order by month name wise chronologically in PowerBI but if the data comes from AAS Tabular model cube then it is really difficult to get correct order. 

The month data comes like Apr-22, Aug-22, Feb-22, Jan-22 from DIM_DATE. but ideally we want month to Chronologically like  Jan-22, Feb-22, Mar-22....................Dec-22.

Table DIM_DATE and FACT_Sales_Order  based on these two table we have to generate report month wise with format of chronologically.

Database: Azure SQL DB

Cube: Azure Analysis Services

Report : Power BI

If you are using Visual Studio for AAS(Azure Analysis services) Tabular model, 

Select DIM_DATE table first and then choose properties  the column C_Month_Year(text data type) you want display as chronologically for month select Sort By column to Month_Num column( Int data type).

You can generate reports like below sorts months chronologically.

SQL Server Books in my Bookshelf

 

Create and manage tabular model Table partitions in AAS

Table in a tabular model is a single partition by default.  the large table like FACT TABLE with multi million rows are processed quickly if you created partitioned for this one.

DIM Table  : Keep default single partition

FACT Table : Try to create number of partition as you required based on partioned column like (year, or date )

let

    Source = #"SQL/sqlserver_database_windows_net ;Contoso",

    dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data],

    #"Filtered Rows" = Table.SelectRows(dbo_Sales, each [OrderDateKey] >= 20220101 and [OrderDateKey] <= 20221231)

in

    #"Filtered Rows"

Announcing SQL Server 2022 public preview

Announcing SQL Server 2022 public preview: Azure-enabled with continued performance and security innovation is currently available to download public preview. Try latest version of SQL Server 2022 and you will find many cool features.

Announcing SQL Server 2022 public preview: Azure-enabled with continued performance and security innovation - Microsoft SQL Server Blog