May 2008 - Posts

Beware of DBMIRROR_SEND wait types with databases mirroring I recently encountered a situation where synchronous mirroring had been set up and working fine for several months. However an application started to perform extremely poorly and the cause was not clear. A batch job that would normally take 50 seconds or more was now taking 15 minutes and timing out.
Upon investigation I could see the DBMIRROR_SEND wait predominantly against the job in question. An investigation of the network utilization between nodes revealed it was only using an additional 25% of its normal capacity. However the fact that it was using synchronous mirroring was having a very extreme impact on the processing of batch jobs.
Setting the Mirroring to Asynchronous successfully managed to bring the job run time down to 15 seconds again. In future I'm going to look at automating the change of the mirroring mode before starting and ending any batch jobs. However it does leave the database susceptible to data loss in the event of a disaster.
Posted by blakmk
Whenever I’m called into a client site to review performance issues, one of the first things I always check is the filesystem fragmentation levels. Fragmentation is something that degrades performance on all databases to verying degrees. Heavily fragmented disks degrade performance by 10-15% for sequential operations (Backup, DBCC, full table scan etc) and 100’s  of percent for operations where multiple tables are accessed in parallel (queries with lots of joins). Drew Robb wrote an interesting article which illustrates the fragmentation issue in more detail http://www.dmreview.com/dmdirect/20020823/5658-1.html  
In light of the performance impact of heavily fragmented filesystems it can be a “no brainer” to implement defragmentation routines, IF there is a window of opportunity to do it.
Typically I use 2 methods of defragmenting (windows defrag and sysinternals contig) just because they are free and easy to schedule as batch files. Contig will force files to be contiguous if the space is available to do this. Windows defrag will also make files contiguous but will also attempt to compact the files so that they are close to the inside of the disk, however I find Windows defrag  less effective than contig at making files contiguous but gives a different benefit of compacting the files. Here is a sample batch script that I execute on a weekend (the deep scrub): 
[ContigPath]\contig -s c:\ > contig.log

[ContigPath]\contig -s d:\ >> contig.log
  
Here is a more typical weekday script:
 
defrag -f c:\ >  defrag.log

defrag -f d:\ >> defrag.log
   
Sysinternals contig can be downloaded from here:
http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

 

Posted by blakmk