Unfortunately, a lumbar puncture won't 'show MS' per se, but it may help narrow the focus for your doctor. It may show things that indicate current or past infections which may lead the doctor in another direction. It may show oligoclonal bands unique to the CSF which is a finding in support of MS (but not the same thing as a home run), or it may show nothing at all. The latter result does not rule out MS, it just means the test didn't really clarify much.
The blood drawn at the same appointment (make sure they do this!) will be compared to your spinal fluid to search for the aforementioned unique (found only in the CSF) oligoclonal bands.
You may find this Health Page of interest.
http://www.medhelp.org/tags/health_page/7687/Multiple-Sclerosis/Can-you-Diagnose-MS-with-an-LP-that-is-Negative-for-O-bands?hp_id=142
As mentioned, everyone is different. Plenty of people have no headache, no adverse effects at all. I did and three years later I'm an avowed coffee drinker (after not drinking it at all my first thirty years) because of the hardcore positive reinforcement from the relief it provided me! It increases intracranial pressure which helps mitigate the loss of pressure from the leaking cerebrospinal fluid that causes the pain. This is why giving up a heavy coffee habit causes headaches. That artificial pressure increase is taken away!
It sounds like you've taken good steps. Arranging for a relaxing day or two is a good idea, regardless of how smoothly the lumbar puncture goes. We have these procedures in the hope of answers, so I think it pays to take a bit of time to relax, realise we're making positive steps to get the answers we need, and generally just be good to ourselves as we wait on the results.
All the best for Wednesday!