Ultimamente las consultas tandan mucho mas que antes

[Beakdan]

Bueno, casi siempre que me solicitan optimizar el tiempo de respuesta de consultas, existen índices en las tablas, pero casi nunca son los más adecuados.
Dependiendo de los dispositivos disponibles en tu servidor, hay muchas cosas que se pueden hacer.
Por ejemplo, si tus consultas utilizan constantemente tablas temporales, es muy conveniente tener tempdb en una unidad de almacenamiento aparte. A veces no las usas explicitamente, pero operacionaes tales como Eager Spool, Lazy Spool, Table Spool, ímplicitamente usan esta base. Al separarla evitarás problemas de contención.
Supongo que no debo mencionar siquiera la necesidad de guardar el log de transacciones en una unidad independiente para evitar también problemas de contención.
También puedes obtener mejor rendimiento guardando la base de datos en multiples Filegroups (y probablemente distribuyendo dichos filegroups en multiples unidades) de tal manera que separes indices y datos en distintos filegroups. De esa manera puede aprovechar mucho mejor el paralelismo de tu server, y reduces la competencia por los recursos.
En caso de bases verdaderamente masivas, analiza la posibilidad de particionar tu base de datos.

Normalmente los usuarios perciben como lentas las cosultas que entregan sumarizados. Resulta evidente que con el paso del tiempo estás parecerán más y más lentas al incrementarse la cantidad de registro a procesar antes de entregar el resultado. Es un problema común en DataMarts y DataWarehouses. Sin embargo, aún procesando cientos de miles de registros, el tiempo respuesta no debe exceder de algunos segundos. Cuando ni esos segundos son aceptables, puedes recurrir a vistas indexadas por ejemplo.

Aún cuando hay muchas cosas que se pueden hacer a nivel server, yo creo que las mayores ganancia se obtienen estructurando correctamente las consultas. Separa la parte relacional en tus consultas de la parte de la presentación. El hecho de que existan indices, no significa que tus consultas los estén aprovechando, y tampoco significa que sean correctos. Muchas veces encuentro que eliminar un índice mejora el rendimiento de consultas problemáticas, y al mismo tiempo, reduce el coste de mantenimiento del motor de SQL.

Por lo anterior es que me gustaría ver tu consulta y plan de ejecución.