[SQL SERVER]善用交易效能分析報表

善用交易效能分析報表,預估資料表或SP移轉至In-Memory效益

SQL2014 提供 ARM(Analysis Migration and Reporting) Tool
1.Configure Data Warehouse
2.Configuration Data collection
3.Generate transaction performance analysis reports to identify performance-critical tables and stored procedures

SQL2016 提供更簡單的交易效能分析報表(資料表或SP)
1.No Data Warehouse
2.NO Data collection
3.Just View Standard Reports
DMV:sys.dm_db_index_operational_stats for table
DMV:sys.dm_exec_procedure_stats for SP

 

/*
RiCo 技術農場
https://dotblogs.com.tw/ricochen

善用交易效能分析報表,預估資料表或SP移轉至In-Memory效益

SQL2014 提供 ARM(Analysis Migration and Reporting) Tool
1.Configure Data Warehouse
2.Configuration Data collection
3.Generate transaction performance analysis reports to identify performance-critical tables and stored procedures

SQL2016 提供更簡單的交易效能分析報表(資料表或SP)
1.No Data Warehouse
2.NO Data collection
3.Just View Standard Reports
DMV:sys.dm_db_index_operational_stats for table
DMV:sys.dm_exec_procedure_stats for SP
*/
create proc dbo.myInmemoryA as
select top 10 * from Sales.InvoiceLines

create proc dbo.myInmemoryB as
select top 10 * from Sales.OrderLines

create proc dbo.myInmemoryC as
select top 10 * from dbo.InvoiceLines

create proc dbo.myInmemoryd(@stockitemid int) as
set nocount on
select stockitemid from dbo.InvoiceLines
where stockitemid=@stockitemid

exec dbo.myInmemoryA
GO 50
exec dbo.myInmemoryB
GO 50
exec dbo.myInmemoryC
GO 50
exec dbo.myInmemoryD 227
GO 50

 

 

參考

Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP

New AMR Tool: Simplifying the Migration to In-Memory OLTP

How to Use Microsoft's AMR Tool

SQL Server 2016: Transaction Performance Analysis Overview & In-Memory OLTP Migration Checklists

In-Memory OLTP Series – Data migration guideline process on SQL Server 2016