When video memory reaches its limit, textures are streamed from main memory causing a huge performance loss. And given Skyrim is already more demanding memory wise than its predecessors, it's more important than ever to keep an eye on memory usage when using texture mods. Unfortunately texture mods, especially large ones, if saved using the wrong format can waste a lot of valuable video memory real estate. However I noticed, delving into the files, that some of them have textures saved in the wrong format, while others often forget mipmaps.
There are so many texture mods of all makes and models that have been released so far, some of them fantastic and very professionally made.
Disclaimer: I'm not actually a texture artist myself, though I have some limited experience manipulating textures (re: MMM), so those more knowledgeable than me please feel free to chime in!