SQL Server Transactional Replication Subscription Streams not being used

I was recently looking at increasing the performance of Transactional Replication across a very high latency line (plenty of bandwidth). I had exhausted all of the agent parameter tweaks so decided to look at subscription streams on the subscriber. There's a bunch of good articles out there describing this, not least this article from SQL Server Advisory team which describes the relative benefits (and dangers) of using subscription streams.

So I gave it a go. If you set the -VerboseHistoryLevel to 2 you will get a useful statistics report periodically. Upon checking this I was pleased to see 16 separate threads, so my set up was right. However, when running tests only the first thread was ever used. Looking at the BOL it says :

Note

Subscriptionstreams do not work for articles configured to deliver Transact-SQL. To use subscriptionstreams, configure articles to deliver stored procedure calls instead. Fine, I'm replicating Stored Procedures calls (rather than table articles) so this should work. Well no, my bad, what that note means is the Standard procedures i.e. SCALL, XCALL, MCALL routines. Not replicated Stored Procedures articles. Simple misunderstanding that made all the difference. Changed replication to be Table Article based, and behold 16 threads of goodness and over 10 times increase of performance.

Run SQL Trace in background

Rather useful this if you need to monitor a trace for a period of time and can not leave the session logged in. Found how to do it here.

First use Profiler to define the events, columns, and filters needed. Some Events are : SQL:BatchCompleted and RPC:Completed, SP:StmtCompleted. Important columns are : Duration, CPU, Reads and Writes. Some advanced events are SP:Recompile and Scan:Started to check for table and index scans

Click the Run button. Immediately stop the trace

Click the File menu, expand the Export option, and then expand the Script Trace Definition option. Choose For SQL Server 2005 (or SQL 2000 if creating script for older SQL Server) and select a filename to save the script.

Once the script has been saved, open it for editing in SQL Server Management Studio.

The following line of the script must be edited, and a valid path must be specified, including a filename:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
@maxfilesize, NULL. The @maxfilesize is 5MB by default

Run the script. The generated script will also select back a @traceID

Once you are done use the @traceID to stop and close the trace:

EXEC sp_trace_setstatus @traceid=99, @status=0
EXEC sp_trace_setstatus @traceid=99, @status=2

The fn_trace_gettable function can be used to read the data from the trace file :

SELECT * FROM ::fn_trace_gettable('C:\Traces\myTrace.trc', 999) where 999 is the number of rollover trace files to read

Easy !

Benchmarking SQL Server

A very useful blog entry on Benchmarking SQL Server.

The article looks at the PAL tool found here

The process is straight forward. Create a performance log for a period of time on the SQL Server you need to benchmark. Typical metrics to record are :

Physical Disk
Logical Disk
Process (I will sometimes tweak this down to just the SQL Server process)
Memory
Network Interface (sometimes I will tweak down to just the counters needed and just the NICs involved)
Paging File
Processor
System
SQL Server: Access Methods
SQL Server: SQL Statistics
SQL Server: Buffer Manager
SQL Server: General Statistics
SQL Server: Latches (Often I just look at the two required counters rather than the object)
SQL Server: Locks
SQL Server: Memory Manager

Save this for a reasonable slice of time and then run it through PAL. This then generates a nice report, highlighting any issues. See the above blog link for a more indepth description.

High I/O high CPU from fragmented indexes

Interesting blog post that goes a long way to describing the mechanics involved when a CLUSTERED index is poorly designed

Conditional Where Clauses

Excellect article by Erland Sommarskog about the various ways of doing SQL conditional where clauses. What sets this apart is that Erland has comparative performance figures, showing what way is best. Also kept up to date.

SQL Server Versions

A rather useful site with all the SQL Server versions and hotfixes. For a more detailed (though less readable) list look here.

Memory check which tables are loaded into memory

Great tip here for analysing whether you are over stretching your memory in SQL. The root of the issue is that clustered indexes are loaded into memory (the whole table is loaded) in order to query. Should your table be of a significant size, then all of your memory (or more) may be required to load a primary index in for querying. The solution to this is to remove the clustered index - this may equate to higher disk i/o however since the size of the index is smaller it may be able to load into memory anyhow.

A check for table memory usage is as follows:

SELECT sys.tables.name TableName,
sum(a.page_id)*8 AS MemorySpaceKB,
SUM(sys.allocation_units.data_pages)*8 AS StorageSpaceKB,
CASE WHEN SUM(sys.allocation_units.data_pages) <> 0 THEN
SUM(a.page_id)/CAST(SUM(sys.allocation_units.data_pages) AS NUMERIC(18,2))
END AS 'Percentage Of Object In Memory'
FROM (SELECT database_id, allocation_unit_id, COUNT(page_id) page_id FROM
sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
JOIN sys.allocation_units ON a.allocation_unit_id =
sys.allocation_units.allocation_unit_id
JOIN sys.partitions ON (sys.allocation_units.type IN (1,3)
AND sys.allocation_units.container_id = sys.partitions.hobt_id)
OR (sys.allocation_units.type = 2 AND sys.allocation_units.container_id
= sys.partitions.partition_id)
JOIN sys.tables ON sys.partitions.object_id = sys.tables.object_id
AND sys.tables.is_ms_shipped = 0
WHERE a.database_id = DB_ID()
GROUP BY sys.tables.name

For further information on this and perfmon settings, look here

Common SQL Server errors and failures

Blatant link, but very useful : Common SQL Server failures and errors

Moving logins when migrating a server

This is how MS recommends migrating logins when moving a database. Very useful, and removes the need to run sp_change_users_login .

Data Table Shrinking

One the the most annoying features of SQL Server is that the GUI based shrink commands rarely work. In a development environment we do not always have the space to store a database and it's predicted growth, so shrinking is a necessity.

The following script will shrink all the files in a database - data and log, and free up that much needed space. Just edit it and replace the logical file names. It will shrink any number of files - just add/remove the commands as required.

use master
go
BACKUP LOG [BSE] with TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE(N'BSE')
GO
USE BSE
GO
DBCC SHRINKFILE(BSE_dat, 10)
GO
DBCC SHRINKFILE(BSE_dat2, 10)
GO
DBCC SHRINKFILE(BSE_dat3, 10)
GO
DBCC SHRINKFILE(BSE_dat4, 10)
GO
DBCC SHRINKFILE(BSE_Log, 3)
GO

Tally Table - loop replacement

Excellent article here on using Tally Tables instead of a loop. The performance increases are amazing. In essence the Tally Table is used to replace the loop, and using set based logic rather than a traditional loop gives high performance gains. Worth a read at least.

SQL String Formatting

Very nice set of functions to format strings in SQL. Have a look here for details

Diagnosing Replication Issues

I'm in the process of creating a Transaction Replication solution that incorporates some transformation. The condensed version is that the publisher data structure is nothing like the subscriber structure. To do this I have replaced the standard sp_MSins/del/upd stored procedures with my own coding. There is some simple transformation going on in these sp's, and debugging it is not nice.

So, imagine my delight when an article from SqlServerCentral dropped into my Inbox - couldn't have been better time.

The important bit for me was establishing where the problem lies and what to do with it. The way to do this is as follows:

In Replication Monitor examine the error message in the detail screen. It will have something like this :

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000018F0000008800A700000000, Command ID: 83)

Error messages:
etc.

The bit to note is the Transaction sequence number and Command ID. You can drop the trailing 0's from the sequence number.

Next SELECT FROM msdistribution_agents and get the publisher_database_id. Note this number, you'll need it.

Finally, call the procedure sp_browsereplcmds :

exec sp_browsereplcmds
@xact_seqno_start = '0x0000018F0000008800A7',
@xact_seqno_end = '0x0000018F0000008800A7',
@publisher_database_id = 15,
@command_id = 83

Examine the contents of the command column - this is what is being sent to your stored procedure. You can now run this by hand and see what the issue is. I recommend using a transaction and rolling back so that you do not introduce other errors.

Parameter Sniffing - SQL executes faster as a query than as a stored procedure

This strangely monickered problem caught me out last week.

The scenario is that you have a piece of SQL that runs quicker as SQL executed in query analyzer than as a stored procedure. The reason for this is a little obtuse, and worth remembering.

It seems that SQL Stored Procedure Plans can have issues with parameter variables. This affects both SQL Server 2005 and 2000. The official line is :

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation"

See Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005


What this means in practice is that the stored procedure plan may be optimized for a particular result set volume, and when the stored procedure is executed with different parameters the plan is no longer efficient.

There are a few things that can be done to circumvent this issue :

1. Only use local variables in your procedure, and assign them to parameter variables at the start. This forces SQL to create a generic plan.
2. Call the stored procedure WITH RECOMPILE each time. This will force a new plan to be created which may be quicker than using the existing optimized plan.
3. Move statements into sub stored procedures - the plans will be smaller and more reliable.
4. Encode any statements that reference the variables into string variables and sp_executesql them.

In the case that I had I reduced a heavy stored procedure from over 4 minutes to about 30 seconds. However, in my case only items 3 and 4 made any descernable difference.

It's a wonder this doesn't happen more often - it seems to be a strange catch out that only appears in certain circumstances.

For a detailed example of this look on Omnibuzz's blog