Para que serve o cache de planos no SQL Server? Para armazenar em cache os planos de execução. Quando você executa uma consulta, o mecanismo de otimização do SQL Server decide uma estratégia de execução, que é chamada de plano de execução. Como esse plano é caro para calcular, é útil mantê-lo em cache, na memória do servidor, para as próximas execuções da consulta.
Podemos visualizar o cache de planos (que historicamente também é chamado de cache de procedimentos) com uma visão de gerenciamento dinâmico chamada sys.dm_exec_cached_plans.
Vemos neste vídeo como o SQL Server pode manter em cache planos específicos ou planos parametrizados, com uma declaração de parâmetro prefixada. A parametrização é escolhida pelo SQL Server em caso de plano trivial.