SQL Server Blog 2008-11-19T22:19:33Z http://sqlblog.maneffa.com/atom.aspx Quick Blogcast Profiler performance on laptops - what has least performance impact? tag:sqlblog.maneffa.com,2008-04-17:24e0fc0c-16b1-4633-a55c-8f5cc64d4532 Nigel Maneffa 2008-04-17T22:34:35Z 2008-04-17T21:41:00Z ‘Everyone’ knows that it is much more efficient to run a 'server side trace' than a 'GUI trace'. I have always been sceptical of this statement, as I prefer to be able to take a ‘peek’ at the GUI every hour or so, which you cannot do with a server side trace, and did not believe there could be that much overhead in running a GUI. However, I was proved wrong, but not for the reasons I originally thought.

I was recently running profiler on my laptop (ie single disk for OS, application db’s, tempdb, logs, temp directory etc etc) in GUI mode – the performance reduction on SQL write operations was incredible. So I started to think...
What setup would give me the least profiler overhead?
1) Local GUI trace
2) Remote GUI trace (using profiler on another machine collecting data from my laptop over the network)
3) Local server side trace

The local server side trace was by far the most efficient. It was so far ahead I could not believe it.
An insert operation loop (ie lots of writes) that took 25 seconds without profiler took 35 seconds with a server side trace but a massive 141 seconds with a local GUI trace. A remote trace took 56 seconds.

What was the cause of this massive performance difference?

The answer was simple – I monitored the disk IO and found that GUI mode profiler makes lots of small writes, but a server side trace makes much smaller numbers of larger disk writes – it’s as simple as that!
If you choose to save the GUI mode to a specific file location (as opposed to just looking at screen monitoring), the GUI makes 2 disk write operations, one write to the temp directory and one write to the 'save to' location dropping the performance even more – the insert operation above taking an incredible 206 seconds. If you want to save to a file and use the GUI it may be better to save the trace manually after it has completed.

Disk IO continues to become more of a bottleneck on single disk computers, where disk IO intensive systems such as databases show a startling performance drop off when there is a lot of (presumably) non sequential disk writes.

Here is a chart of the time taken - drop me a line if you need help understanding the detail

 Parameter No Profiler  Local GUI - no save to file  Local GUI + save to file  Local GUI - temp dir on USB Server Side Trace  Remote Trace 
Profiling Local or remote  Local  Local  Local  Local  Local  Remote
Profiler GUI ON  n/a  Yes  Yes  Yes  NO = 'server side trace'   n/a
Temp Directory  n/a  c:\...(local)  c:\...(local)  d:\...(Ext US   c:\...(local)   n/a
 Profiler 'Save to file'  n/a  n/a  c:\...(local) No   c:\...(local)   n/a
Network bandwidth (gigabit)  n/a  n/a  n/a  n/a  n/a  6%
 Time seconds  25  141  206  124  35  56

This image shows this disk IO activity in the worst case scenario of selecting a specific file location for the trace, as well as running the GUI, where profiler is writing the same data twice, dragging down performance even more.



Happy profiling – server side of course!

]]> SQL Server 2000 SP4 Upgrade Deleted Column Non-Convergence tag:sqlblog.maneffa.com,2008-01-23:913ff3eb-b18e-49ea-991f-bcddc33f5970 Nigel Maneffa 2008-01-23T00:05:53Z 2008-01-23T00:04:00Z
Publisher insert/delete and subscriber insert/delete/update worked fine.
Either colv1 was being updated incorrectly by the merge trigger (most likely an error in @missingbm) or the stored procedure that applies the update was wrong. Either way it was a major problem.
I thought that adding a new dummy field might ‘throw a spanner in the works’ so I tried it on the test system (an exact restore to a computer of the same name to make sure the deleted columns were still ‘ghosts’ in the merge tables) and amazingly the problem was fixed.

I applied the ‘fix’ to the production server and that was fixed as well. Sometimes it pays to try the simple things first.]]> Non-Convergence Bug in SQL2005 tag:sqlblog.maneffa.com,2008-01-23:00681849-a35c-4c0b-a682-9a0d027cd254 Nigel Maneffa 2008-01-23T00:03:56Z 2008-01-23T00:02:00Z I recently reported an SP2 bug that creates non-convergence in merge publications with horizontal and join filters to MS. They are currently investigating. It only seems to affect new SQL2005 SP2 publications and SQL2000 that has been 'directly' upgraded to SQL2005 SP2 before having the snapshot run.
I recommend you check out that this bug does not affect any new publications you have planned. It can catch you unawares since you just expect the functionality to work, it having worked so well in the past. I have found that correctly fixing non-convergence on partitioned data in a production environment where you have lots of tables and filtering is a distinctly non trivial activity, especially as the bug will typically move over new child records, but not the originals!

My current workaround is to delete the out of sync parent and child (more complex in my real life scenario as there are multiple join filters at various levels) and reinsert with the correct partition data already set. Fortunately, there are not many of these errors to contend with and the custom application now checks for this new 'feature' so the data can be corrected quickly. The normal fix for non-convergence, sp_mergedummyupdate, does not work in this scenario, when run on either the parent or child record(s). Newly inserted child records move to the correct partition however. So in summary, the child records that should be moved into the new partition at the time of partition change remain trapped in a non-converging limbo.

A potential workaround is to change the value of the merge publication option @use_partition_groups to false, as this forces(?) the older 'setupbelongs' functionality (evaluate partition membership at sync time) to be used. Unfortunately, due to the nature of this bug you cannot change the value of this setting in an existing publication, create a new snapshot and be on your way. You must first drop existing publications and create them with the @use_partition_groups set to false. You will need to experiment on your test environment for what works best for you. I did not test all eventualities in this area. The problem is that the merge GUI nearly always chooses the new feature precomputed partitions.

To give you a feel of what this bug might do in practise I have created a simplified version in the form of a story - the poor DBA. ...

]]> Merge Trace Logs tag:sqlblog.maneffa.com,2008-01-22:c3f446c9-6c8d-4073-affe-bd4b16d14127 Nigel Maneffa 2008-01-23T00:01:04Z 2008-01-22T23:59:00Z In SQL2005 a new merge trace log is automatically maintained by SQL server. This is primarily of use when you have a problem, as it shows the internal workings of synchronisation. If you do run into problems, it’s well worth taking a look into here. There appear to be 3 files, a current log called replmerg.log, and 2 previous 10 meg log files named replmerg.log.1 and replmerg.log.2. These log files continually roll over so there are never more than 3 files.

I suspect MS added this auto logging as it would take considerable time for PSS to talk someone who knew little about replication to create a log file in SQL2000 by running the merge agent from the command line or modifying the job steps . These ready made files are always ready to go once the synchronisation has completed.

The log files will be found typically in the folder:

C:\Program Files\Microsoft SQL Server\90\COM\

]]>