SQL Server da stored procedure daki işlemler ne kadar sürede çalışıyor?

SQL Server'da bir stored procedure yazdınız ve işinde birçok iş yapıyorsunuz. Ancak prosedür yavaş çalışıyor. Ne yaparsınız ?

Ya execution plan'i çalıştırır ve hangi kodun yavaş çalıştığını anlamaya çalışırız veya teker teker kod parçalarını çalıştırız.

Bunun için kullandığım yöntem, stored procedure'a @Timer diye bir parametre eklemek ve bu parametreye 1 geçilirse her işlem için bu zamanları milisaniye cinsinden hesaplamak.

Örnek prosedür :

1 CREATE PROCEDURE dbo.sp_SampleProcedure

2 @Timer smallint = 0

3 AS BEGIN

4

5 IF @Timer = 1 BEGIN

6 DECLARE @Tm TABLE(dt datetime,com varchar(255), tm int NULL)

7 DECLARE @BeginDate datetime,@EndDate datetime,@OldDate datetime

8 SELECT @BeginDate = GETDATE()

9 INSERT INTO @Tm(dt,com) VALUES(GETDATE(),'***BEGIN***')

10 SELECT @OldDate = GETDATE()

11 END

12

13

14 -- Do something

15 SELECT 'This is a sample operation 1 '

16

17 IF @Timer = 1 BEGIN

18 INSERT INTO @Tm(dt,com,tm)

VALUES(GETDATE(),'10.Sample Operation 1',DATEDIFF(ms,@OldDate,GETDATE()))

19 SELECT @OldDate = GETDATE()

20 END

21

22

23 -- Do another thing

24 DECLARE @Result TABLE (OrderNo int, Number int )

25

26 DECLARE @i int = 1,@OrderNo int = 0 , @Step int = 1

27

28 WHILE @i<= 200 BEGIN

29 INSERT INTO @Result

30 VALUES(@OrderNo,@i)

31 SELECT @i = @i + @Step, @OrderNo = @OrderNo + 1

32 END

33

34 SELECT * FROM @Result

35

36 IF @Timer = 1 BEGIN

37 INSERT INTO @Tm(dt,com,tm) VALUES(GETDATE(),'20.Sample Operation 2',DATEDIFF(ms,@OldDate,GETDATE()))

38 SELECT @OldDate = GETDATE()

39 END

40

41

42 -- Do one more operation and get the size of all tables. This will take a time

43

44 CREATE TABLE #TableSize

45 (

46 [name] nvarchar(255),

47 [rows] int,

48 [reserved] varchar(20),

49 [data] varchar(20),

50 [index_size] varchar(20),

51 [unused] varchar(20)

52 )

53 -- Mevcut database deki tüm tablolar için sp_spaceused proc. ü çağırılıp, dönen recordset #TableSize tablosu eklenir

54 EXEC sp_MSforeachtable @command1='INSERT INTO #TableSize EXEC sp_spaceused ''?'' '

55

56 SELECT * FROM #TableSize

57

58

59 IF @Timer = 1 BEGIN

60 INSERT INTO @Tm(dt,com,tm) VALUES(GETDATE(),'30.Get Table sizes',DATEDIFF(ms,@OldDate,GETDATE()))

61 SELECT @OldDate = GETDATE()

62 END

63

64 ---------------------------------------------

65 -- Get the execution time

66 IF @Timer = 1 BEGIN

67 INSERT INTO @Tm(dt,com) VALUES(GETDATE(),'***END***')

68 SELECT @EndDate = GETDATE()

69 INSERT INTO @Tm(dt,com,tm) VALUES(GETDATE(),'Total SP Duration',DATEDIFF(ms,@BeginDate,@EndDate))

70 SELECT * FROM @Tm

71 END

72 SET NOCOUNT OFF

73 SET ANSI_WARNINGS ON

74

75 END

76 GO

Prosedürü oluşturduktan sonra çalıştıralım :

exec sp_SampleProcedure @Timer = 1

En son sonuç olarak bize her işlemin ne kadar sürede çalıştığını gösterecek.

dt	com	tm
14.04.2010 15:34	***BEGIN***
14.04.2010 15:34	10.Sample Operation 1	0
14.04.2010 15:34	20.Sample Operation 2	6
14.04.2010 15:34	30.Get Table sizes	4843
14.04.2010 15:34	***END***
14.04.2010 15:34	Total SP Duration	4850