I started with series of post, I would like to show how to prepare simple testing environment, you can get the data when you test or compare queries performance for example, and you would like to save results and visualise them.
In this post we create simple table structure and SQL procedure which will be used in next post as Chart dataset.
I would like to save following information’s:
- Test identification – identification of test – for example testing clustered vs nanocluster index, sorting group vs hash group, etc.
- Task name – identification of task we made in test – for example task1 = table with clustered index, task2 heap table
- Counter name (for example number of reads, writes, rows, etc.)
- Counter value – measured results of tests
As you can see the structure is very simple, I believe we could design much sophisticated solution but for our purposes will be enough. Next time we can extend our solution.
Here is the final structure:
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE TABLE [dbo].[LabTable](
[WorkoutName] [sysname] NOT NULL,
[TaskOwnerName] [sysname] NOT NULL,
[CounterName] [sysname] NOT NULL,
[CounterValue] [sql_variant] NULL,
[InsertDateTime] [datetime] NULL
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[LabTable] ADD
CONSTRAINT [df_InsertDateTime] DEFAULT (getdate())
FOR [InsertDateTime]
GO
Stored procedure returning data to dataset:
SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SSRSGetDatset]
@WorkoutName SYSNAME
AS
BEGIN
SELECT WorkoutName,
TaskOwnerName,
CounterName,
TRY_CONVERT(BIGINT,TRY_CONVERT(DATETIME, CounterValue))
AS CounterValue,
InsertDateTime
FROM dbo.LabTable
WHERE WorkoutName = @WorkoutName
END
GO
In next post we fill structures with tested data and create graph to visualise our results. Stay tuned.
