Monitor open transactions awaiting commit or rollback run Query

 SELECT tst.session_id, [database_name] = db_name(s.database_id)

    , tat.transaction_begin_time

    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 

    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'

        WHEN 2 THEN 'Read-only transaction'

        WHEN 3 THEN 'System transaction'

        WHEN 4 THEN 'Distributed transaction' END

    , input_buffer = ib.event_info, tat.transaction_uow     

    , transaction_state  = CASE tat.transaction_state    

        WHEN 0 THEN 'The transaction has not been completely initialized yet.'

        WHEN 1 THEN 'The transaction has been initialized but has not started.'

        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'

        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'

        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'

        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'

        WHEN 6 THEN 'The transaction has been committed.'

        WHEN 7 THEN 'The transaction is being rolled back.'

        WHEN 8 THEN 'The transaction has been rolled back.' END 

    , transaction_name = tat.name, request_status = r.status

    , tst.is_user_transaction, tst.is_local

    , session_open_transaction_count = tst.open_transaction_count  

    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process

FROM sys.dm_tran_active_transactions tat 

INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id

INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 

LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id

CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib

ORDER BY tat.transaction_begin_time DESC;

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"