USE [wartung]
GO


/****** Object:  StoredProcedure [dbo].[pr_testQueryPerformance]    Script Date: 30.09.2016 13:08:40 ******/
IF (OBJECT_ID('dbo.pr_testQueryPerformance') IS NOT NULL)
	DROP PROCEDURE [dbo].[pr_testQueryPerformance]
GO

/****** Object:  StoredProcedure [dbo].[pr_testQueryPerformance]    Script Date: 30.09.2016 13:08:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE
	[dbo].[pr_testQueryPerformance]
AS
/*
	Procedure for testing performance of queries:
	Takes them from table dbo.Query, executes them and writes resulting execution time and IOs to table QueryResultTest.
*/
BEGIN
	SET NOCOUNT ON;

	DECLARE
		@queryId			INTEGER	= NULL, /* id of query to test */
		@sqltext			NVARCHAR(MAX) = NULL, /* SQL of query to test */
		@executionDateTime	DATETIME = NULL,	/* DateTime of query execution */
		@numberOfRows		INTEGER = NULL /* number of rows returned by query (for SQL 2008) */
	;

	/* cursor for the relevant testqueries */
	DECLARE curSQLText CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
		SELECT  
			qu_id,
			qu_sqlText
		FROM
			dbo.Query
		WHERE
			qu_performTests = 1
	;

	OPEN curSQLText;
	FETCH NEXT FROM curSQLText INTO @queryId, @sqltext
	WHILE @@FETCH_STATUS = 0
	BEGIN
		/* set current executionDateTime */
		SET @executionDateTime = GETDATE();
		/* execute the test query */
		EXECUTE sp_executesql @sqltext;

		SET @numberOfRows = @@ROWCOUNT;


		/* 
			collect results from dm_exec_query_stats: 
				there is a risk that the query would take the "wrong" execution from the plan cache in a highly concurrent environment
				this could be mitigated by adding the query id as comment into the sql text and filtering on that
				however this option was not chosen as this would result in separate plans filling up the plan cache
		*/
		INSERT INTO
			dbo.QueryTestResult
		(
			qtr_qu_id,
			qtr_executionDateTime,
			qtr_logicalReads,
			qtr_elapsedTime,
			qtr_workerTime,
			qtr_numberOfRows,
			qtr_queryPlan
		)
		SELECT TOP 1 
			@queryId, 
			deqs.last_execution_time,
			deqs.last_logical_reads,
			/* convert microseconds into time format */
			DATEADD(MCS,deqs.last_elapsed_time,CAST('0:00' AS TIME)) AS last_elapsed_time,
			DATEADD(MCS,deqs.last_worker_time,CAST('0:00' AS TIME)) AS last_worker_time,
			@numberOfRows,
			deqp.query_plan
		FROM	
					sys.dm_exec_query_stats AS deqs
		CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
		CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
		WHERE 
				dest.text = @sqltext
			AND deqs.last_execution_time >= @executionDateTime
		ORDER BY
			last_execution_time 
		;
		FETCH NEXT FROM curSQLText INTO @queryId, @sqltext
	END
	CLOSE curSQLText;
	DEALLOCATE curSQLText;
END


GO

EXEC sys.sp_addextendedproperty @name=N'Author', @value=N'Martin Guth' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'pr_testQueryPerformance'
GO

EXEC sys.sp_addextendedproperty @name=N'ChangeDate', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'pr_testQueryPerformance'
GO

EXEC sys.sp_addextendedproperty @name=N'ChangeHistory', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'pr_testQueryPerformance'
GO

EXEC sys.sp_addextendedproperty @name=N'CreationDate', @value=N'14.07.2016' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'pr_testQueryPerformance'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Procedure for testing performance of queries:	Takes them from table dbo.Query, executes them and writes resulting execution time and IOs to table QueryResultTest.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'pr_testQueryPerformance'
GO