A couple of weeks ago I posted a method to convert trace files from the SQL Server 2012 format to the SQL Server 2008 format.
The trick works quite well and the trace file can be opened with Profiler or with ReadTrace from RML Utilities. What doesn’t seem to work just as well is the trace replay with Ostress (another great tool bundled in the RML Utilities).
For some reason, OStress refuses to replay the whole trace file and starts throwing lots of errors.
Some errors are due to the workload I was replaying (it contains CREATE TABLE statements and that can obviuosly work just the first time it is issued), but some others seem to be due to parsing errors, probably because of differences in the trace format between version 11 and 10.
11/20/12 12:30:39.008 [0x00001040] File C:\RML\SQL00063.rml: Parser Error: [Error: 60500][State: 1][Abs Char: 1068][Seq: 0] Syntax error [parse error, expecting `tok_RML_END_RPC'] encountered near 0x0000042C: 6C000D00 0A005700 48004500 52004500 l.....W.H.E.R.E. 0x0000043C: 20005500 6E006900 74005000 72006900 .U.n.i.t.P.r.i. 0x0000044C: 63006500 20002600 6C007400 3B002000 c.e. .&.l.t.;. . 0x0000045C: 24003500 2E003000 30000D00 0A004F00 $.5...0.0.....O. 0x0000046C: 52004400 45005200 20004200 59002000 R.D.E.R. .B.Y. . 0x0000047C: 50007200 6F006400 75006300 74004900 P.r.o.d.u.c.t.I. 0x0000048C: 44002C00 20004C00 69006E00 65005400 D.,. .L.i.n.e.T. 0x0000049C: 6F007400 61006C00 3B000D00 0A003C00 o.t.a.l.;.....<. 0x000004AC: 2F004300 4D004400 3E000D00 0A003C00 /.C.M.D.>.....<. 0x000004BC: 2F004C00 41004E00 47003E00 0D000A00 /.L.A.N.G.>..... 0x000004CC: 11/20/12 12:30:39.010 [0x00001040] File C:\RML\SQL00063.rml: Parser Error: [Error: 110010][State: 100][Abs Char: 1068][Seq: 0] SYNTAX ERROR: Parser is unable to safely recover. Correct the errors and try again.
The error suggests that the two formats are indeed more different than I supposed, thus making the replay with Ostress a bit unrealiable.
Are there other options?
Sure there are! Profiler is another tool that allows replaying the workload, even if some limitations apply. For instance, Profiler cannot be scripted, which is a huge limitation if you are using Ostress in benchmarking script and want to replace it with something else.
That “something else” could actually be the Distributed Replay feature introduced in SQL Server 2012.
Basically, Distributed Replay does the same things that Ostress does and even more, with the nice addition of the possibility to start the replay on multiple machines, thus simulating a workload that resembles more the one found in production.
An introduction to Distributed Replay can be found on Jonathan Kehayias’ blog and I will refrain from going into deep details here: those posts are outstanding and there’s very little I could add to that.
Installing the Distributed Replay feature
The first step for the installation is adding a new user for the distributed replay services. You could actually use separate accounts for the Controller and Client services, but for a quick demo a single user is enough.
The Distributed Replay Controller and Client features must be selected from the Feature Selection dialog of SQLServer setup:
In the next steps of the setup you will also be asked the service accounts to use for the services and on the Replay Client page you will have to enter the controller name and the working directories.
Once the setup is over, you will find two new services in the Server Manager:
After starting the services (first the Controller, then the Client), you can go to the log directories and check in the log files if everything is working.
The two files to check are in the following folders:
- C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\Log
- C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\Log
Just to prove one more time that “if something can wrong, it will”, the client log will probably contain an obnoxious error message.
Setting up the distributed replay services can get tricky because of some permissions needed to let the client connect to the controller. Unsurprisingly, the client/controller communication is provided by DCOM, which must be configured correctly.
Without granting the appropriate permissions, in the distributed replay client log file you may find the following message:
2012-11-03 00:43:04:062 CRITICAL [Client Service] [0xC8100005 (6)] Failed to connect controller with error code 0x80070005.
In practical terms, the service account that executes the distributed replay controller service must be granted permissions to use the DCOM class locally and through the network:
- Run dcomcnfg.exe
- Navigate the tree to Console Root, Component Services, Computers, My Computer, DCOM Config, DReplayController
- Right click DReplayController and choose “properties” from the context menu.
- Click the Security tab
- Click the “Launch and Activation Permissions” edit button and grant “Local Activation” and “Remote Activation” permissions to the service account
- Click the “Access Permissions” edit button and grant “Local Access” and “Remote Access” permissions to the service account
- Add the service user account to the “Distributed COM Users” group
- Restart the distributed replay controller and client services
After restarting the services, you will find that the message in the log file has changed:
2012-11-20 14:01:10:783 OPERATIONAL [Client Service] Registered with controller "WIN2012_SQL2012".
Using the Replay feature
Once the services are successfully started, we can now start using the Distributed Replay feature.
The trace file has to meet the same requirements for replay found in Profiler, thus making the “Replay” trace template suitable for the job.
But there’s one more step needed before we can replay the trace file, which cannot be replayed directly. In fact, distributed replay needs to work on a trace stub, obtained preprocessing the original trace file.
The syntax to obtain the stub is the following:
"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\dreplay.exe" preprocess -i "C:\SomePath\replay_trace.trc" -d "C:\SomePath\preprocessDir"
Now that the trace stub is ready, we can start the replay admin tool from the command line, using the following syntax:
"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\dreplay.exe" replay -s "targetServerName" -d "C:\SomePath\preprocessDir" -w "list,of,allowed,client,names"
A final word
A comparison of the features found in the different replay tools can be found in the following table:
The Distributed Replay Controller can act as a replacement for Ostress, except for the ability to replay SQL and RML files.
Will we be using RML Utilities again in the future? Maybe: it depends on what Microsoft decides to do with this tool. It’s not unlikely that the Distributed Replay feature will replace the RML Utilities entirely. The tracing feature itself has an unceartain future ahead, with the deprecation in SQL Server 2012. Probably this new feature will disappear in the next versions of SQLServer, or it will be ported to the Extended Events instrastructure, who knows?
One thing is sure: today we have three tools that support replaying trace files and seeing this possibilty disappear in the future would be very disappointing. I’m sure SQL Server will never disappoint us. 🙂
It’s been quite a lot since I last posted on this blog and I apologize with my readers, both of them :-).
Today I would like to share with you a handy script I coded recently during a SQL Server health check. One of the tools I find immensely valuable for conducting a SQL Server assessment is Glenn Berry’s SQL Server Diagnostic Information Queries. The script contains several queries that can help you collect and analyze a whole lot of information about a SQL Server instance and I use it quite a lot.
The script comes with a blank results spreadsheet, that can be used to save the information gathered by the individual queries. Basically, the spreadsheet is organized in tabs, one for each query and has no preformatted column names, so that you can run the query, select the whole results grid, copy with headers and paste everything to the appropriate tab.
When working with multiple instances, SSMS can help automating this task with multiserver queries. Depending on your SSMS settings, the results of a multiserver query can be merged into a single grid, with an additional column holding the server name.
This feature is very handy, because it lets you run a statement against multiple servers without changing the statement itself.
This works very well for the queries in the first part of Glenn Berry’s script, which is dedicated to instance-level checks. The second part of the script is database-specific and you have to repeat the run+copy+paste process for each database in your instance.
It would be great if there was a feature in SSMS that allowed you to obtain the same results as the multiserver queries, scaled down to the database level. Unfortunately, SSMS has no such feature and the only possible solution is to code it yourself… or borrow my script!
Before rushing to the code, let’s describe briefly the idea behind and the challenges involved.
It would be quite easy to take a single statement and use it with sp_MsForEachDB, but this solution has several shortcomings:
- The results would display as individual grids
- There would be no easy way to determine which results grid belongs to which database
- The statement would have to be surrounded with quotes and existing quotes would have to be doubled, with an increased and unwanted complexity
The ideal tool for this task should simply take a statement and run it against all [user] databases without modifying the statement at all, merge the results in a single result set and add an additional column to hold the database name. Apparently, sp_MSForEachDB, besides being undocumented and potentially nasty, is not the right tool for the job.
That said, the only option left is to capture the statement from its query window, combining a trace, a loopback linked server and various other tricks.
Here’s the code:
-- ============================================= -- Author: Gianluca Sartori - @spaghettidba -- Create date: 2012-06-26 -- Description: Records statements to replay -- against all databases. -- ============================================= CREATE PROCEDURE replay_statements_on_each_db @action varchar(10) = 'RECORD', @start_statement_id int = NULL, @end_statement_id int = NULL AS BEGIN SET NOCOUNT ON; DECLARE @TraceFile nvarchar(256); DECLARE @TraceFileNoExt nvarchar(256); DECLARE @LastPathSeparator int; DECLARE @TracePath nvarchar(256); DECLARE @TraceID int; DECLARE @fs bigint = 5; DECLARE @r int; DECLARE @spiid int = @@SPID; DECLARE @srv nvarchar(4000); DECLARE @ErrorMessage nvarchar(4000); DECLARE @ErrorSeverity int; DECLARE @ErrorState int; DECLARE @sql nvarchar(max); DECLARE @statement nvarchar(max); DECLARE @column_list nvarchar(max); IF @action NOT IN ('RECORD','STOPRECORD','SHOWQUERY','REPLAY') RAISERROR('A valid @action (RECORD,STOPRECORD,SHOWQUERY,REPLAY) must be specified.',16,1) -- *********************************************** -- -- * RECORD * -- -- *********************************************** -- IF @action = 'RECORD' BEGIN BEGIN TRY -- Identify the path of the default trace SELECT @TraceFile = path FROM master.sys.traces WHERE id = 1 -- Split the directory / filename parts of the path SELECT @LastPathSeparator = MAX(number) FROM master.dbo.spt_values WHERE type = 'P' AND number BETWEEN 1 AND LEN(@tracefile) AND CHARINDEX('\', @TraceFile, number) = number --' fix WordPress's sql parser quirks' SELECT @TraceFile = SUBSTRING( @TraceFile ,1 ,@LastPathSeparator ) + 'REPLAY_' + CONVERT(char(8),GETDATE(),112) + REPLACE(CONVERT(varchar(8),GETDATE(),108),':','') + '.trc' SET @TraceFileNoExt = REPLACE(@TraceFile,N'.trc',N'') -- create trace EXEC sp_trace_create @TraceID OUTPUT, 0, @TraceFileNoExt, @fs, NULL; --add filters and events EXEC sp_trace_setevent @TraceID, 41, 1, 1; EXEC sp_trace_setevent @TraceID, 41, 12, 1; EXEC sp_trace_setevent @TraceID, 41, 13, 1; EXEC sp_trace_setfilter @TraceID, 1, 0, 7, N'%fn_trace_gettable%' EXEC sp_trace_setfilter @TraceID, 1, 0, 7, N'%replay_statements_on_each_db%' EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @spiid --start the trace EXEC sp_trace_setstatus @TraceID, 1 --create a global temporary table to store the statements IF OBJECT_ID('tempdb..##replay_info') IS NOT NULL DROP TABLE ##replay_info; CREATE TABLE ##replay_info ( trace_id int, statement_id int, statement_text nvarchar(max) ); --save the trace id in the global temp table INSERT INTO ##replay_info (trace_id) VALUES(@TraceID); END TRY BEGIN CATCH --cleanup the trace IF EXISTS( SELECT 1 FROM sys.traces WHERE id = @TraceId AND status = 1 ) EXEC sp_trace_setstatus @TraceID, 0; IF EXISTS( SELECT 1 FROM sys.traces WHERE id = @TraceId AND status = 0 ) EXEC sp_trace_setstatus @TraceID, 2; IF OBJECT_ID('tempdb..##replay_info') IS NOT NULL DROP TABLE ##replay_info; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END -- *********************************************** -- -- * STOP RECORDING * -- -- *********************************************** -- IF @action = 'STOPRECORD' BEGIN BEGIN TRY -- gather the trace id SELECT @TraceID = trace_id FROM ##replay_info; IF @TraceId IS NULL RAISERROR('No data has been recorded!',16,1) DELETE FROM ##replay_info; -- identify the trace file SELECT TOP(1) @TraceFile = path FROM sys.traces WHERE path like '%REPLAY[_]______________.trc' ORDER BY id DESC -- populate the global temporary table with -- the statements recorded in the INSERT INTO ##replay_info SELECT @TraceID, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), TextData FROM fn_trace_gettable(@traceFile, DEFAULT) WHERE TextData IS NOT NULL; --stop and deltete the trace IF EXISTS( SELECT 1 FROM sys.traces WHERE id = @TraceId AND status = 1 ) EXEC sp_trace_setstatus @TraceID, 0; IF EXISTS( SELECT 1 FROM sys.traces WHERE id = @TraceId AND status = 0 ) EXEC sp_trace_setstatus @TraceID, 2; END TRY BEGIN CATCH --stop and deltete the trace IF EXISTS( SELECT 1 FROM sys.traces WHERE id = @TraceId AND status = 1 ) EXEC sp_trace_setstatus @TraceID, 0; IF EXISTS( SELECT 1 FROM sys.traces WHERE id = @TraceId AND status = 0 ) EXEC sp_trace_setstatus @TraceID, 2; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END -- *********************************************** -- -- * SHOW COLLECTED QUERIES * -- -- *********************************************** -- IF @action = 'SHOWQUERY' BEGIN BEGIN TRY IF OBJECT_ID('tempdb..##replay_info') IS NULL RAISERROR('No data has been recorded yet',16,1); SET @sql = 'SELECT statement_id, statement_text FROM ##replay_info '; IF @start_statement_id IS NOT NULL AND @end_statement_id IS NULL SET @sql = @sql + ' WHERE statement_id = @start_statement_id '; IF @start_statement_id IS NOT NULL AND @end_statement_id IS NOT NULL SET @sql = @sql + ' WHERE statement_id BETWEEN @start_statement_id AND @end_statement_id'; EXEC sp_executesql @sql ,N'@start_statement_id int, @end_statement_id int' ,@start_statement_id ,@end_statement_id; END TRY BEGIN CATCH SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END -- *********************************************** -- -- * REPLAY * -- -- *********************************************** -- IF @action = 'REPLAY' BEGIN BEGIN TRY --load the selected statement(s) SET @statement = ' SET @sql = '''' SELECT @sql += statement_text + '' '' FROM ##replay_info '; IF @start_statement_id IS NOT NULL AND @end_statement_id IS NULL SET @statement = @statement + ' WHERE statement_id = @start_statement_id '; IF @start_statement_id IS NOT NULL AND @end_statement_id IS NOT NULL SET @statement = @statement + ' WHERE statement_id BETWEEN @start_statement_id AND @end_statement_id'; EXEC sp_executesql @statement ,N'@start_statement_id int, @end_statement_id int, @sql nvarchar(max) OUTPUT' ,@start_statement_id ,@end_statement_id ,@sql OUTPUT; IF NULLIF(LTRIM(@sql),'') IS NULL RAISERROR('Unable to locate the statement(s) specified.',16,1) SET @srv = @@SERVERNAME; -- gather this server name IF EXISTS (SELECT * FROM sys.servers WHERE name = 'TMPLOOPBACK') EXEC sp_dropserver 'TMPLOOPBACK'; -- Create a loopback linked server EXEC master.dbo.sp_addlinkedserver @server = N'TMPLOOPBACK', @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer” @provider = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000 @datasrc = @srv; -- Set the authentication to "current security context" EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TMPLOOPBACK', @useself = N'True', @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL; -- Use a permanent table in Tempdb to store the output IF OBJECT_ID('tempdb..___outputTable') IS NOT NULL DROP TABLE tempdb..___outputTable; -- Execute the statement in Tempdb to discover the column definition SET @statement = ' SELECT TOP(0) * INTO tempdb..___outputTable FROM OPENQUERY(TMPLOOPBACK,'' SET FMTONLY OFF; EXEC tempdb.sys.sp_executesql N''''' + REPLACE(@sql,'''','''''''''') + ''''' '') '; EXEC(@statement); SET @statement = @sql; -- Build the column list of the output table SET @column_list = STUFF(( SELECT ',' + QUOTENAME(C.name) FROM tempdb.sys.columns AS C INNER JOIN tempdb.sys.tables AS T ON C.object_id = T.object_id WHERE T.name = '___outputTable' FOR XML PATH('') ),1,1,SPACE(0)); -- Add a "Database Name" column ALTER TABLE tempdb..___outputTable ADD Database__Name sysname; -- Build a sql statement to execute -- the recorded statement against all databases SET @sql = 'N''INSERT tempdb..___outputTable(' + @column_list + ') EXEC(@statement); UPDATE tempdb..___outputTable SET Database__Name = DB_NAME() WHERE Database__Name IS NULL;'''; -- Build a statement to execute on each database context ;WITH dbs AS ( SELECT *, system_db = CASE WHEN name IN ('master','model','msdb','tempdb') THEN 1 ELSE 0 END FROM sys.databases WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0 AND HAS_DBACCESS(name) = 1 AND state_desc = 'ONLINE' ) SELECT @sql = ( SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql ' + @sql + ',' + 'N''@statement nvarchar(max)'',' + '@statement;' + char(10) AS [text()] FROM dbs ORDER BY name FOR XML PATH('') ); -- Execute multi-db sql and pass in the actual statement EXEC sp_executeSQL @sql, N'@statement nvarchar(max)', @statement -- SET @sql = ' SELECT Database__Name AS [Database Name], ' + @column_list + ' FROM tempdb..___outputTable ORDER BY 1; ' EXEC sp_executesql @sql; EXEC tempdb.sys.sp_executesql N'DROP TABLE ___outputTable'; END TRY BEGIN CATCH SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END END
As you can see, the code creates a stored procedure that accepts a parameter named @action, which is used to determine what the procedure should do. Specialized sections of the procedure handle every possible value for the parameter, with the following logic:
First of all you start recording, then you execute the statements to repeat on each database, then you stop recording. From that moment on, you can enumerate the statements captured and execute them, passing a specific statement id or a range of ids.
The typical use of the procedure could look like this:
-- start recording EXECUTE replay_statements_on_each_db @action = 'RECORD' -- run the statements you want to replay SELECT DATABASEPROPERTYEX(DB_NAME(),'Recovery') AS RecoveryModel -- stop recording EXECUTE replay_statements_on_each_db @action = 'STOPRECORD' -- display captured statements EXECUTE replay_statements_on_each_db @action = 'SHOWQUERY' -- execute the first statement EXECUTE replay_statements_on_each_db @action = 'REPLAY', @start_statement_id = 1, @end_statement_id = 1
You can see the results of the script execution here:
Obviuosly this approach is totally overkill for just selecting the database recovery model, but it can become very handy when the statement’s complexity raises.
This seems a perfect fit for Glen Berry’s diagnostic queries, which is where we started from. You can go back to that script and add the record instructions just before the database specific queries start:
At the end of the script you can add the instructions to stop recording and show the queries captured by the procedure.
Once the statements are recorded, you can run any of the statements against all databases. For instance, I decided to run the top active writes index query (query 51).
As expected, the procedure adds the database name column to the result set and then displays the merged results.
You may have noticed that I skipped the first statement in the database-specific section of the script, which is a DBCC command. Unfortunately, not all kind of statement can be captured with this procedure, because some limitations apply. Besides the inability to capture some DBCC commands, please note that the column names must be explicitly set.
I think that a CLR procedure could overcome these limitations, or at least some of them. I hope I will find the time to try the CLR method soon and I promise I will blog the results.