If so, what kind of weight should I use? Should it be enough to sink the bag to the bottom of the carboy?
It helps, but is not necessary
The more the hops are exposed to wort the better the aromas and flavors will transfer to the beer. Use something you can sanitize - smooth like marbles, coins, washers or silverware. It shouldn't lay on the bottom of the vessel; instead, suspend it in the middle of the brew. Brew Strong recorded an episode all about dry hopping. It's a good listen with some surprising information.
I know you specified carboy, but for those looking to dry-hop in the keg, it's also important not to have the bag sink to the bottom and sit over the outlet. You pull out all sorts of nasty sediment and flavors that way, particularly when using pellet hops.
I've managed to solve this by throwing both a sanitized ping pong ball and a sanitized washer in the hop bag. The washer sinks, and the ball floats, suspending the bag in the top-middle of the beer.
I feel the best way is to not use a hop bag. Take your hops for dry hopping out of the fridge 24 hrs before you want to add them. That will allow the oils to warm up and increase the aroma potential. I boil a small amount of water then let it cool to "warm". Add the hops dry, to a sanitized jar or measuring cup. Then add just enough water to cover the hops. Let the hops get all mushy (~3-5 min), swirl and add the hop slurry to your secondary. I have had amazing success with this method, a real noticeable difference between using a hop bag and letting them float free. I find the hops settle to the bottom and generally stay there when racking off to bottles or a keg.
Be careful of metal. I once bought some ball bearings and used them to weight the hop bag. When I removed them, the formerly shiny silver ball bearings were black and corroded, the beer had a powerful metallic taste and was ruined. I now use a hop bag, and weight it with some lead sinkers that are vacuum sealed in plastic. I once dry hopped pellets without a bag, and the filtration when racking to keg was a real PIA, so I always use a bag now
Thought I would add this to the thread. Amazon has these Stainless Steel Mesh containers that look perfect for this:
In my experience, I have found if you want that true IPA aroma you get from fruity hops when dry hopping, you definitely want that bag weighted down. It is the same as making a cup of tea. If the bag is not fully submerged, you're not wetting all of the tea leaves and as such, not making the strongest tea you possibly can. Dry hopping is same thing.
My advice as others have pointed out is to drop the hops right into the fermenting vessel. They will float at first and then you will find they will all mostly sink. When you use a hop bag, I find air gets trapped and the bag floats instead of sinking like it should. You could weigh it down, but you risk contaminating your brew (which could be a problem if you bottle condition especially).