April 2008 - Posts

Come to the UK SSUG

Support your local user group!

If you missed out on attending the UK SSUG Meeting in London last night then you missed an excellent evening, OK if you can't get to London Victoria beacuse you're in Scotland fair enough, but with the close proximity to Victoria station and underground it's an easy to get to location and you get free food and drink.
Subjects covered last night were x64 and cursors, so thanks to Christian and Eric.
There should be another meeting in London in two months time, next month should be at Reading, not quite so easy to get to except for me as I only live 11 miles from the Microsoft reading Campus!

Coming soon is SQL Bits III, this will be held in Hatfield ( where? ) 

http://maps.google.co.uk/maps?utm_campaign=en&utm_source=en-ha-emea-uk-goog-gm&utm_medium=ha&utm_term=map

Note that this Hatfield is in Herts as against the Hatfields in Doncaster, Humberside or Leominster. I understand there may be a prize just for getting there as it must be within the absolute worst part of the UK motorway system - unless you know better ! I think I sense a nearness of location to a certain MVP but I could be wrong.

Here's what it says about Hatfield ( herts )
Hatfield is a modern town, situated either side of the A1(M) motorway and just a few miles north of the M25 motorway. Hmmm .. so they just built a motorway straight though it then.
Hatfield started as an old coaching town dating back to Saxon times. The town expanded following the railway's arrival in 1850 and in the 1930s, the growth was linked to the development of the aviation industry. Famous aircraft such as the Mosquito, the Comet and Trident were built here.Hmmm ... nothing much of late then.
I did search the Hatfield events page on  www.hertfordshire.com, just in case SQL Bits was there, sadly there are no events listed for Hatfield this year.

So come along to a User Group meeting, it's always good to share experiences, problems and solutions; learning from your peers is probably the most effective method I know and no matter where you think you fit in the big picture there's always something you'll know that someone else will not, all systems and experiences are different. The most important point is that the only cost is your time.

Disappointing Review.

It's not very often I'm provoked into making a criticism of a respected publication, however there's nothing like being grumpy!
I've published a number of posts about baselines, trending and performance analysis and I've been quick to remark when published examples don't really match real world, so I was surprised to read a review of compressed backup software for SQL Server and the conclusions drawn in a leading publication.

I'm a keen user of such a product, which wasn't part of the review, and although I've only used the one, but at several client sites, I suspect that the choice of the Quest or Redgate product is more a personal one, however if one is to write a review of such a product then it would make sense to provide accurate comparisions and test in an enterprise environment.

From the description of the test "server" I'd figure the tests were carried out on a laptop with an external drive. A "large", 540Gb, database was used to be fair but because the database was "so large" the tester could not actually perform a native backup or restore as there wasn't sufficient disk space, only a single TB disk was availble, so estimated times were published as the base to compare against. Hmmm - pick a number, add 5, divide by the number you first thought of, add 15 seconds to make it look good - you get my feeling on this.
A single core 1.8Ghz processor also doesn't really cut the mustard either.

Now I may be wrong but I can't really think that the average enterprise user of Lite Speed or Redgate SQL Backup will be using a low spec single processor with two sata drives - but feel free to comment if indeed your production databases do run on such a system.

So my point is that the average Production Server is likely to have multiple cores and at least half way decent scsi/fc storage, the product I use can be configured to use threads, buffers and compression ratio's - I spent some time testing which combination of these settings gave me the best compression vs time performance, and I didn't even touch the use of multiple backup files, all the types of things you'd investigate if you were looking to back up "large" databases or you want really fast backups and restores. And if you were going to write a review aimed at Database Professionals who'd be looking into deployment in the enterprise.

Such is the same with performance testing or trending, you must have a valid baseline and you must have a test environment which at least gets somewhere near to the actual production environment you intend to deploy to.

My personal view is such reviews do not add any value and also give a misguided view of the technology and products, I obviously also disagree with the recommendations of the review, but that's another matter.

Disk Partition Alignment ( SANs and Diskpart )

As a further post to my series on what else can affect SQL Server Performance here’s the thorny issue of disk partition alignment. Disclaimer: I don’t get to configure SANs, places I work have dedicated teams and it’s unlikely a DBA would be considered a reference point regarding storage. What I can say is that in my experiences SAN storage has usually shown as a performance bottleneck.
  • I’ve recently had the experience of being involved in the migration of storage for a clustered production system from one storage unit ( the physical tin ) to another on the SAN. ( It’s a bit of a misnomer to say migrating from one san to another  <grin> )
  • When I viewed the configuration document from the vendor I noted there were no references to HBA buffers, block size for the formatting or partition alignment.
  • I asked a number of questions and there was some debate as it was assumed that as the vendor had been engaged to produce the upgrade and deployment document they would have configured the storage optimally for SQL Server.
  • During the process it became necessary to involve Microsoft and their engineer was asked about partition alignment, he confirmed that yes it was important and the performance difference could be around 25%. 
  • This confirms a couple of previous experiences before I had been on my SNIA courses and had learned about partition alignment.
  • Interestingly there was a series of posts on SQLServerCentral concerning this very subject just before our migration. I’ve put the post contents below, with names removed, I’d used this as part of my evidence that our provider should align partitions on the migration. 
 
  • I think the important point here is to always ask the question, regardless of who the Vendor might be.
  • NEVER ASSUME IT’S RIGHT ! 
Links to DiskPart: http://technet2.microsoft.com/windowsserver/en/library/ca099518-dde5-4eac-a1f1-38eff6e3e5091033.mspx?mfr=true http://technet.microsoft.com/en-us/library/aa995867.aspx  This is a link about HBA queue/buffer depth: http://sqlblog.com/blogs/linchi_shea/archive/2007/09/18/sql-server-and-sans-the-queuedepth-setting-of-a-host-bus-adapter-hba.aspx  Link to SQLServerCentral forum post: http://www.sqlservercentral.com/Forums/Topic446040-360-1.aspx?Highlight=san 
 
 Here is a forum post which talks about Partition Alignment:
( in full so no selective editing – the interesting stuff in bold ) 
 Hello guys

We have a strange case. We have a
SAP , on Windows 2003, using SQL Server 2005, and using a EMC Clarion for Storage.

Globally , response time of
SAP is very good.
SQL Server response time is good.
CPUs have an excellent performance
..
But we have noticed I/O performance of Datafiles of SQL server is not excellent. This database have 8 datafiles, and I/O per file, is between 10 and 20 ms. Is not bad, but is not excellent...

The curious thing is: the number of I/O is well balanced between 8 datafiles but ms/IO per each datafile is different, and is growing (!?). I mean:

MTPDATA1 Data I: 13,168392
MTPDATA2 Data I: 13,965697
MTPDATA3 Data I: 15,507416
MTPDATA4 Data I: 16,531073
MTPDATA5 Data I: 17,404987
MTPDATA6 Data I: 18,767620
MTPDATA7 Data I: 20,017096
MTPDATA8 Data I: 20,813471

Any explanation for this???

This Database comes from an Heterogeneous System Copy. Originally was on OS/400 DB2/400. One week ago we are working in new platform Windows 2003 /SQL Server 2005.

First point:This database have been created with an export/import. So this database should be free of fragmentation problems. Should be reorganized.

Second: He details of I/O per file:
Filename Type Partition ms/IO Reads Writes I/O wait ms

MTPDATA1 Data I: 11,071782 8.728.781 371.484 100.756.151
MTPDATA2 Data I: 11,768664 8.734.624 358.552 107.014.530
MTPDATA3 Data I: 12,402772 8.722.614 365.930 112.723.139
MTPDATA4 Data I: 13,181894 8.737.115 372.423 120.080.961
MTPDATA5 Data I: 13,916623 8.749.069 382.267 127.077.363
MTPDATA6 Data I: 15,081958 8.734.311 380.829 137.474.162
MTPDATA7 Data I: 16,147454 8.776.978 380.401 147.868.360
MTPDATA8 Data I: 16,823962 8.731.213 374.198 153.189.086
Data 69.914.705 2.986.084 1.006.183.752
MTPLOG1 Log J: 2,778653 4.022.434 3.234.530 20.164.584
Log 4.022.434 3.234.530 20.164.584
73.937.139 6.220.614 1.026.348.336

As you can see the balancing is right. Each datafile has the same ratio of reads and writes.

The thing is: time I/O access of each datafile is different. And, curiously is scaling...MTPDATA1 < MTPDATA 2 < MTPDATA 3... < MTPDATA8

I 'm just talked to Storage Manager. He told me that cache of Clarion has a low activity.

Is very strange. The most access is sequential...

Any help please???
Thanks and regards
 
I can only make general observations from experience with SAN's ..
are your luns dedicated spindles - if not then you will have contention.
are all your trays/luns on the same fibre speed
are the number of spindles for each lun identical
I don't know how your san configures it's cache, but I'd say check it's config is the same - in general terms read cache tends to slow performance and write cache improve performance.
I've never found SAN engineers generally very helpful - there seems to be an inbuilt arrogance that performance should not be questioned !
 
 1) were all files created exactly the same size and time?2) were all files created large enough for all data and data growth or did you let autogrowth manage the size as you loaded data and began processing?
3) are all files now exactly the same size?
4) are all files part of the same filegroup?



=======================================================================================

First of all, thanks for your answer.

Past day, we had a meeting with Clarion engineers, customer and us.

Clarion engineers admitted to have set up a bad configuration of LUNS.

It seems there is problem of missalignment, in case of Windows. Is necessary to do a format manually to Labels of Windows, using Diskpart

By default whenMicrosoft Disk Manager formats Clarion LUNs it creates a partition starting at 63rd sector,which misaligns the partition with the underlying storage subsystem.This can cause a greater I/O load than is necessary.

If anybody wants more information, ask me.
 
 
That is something that almost every non-experienced (and quite a few of the experienced) SAN configuration personnel miss. However, I am not sure I understand why it has led to different performance for each file.

Note that there are a bunch of other settings to be manipulated to achieve optimal
SAN storage for a SQL Server installation. A good consultant is worth his/her weight in gold here. I see it time and again where companies drop 6 or even 7 figures on a storage system and then try to configure it themselves (or with just the assistance of a vendor-provided-generic-installation-consultant. Such a waste. 
 
It seems there is missaligment regarding to initial sector of disk. So, probably file1 has an offset , and file2 has more offset, file3 more, and so on..

Isn't?
 
 I didn't understand it to work that way. I thought the actual files that were created were all made with even block sizes (usually 64 512 byte sectors or 32K) and would all thus be 512 bytes misaligned. I could be incorrect in this. 
 Of course - if your block size is 32 (like you mentioned) and your file sizes aren't an even increment of said block size - wouldn't your alignment be all over the place?

For alignment to really work, EVERYTHING needs to be aligned, right? Partition start,File starts, file sizes, etc....? and in the case of SQL files, growth factors?
 
 
See here for description of DISKPART commands: http://support.microsoft.com/kb/300415. Down at the start of the CREATE section "On all MBR disks, the size or offset parameters are rounded up to cylinder alignment. On GPT disks, the size or offset parameters are rounded to sector alignment." This should result in things not getting worse, but again I could be wrong. I think the GUI tools for making volumes do the same. 
 I can confirm my theory.

EMC Clarion Manager have performed changes on configurarion of SAN: one of them regarding to right alignment.

Now, the disk access time have been improved, and now access time of each datafile is not sequentally but similars.

Regards and thanks for your suggestions