Translate

Tuesday, December 26, 2017

Database Re-Index script

Hi, Below is the script to re-build the database indesxes- to imporove performance of the system. hope this helps




SET nocount ON;

DECLARE @objectid INT;

DECLARE @indexid INT;

DECLARE @partitioncount BIGINT;

DECLARE @schemaname NVARCHAR(130);

DECLARE @objectname NVARCHAR(130);

DECLARE @indexname NVARCHAR(130);

DECLARE @partitionnum BIGINT;

DECLARE @partitions BIGINT;

DECLARE @frag FLOAT;

DECLARE @command NVARCHAR(4000);



-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT OBJECT_ID AS objectid,

INDEX_ID AS indexid,

PARTITION_NUMBER AS partitionnum,

AVG_FRAGMENTATION_IN_PERCENT AS frag

INTO #work_to_do

FROM sys.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, 'LIMITED')

WHERE AVG_FRAGMENTATION_IN_PERCENT > 10.0

AND INDEX_ID > 0;



-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR

SELECT *

FROM #work_to_do;



-- Open the cursor.

OPEN partitions;



-- Loop through the partitions.

WHILE ( 1 = 1 )

BEGIN;

FETCH next FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0

BREAK;

SELECT @objectname = QUOTENAME(o.NAME),

@schemaname = QUOTENAME(s.NAME)

FROM sys.OBJECTS AS o

JOIN sys.SCHEMAS AS s

ON s.SCHEMA_ID = o.SCHEMA_ID

WHERE o.OBJECT_ID = @objectid;

SELECT @indexname = QUOTENAME(NAME)

FROM sys.INDEXES

WHERE OBJECT_ID = @objectid

AND INDEX_ID = @indexid;

SELECT @partitioncount = COUNT (*)

FROM sys.PARTITIONS

WHERE OBJECT_ID = @objectid

AND INDEX_ID = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON '

+ @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON '

+ @schemaname + N'.' + @objectname

+ N' REBUILD WITH(ONLINE = ON, MAXDOP = 8)';

IF @partitioncount > 1

SET @command = @command + N' PARTITION='

+ CAST(@partitionnum AS NVARCHAR(10));

begin try

EXEC (@command);

end try

begin catch

print 'whoops'

end catch



PRINT N'Executed: ' + @command;

END;



-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;



-- Drop the temporary table.

DROP TABLE #work_to_do;

GO



sp_updatestats

Wednesday, May 3, 2017

List of table Ids in AX- SQL dictionary

Hi,


If you ever want to know where all table Id of AX tables are stored? here is the answer its SQLDICTIONARY


here is a query to know the table ID for VendTable

select tabId from sql where sql.sqlName=='VendTable'
&& sql.array==0;

Hope this Helps!
Prasan

Tuesday, March 14, 2017

Power BI documents

Hi,

Found some interesting topics that would help to explore much on data analysis and reporting.

Microsoft Power BI Documents

Introducing Microsoft Power BI, by Marco Russo and Alberto Ferrari:
http://www.sqlbi.com/books/introducing-microsoft-power-bi/

Power BI tutorial videos from Adam Saxton:
 
hope you get on to this.
 
Thanks,
Prasan

Monday, March 13, 2017

Dynamics AX 7 Wikipedia

Hi folks,

Below is the link where you can find documentation of AX 7.0 and dynamics 365 for operations. Happy Daxing!!

https://ax.help.dynamics.com/en/

Thanks,
Prasan