I agree, it's very subjective, especially since you were so vague about what kind of mods you might want. Are you looking for custom content like hair, skin, clothes, furniture, walls, floors, etc? Do you want houses or Sims? Or are you looking for hacks that change the way the game is played?
As already mentioned, Mod the Sims is a great site because it has a lot of every type of Sims mod, it doesn't specialize.
If you're looking for some specific content or even if you just want to browse, this list is great: http://www.sims2wiki.info/wiki.php?title=Content_List:Main_Page - it has links to specific kinds of Sims content you can download.