I think I'm a little late to the discussion, but in my mind, the first and foremost thing I have always wanted from the SDK is to add more hooks and functionality to grant modders the ability to effect things which were overlooked in the original modding SDK. Some have already been mentioned, like unit Domains. Some others include resource requirements for units, adding health to a city, Event hooks for pre-combat calculations, etc. Personally, the first and primary thing I would want to do with the SDK is create an OO framework for the game system to allow for "full" python flexibility. By this I mean creating an object style API for each Civ4 entity, and allowing hooks in Python to override the default values returned by such. To open up the XML information read in on startup to be modifiable dynamically. And to add the event hooks we all know are missing, as well as clean up the names of the event hooks that currently exist (*cough* OnBeginPlayerTurn, OnEndPlayerTurn *cough*). As a fourth area of expansion, I have in mind to allow the creation both of "generic" XML data files for mod use which can be accessed in game for mod specific purposes--be they random events, spells, new goody generation entries--whatever. So as to allow coders in projects to create new features while letting their team's XML wielding members add specifics independently. Similarly, adding the ability to add arbitrary fields to existing XML entities which could be accessed by a generic Python API would dramatically expand what various teams could accomplish. A Summoned flag for units, or a Dark Side of the Moon only flag for buildings (just.. you know.. as an example

. These sort of highly leverageable changes are what I see as the greatest things we could add to the modding community.
With modifications such as these, one, version-controlled, modder's expansion patch could allow all Python capable modders in the community to accomplish a wide variety of far more ambitious mods without having to touch or know anything about the presumably more esoteric and difficult to approach C++ SDK.
I haven't had the chance to check out the SDK yet, (I literally just started reading about it 60m ago :/.), but this area, more then UI or Performance definitely, and even more then AI strikes me as the most compelling application for the SDK. I don't know yet if it's possible to do something similar to allow for AI tweaking for various mods by adding more Python hooks in that arena. Only experience will be able to guide me in that :/.
P.S. I forgot to mention, I also am proficient with C++ and Visual Studio, since we seem to be taking a sort of roll call

.