Stored procedure generating dynamic SQL is slow for some parameters

Robin16 2016-06-26 23:24:24

I'm having issue with a stored procedure that generates dynamic SQL, it performs ok for some parameters but very slow for others.

This has been an issue everyday and we are making some changes to filters in where clause and it fixes the issue.

Overview:

  • SQL Server 2014
  • Windows 2012
  • Ram 512 GB, assigned 460 GB to SQL
  • CPU – 16, MaxDop – 8, Cost threshold of parallelism – 25

This is a reporting query. I checked the profiler and Idera SQLdm for any wait stats or blocking – Nothing reported.

Stored procedure creates 6 temp tables and loads data and pulls information from views and other procedures.

There is one view that this procedure uses and it has 900 columns and contains a table with 1.2 billion rows, we are planning to implement table partitioning in couple of weeks but not sure if partitioning fixes the issue if data access is from all the partitions.

I know this is expected for relational database but can anyone please help me in finding a permanent fix to this issue ?

My suggestions to dev team:

Create staging tables and load data instead of creating temp tables for every execution and use the staging tables to access data.
Create filtered indexes (but creating the indexes on large table takes lot of time).