How effective moats were in defending Medieval castles varied a lot according to in which time period the castle was built, and where in Europe it was built.
The first large wave of castle building across northern Europe featured mostly "motte and bailey" castles. These castles were built by raising a mound of earth (the motte) and one or several palisaded enclosures (the bailies) all surrounded by a ditch which aided the defences and was also the source of the earth to make the motte. The palisade around the bailey (or bailies) and the "keep" on top of the motte, were wooden structures.
Eventually, the ditch was called a "moat", derived from the word "motte" which meant the mound. Many of these castles were built in northern France, Germany, England, Wales, Scotland, Ireland and Denmark in the 11th and 12th centuries.
The moats were usually dry. The moats added to the defense of the structure (by making the vertical distance to the top of the palisade greater, making scaling the walls more difficult, and by making the base of the bailey walls the earthen slopes of the moat, rather than the wooden palisade, rendering the use of axes, battering rams, or fire to breach the palisade more difficult).
The main defensive purpose of the moat, however, was to supply the earth to make the motte, which raised the keep and made its defenses more effective.
By the late 12th century, however, stone began to replace wood in castle construction in northern Europe (and stone had been used earlier and wood seldom in building castles in southern Europe).
The motte of piled earth was not a good stable structure on which to place the foundations of a heavy stone keep (although some were built on earlier mottes). Stone castles tended to put their foundations on bedrock or on more stable ground. Without the need for earth to build a motte, there was less incentive to dig a moat.
A moat still added to the defenses of the outer walls, and so they were sometimes still dug (especially if the castle was built on earth rather than on rock), and a wet moat was an even better defense than a dry moat (because it made mining under the castle walls more difficult), so castles built on level earth near suitable water might incorporate wet moats.
By the middle and later Medieval period, moats had become less common. Most castles in the South (Southern France for example) don't have them. Most castles built on rock don't have them. Castles built on earth in Northern Europe might well still have them, and they might be wet moats if possible.
Even castles which did not have a moat completely surrounding their walls might have some sort of moat like feature outside the main gate, protecting this vulnerable entry to the castle and making it difficult to get battering rams or axes up to the gate.
Moats made scaling ladders, siege towers, and mining (especially wet moats, which tended to flood mines) more difficult when attacking stone castles. They also made axes and setting fire against the palisades more difficult when attacking wood castles.
If an attacking army wanted to overcome a moat (rather than besiege the castle, or take it through treachery, surprise, or negotiation) they would generally fill in the moat. Under cover of wooden defensive structures, they would fill the moat with wooden stakes, rocks, earth, until their siege engines could advance to the base of the walls.
All this would take time. Time (if the castle was well provisioned) was advantageous to the defenders. Allies might always arrive to lift the siege. One of the main purposes of castles was also to buy time and slow down any advancing enemy.
So, moats were a very common feature in early Medieval castles in Northern Europe. They were much less common in Southern Europe and later in the Middle Ages, though many castles built then still incorporated moats, either wet or dry, if built on earth on low ground, rather than on rock on hills or ridges.
Sources:
R. Allen Brown, Allen Brown’s English Castles,
https://books.google.co.uk/books?id=vkdoV44211EC&printsec=frontcover&hl=en#v=onepage&q&f=false
Jean-Denis G.G. LePage, Castles and Fortified Cities of Medieval Europe,
See the sources below for some pictures and illustrations of motte and bailey castles:
http://www.castles-of-britain.com/motteandbaileycastles.htm
http://en.wikipedia.org/wiki/Motte-and-bailey_castle
http://en.wikipedia.org/wiki/Moat permalink
I think it would be immensely difficult to quantify the exact effectiveness of a single component of what was an integrated fortification system. This would be rather like saying "which is more important to a sword's effectiveness - the tip, the blade, or the handle?" With that said, I think I can talk a little about the general usefulness of a moat, and how it could be countered.
Moats were a very common feature in military architecture, substantially preceding the era of the castle. A classicist could tell you more, but Roman legionary camps employed moats, along with berms (low earthen walls), as temporary defenses. The Theodosian Walls of Constantinople, constructed and reconstructed (due to earthquakes) in the early through mid 5th century, were protected by a 60-foot wide, 10-foot deep dry moat.
This brings us to another point: in most cases, moats (also called a ditch or fosse) were dry rather than wet. A moat, in simplest terms, is a trench surrounding the base of a fortification's wall, meant to disrupt or slow down attacks upon the walls. The ditch must be filled in before a siege tower can be brought up to the walls, and it makes escalades (assault with ladders) a bit more difficult, since the men carrying the ladders must negotiate a drop-off of six feet or more. They were popular, at least in part, because they can be constructed quickly, cheaply, and require only unskilled labor. In contrast, masonry walls were enormously expensive, slow to build, and required the services of the most skilled craftsmen in Europe.
How could moats be defeated? The most common solution was to fill it in preparatory to an attack on the walls, or to go under it via sapping and attempt to bring down the wall itself. Both would expose the laborers to the fire of the defenders, so it was often desirable to construct a covered way (think: a temporary armored above-ground tunnel) up to the ditch, or at least very near to it.
This is, partially, how Chateau Gaillard, possibly the strongest and most powerful castle of early 13th century Europe, fell in 1204. Sappers undermined the outer wall, forcing the defenders back into the middle ward, which subsequently fell to subterfuge, and a combination of mining and trebuchets apparently opened the inner ward.
In the case of Constantinople, Ottoman troops and Italian-Greek defenders waged a bloody, 24-hour-a-day battle over the ditch. The Ottomans, especially at night, attempted to fill the moat with rocks, timbers, and earth; the defenders bombarded them from the walls with arrows and bolts, sprung night ambushes, and launched sorties in an attempt to disrupt the filling of the ditch. But they were unable to do more than slow the Ottomans' progress, and with the ditch filled, the walls were open to attack, and fell in fairly short order.
This is necessarily broad and general, so if there's anything I'm leaving out, or that you're curious about, don't hesitate to ask for clarification.
The Medieval Fortress: Castles, Forts, and Walled Cities of the Middle Ages. By J.E. & H.W. Kaufmann.
The Normans: Warrior Knights and Their Castles. By Christopher Gravett & David Nicolle.
1453: The Holy War for Constantinople and the Clash of Islam and the West. By Roger Crowley.
The Fall of Constantinople 1453. By Steven Runciman.