Recently I was creating a c# windows service which interacted with VMware machines.

When trying to start up a VM by calling VMrun.exe  i got the message

"Unable to connect to host"

I found this by piping the response to a text file.

To get around this I changed the command i was using to no t use the GUI (nogui) option.

The only thing you need to consider is that running VMs will not be controllable by your manual login/UI as they are running as a different process.

Full command line details are available here:

http://www.vmware.com/pdf/vix162_vmrun_command.pdf

If you find a select statement is doing a lot of reads when you only have a few records or one record in the table, this may be down to image data types.

I've found in SQL server i was getting 4000 + reads on a select statement to retrive 1 or 1 records

This is probably down to the image datatype which probably needs several reads across pages to retreive it.

I sped this up by changing one of the selects to not return the image as it was not needed.

Recently i've been required to insert 6 million database records into the latest version of one of our products.

Actually, it's a lot more than this because it it 6 million business objects which of course tend to have their related lookup tables so the figure is probably much more than this.

The problem I have is that for reliable data, the information needs to be inserted through the interface to guarantee it is like a production environment, and this is slow... especially when you get over 1 million database records in your table.

As an experienced dataabase developer, my first thoughts were to look at profiler, and get some information on improving the performance with code tweaks or better indexing. Being an almost mature product, there was little to do here, but a few changes to the indexes and stored procedures helped a bit. This gave be about 50% speed improvement but this didnt last for long.

Soon the process of inserting records, and the backgroung logic doing lookups to check for duplicates etc... was slowing down to 3 or four records per second, which was going to take forever. At 2 million records, it was clear that this needed a different approach.

To rub salt in the wound there were other factors stopping me doing my task.

• I had a brilliant laod testing app, i had written that could reduce call time/latency to the website by doing all this multithreaded. Unfortunately the code wasnt very threadsafe, and performance got worse with multiple threads due to all the locking
• My super fast servers from last year had been hijacked by IT to run sharepoint
• I could just run several databases at low volumes and piec ehem all together. THis involved a big manual manipulation of the data (a risk) and also ..i didnt have time. At table sizes of this size , even SQL server's interface was struggling.

The solution was based around previous tests where we found a super fast SAN storage system sped up performance significantly. However i didnt have access to one in such a short time.

I decided to improvise  adn create a RAM disc  to store the SQL database on and hopefully speed things up. This in theory should be much faster at seeking reading/writing than a standard mechanical hard drive.

I used a PC with 12GB ram, and created A RAM DRIVE.

I downloaded a trial version of this software: http://www.superspeed.com/servers/ramdisk.php and created an 8GB drive within 5 mins.

I then 'detached' my database using the SQL server interface, and moved the mdf,ldb files to the ram drive.

I then  re-attached the database amking sure sql server had permission to the new files and their storage location.

After a database shrink and index rebuild on all tables i re-started my task.

Insertion continued at 50-100 inserts per second which was much faster than before. And slowdown has stopped or at least is slowing down at a slower rate than before.

You can also move the system temp db file to the ramdisk too which can speed things up further, however, bear in mind if you need to do any large data deletion or modification on large numbers of rows, you may find the tempdb grows and fills your ram disk whilst trying to do this. For this reason , i left this out on my final run.

Dont forget a large dataabse will probably get several maintenance tasks carried out on it over a growth like this so it may be handy to set an hourly index rebuild task whilst your insertion run is executing.

Annoyingly this should all be ready just before our much more efficient mass insertion feature passes testing.

If you have the resources, i'd try a solid state drive or SAM system as you must remember your dataa will be lost as soon as the power goes off.

Dont forget to back it up once you've finished

Some time ago , i attended a training course and got chatting to a fellow learner, who was talking about ISNULL.

For a while i was thinking he was saying IS NULL, which confused me , but he explained the difference and , if like me , you never got round to readingthat part of the manual, you'll find it quite usefull.

ISNULL effectively is a nicelittle command that  tells SQL server .... "If this vaue is null, then replace it with another value"

whereas...

IS NULL is a comparison for any values that are NULL.

THE ISNULL statement is particularly usefull for when you've got data that hasn't been filled in or collected and yet is displayed in a report to an end user.

It's been a while since i created it all, but the IT guy has now sent an allstaff email to request clearing up excess data.

My biggest sin is the reams of Load testing data produced by a very enthusiastic bid to prove our system was scalable.

If you're going to have to generate huge amounts of traffic directed at a database driven site, then prepared to create lots and lots of data.

...especially if the client wants proof!

Items they'll probably want recorded and stored are:

• Response times
• Throughput
• Requests per second
• Web logs proving the activity
• Performace monitor information
• SQL analysis data
• Machine /drone data
• Number of errors occurred
• performance verses user info  / ramp up info.

A lot of this information will be duplicated and not entiely necessary, but if the customer wants it, it must be provided.It also adds credibility to your testing and backs up any in-house tools you are using to create the loads.

Web logs are especially storage hungry as they're main purpose is to be produced and stores as efficiently as possible. Zipping these up helps considerably and it's worth having a good zipping tool at hand as your fall back plan.

If you have time it's worth archiving the data in a more efficient storage system. For instance writing a parser to place IIS log entries in a database and to normalise the data. This will save lots of storage space and hours of zipping and unzipping. It will also leave your information retreivable and searchable when youve finished.

• Place all your code in stored procedures where possible
• Move declarations  to the start of stored procedures.
• Are transactions required??? If there are no updates or inserts, then you should remove any transactions and rollback commands. These will just fill your transaction log up.
• Repeat getdate() commands. Why not decalra a variable at the start of the stored procedure and re-use it later on in the code.
• Count(*) and count(fieldname). If your deciding if there is data in a table or looping through a numbe rof rows. Use the EXISTS command instead. If a cound is required, just use one field instead of * as is reducess the number of reads.
• Rollback transactions as soon as possible. Sometimes people build error messages before rolling back... these can be done outside the transaction.
• XML use... try upgrading to the latest XML methods.
• Prevent recompiliation
• Avoid temporary table modifications
• Reduce multiple lookups into one.
• Move select statemnets to the start of stored procedures where possible. This reduces record locking times.
• Modify multiple update statements to use CASE.
• Reduce UNION commands
• Get a tool to help you....MS query analyser is pretty valuable.
• Avoid nested loops
• Avoid functions in the WHERE clause as this affects performance.
• Use performance monitors where available.
• Use a fixed load rather than the maximum load when trying to improve performance
• Call stored procedures using their fully qualified name
• Split larger stored procedures into smaller ones and have  acontrolling procedure.
• Do not use sp_ as a stored procedure prrefix
• Consider returning the integer value as a RETURN statement instead of returning an integer value as part of a recordset.
• Avoid temporary tables
• SET NOCOUNT ON
• Use the sp_executesql stored procedure instead of the EXECUTE statement.
• Check the order of ields in the WHERE clause
• Consider additional indexes on popular search fileds
• Reduce the data returned (avoid select *)
• Are ORDER BY and GROUP by required. - these are quite expensive operations
• INcrease workerthreads
• Increase CPUs / hard drive speed / Memory
• Consider offloading work.
• Use the select statements with TOP keyword or the SET ROWCOUNT
  statement, if you need to return only the first n rows.
• Clustered indexes are more preferable than nonclustered, if you need
  to select by a range of values or you need to sort results set with
  GROUP BY or ORDER BY.
• Try to avoid using the DISTINCT clause
• Use table variables instead of temporary tables

• Create indexes on XML columns

• De-normalization

• Avoid use of NULL