First, ALL breeds are capable of really enjoying agility and turning it into a job. And that's the point. It's incredibly hard to make finals at Nationals or qualify for the World team. So the idea of getting started in agility for the purpose of winning a lot is ridiculous. It's like starting to jog only because your intention is to win a gold medal in the Olympics in the marathon.
Second, there are huge differences agility-wise among dogs on the basis by size. Big dogs tend to have bigger strides (so they cover ground quicker and require cues sooner). Smaller dogs are generally easier to keep up with, you can cue later, turn quicker, handle wraps better.
That said, there are about 40-50 factors that determine agility success and size wouldn't be on that list. There are incredible kick-butt small dogs and outstanding, amazing big-dogs in agility.
I personally prefer a smaller dog. They're easier to travel with (he can fly under my seat in a carrier to flights outside the area), I can ask him to jump into my arms at the end of a run, and with a small backyard, I can set up more of a course than if I had a saluki or lab or even a BC. But that's just my preference.
As I said earlier, all dogs can enjoy and benefit from agility. Really big dogs (Leonbergers, St. Bernards, Wolfhounds) tend not to do as well because they have to crawl through the tunnel. Dachshunds and bulldogs have physical limitations. That said, I've seen representatives from each of these breeds competing (and enjoying) the sport.
Agility (at the highest levels) tends to be biased towards dogs that are long-limbed (doesn't mean big dog, just longer legs to body ratio), light-boned (so more muscle and less skeletal weight) dogs that are highly biddable with strong drives. Generally speaking, that means herding breeds tend to do well. A short list of breeds that consistently show up in World finals would be:
--BC
--Aussie
--Sheltie
--Pyrenean Shepherd
But there are examples of each of these breeds that were no good at agility. It really isn't the breed, it's the individual dog, its temperament, quality of the handler and trainer.
Specifically, some smaller dogs that rock at agility:
--mini-aussie (tough to find one under 13 but it would be my first choice for a mini agility dog--if you can handle the shedding)
--sheltie (I don't care for the nonstop barking but this is the default small agility dog--quick, agile, very biddable)
--JRT (the only better outlet for them is earthdog--capable of being good at agility. But make sure you get one from good stock).
--Pom (lots of strong competitors in the smaller dog categories use Poms).
--Paps (again, a popular small dog on the agility circuit).
--Rat Terrier (my current agility dog--super athlete that loves to please)
--Min-pin (often tests authority but I know of one min-pin that just flies on the agility course)
--Toy Fox Terrier (think of a much smaller rat terrier, plus it's so small that it's not going to outrun you on the course).
--Schipperke (very athletic, small dogs, that shed alot and bark even more).
Finally, first and last of all, the dog is your pet. First, choose a good pet, a dog you want to be around. Then look for temperament. Which small dog is best than is a function of YOU as a handler. If you're slow or inexperienced, I wouldn't go with a sheltie. If you want a dog you can run with than I'd get the smallest dog you can (say...a TFT) and you'd run the course together. Any dog (other than a bulldog or dachshund) can be outstanding at agility (with the right temperament and training).