Firstly, welcome to Nexus. Hope you enjoy your time here.
Secondly, hopefully I can stumble along and provide some
answers from my personal experience.
Mods are scanned and there is a notation on the upper right
hand side of the download page. I have downloaded well
over 1000 mods and none have yet to have any effect to
my computer. Some have totally messed up games and I've
had to delete and re-install. Also, I have a hard drive that is
only for games, keeping them away from the OS drive.
After I install a mod, I then go into the game and make sure
things function correctly because sometimes there are conflicts.
If the game crashes, I know something in the game does not
like the mod. I install all my mods manually by copying, not
moving the contents of the mod. That way I can make sure I
remove all the contents of the mod should problems arise.
Just yesterday I had problems with S.T.A.L.K.E.R. : Call of
Chernobyl because it did not like a 22kb sub file, so I removed
the mod. Game refused to function, so I had to delete/re-install.
Some conflicts are not found until later in the game, like entering
an area and then suddenly it's... ooo lookie at my nice desk top.
( can happen in open world games )
Usually a mod installed conflicts with something else in the area
for some unknown reason only shows up when you enter that
area. Easiest thing to do is remove a mod that is used for that
section of the landscape, town, or whatever and see if that
corrects the problem.
A lot of people have crashing problems because they go crazy
with "beauty mods"...wanting things to look pretty and will
over do it with textures related mods.
In Oblivion, I had over 250 mods and rarely had a crash.
I'm not a self proclaimed "expert", but I hope this helps a bit.