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

2017-03-25

1652
0
SQL Server 2016
2020-11-29

善用交易效能分析報表，預估資料表或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