How to set execution priority for certain Stored procedures ?

On our heavily used data warehouse server where some reports running for tens of minutes and sometimes for hours, we have some processes that we want to execute within seconds . Ideally this stored procedure executes within 1 sec on empty development server where no heavy processes are running. But on our production data warehouse it may take over 30 sec because it has to wait in queue to obtain server resources such as CPU and I/O.

I am wondering whether SQL Server 2005 has such feature to set high priority for certain stored procedures to by-pass other processes in queue and thereby to reduce wait time ?

Thanks.

There isn't such a setting in sqlserver.|||

Unfortunately, not. There is no hidden "Turbo" button.

Instead, I would recommend running the stored proc in Management Studio with SET STATISTICS IO ON, and with Display Actual Execution Plan enabled. If you are unfamiliar with how to tune a query, I would try running the SP in the Database Tuning Advisor to see what it recommends. In a DW type of database, you can be much more aggressive with indexes than in an OLTP database. You would also want to make sure your statistics are up to date.

You would also want to do some analysis of wait states at the instance and server level to get a feel as to whether you are seeing memory pressure, CPU pressure, or IO pressure.