Zbigniew Brzezinski was Carter's National Security Advisor when he made a couple of trenchant observations. The first of these was that Soviet demographics were rapidly changing. Due to lower infant mortality and birth rates, Brzezinski expected that, within a generation, a majority of Soviet citizens would hail from traditionally Islamic SSRs (Turkmenistan, Kazakhstan, etc). At the same time, the Soviet government remained dominated by ethnic Russians, and this power arrangement wasn't something that the Russians would likely surrender, in Brzezinski's estimation. (He was correct in this - the Politburo remained dominated by ethnic Russians until the end).

These two factors convinced him that the USSR had a demographic time-bomb on their hands, one that they had no easy way of defusing. By forcing a Soviet occupation of Afghanistan, Brzezinski saw an opportunity to exacerbate these pressures.

While Afghanistan exposed a great number of flaws in Red Army doctrine, it represented more of a morale problem than an existential crisis, but the one thing it did show Moscow was that the vaunted Red Army could not be counted upon to contain an insurrection if this spread to the Islamic SSR's. Basically, it demonstrated to Moscow that the cupboard was bare, and it had huge vulnerabilities. These vulnerabilities were significant enough that even the Americans were wary of exploiting them (The mujahideen in Afghanistan, especially Dostum, were fully capable of launching cross-border operations into neighboring SSR's, but the US warned them against doing so for fear of spreading uncontrollable chaos).

In 1986, Gorbachev replaced the Politburo's Kazakh First Secretary with an ethnic Russian, precipitating nationalistic violence which was quashed by bloodshed. Armenians in Azerbijan demonstrated for union with Armenia, and this was met by an Azerbijani-nationalist reaction. As nationalist sentiment spread throughout the Soviet Union, Gorbachev tried to respond by offering greater freedom within the context of the USSR, but every meaningful freedom he granted only furthered the nationalistic breakdown.

In the end, the USSR was ripped apart by the same nationalistic forces which had been in check for half a century by a strong central state, the Red Army, and little else. The release of pressure simply came too late.

It's of course debatable whether or not Afghanistan was instrumental to this dynamic, but it was certainly the first challenge faced by the USSR where the state proved itself impotent in the face of new splintering forces.