Server Driven Test
When enabled retrieves general and workload settings dynamically from a server. Furthermore the test is driven from the server, can exit SQLTest, can be cancelled, another workload can be started.
Stored procedure name for retrieving general and workload settings is “Stored Procedure Prefix”_TestSettings_Proc. Stored procedure name for message handling is “Stored Procedure Prefix”_TestMessages_Proc.
In the below example connection string, SQL command, iterations, duration and thread count are supplied for 2 workloads and the workload is auto started.
The test is executed 2 times, message interval is set to 2.
Message interval specifies frequency of “Workload Running” messages in seconds.
The below example uses “Data Source=SQLWorkshops\SQL2012;Database=test;Integrated Security=true” as “Connection String” and “SQLTest1” as “Stored Procedure Prefix” in test database.
SQLTest1_TestSettings_Proc and SQLTest1_TestMessages_Proc:
use test
go
drop proc SQLTest1_TestSettings_Proc
go
drop table SQLTest1_TestGeneralSettings_Log_Table
go
drop table SQLTest1_TestWorkloadSettings_Log_Table
go
create table SQLTest1_TestGeneralSettings_Log_Table (id int identity primary key, ctime datetime, SQLTestInstanceGUID uniqueidentifier, SQLTestWorkloadGUID uniqueidentifier, SQLTestWorkloadName nvarchar(256), TestCount int, CurrentWorkload int, AbortWorkloadOnError bit, CancelQueryOnDurationExpiration bit, StartAllWorkloadAtStartup bit, ExitOnStartupWorkloadCompletion bit, AllowFindAndReplaceInSQLCommand bit, AllowFindAndReplaceInConnectionString bit, AutoScrollResultsTab bit, IncludeConnectionInformationInResults bit, InReportIncludeWorkloadStatistics bit, InReportIncludeConnectionStatistics bit, InReportIncludeThreadStatistics bit, InReportIncludeDetailedStatistics bit, ApplicationNamePrefix nvarchar(max), NumberOfConnectionRetries int, DelayBeforeConnectionRetry int, NumberOfCommandRetries int, DelayBeforeCommandRetry int, TraceLevel int, CustomWorkload nvarchar(max), NextFileToLoadOnWorkloadCompletion nvarchar(max), Comments nvarchar(max), LogErrors bit, LogMessages bit, LogResults bit, LogTrace bit, LogReport bit, LogTableName nvarchar(max), AppendDateTimeToLogFileName bit, LogFileBufferSize int, LogFileName nvarchar(max), EmailLogFile bit, EmailOnlyOnErrors bit, EmailToAddresses nvarchar(max), EnableStepAtSynchronization bit, SynchronizeIteration bit, SynchronizeConnect bit, SynchronizeBeginTransaction bit, SynchronizeExecuteNonQuery bit, SynchronizeExecuteReader bit, SynchronizeRead bit, SynchronizeNextResult bit, SynchronizeCommitTransaction bit, SynchronizeRollbackTransaction bit, SynchronizeDisconnect bit, IgnoreErrors nvarchar(max), FindAndReplace nvarchar(max), SQLTestConnectionString nvarchar(max), SQLTestStoredProcedurePrefix nvarchar(max), ServerDrivenTest bit, ServerDrivenWorkload bit, EvaluateDBCalls bit, EvaluateResults bit, EvaluateChecksum bit, EvaluateRecordsAffected bit, Response int)
go
create table SQLTest1_TestWorkloadSettings_Log_Table (id int identity primary key, ctime datetime, SQLTestInstanceGUID uniqueidentifier, SQLTestWorkloadGUID uniqueidentifier, SQLTestWorkloadName nvarchar(256), TestCount int, Workload int, ConnectionStrings nvarchar(max), SQLCommands nvarchar(max), IterationsFlag bit, IterationsConfigured int, DurationConfigured int, ThreadsConfigured int, ConnectBeforeEveryIteration bit, BeginTransactionOnceForAllIterations bit, BeginTransactionBeforeEveryIteration bit, CommitTransaction bit, ExecuteNonQuery bit, ExecuteRead bit, StoredProcedure bit, ExecuteNextResult bit, RetrieveInfoMessages bit, FetchAllRows bit, FetchTopRows int, FetchAllResults bit, FetchTopResults int, ParameterizeQuery bit, PrepareQuery bit, SubstituteValues bit, AllowIgnoreErrors bit, CommandTimeout int, ShowResults bit, EnableProviderStatistics bit, TransactionIsolationLevel nvarchar(max), DelayBeforeFirstIterationFrom int, DelayBeforeFirstIterationTo int, DelayBeforeIterationFrom int, DelayBeforeIterationTo int, DelayBeforeConnectFrom int, DelayBeforeConnectTo int, DelayBeforeDisconnectFrom int, DelayBeforeDisconnectTo int, DelayBeforeBeginTransactionFrom int, DelayBeforeBeginTransactionTo int, DelayBeforeCommitTransactionFrom int, DelayBeforeCommitTransactionTo int, DelayBeforeRollbackTransactionFrom int, DelayBeforeRollbackTransactionTo int, DelayBeforeExecuteNonQueryFrom int, DelayBeforeExecuteNonQueryTo int, DelayBeforeExecuteReaderFrom int, DelayBeforeExecuteReaderTo int, DelayBeforeExecuteReadFrom int, DelayBeforeExecuteReadTo int, DelayBeforeExecuteNextResultFrom int, DelayBeforeExecuteNextResultTo int, Response int)
go
drop type GeneralSettings
go
create type GeneralSettings as table (CurrentWorkload int, AbortWorkloadOnError bit, CancelQueryOnDurationExpiration bit, StartAllWorkloadAtStartup bit, ExitOnStartupWorkloadCompletion bit, AllowFindAndReplaceInSQLCommand bit, AllowFindAndReplaceInConnectionString bit, AutoScrollResultsTab bit, IncludeConnectionInformationInResults bit, InReportIncludeWorkloadStatistics bit, InReportIncludeConnectionStatistics bit, InReportIncludeThreadStatistics bit, InReportIncludeDetailedStatistics bit, ApplicationNamePrefix nvarchar(max), NumberOfConnectionRetries int, DelayBeforeConnectionRetry int, NumberOfCommandRetries int, DelayBeforeCommandRetry int, TraceLevel int, CustomWorkload nvarchar(max), NextFileToLoadOnWorkloadCompletion nvarchar(max), Comments nvarchar(max), LogErrors bit, LogMessages bit, LogResults bit, LogTrace bit, LogReport bit, LogTableName nvarchar(max), AppendDateTimeToLogFileName bit, LogFileBufferSize int, LogFileName nvarchar(max), EmailLogFile bit, EmailOnlyOnErrors bit, EmailToAddresses nvarchar(max), EnableStepAtSynchronization bit, SynchronizeIteration bit, SynchronizeConnect bit, SynchronizeBeginTransaction bit, SynchronizeExecuteNonQuery bit, SynchronizeExecuteReader bit, SynchronizeRead bit, SynchronizeNextResult bit, SynchronizeCommitTransaction bit, SynchronizeRollbackTransaction bit, SynchronizeDisconnect bit, IgnoreErrors nvarchar(max), FindAndReplace nvarchar(max), SQLTestConnectionString nvarchar(max), SQLTestStoredProcedurePrefix nvarchar(max), ServerDrivenTest bit, ServerDrivenWorkload bit, EvaluateDBCalls bit, EvaluateResults bit, EvaluateChecksum bit, EvaluateRecordsAffected bit)
go
drop type WorkloadSettings
go
create type WorkloadSettings as table (Workload int, ConnectionStrings nvarchar(max), SQLCommands nvarchar(max), IterationsFlag bit, IterationsConfigured int, DurationConfigured int, ThreadsConfigured int, ConnectBeforeEveryIteration bit, BeginTransactionOnceForAllIterations bit, BeginTransactionBeforeEveryIteration bit, CommitTransaction bit, ExecuteNonQuery bit, ExecuteRead bit, StoredProcedure bit, ExecuteNextResult bit, RetrieveInfoMessages bit, FetchAllRows bit, FetchTopRows int, FetchAllResults bit, FetchTopResults int, ParameterizeQuery bit, PrepareQuery bit, SubstituteValues bit, AllowIgnoreErrors bit, CommandTimeout int, ShowResults bit, EnableProviderStatistics bit, TransactionIsolationLevel nvarchar(max), DelayBeforeFirstIterationFrom int, DelayBeforeFirstIterationTo int, DelayBeforeIterationFrom int, DelayBeforeIterationTo int, DelayBeforeConnectFrom int, DelayBeforeConnectTo int, DelayBeforeDisconnectFrom int, DelayBeforeDisconnectTo int, DelayBeforeBeginTransactionFrom int, DelayBeforeBeginTransactionTo int, DelayBeforeCommitTransactionFrom int, DelayBeforeCommitTransactionTo int, DelayBeforeRollbackTransactionFrom int, DelayBeforeRollbackTransactionTo int, DelayBeforeExecuteNonQueryFrom int, DelayBeforeExecuteNonQueryTo int, DelayBeforeExecuteReaderFrom int, DelayBeforeExecuteReaderTo int, DelayBeforeExecuteReadFrom int, DelayBeforeExecuteReadTo int, DelayBeforeExecuteNextResultFrom int, DelayBeforeExecuteNextResultTo int)
go
create proc SQLTest1_TestSettings_Proc @SQLTestInstanceGUID as uniqueidentifier, @SQLTestWorkloadGUID uniqueidentifier, @SQLTestWorkloadName nvarchar(256), @TestCount int, @GeneralSettings GeneralSettings readonly, @WorkloadSettings WorkloadSettings readonly
as
begin
declare @tab1 table (id int)
declare @tab2 table (id1 int identity, id int)
declare @tab3 table (id int)
declare @tab4 table (id1 int identity, id int)
insert into SQLTest1_TestGeneralSettings_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, TestCount, CurrentWorkload, AbortWorkloadOnError, CancelQueryOnDurationExpiration, StartAllWorkloadAtStartup, ExitOnStartupWorkloadCompletion, AllowFindAndReplaceInSQLCommand, AllowFindAndReplaceInConnectionString, AutoScrollResultsTab, IncludeConnectionInformationInResults, InReportIncludeWorkloadStatistics, InReportIncludeConnectionStatistics, InReportIncludeThreadStatistics, InReportIncludeDetailedStatistics, ApplicationNamePrefix, NumberOfConnectionRetries, DelayBeforeConnectionRetry, NumberOfCommandRetries, DelayBeforeCommandRetry, TraceLevel, CustomWorkload, NextFileToLoadOnWorkloadCompletion, Comments, LogErrors, LogMessages, LogResults, LogTrace, LogReport, LogTableName, AppendDateTimeToLogFileName, LogFileBufferSize, LogFileName, EmailLogFile, EmailOnlyOnErrors, EmailToAddresses, EnableStepAtSynchronization, SynchronizeIteration, SynchronizeConnect, SynchronizeBeginTransaction, SynchronizeExecuteNonQuery, SynchronizeExecuteReader, SynchronizeRead, SynchronizeNextResult, SynchronizeCommitTransaction, SynchronizeRollbackTransaction, SynchronizeDisconnect, IgnoreErrors, FindAndReplace, SQLTestConnectionString, SQLTestStoredProcedurePrefix, ServerDrivenTest, ServerDrivenWorkload, EvaluateDBCalls, EvaluateResults, EvaluateChecksum, EvaluateRecordsAffected) output inserted.id into @tab1 select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @TestCount, CurrentWorkload, AbortWorkloadOnError, CancelQueryOnDurationExpiration, StartAllWorkloadAtStartup, ExitOnStartupWorkloadCompletion, AllowFindAndReplaceInSQLCommand, AllowFindAndReplaceInConnectionString, AutoScrollResultsTab, IncludeConnectionInformationInResults, InReportIncludeWorkloadStatistics, InReportIncludeConnectionStatistics, InReportIncludeThreadStatistics, InReportIncludeDetailedStatistics, ApplicationNamePrefix, NumberOfConnectionRetries, DelayBeforeConnectionRetry, NumberOfCommandRetries, DelayBeforeCommandRetry, TraceLevel, CustomWorkload, NextFileToLoadOnWorkloadCompletion, Comments, LogErrors, LogMessages, LogResults, LogTrace, LogReport, LogTableName, AppendDateTimeToLogFileName, LogFileBufferSize, LogFileName, EmailLogFile, EmailOnlyOnErrors, EmailToAddresses, EnableStepAtSynchronization, SynchronizeIteration, SynchronizeConnect, SynchronizeBeginTransaction, SynchronizeExecuteNonQuery, SynchronizeExecuteReader, SynchronizeRead, SynchronizeNextResult, SynchronizeCommitTransaction, SynchronizeRollbackTransaction, SynchronizeDisconnect, IgnoreErrors, FindAndReplace, SQLTestConnectionString, SQLTestStoredProcedurePrefix, ServerDrivenTest, ServerDrivenWorkload, EvaluateDBCalls, EvaluateResults, EvaluateChecksum, EvaluateRecordsAffected from @GeneralSettings
insert into SQLTest1_TestWorkloadSettings_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, TestCount, Workload, ConnectionStrings, SQLCommands, IterationsFlag, IterationsConfigured, DurationConfigured, ThreadsConfigured, ConnectBeforeEveryIteration, BeginTransactionOnceForAllIterations, BeginTransactionBeforeEveryIteration, CommitTransaction, ExecuteNonQuery, ExecuteRead, StoredProcedure, ExecuteNextResult, RetrieveInfoMessages, FetchAllRows, FetchTopRows, FetchAllResults, FetchTopResults, ParameterizeQuery, PrepareQuery, SubstituteValues, AllowIgnoreErrors, CommandTimeout, ShowResults, EnableProviderStatistics, TransactionIsolationLevel, DelayBeforeFirstIterationFrom, DelayBeforeFirstIterationTo, DelayBeforeIterationFrom, DelayBeforeIterationTo, DelayBeforeConnectFrom, DelayBeforeConnectTo, DelayBeforeDisconnectFrom, DelayBeforeDisconnectTo, DelayBeforeBeginTransactionFrom, DelayBeforeBeginTransactionTo, DelayBeforeCommitTransactionFrom, DelayBeforeCommitTransactionTo, DelayBeforeRollbackTransactionFrom, DelayBeforeRollbackTransactionTo, DelayBeforeExecuteNonQueryFrom, DelayBeforeExecuteNonQueryTo, DelayBeforeExecuteReaderFrom, DelayBeforeExecuteReaderTo, DelayBeforeExecuteReadFrom, DelayBeforeExecuteReadTo, DelayBeforeExecuteNextResultFrom, DelayBeforeExecuteNextResultTo) output inserted.id into @tab2 select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @TestCount, Workload, ConnectionStrings, SQLCommands, IterationsFlag, IterationsConfigured, DurationConfigured, ThreadsConfigured, ConnectBeforeEveryIteration, BeginTransactionOnceForAllIterations, BeginTransactionBeforeEveryIteration, CommitTransaction, ExecuteNonQuery, ExecuteRead, StoredProcedure, ExecuteNextResult, RetrieveInfoMessages, FetchAllRows, FetchTopRows, FetchAllResults, FetchTopResults, ParameterizeQuery, PrepareQuery, SubstituteValues, AllowIgnoreErrors, CommandTimeout, ShowResults, EnableProviderStatistics, TransactionIsolationLevel, DelayBeforeFirstIterationFrom, DelayBeforeFirstIterationTo, DelayBeforeIterationFrom, DelayBeforeIterationTo, DelayBeforeConnectFrom, DelayBeforeConnectTo, DelayBeforeDisconnectFrom, DelayBeforeDisconnectTo, DelayBeforeBeginTransactionFrom, DelayBeforeBeginTransactionTo, DelayBeforeCommitTransactionFrom, DelayBeforeCommitTransactionTo, DelayBeforeRollbackTransactionFrom, DelayBeforeRollbackTransactionTo, DelayBeforeExecuteNonQueryFrom, DelayBeforeExecuteNonQueryTo, DelayBeforeExecuteReaderFrom, DelayBeforeExecuteReaderTo, DelayBeforeExecuteReadFrom, DelayBeforeExecuteReadTo, DelayBeforeExecuteNextResultFrom, DelayBeforeExecuteNextResultTo from @WorkloadSettings Order by Workload
insert into SQLTest1_TestGeneralSettings_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, TestCount, CurrentWorkload, AbortWorkloadOnError, CancelQueryOnDurationExpiration, StartAllWorkloadAtStartup, ExitOnStartupWorkloadCompletion, AllowFindAndReplaceInSQLCommand, AllowFindAndReplaceInConnectionString, AutoScrollResultsTab, IncludeConnectionInformationInResults, InReportIncludeWorkloadStatistics, InReportIncludeConnectionStatistics, InReportIncludeThreadStatistics, InReportIncludeDetailedStatistics, ApplicationNamePrefix, NumberOfConnectionRetries, DelayBeforeConnectionRetry, NumberOfCommandRetries, DelayBeforeCommandRetry, TraceLevel, CustomWorkload, NextFileToLoadOnWorkloadCompletion, Comments, LogErrors, LogMessages, LogResults, LogTrace, LogReport, LogTableName, AppendDateTimeToLogFileName, LogFileBufferSize, LogFileName, EmailLogFile, EmailOnlyOnErrors, EmailToAddresses, EnableStepAtSynchronization, SynchronizeIteration, SynchronizeConnect, SynchronizeBeginTransaction, SynchronizeExecuteNonQuery, SynchronizeExecuteReader, SynchronizeRead, SynchronizeNextResult, SynchronizeCommitTransaction, SynchronizeRollbackTransaction, SynchronizeDisconnect, IgnoreErrors, FindAndReplace, SQLTestConnectionString, SQLTestStoredProcedurePrefix, ServerDrivenTest, ServerDrivenWorkload, EvaluateDBCalls, EvaluateResults, EvaluateChecksum, EvaluateRecordsAffected) output inserted.id into @tab3 select ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, TestCount, CurrentWorkload, AbortWorkloadOnError, CancelQueryOnDurationExpiration, StartAllWorkloadAtStartup, ExitOnStartupWorkloadCompletion, AllowFindAndReplaceInSQLCommand, AllowFindAndReplaceInConnectionString, AutoScrollResultsTab, IncludeConnectionInformationInResults, InReportIncludeWorkloadStatistics, InReportIncludeConnectionStatistics, InReportIncludeThreadStatistics, InReportIncludeDetailedStatistics, ApplicationNamePrefix, NumberOfConnectionRetries, DelayBeforeConnectionRetry, NumberOfCommandRetries, DelayBeforeCommandRetry, TraceLevel, CustomWorkload, NextFileToLoadOnWorkloadCompletion, Comments, LogErrors, LogMessages, LogResults, LogTrace, LogReport, LogTableName, AppendDateTimeToLogFileName, LogFileBufferSize, LogFileName, EmailLogFile, EmailOnlyOnErrors, EmailToAddresses, EnableStepAtSynchronization, SynchronizeIteration, SynchronizeConnect, SynchronizeBeginTransaction, SynchronizeExecuteNonQuery, SynchronizeExecuteReader, SynchronizeRead, SynchronizeNextResult, SynchronizeCommitTransaction, SynchronizeRollbackTransaction, SynchronizeDisconnect, IgnoreErrors, FindAndReplace, SQLTestConnectionString, SQLTestStoredProcedurePrefix, ServerDrivenTest, ServerDrivenWorkload, EvaluateDBCalls, EvaluateResults, EvaluateChecksum, EvaluateRecordsAffected from SQLTest1_TestGeneralSettings_Log_Table where id = (select id from @tab1)
update SQLTest1_TestGeneralSettings_Log_Table set
StartAllWorkloadAtStartup = 1
where id = (select id from @tab3)
update SQLTest1_TestGeneralSettings_Log_Table set Response = (select id from @tab3) where id = (select id from @tab1)
insert into SQLTest1_TestWorkloadSettings_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, TestCount, Workload, ConnectionStrings, SQLCommands, IterationsFlag, IterationsConfigured, DurationConfigured, ThreadsConfigured, ConnectBeforeEveryIteration, BeginTransactionOnceForAllIterations, BeginTransactionBeforeEveryIteration, CommitTransaction, ExecuteNonQuery, ExecuteRead, StoredProcedure, ExecuteNextResult, RetrieveInfoMessages, FetchAllRows, FetchTopRows, FetchAllResults, FetchTopResults, ParameterizeQuery, PrepareQuery, SubstituteValues, AllowIgnoreErrors, CommandTimeout, ShowResults, EnableProviderStatistics, TransactionIsolationLevel, DelayBeforeFirstIterationFrom, DelayBeforeFirstIterationTo, DelayBeforeIterationFrom, DelayBeforeIterationTo, DelayBeforeConnectFrom, DelayBeforeConnectTo, DelayBeforeDisconnectFrom, DelayBeforeDisconnectTo, DelayBeforeBeginTransactionFrom, DelayBeforeBeginTransactionTo, DelayBeforeCommitTransactionFrom, DelayBeforeCommitTransactionTo, DelayBeforeRollbackTransactionFrom, DelayBeforeRollbackTransactionTo, DelayBeforeExecuteNonQueryFrom, DelayBeforeExecuteNonQueryTo, DelayBeforeExecuteReaderFrom, DelayBeforeExecuteReaderTo, DelayBeforeExecuteReadFrom, DelayBeforeExecuteReadTo, DelayBeforeExecuteNextResultFrom, DelayBeforeExecuteNextResultTo) output inserted.id into @tab4 select ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, TestCount, Workload, ConnectionStrings, SQLCommands, IterationsFlag, IterationsConfigured, DurationConfigured, ThreadsConfigured, ConnectBeforeEveryIteration, BeginTransactionOnceForAllIterations, BeginTransactionBeforeEveryIteration, CommitTransaction, ExecuteNonQuery, ExecuteRead, StoredProcedure, ExecuteNextResult, RetrieveInfoMessages, FetchAllRows, FetchTopRows, FetchAllResults, FetchTopResults, ParameterizeQuery, PrepareQuery, SubstituteValues, AllowIgnoreErrors, CommandTimeout, ShowResults, EnableProviderStatistics, TransactionIsolationLevel, DelayBeforeFirstIterationFrom, DelayBeforeFirstIterationTo, DelayBeforeIterationFrom, DelayBeforeIterationTo, DelayBeforeConnectFrom, DelayBeforeConnectTo, DelayBeforeDisconnectFrom, DelayBeforeDisconnectTo, DelayBeforeBeginTransactionFrom, DelayBeforeBeginTransactionTo, DelayBeforeCommitTransactionFrom, DelayBeforeCommitTransactionTo, DelayBeforeRollbackTransactionFrom, DelayBeforeRollbackTransactionTo, DelayBeforeExecuteNonQueryFrom, DelayBeforeExecuteNonQueryTo, DelayBeforeExecuteReaderFrom, DelayBeforeExecuteReaderTo, DelayBeforeExecuteReadFrom, DelayBeforeExecuteReadTo, DelayBeforeExecuteNextResultFrom, DelayBeforeExecuteNextResultTo from SQLTest1_TestWorkloadSettings_Log_Table where id in (select id from @tab2) order by id
update SQLTest1_TestWorkloadSettings_Log_Table set
ConnectionStrings = 'Data Source=SQLWorkshops\SQL2012;Database=master;Integrated Security=true;Pooling=false' + char(13) + char(10) + 'Data Source=SQLWorkshops\SQL2014;Database=master;Integrated Security=true;Pooling=false',
SQLCommands = 'waitfor delay ''00:00:05''',
IterationsFlag = 1,
IterationsConfigured = 3,
ThreadsConfigured = 2
where id in (select id from @tab4) and Workload = 1
update SQLTest1_TestWorkloadSettings_Log_Table set
ConnectionStrings = 'Data Source=SQLWorkshops\SQL2014;Database=master;Integrated Security=true;Pooling=false',
SQLCommands = 'select @@version',
IterationsFlag = 0,
DurationConfigured = 10,
ThreadsConfigured = 1
where id in (select id from @tab4) and Workload = 2
update SQLTest1_TestWorkloadSettings_Log_Table set
ThreadsConfigured = 0
where id in (select id from @tab4) and Workload = 3
update SQLTest1_TestWorkloadSettings_Log_Table set
ThreadsConfigured = 0
where id in (select id from @tab4) and Workload = 4
update SQLTest1_TestWorkloadSettings_Log_Table set Response = t4.id from SQLTest1_TestWorkloadSettings_Log_Table t inner join @tab2 t2 on (t.id = t2.id) inner join @tab4 t4 on (t2.id1 = t4.id1) where t.id in (select id from @tab2)
select CurrentWorkload, AbortWorkloadOnError, CancelQueryOnDurationExpiration, StartAllWorkloadAtStartup, ExitOnStartupWorkloadCompletion, AllowFindAndReplaceInSQLCommand, AllowFindAndReplaceInConnectionString, AutoScrollResultsTab, IncludeConnectionInformationInResults, InReportIncludeWorkloadStatistics, InReportIncludeConnectionStatistics, InReportIncludeThreadStatistics, InReportIncludeDetailedStatistics, ApplicationNamePrefix, NumberOfConnectionRetries, DelayBeforeConnectionRetry, NumberOfCommandRetries, DelayBeforeCommandRetry, TraceLevel, CustomWorkload, NextFileToLoadOnWorkloadCompletion, Comments, LogErrors, LogMessages, LogResults, LogTrace, LogReport, LogTableName, AppendDateTimeToLogFileName, LogFileBufferSize, LogFileName, EmailLogFile, EmailOnlyOnErrors, EmailToAddresses, EnableStepAtSynchronization, SynchronizeIteration, SynchronizeConnect, SynchronizeBeginTransaction, SynchronizeExecuteNonQuery, SynchronizeExecuteReader, SynchronizeRead, SynchronizeNextResult, SynchronizeCommitTransaction, SynchronizeRollbackTransaction, SynchronizeDisconnect, IgnoreErrors, FindAndReplace, SQLTestConnectionString, SQLTestStoredProcedurePrefix, ServerDrivenTest, ServerDrivenWorkload, EvaluateDBCalls, EvaluateResults, EvaluateChecksum, EvaluateRecordsAffected from SQLTest1_TestGeneralSettings_Log_Table where id = (select id from @tab3)
select Workload, ConnectionStrings, SQLCommands, IterationsFlag, IterationsConfigured, DurationConfigured, ThreadsConfigured, ConnectBeforeEveryIteration, BeginTransactionOnceForAllIterations, BeginTransactionBeforeEveryIteration, CommitTransaction, ExecuteNonQuery, ExecuteRead, StoredProcedure, ExecuteNextResult, RetrieveInfoMessages, FetchAllRows, FetchTopRows, FetchAllResults, FetchTopResults, ParameterizeQuery, PrepareQuery, SubstituteValues, AllowIgnoreErrors, CommandTimeout, ShowResults, EnableProviderStatistics, TransactionIsolationLevel, DelayBeforeFirstIterationFrom, DelayBeforeFirstIterationTo, DelayBeforeIterationFrom, DelayBeforeIterationTo, DelayBeforeConnectFrom, DelayBeforeConnectTo, DelayBeforeDisconnectFrom, DelayBeforeDisconnectTo, DelayBeforeBeginTransactionFrom, DelayBeforeBeginTransactionTo, DelayBeforeCommitTransactionFrom, DelayBeforeCommitTransactionTo, DelayBeforeRollbackTransactionFrom, DelayBeforeRollbackTransactionTo, DelayBeforeExecuteNonQueryFrom, DelayBeforeExecuteNonQueryTo, DelayBeforeExecuteReaderFrom, DelayBeforeExecuteReaderTo, DelayBeforeExecuteReadFrom, DelayBeforeExecuteReadTo, DelayBeforeExecuteNextResultFrom, DelayBeforeExecuteNextResultTo from SQLTest1_TestWorkloadSettings_Log_Table where id in (select id from @tab4) order by id
end
go
drop proc SQLTest1_TestMessages_Proc
go
drop table SQLTest1_TestMessages_Log_Table
go
create table SQLTest1_TestMessages_Log_Table (id int identity primary key, ctime datetime, SQLTestInstanceGUID uniqueidentifier, SQLTestWorkloadName nvarchar(256), SQLTestWorkloadGUID uniqueidentifier, TestCount int, MessageCount int, Message nvarchar(max), Response nvarchar(max))
go
create proc SQLTest1_TestMessages_Proc @SQLTestInstanceGUID uniqueidentifier, @SQLTestWorkloadGUID uniqueidentifier, @SQLTestWorkloadName nvarchar(256), @TestCount int, @MessageCount int, @Message nvarchar(max)
as
begin
declare @tab table (id int)
insert into SQLTest1_TestMessages_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, TestCount, MessageCount, Message) output inserted.id into @tab select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @TestCount, @MessageCount, @Message
declare @Response nvarchar(max) = ''
if (@Message like 'Workload Starting%')
begin
select @Response = 'MessageInterval = 2'
end
else if (@Message like 'Workload Exiting%')
begin
if (@TestCount < 2)
select @Response = 'ServerDrivenTest'
--else
-- select @Response = 'Exit'
end
--else if (@TestCount = 2 and @Message like 'Workload Running%')
--begin
-- if(exists (select * from SQLTest1_TestMessages_Log_Table where SQLTestInstanceGUID = @SQLTestInstanceGUID and SQLTestWorkloadGUID = @SQLTestWorkloadGUID and SQLTestWorkloadName = @SQLTestWorkloadName and ctime < dateadd(second, -5, getdate())))
-- select @Response = 'Cancel'
--end
update SQLTest1_TestMessages_Log_Table set Response = @Response where id = (select id from @tab)
select Response from SQLTest1_TestMessages_Log_Table where id = (select id from @tab)
end
go
select * from SQLTest1_TestGeneralSettings_Log_Table
go
select * from SQLTest1_TestWorkloadSettings_Log_Table
go
select * from SQLTest1_TestMessages_Log_Table
go
Please help us improve this page by entering your comments and suggestions below: