So first of all, is that normal behaviour, that when a server is running normally, it sometimes sets all cores at 100%?
Como desenvolvedor, você deve saber que a única resposta para isso é "depende". é possível que um processo use toda a CPU por um período de tempo significativo - adivinhe, a resposta é sim.
Então, a questão é principalmente - o que o SQL Server faz aqui (e existem ferramentas para descobrir isso). Conecte-se, veja o que SQL Statements são executados por quanto tempo.
So when the cpu is at 100%, how can we check which app's are causing this?
Conecte-se usando a conta SA, examine as exibições dinâmicas para descobrir o que o SQL Server está ocupado processando no momento. Possivelmente anexar um traço.
Mas primeiro verifique se é o SQL Server. É muito raro que um SQL Server seja ligado à CPU. O primeiro gargalo é normalmente o subsistema de disco.