SQL 效能
======

###### tags: `SQL`

> ***不知自己不知道, 那你會以為你知道.***

## Index ??
Index 可加快查詢速度，Index 是以空間換取時間。
- 查詢沒有任何索引的資料表，是逐筆搜尋(scan)。最差的情況是資料在最後一筆。
- 每個 Index 都有自己的 B+ Tree，因索引有排序的特性。所以透過搜尋索引時，就可以比較快找到找到資料。(*seek*)
- Clustered Index(叢集索引)，每個資料表只能有一個
- 每本書只能有一個目錄
- NonClustered Index(非叢集索引)，每個資料表可有多個
- 每本書可以有很多種附錄，常見的像是透過字母去做分類。
- 會額外存放 Clustered 的 key
- 查詢時依據撈取的欄位與查詢的條件，sql engine 會找到它認為的最優解。所以不同 query 與情況，都可能影響是否使用索引。
- 如果 query 只透過 nonClustered 就可以完成任務，通常這個情況是最好的。代表不用再 key lookup 找 Clustered Index
- 因上述所以底下舉的例子所得到的結果，不一定都是相同的
- where 包含叢集索引中的第一個欄位 => clustered seek
- where 只包含叢集索引中的第二個欄位 => nonClustered scan
- select 只包含叢集與非叢集欄位，where 只包含非叢集索引 => nonClustered seek
- select 如果包含非索引欄位 => clustered scan

## 從開發者的角度看 SQL 效能
參考影片: https://www.youtube.com/watch?v=v18Qs_cbp-Y

### 簡易測試硬碟的讀寫速度
- 透過 SQL SCRIPT 測試硬碟讀取速度:
- BACKUP DATABASE [DbName] TO DISK='NUL' WITH COPY ONLY
- 透過 SQL SCRIPT 測試硬碟讀寫速度:
- BACKUP DATABASE [DbName] TO DISK='C:\Temp\Test.BAK' WITH COPY ONLY
- 做個簡單運算求出寫入速度
- Print 分頁數 * 8 / 1024 / (讀寫速度 - 讀取速度)

### 比對同一個需求但不同 sql script 哪個成本花費比較低?
- 透過 SSMS 比對不同的 sql script, 可以算出兩個語法在此次的查詢下各花費多少成本

### SQL Script 寫出好維護的例子
#### 題目
- 從 **銷售訂單** 與 **銷售明細** 依據需求查出資料
- 需求: 查詢結果要呈現 **完整的銷售訂單資料** 還有對應於 **銷售明細中金額最高** 的一筆資料.

~~~~sql=
-- 解法1
select a.*, MAX(b.Total) Total
from SalesOrderHeader a
join SalesOrderDetail b on a.SalesOrderID = b.SalesOrderID
group by a.SalesOrderID, a.col1, a.col2, a.col3, a.col4; -- 看要呈現那些欄位資訊全都要放到 group by 底下
~~~~

~~~~sql=
-- 解法2
with b as
(
select SalesOrderID, MAX(Total) Total from SalesOrderDetail
group by SalesOrderID
)
select a.* Total
from SalesOrderHeader a
join b on a.SalesOrderID = b.SalesOrderID
~~~~

~~~~sql=
-- 解法3
with b as
(
select *, ROW_NUMBER() over (Partition By SalesOrderID Order By Total Desc) RowID
from SalesOrderDetail
)
select a.*, b.*
from SalesOrderHeader a
join b on a.SalesOrderID = b.SalesOrderID And b.RowID = 1
~~~~

:::warning
:grey_question:上述三種解法切可滿足需求, 請問哪種易於維護呢? 請問哪種執行成本較低呢?
:::

### MS Sql Server 需要注意的細節
- Database 設定中, 相容性層級是否有用到最新的版本?
- 因為從舊的環境中 backup 過來的 DB, 相容性層級可能會是舊的.
- Database 對應的 DataFile 須依據電腦核心數, 去做對應的數量.
- 使用 sql script 在正式環境去建立 Database
- Sql script 的效能直接用 SSMS 去查詢最為準確, 因為根據不同資料庫引擎最佳化**在相同語法下可能會有不同結果**.
- 平時處理須搭配 DataFile, 且資料庫 instance 與對應的 Database 可以做其設定(這部分還沒搞懂, 可從影片 52:20 開始看)

### RDBMS 關聯式資料庫
- 當 Table 資料變的巨量時，意味著 CRUD 的成本大大增加，很大的機會有低效率的表現。
- 關連式資料庫的基礎，就是透過**欄位關聯**的方式，來打破一張大表的概念
- 從需求分析到建立領域模型，再來就是設計 schema。好的欄位關聯即是利用 RDBMS 的優勢，可以透過正規畫來檢驗 scheam:
- 1NF(第一正規畫): 要點如下
- 定義與設計主鍵值（primary key)
- 單一值: 能讓資料庫計算的型別，則不使用字串
- 數值或日期不要用字串儲存，除非有特殊需求。
- 降低複雜的邏輯
- 盡量已最少空間儲存
- 2NF(第二正規劃): 要點為去除重複資料，與建立 Foreign Key
- 常見的例子會是 Order 對應 OrderShop，訂單有對應的門店，如果只用 Order 儲存就需要多開欄位，且訂單肯定會有多筆重複地門店就會導致大量的重複資料。此時會是新開 Table 的時機。
- 以上的情形不是絕對，太多的 join 會導致程式不方便使用且不好閱讀。至於要拆分成多細也是要經驗積累，沒有絕對。
- 外來鍵是必須建立的
- 3NF(第三正規畫):如果可以用其它欄位計算或推斷得出，就不要額外設立一個欄位去紀錄。
- 常見的例子會是在金額總計上，比如買了三之50元的筆，對應在資料庫的欄位會有訂購數量、筆的價錢、Total(總價。Total 是很方便，但有時可能會讓資料錯亂，比如筆的價錢或訂購數量調整了，卻沒去修改 total 就會發生錯誤。**比較好的解決方式是取資料時順便計算總價，Total = 訂購量 * 筆的價錢。**
- 簡單來說即是每個非鍵值欄位都得盡量避免和其他非鍵值欄位產生相關性

## 發現 SQL 執行速度下降時, 優化可以怎麼做?
- 檢查有使用 join、子查詢、union 的 sql script
- 檢查一張表是否過多的索引或者查詢的欄位沒有索引
- 從執行計畫中看出端倪
- 資料量很大的 table 可做水平與垂直的切割:
- 水平分割: 1~100萬放 table1, 100~200萬放 table2
- 垂直分割: 如果有20個欄位那 1~10 欄位放 table1, 11~20 放 table2
- 主從架構: 讀寫分離再不同的 DB。
- 透過 sql server 提供的函式查詢
- 查詢最常使用 sql script 的排名
- 查詢花費成本 sql script 的排名
- 查詢是否有沒有使用過的 index
- 查詢是否有重複的索引

系統設計
------
### 當有一個資料表 (Table) 的內容高達 10GB、上千萬筆時，你會用啥方法增進存取的效能？
https://blog.xuite.net/tolarku/blog/31086989
- 使用 Disk Array（當然大家都會將資料庫放到磁碟陣列中）
- 檔案群組: 您可在三部磁碟機內分別建立 Data1.ndf、Data2.ndf 及 Data3.ndf，並將這些檔案指派給檔案群組 fgroup1。 接著您可根據檔案群組 fgroup1 來建立資料表。 資料表的資料查詢可分散至三個磁碟，藉此改善效能。
- 針對該資料表常被 Query、Join 的欄位 (Column) 做索引 (Index)

**但是？單一 Table 超大時，卻無法單純的因為多了幾個 ndf 就飛快了起來**
※當某資料表連做索引都無法有效提昇效能時，可以利用 DB Partition 將資料表內容分割到多個檔案群組去，以加快存取速度。※

DB Sharding
- https://oldmo860617.medium.com/%E7%AD%86%E8%A8%98-database-sharding-22e22f0809c0
- https://www.instagram.com/p/C2TbhNASHan/?img_index=1

### Sql server 多個 DB 實現負載平衡
- 一個 DbContext(EF) 去對多個 DB, 透過連線字串去做切換
- 多個DB分為主從, DB(master) 對於新刪修, DB(slave) 對於查詢.
- 問題考慮
- 資料異動時, 如何同步到 DB(master)
- 查詢時是否只能來自於 DB(slave)
- 負載配置 DB(slabe), 查詢時如何去分配? (隨機, 循環, 權重...)
- DB掉線或其他異常情況發生時, 導致資料不同步是否可追朔?
- 將 DB(master) 資料同步到 DB(slave), 會有延遲的問題. 這時候可能會在業務上做妥協.(非不得已才從主DB去讀資料)
- 透過 catch 去達成, 寫入主 DB 時同時將資料更新到 catch, 並返回 catch 的資料.
- ?? catchDB 性能很強, 是否能直接取代 RMDB(slave)?
- ?? catchDB 性能很強, 是否能直接取代 RMDB(master)?
- ?? catch 資料能否永久保存 ??
- ?? 如果永久保存選擇 RMDB, 要如何去做同步(DB slave)??

------
## 代整理筆記
### 注意事項
- 不要在開發階段的時候就設定 index 除了``` Clustered Index | unique Index | forogin Key Index```
- PK 欄位若沒有排序性(通常是在客戶端使用 GUID), 資料增長後會導致索引破碎. 可使用以下方式來解決
- NOTE: GUID 最好不要拿來當作 PK 除非預期資料量不大, 某些情境確實一定要用到 GUID 那請別把它當 PK.
- 第一種 sqlScript: Alter Table xxx Rebuild => 重建索引
- 其他解決方案
- 查看 SQL SERVER 索引資訊, 與解決方案
- https://dotblogs.com.tw/jamesfu/2016/01/20/guid_2
- https://www.pupuliao.info/2015/02/%E6%8F%90%E5%8D%87%E8%B3%87%E6%96%99%E5%BA%AB-sql%E6%95%88%E8%83%BD/
- 使用 GUID 遇到索引破碎的問題, 即在客戶端建立無序性的 GUID 之解決方案
- https://blog.darkthread.net/blog/guid-as-pk-on-db/
- 客戶端生成有排序性的 GUID
- https://dotblogs.com.tw/ricochen/2014/08/26/146371

- SQL SERVER 欄位使用 default NEWSEQUENTIALID() 讓 DB 自動生成有序性的 GUID
- NOTE: GUID 最好不要拿來當作 PK 除非預期資料量不大, 某些情境確實一定要用到 GUID 那請別把它當 PK.
- 如果有 頭、身 表的問題, 因效能問題 GUID 只能是在程式端產出. 此時又遇到 PK 值需要排序的問題
- 後來是使用兩個欄位來創造出有序的 PK

            CREATE TABLE [Test_Master] (
                [SYSNUM] [uniqueidentifier] NOT NULL,
                [data] NVARCHAR (255) NULL,
                [Createtime]  DATETIME2(7) NOT NULL
                CONSTRAINT [PK_Test_Master] PRIMARY KEY CLUSTERED ([Createtime] DESC, [SYSNUM] DESC),
                CONSTRAINT [UNIQUE_ID] UNIQUE NONCLUSTERED ([SYSNUM] DESC)
            );

- 參考專案: C:\D\KevinFile\C-_Console開發測試區\測試開發區\GuidTest\

- Point: 索引建立要點
- Reference: https://www.pupuliao.info/2015/02/%E6%8F%90%E5%8D%87%E8%B3%87%E6%96%99%E5%BA%AB-sql%E6%95%88%E8%83%BD/
- 資料庫設計與規劃
- Primary Key 欄位的長度儘量小，能用 small integer 就不要用 integer。例如員工資料表，若能用員工編號當主鍵，就不要用身分證字號。
- 一般欄位亦同。若該資料表要存放的資料不會超過 3 萬筆，用 small integer 即可，不必用 integer。
- 文字資料欄位若長度固定，如：身分證字號，就不要用 varchar 或 nvarchar，應該用 char 或 nchar。
- 文字資料欄位若長度不固定，如：地址，則應該用 varchar 或 nvarchar。除了可節省儲存空間外，存取磁碟時也會較有效率。
- 設計欄位時，若其值可有可無，最好也給一個預設值，並設成「不允許 NULL」(一般欄位預設為「允許 NULL」)。
- 因為 SQL Server 在存放和查詢有 NULL 的資料表時，會花費額外的運算動作 [2]。
- 若一個資料表的欄位過多，應垂直切割成兩個以上的資料表，並用同名的 Primary Key 一對多連結起來，
- 如：Northwind 的 Orders、Order Details 資料表。以避免在存取資料時，以叢集索引掃描時會載入過多的資料，或修改資料時造成互相鎖定或鎖定過久。
- 適當地建立索引
- 記得自行幫 Foreign Key 欄位建立索引，即使是很少被 JOIN 的資料表亦然。(這裡建立的是非關聯索引!!)
- 替常被查詢或排序的欄位建立索引，如：常被當作 WHERE 子句條件的欄位。
- 用來建立索引的欄位，長度不宜過長，不要用超過 20 個位元組的欄位，如：地址。
- 不要替內容重複性高的欄位建立索引，如：性別；反之，若重複性低的欄位則適合建立索引，如：姓名。
- 不要替使用率低的欄位建立索引。
- 不宜替過多欄位建立索引，否則反而會影響到新增、修改、刪除的效能，尤其是以線上交易 (OLTP) 為主的網站資料庫。
- 若資料表存放的資料很少，就不必刻意建立索引。否則可能資料庫沿著索引樹狀結構去搜尋索引中的資料，反而比掃描整個資料表還慢。
- 若查詢時符合條件的資料很多，則透過「非叢集索引」搜尋的效能，可能反而不如整個資料表逐筆掃描。
- 建立「叢集索引」的欄位選擇至為重要，會影響到整個索引結構的效能。要用來建立「叢集索引」的欄位，務必選擇「整數」型別 (鍵值會較小)、唯一、不可為 NULL。

- sql server 可以設定 腳色 對應 資料結構描述 說明:
- 大多在查詢資料表時, 會預設 [dbo].[tableName]. 前面的 [dbo] 這個就是資料結構描述. 這個資料結構描述也是 sql server 的預設值.
- 創建 table 時可以定義資料結構描述. 舉例 [ezcatapp].[tableName]
- 一個 角色/登入帳號 可以與資料結構做繫結. 這樣以這個角色登入後就可以直接查詢, [tableSchema].[tableName]
- 做了 schema 上的設定後, 都不用去設定權限相關的問題
- 如果要讀取不同 schema 的 table, 要怎麼辦呢? 在對應的表中開放權限給 角色/登入帳戶(既如以往)

- 清除 .mdf(LOG 指令
--DBCC SHRINKDATABASE('資料庫名稱', TRUNCATEONLY)

### 效能調整(待吸收)

- SQL Server filegroup & partition 可組合多個 DISK 去存放 filegroup || file 做到平行處理, 需注意的細節很多
- 使用查詢計畫時, 簡單看的話需注意兩個數據
- 執行計畫
- 邏輯讀取次數 logical reads
- Table 的 Index 配置數量最多請控制在 16 個左右
- Index 相關
- Index 另外存放在 Page, Page 有固定大小. Page 存放 Index 本身 & Clustered Index 的資料
- Index 的組成越大, 對應在 page 中的一筆資料就越大. 導致這個 Index 的資料在資料量大時需要多個 Page 來存放
- sql lookup 是指說 sql script 需要的資料, 索引的資料沒辦法提供. 需要去查詢資料本身(table scan | other index scan)

table schema 
    A, -- ci
    B, -- index 
    C


select B where B = "i1234";     -- table scan 沒有, other index scan 沒有

select A, B where B = "i1234";  -- table scan 沒有, other index scan 沒有

select A where A = "A001";      -- table scan 沒有, other index scan 沒有

select A, B where A = "A001";   -- table scan 沒有, other index scan 有

select * where A = "A001"; -- table scan 有, other index scan 有 (lookup)

select A, C where A = "A001"; -- table scan 有, other index scan 有 (lookup)

- 簡單查看 sql 效能指標
1. 查詢 Index 被使用的次數 => 查詢 Index 到底有沒有沒用的 Index
2. 重複的 Index => 同一張 Table 有一樣 欄位的 索引
3. 查詢 花費成本 最高的 sql script

- 找出未使用及無效率的 indexes sql script

SELECT
    @@SERVERNAME AS [ServerName]
    , DB_NAME() AS [DatabaseName]
    , SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
    , [sObj].[name] AS [ObjectName]
    , CASE
        WHEN [sObj].[type] = 'U' THEN 'Table'
        WHEN [sObj].[type] = 'V' THEN 'View'
        END AS [ObjectType]
    , [sIdx].[index_id] AS [IndexID]
    , ISNULL([sIdx].[name], 'N/A') AS [IndexName] 
    , CASE
        WHEN [sIdx].[type] = 0 THEN 'Heap'
        WHEN [sIdx].[type] = 1 THEN 'Clustered'
        WHEN [sIdx].[type] = 2 THEN 'Nonclustered'
        WHEN [sIdx].[type] = 3 THEN 'XML'
        WHEN [sIdx].[type] = 4 THEN 'Spatial'
        WHEN [sIdx].[type] = 5 THEN 'Reserved for future use'
        WHEN [sIdx].[type] = 6 THEN 'Nonclustered columnstore index'
        END AS [IndexType]
    , [sdmvIUS].[user_seeks] AS [TotalUserSeeks]
    , [sdmvIUS].[user_scans] AS [TotalUserScans]
    , [sdmvIUS].[user_lookups] AS [TotalUserLookups]
    , [sdmvIUS].[user_updates] AS [TotalUserUpdates]
    , [sdmvIUS].[last_user_seek] AS [LastUserSeek]
    , [sdmvIUS].[last_user_scan] AS [LastUserScan]
    , [sdmvIUS].[last_user_lookup] AS [LastUserLookup]
    , [sdmvIUS].[last_user_update] AS [LastUserUpdate]
    , STATS_DATE([sIdx].object_id, [sIdx].[index_id]) AS StatsUpdated
    , [sdmfIOPS].[leaf_insert_count] AS [LeafLevelInsertCount]
    , [sdmfIOPS].[leaf_update_count] AS [LeafLevelUpdateCount]
    , [sdmfIOPS].[leaf_delete_count] AS [LeafLevelDeleteCount]
    /* --trans & lock
    , [sdmfIOPS].[row_lock_count]  AS [RowLocksRequested]
    , [sdmfIOPS].[row_lock_wait_count] AS [RowLockWaited]
    , [sdmfIOPS].[row_lock_wait_in_ms] AS [RowLockWaitedMS]
    , [sdmfIOPS].[page_lock_count] AS [PageLocksRequested]
    , [sdmfIOPS].[page_lock_wait_count] AS [PageLockWaited]
    , [sdmfIOPS].[page_lock_wait_in_ms] AS [PageLockWaitedMS]
    , [sdmfIOPS].[index_lock_promotion_attempt_count] AS [TriedEscalateLocks]
    , [sdmfIOPS].[index_lock_promotion_count] AS [EscalatedLocks]
    */
    FROM
    [sys]. [indexes] AS [sIdx]
    INNER JOIN [sys].[objects] AS [sObj]
        ON [sIdx].[object_id] = [sObj].[object_id]
    LEFT JOIN [sys].[dm_db_index_usage_stats] AS [sdmvIUS]
        ON [sIdx].[object_id] = [sdmvIUS].[object_id]
        AND [sIdx].[index_id] = [sdmvIUS].[index_id]
        AND [sdmvIUS].[database_id] = DB_ID ()
    LEFT JOIN [sys].[dm_db_index_operational_stats] (DB_ID(),NULL,NULL,NULL) AS [sdmfIOPS]
        ON [sIdx].[object_id] = [sdmfIOPS].[object_id]
        AND [sIdx].[index_id] = [sdmfIOPS].[index_id]
    WHERE
    [sObj]. [type] IN ('U' ,'V')          -- Look in Tables & Views
    AND [sObj].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects
    AND [sIdx].[is_disabled] = 0x0     -- Exclude Disabled Indexes


- 找出疑似重複的 indexes 

select
s.Name + N'.' + t.name as [Table]
,i1.index_id as [Index1 ID], i1.name as [Index1 Name]
,dupIdx.index_id as [Index2 ID], dupIdx.name as [Index2 Name]
,c.name as [Column]
from
sys.tables t join sys.indexes i1 on
t.object_id = i1.object_id
join sys.index_columns ic1 on
ic1.object_id = i1.object_id and
ic1.index_id = i1.index_id and
ic1.index_column_id = 1
join sys.columns c on
c.object_id = ic1.object_id and
c.column_id = ic1.column_id
join sys.schemas s on
t.schema_id = s.schema_id
cross apply
(
select i2.index_id, i2.name
from
sys.indexes i2 join sys.index_columns ic2 on
ic2.object_id = i2.object_id and
ic2.index_id = i2.index_id and
ic2.index_column_id = 1
where
i2.object_id = i1.object_id and
i2.index_id > i1.index_id and
ic2.column_id = ic1.column_id
) dupIdx
order by
s.name, t.name, i1.index_id;

    
- 找出曾經被執行的語法，而沒有用到 index 的欄位 

-- find missing indexes v1
SELECT mig.*, statement AS table_name, column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;

-- find missing indexes v2
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
--REPLACE(REPLACE(LEFT(t.[text], 50), CHAR(10),''), CHAR(13),'') AS [Short Query Text],
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time],
qs.min_elapsed_time AS [Min Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.max_elapsed_time AS [Max Elapsed Time],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],
qs.execution_count AS [Execution Count],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
dateadd(hour, 8, qs.creation_time) AS [Creation Time]
,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
and qs.creation_time >= '2020-05-07'
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

    

- 列出前 50 名吃最重的查詢及stored procedure 

--50 most expensive queries
SELECT TOP 50 SUBSTRING(qt.text,qs.statement_start_offset / 2 + 1,(CASE qs.statement_end_offset
WHEN-1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 + 1)AS Sql
,qs.execution_count AS [Exec Cnt]
,cp.objtype
,(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [Avg IO]
,qs.total_logical_reads AS [Total Reads]
,qs.last_logical_reads AS [Last Reads]
,qs.total_logical_writes AS [Total Writes]
,qs.last_logical_writes AS [Last Writes]
,qs.total_worker_time AS [Total Worker Time]
,qs.last_worker_time AS [Last Worker Time]
,qs.total_elapsed_time / 1000 AS [Total Elps Time]
,qs.last_elapsed_time / 1000 AS [Last Elps Time]
,qs.creation_time AS [Compile Time]
,qs.last_execution_time AS [Last Exec Time]
,cp.size_in_bytes
,cp.cacheobjtype
--,qp.query_plan AS [Plan]
FROM sys.dm_exec_query_stats qs WITH (nolock)
JOIN sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)qp
ORDER BY [Avg IO] DESC
OPTION(RECOMPILE)

--50 most expensive stored procedure
SELECT TOP 50 s.name + '.' + p.name AS [Procedure]
,(ps.total_logical_reads + ps.total_logical_writes) / ps.execution_count AS [Avg IO]
,ps.execution_count AS [Exec Cnt]
,ps.cached_time AS Cached
,ps.last_execution_time AS [Last Exec Time]
,ps.total_logical_reads AS [Total Reads]
,ps.last_logical_reads AS [Last Reads]
,ps.total_logical_writes AS [Total Writes]
,ps.last_logical_writes AS [Last Writes]
,ps.total_worker_time AS [Total Worker Time]
,ps.last_worker_time AS [Last Worker Time]
,ps.total_elapsed_time AS [Total Elapsed Time]
,ps.last_elapsed_time AS [Last Elapsed Time]
,cp.size_in_bytes
--,qp.query_plan AS [Plan]
FROM sys.procedures AS p WITH (nolock) JOIN sys.schemas s WITH (nolock)ON p.schema_id = s.schema_id
JOIN sys.dm_exec_procedure_stats AS ps WITH (nolock) ON p.object_id = ps.object_id
LEFT JOIN sys.dm_exec_cached_plans cp on ps.plan_handle = cp.plan_handle
OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle)qp
ORDER BY [Avg IO] DESC
OPTION(RECOMPILE);