Notify me when InventSumDate has Content

The InventSumDateTable and InventSumDateTrans tables are used for some reports in Dynamics AX (at least in 2009).  When the reports finish they delete the records stored in these tables, however if the report is terminated mid-process these records aren't cleaned out and this can cause substantial performance problems for these reports.

I wanted to setup some sort of notification to tell me when there is data in these tables when it's unlikely that someone would be running a report.  I decided I didn't want to automatically clean it out since it's possible they could actually be running a report and because I'd like to restart the AOS at the same time... don't know if it matters, but don't want anything cached.

Here is a the SQL job that I created which will email me when there are records in the table, but won't email me when there isn't.  I scheduled the job to run nightly after the backup completes.  The database name, database mail profile, and recipient email address need to be configured.

USE [AXDATABASE]

GO

 

SET ANSI_NULLS ON

GO

 

DECLARE @RECORD_CHECK INT

 

SELECT  COUNT(RECID) [Count]

INTO    #tmpCountInventSumDate

FROM    InventSumDateTable

 

INSERT  #tmpCountInventSumDate

        ( [Count]

        )

        SELECT  COUNT(RECID)

        FROM    InventSumDateTrans

 

SET @RECORD_CHECK = ( SELECT    SUM([Count])

                      FROM      #tmpCountInventSumDate

                    )

DROP TABLE #tmpCountInventSumDate

 

WHILE @RECORD_CHECK > 0

      BEGIN

            EXEC msdb.dbo.sp_send_dbmail

                @profile_name = '[Database Mail Profile]'

              , @recipients = '[recipient email address]'

              , @subject = 'InventSumDate Tables'

              , @body = 'InventSumDateTable and InventSumDateTrans temp tables are populated.'

              , @importance = 'high'

              , @query = 'SELECT  ''InventSumDateTable'' [Table]

                                      , COUNT(RECID) [Count]

                                INTO    #tmpCountInventSumDate

                                FROM    AXDATABASE.dbo.InventSumDateTable

                             

                                INSERT  #tmpCountInventSumDate

                                            ( [Table]

                                            , [Count]

                                            )

                                            SELECT  ''InventSumDateTrans''

                                                  , COUNT(RECID)

                                            FROM    AXDATABASE.dbo.InventSumDateTrans

                             

                                SELECT  *

                                FROM    #tmpCountInventSumDate

                             

                                DROP TABLE #tmpCountInventSumDate'

           

            SET @RECORD_CHECK = 0

      END

SQL Index maintenance

I don't know where these scripts originated... I've tried to search it out to give appropriate credit, but it's on MSDN blogs, TechNet blogs, SQLServerCentral, and other sql related sites.  I first came across them on the Dynamics Communities site.  If I learn of the origin I will come back and include it.  For now, I'm including it here for my own reference.  Consider changing the recovery mode to simple temporarily if there are going to be massive changes.

USE [DATABASE]

--Check Fragmentation

SELECT s.DATABASE_ID,

       s.OBJECT_ID,

       s.INDEX_ID,

       b.NAME,

       s.AVG_FRAGMENTATION_IN_PERCENT

FROM   sys.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS s

       INNER JOIN sys.INDEXES AS b

               ON s.OBJECT_ID = b.OBJECT_ID

                  AND s.INDEX_ID = b.INDEX_ID

WHERE  s.DATABASE_ID = DB_ID()

ORDER  BY s.AVG_FRAGMENTATION_IN_PERCENT DESC
 

/**Fragmentation is also covered in the Performance Analyzer for Microsoft

   Dynamics AX (also known as DynamicsPerf). When running an sp_capturestats,

   pass the parameter @INDEX_PHYSICAL_STATS =’Y’. Note that this places a

   heavier load on SQL when capturing, so you should consider executing this

   during non-peak hours. Once the index fragmentation statistics have been

   captured you can view them with this script.

**/
 

USE [DATABASE]

SELECT DATABASE_NAME,

       TABLE_NAME,

       INDEX_NAME,

       AVG_FRAGMENTATION_IN_PERCENT,

       FRAGMENT_COUNT

FROM   INDEX_STATS_CURR_VW

ORDER  BY AVG_FRAGMENTATION_IN_PERCENT DESC 
 

--This script will reorganize or rebuild indexes based on the fragmentation.  It's set at 30% in this script.
 

-- Ensure a USE  statement has been executed first.

USE [DATABASE]

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( MAXDOP = 8)';
 --Enterprise version:  + N' REBUILD WITH(ONLINE = ON, MAXDOP = 8)';

      IF @partitioncount > 1

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

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

      EXEC (@command);

      PRINT N'Executed: ' + @command; 
  END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;
 

-- Drop the temporary table.

DROP TABLE #work_to_do;
 

GO