AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
DAX
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 01.09.2014, 14:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
emeadaxsupport: Microsoft Dynamics AX general performance analysis scripts page 6
Источник: http://blogs.msdn.com/b/axsupport/ar...ts-page-6.aspx
==============

This is page 6 of 8 of the general performance analysis scripts online for the Performance Analyser 1.20 tool. See page 1 for the introduction. Use the links in the table below to navigate between pages.

- General analysisAnalyse SQL ConfigurationPage 1Analyse SQL IndexesPage 2Analyse SQL QueriesPage 3Analyse BlockingPage 4Baseline - benchmark queriesPage 5- AX SpecificAnalyse AX ConfigurationPage 6Analyse AX IndexesPage 7Analyse AX QueriesPage 8Analyse AX Configuration

AOS_DEBUG
CONNECTION_CONTEXT
TOO_BIG_FOR_ENTIRE_TABLE_CACHE
TABLES_THAT_COULD_BE_ENTIRE_TABLE_CACHE
ENTIRE_TABLE_CACHE_WITH_UPDATES
OCC_DISABLED
AX_DATABASE_LOGGING
AX_ALERTS_ON_TABLE
AX_BATCH_CONFIGURATION
AOS_CLUSTER_CONFIG
AX_DB_LOGGING_BY_TABLE
NUMBER_SEQUENCE_USAGE



USE DynamicsPerf

--AOS Configuration issues
--
-- AOS_DEBUG
-- --------------------------------------------------------------
-- Is Enable X++ Debug enabled on any AOS Servers.
-- 20% decline in transactions processed on the AOS instances with this enabled
-----------------------------------------------------------------

SELECT SERVER_NAME,
AOS_INSTANCE_NAME,
SETTING_NAME,
SETTING_VALUE
FROM AOS_REGISTRY
WHERE IS_CONFIGURATION_ACTIVE = 'Y'
AND SETTING_NAME = 'xppdebug'
AND SETTING_VALUE '0'

-- --------------------------------------------------------------
-- CONNECTION_CONTEXT
-- Is Context_Info enabled on any AOS Servers.
--
-----------------------------------------------------------------

SELECT SERVER_NAME,
AOS_INSTANCE_NAME,
SETTING_NAME,
SETTING_VALUE
FROM AOS_REGISTRY
WHERE IS_CONFIGURATION_ACTIVE = 'Y'
AND SETTING_NAME = 'connectioncontext'
AND SETTING_VALUE '0'



--AOT configuration issues

-- TOO_BIG_FOR_ENTIRE_TABLE_CACHE

-- --------------------------------------------------------------
-- Find tables that have entire table cache enabled that are larger than 128K
-- Causes the cache to overflow to disk on the AOS Server
-----------------------------------------------------------------

SELECT A.TABLE_NAME,
APPLICATION_LAYER,
CACHE_LOOKUP,
PAGE_COUNT
FROM AX_TABLE_DETAIL_CURR_VW A,
INDEX_STATS_CURR_VW I
WHERE A.DATABASE_NAME = I.DATABASE_NAME
AND A.TABLE_NAME = I.TABLE_NAME
AND CACHE_LOOKUP = 'EntireTable'
AND ( INDEX_DESCRIPTION = 'HEAP'
OR INDEX_DESCRIPTION LIKE 'CLUSTERED%' )
AND PAGE_COUNT > 16 -- 128kb
--AND PAGE_COUNT> 4 --32KB AX2012RTM
--AND PAGE_COUNT> 12 --96KB AX2012R2

ORDER BY PAGE_COUNT DESC

-- TABLES_THAT_COULD_BE_ENTIRE_TABLE_CACHE

-- --------------------------------------------------------------
-- Find tables that have no cache enabled that are smaller than 128K
-- These could cause lots of roundtrips between AOS and SQL
--
-- NOTE:
-- Table should be static and not updated much before changing
-- cache to Entiretable
-----------------------------------------------------------------

SELECT A.TABLE_NAME,
APPLICATION_LAYER,
CACHE_LOOKUP,
PAGE_COUNT
FROM AX_TABLE_DETAIL_CURR_VW A,
INDEX_STATS_CURR_VW I
WHERE A.DATABASE_NAME = I.DATABASE_NAME
AND A.TABLE_NAME = I.TABLE_NAME
AND CACHE_LOOKUP = 'None'
AND ( INDEX_DESCRIPTION = 'HEAP'
OR INDEX_DESCRIPTION LIKE 'CLUSTERED%' )
AND PAGE_COUNT < 16 -- 128kb
--AND PAGE_COUNT> 4 --32KB AX2012RTM
--AND PAGE_COUNT> 12 --96KB AX2012R2
AND PAGE_COUNT > 0

ORDER BY TABLE_NAME DESC
--
-- ENTIRE_TABLE_CACHE_WITH_UPDATES
--
-- --------------------------------------------------------------
-- Find tables that have entire table cache and show update rate
-- Causes the cache to be refreshed on all AOS instances
-----------------------------------------------------------------

SELECT A.TABLE_NAME,
APPLICATION_LAYER,
CACHE_LOOKUP,
USER_UPDATES
FROM AX_TABLE_DETAIL_CURR_VW A,
INDEX_STATS_CURR_VW I
WHERE A.DATABASE_NAME = I.DATABASE_NAME
AND A.TABLE_NAME = I.TABLE_NAME
AND CACHE_LOOKUP = 'EntireTable'
AND ( INDEX_DESCRIPTION = 'HEAP'
OR INDEX_DESCRIPTION LIKE 'CLUSTERED%' )
ORDER BY USER_UPDATES DESC

--
-- OCC_DISABLED
--
-- --------------------------------------------------------------
-- Find tables above SYS layer that do not have OCC enabled:
--
-----------------------------------------------------------------

SELECT TABLE_NAME
FROM AX_TABLE_DETAIL_CURR_VW
WHERE APPLICATION_LAYER NOT IN ( 'SYS', 'System Table' )
AND OCC_ENABLED = 0
ORDER BY TABLE_NAME

--
-- AX_DATABASE_LOGGING
--
-- --------------------------------------------------------------
-- Find tables above SYS layer that have logging enabled
--
-----------------------------------------------------------------

SELECT *
FROM AX_TABLE_DETAIL_CURR_VW
WHERE APPLICATION_LAYER NOT IN ( 'SYS', 'System Table' )
AND ( DATABASELOG_INSERT = 1
OR DATABASELOG_DELETE = 1
OR DATABASELOG_UPDATE = 1
OR DATABASELOG_RENAME_KEY = 1 )
ORDER BY TABLE_NAME

--
-- AX_ALERTS_ON_TABLE
--
-- --------------------------------------------------------------
-- Find tables above SYS layer that have events enabled
--
-----------------------------------------------------------------

SELECT *
FROM AX_TABLE_DETAIL_CURR_VW
WHERE APPLICATION_LAYER NOT IN ( 'SYS', 'System Table' )
AND ( EVENT_INSERT = 1
OR EVENT_DELETE = 1
OR EVENT_UPDATE = 1
OR EVENT_RENAME_KEY = 1 )
ORDER BY TABLE_NAME

-- SELECT * FROM EVENTRULE -- DO THIS IN THE AX DATABASE TO DISCOVER ABOVE DATA

--AX Application configuration issues

--
-- AX_BATCH_CONFIGURATION
--
-- -----------------------------------------------------------------------------
-- List BATCHGROUP configuration in Dynamics AX
--------------------------------------------------------------------------------

SELECT *
FROM AX_BATCH_CONFIGURATION_VW

--
-- AOS_CLUSTER_CONFIG
--
-- -----------------------------------------------------------------------------
-- List AOS cluster configuration in Dynamics AX
--------------------------------------------------------------------------------

SELECT *
FROM AX_SERVER_CONFIGURATION_VW

--
-- AX_DB_LOGGING_BY_TABLE
--
-- --------------------------------------------------------------
-- List top 200 tables be logged in Dynamics AX
-- NOTE: if this query returns zero rows
-- the AOTEXPORT class has not been run
-----------------------------------------------------------------

SELECT [TABLE_NAME],
[ROWS_LOGGED],
[DATABASELOG_UPDATE],
[DATABASELOG_DELETE],
[DATABASELOG_INSERT]
FROM [AX_DATABASELOGGING_VW]
ORDER BY [ROWS_LOGGED] DESC

--
-- NUMBER_SEQUENCE_USAGE
--
-- -----------------------------------------------------------------------------
-- List NUMBERSEQUENCE table configuration in Dynamics AX
-- Are sequences marked as Coninuous? If so why?
-- Is FETCHAHEADQTY > 0, if not preallocation is not setup for this sequence
-- Pre-allocation requires knowledge of the avg. number of numbers consumed
-- per user process to determine a good value.
--------------------------------------------------------------------------------

SELECT RUN2.[DATABASE_NAME],
RUN2.[COMPANYID],
RUN2.[NUMBERSEQUENCE],
RUN2.[TEXT],
Datediff(hh, RUN1.STATS_TIME, RUN2.STATS_TIME) AS ELAPSED_HOURS,
RUN2.NEXTREC - RUN1.NEXTREC AS TOTAL_NUMBERS_CONSUMED,
( RUN2.NEXTREC - RUN1.NEXTREC ) / ( Datediff(hh, RUN1.STATS_TIME, RUN2.STATS_TIME) ) AS HOURLY_CONSUMPTION_RATE,
RUN2.HIGHEST - RUN2.NEXTREC AS [NUMBERSREMAINING],
RUN2.[CONTINUOUS],
RUN2.[FETCHAHEAD],
RUN2.[FETCHAHEADQTY]
FROM AX_NUM_SEQUENCES_VW RUN1
INNER JOIN AX_NUM_SEQUENCES_VW RUN2
ON RUN1.NUMBERSEQUENCE = RUN2.NUMBERSEQUENCE
AND RUN1.COMPANYID = RUN2.COMPANYID
WHERE RUN1.RUN_NAME = 'BASE_to_compare_to'
AND RUN2.RUN_NAME = 'Feb_26_2020_804AM'
ORDER BY 6 DESC



--To find run_name run the following query

SELECT *
FROM STATS_COLLECTION_SUMMARY
ORDER BY STATS_TIME DESC

-- --------------------------------------------------------------
-- Review number sequence configuration in Dynamics AX
-----------------------------------------------------------------

SELECT *
FROM AX_NUM_SEQUENCES_CURR_VW
WHERE CONTINUOUS = 'Yes'






Источник: http://blogs.msdn.com/b/axsupport/ar...ts-page-6.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
DAX: Microsoft Dynamics AX 2012 R3 is now available! Blog bot DAX Blogs 1 02.05.2014 23:00
DAX: Official Dynamics AX 2012 R2 Content (update) - Where is it, and how can you find out about updates? Blog bot DAX Blogs 0 03.12.2012 11:11
Microsoft Dynamics CRM Team Blog: Update Rollup 3 for Microsoft Dynamics CRM 2011 Blog bot Dynamics CRM: Blogs 3 03.08.2011 09:11
dynamics-ax: Interview with Microsoft's Lachlan Cash on his new role, AX 2012 and more Blog bot DAX Blogs 6 22.04.2011 14:55
emeadaxsupport: Resolving some issues you may experience when creating an AX 2009 Role Center and Enterprise Portal Site using SharePoint Server/Foundation 2010 after installing Microsoft Dynamics AX 2009 SP1 hotfix 2278963 Blog bot DAX Blogs 1 24.09.2010 11:34
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 12:55.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.