老皮的程式筆記

．SQL效能監控、優化工具

OpServer，並且環境建置請參考

https://dotblogs.com.tw/aken1215/2016/09/25/133956

 

．查出使用量較高的Store Procedure

SELECT TOP 100 d.object_id, d.database_id , DB_NAME (d.database_id), OBJECT_NAME(object_id, database_id) 'proc name',   
    d.cached_time, d.last_execution_time, d.total_elapsed_time,  
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_procedure_stats AS d  
where d.database_id > 4
ORDER BY [execution_count] DESC;  

 

 

 

查詢特定關鍵字是否在SP出現

--要放到對應的DB查詢

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%your_keyword%'
AND ROUTINE_TYPE='PROCEDURE'

 

cursor語法 (類似迴圈)

DECLARE @cur_column_1 int;
DECLARE	@cur_column_2 int


DECLARE myCursor CURSOR FOR
	
	--資料來源
	select UserID , UserName
	from [Users]


OPEN myCursor


--上面select幾個欄位，這邊就要幾個欄位(並且順序要一樣)
FETCH NEXT FROM myCursor INTO @cur_column_1, @cur_column_2
WHILE @@FETCH_STATUS = 0
BEGIN
	    

	---開始迴圈處理指定的事情(e.g. 呼叫SP)
	SELECT '123'
	SELECT @cur_column_1 , @cur_column_2

		
	FETCH NEXT FROM myCursor
	INTO @cur_column_1, @cur_column_2

END
	
CLOSE myCursor
DEALLOCATE myCursor