search

spaghettidba / WorkloadTools

A collection of tools to collect, analyze and replay SQL Server workloads, on premises and in the cloud
MIT License
234 stars 53 forks source link

Analysis via stored procedure #14

Closed spaghettidba closed 5 years ago

spaghettidba commented 6 years ago

At the present moment, the views required to extract the data for the analysis are hardcoded for the "baseline" and "replay" schemas, while the schema is instead free in the input parameters. A stored procedure should be added to the analysis database and should be able to create the views for the analysis in the correct schemas or, in alternative, run the comparison query using the supplied values for the schema. This stored procedure should be added automatically whenever the analysis consumer starts writing to an analysis database.

rducic commented 6 years ago

Looking at the
"__type": "AnalysisConsumer", "ConnectionInfo": { "ServerName": "xxxx", "DatabaseName": "Admin", "SchemaName": "baseline", }

U see baseline objects created but I don't see "replay" schema. Additionally, the powerBI tool is looking for dbo_WorkloadOverView = Source{[Schema="dbo",Item="WorkloadOverView"]}[Data] in dbo_WorkloadOverView But I don't see this view created. Is there anything else I need to execute to create this view ?

spaghettidba commented 6 years ago

Yes, I'm aware of the issue. At the moment, the view must be created manually from the stored procedure included in the database project, passing the two schemas as parameters. The analysis consumer produces data only on one of the schemas, the schema for comparison should be populated from another instance of SqlWorkload running against the repllay server. Obviously, this only makes sense when you're also performing the replay from the first instance of SqlWorkload. At the moment I did not include a PowerBI report to analyze the data from a single SqlWorkload instance, so it can only compare two workloads. Sorry about that. I will include a second report, but I will probably move away from PowerBI because it does not provide all the flexibility I need.

spaghettidba commented 5 years ago

The stored procedure createAnalysisView takes care of creating a view for the analysis based on the last two created schemas in the database. The view is named [dbo].[WorkloadOverview] and is unique in the database: it will always point to the latest schemas used