The SNP has emerged as the biggest party in local government as Labour citadels across the country fell.