Subhanallah it is a totally amazing experience. There is normally a drop silence and you can hear the other people crying in the haram - it also echoes - man it makes your hair stand on end - especially if your standing near the imams!
But the most awesome time was when Shaykh Salah Budair used to cry in trawee and his duaa after the trawee used to have the whole of the haram in tears! Mashallah, his recitation is unbelievable prays with real emotion especially when you hear him live - i used to wait for him to come on! And when Shaykh Salah Budair used to lead Tahajjud - walahi there are no words to explain it (Just think you are standing in Riyadul Jannah, near the Raudha of the Prophet (Sallalah-ho-alahi-wassalam) and your thinking man I am standing praying where the Sahaba used to pray, where Jibreel used to bring the Quran the message of Allah, where Bilal used to give adhan, you think of the times when the Prophet was alive - what it would have been like praying behind him)!
Another beautiful experience was when I went in ramadhan 2003 - I had just finished my umrah and was sitting in the mataf waiting for Fajr and SST was praying (first time I heard him) and when he started praying I thought this guy is good. Anyway he prayed Fatiha and then started on Surah Bani Israeel - it was just mind blowing - I will never forget it!