I experienced the exact same problem several times recently (with MS-SQL 2008). Specific stored procedures will be extremely slow to start (minutes), but the same SQL inserted into SSMS took only a few seconds.
The problem is that the stored procedure uses a poor execution plan, while the inserted SQL uses a different (and much better) execution plan.
Compare execution plans
To test this hypothesis, open a new query window in SSMS and turn on “Enable actual execution plan” ( Ctrl-M is the keyboard shortcut for this).
Then paste the contents of the stored procedure into the window and follow this when calling the actual stored procedure. For instance:
SELECT FirstName, LastName FROM Users, where ID = 10
EXEC dbo.spGetUserById 10
Run both queries together, and then compare the execution plans for both. I have to say that in my case, the "Request Cost" rating for each request did not help at all and pointed me in the wrong direction. Instead, look carefully at the indexes used, whether scans are performed instead of searching, and how many rows are processed.
There should be a difference in plans, and this should help identify tables and indexes that need to be explored further.
To fix this problem, in one instance, I was able to rewrite the stored procedure to avoid using index scanning and instead rely on index queries. In another instance, I found that the update, which rebuilds the indexes for the specific table used in the query, did everything possible.
Search and update indexes
I used this SQL to find and restore the corresponding indexes:
/* Originally created by Microsoft */ /* Error corrected by Pinal Dave (http://www.SQLAuthority.com) */ /* http://blog.sqlauthority.com/2008/03/04/sql-server-2005-a-simple-way-to-defragment-all-indexes-in-a-database-that-is-fragmented-above-a-declared-threshold/ */ /* Catch22: Added parameters to filter by table & view proposed changes */ -- Specify your Database Name USE AdventureWorks /* Parameters */ Declare @MatchingTableName nvarchar(100) = 'MyTablePrefix' -- Specify Table name (can be prefix of table name) or blank for all tables DECLARE @ViewOnly bit = 1 -- Set to 1 to view proposed actions, set to 0 to Execute proposed actions: -- Declare variables SET NOCOUNT ON DECLARE @tablename VARCHAR(128) DECLARE @execstr VARCHAR(255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag decimal DECLARE @maxreorg decimal DECLARE @maxrebuild decimal DECLARE @IdxName varchar(128) DECLARE @ReorgOptions varchar(255) DECLARE @RebuildOptions varchar(255) -- Decide on the maximum fragmentation to allow for a reorganize. -- AVAILABLE OPTIONS: http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx SET @maxreorg = 20.0 SET @ReorgOptions = 'LOB_COMPACTION=ON' -- Decide on the maximum fragmentation to allow for a rebuild. SET @maxrebuild = 30.0 -- NOTE: only specifiy FILLFACTOR=x if x is something other than zero: SET @RebuildOptions = 'PAD_INDEX=OFF, SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON' -- Declare a cursor. DECLARE tables CURSOR FOR SELECT CAST(TABLE_SCHEMA AS VARCHAR(100)) +'.'+CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME like @MatchingTableName + '%' -- Create the temporary table. if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%') drop table