Hi all I have an app that runs a advanced search query as below CREATE PROC [dbo].[CustomerAdvancedSearch] @FirstName NVARCHAR(50) = '' , @LastName NVARCHAR(50) = '' , @City NVARCHAR(50) = '' , @Postcode NVARCHAR(20) = '' , @Country NVARCHAR(50) = '' , @Name NVARCHAR(50) = '' , @DateOfBirth NVARCHAR(12) = '' AS SET nocount ON --DECLARE @FirstName NVARCHAR(50) = '' , -- @LastName NVARCHAR(50) = '' , -- @City NVARCHAR(50) = '' , -- @Postcode NVARCHAR(10) = '' , -- @Country AS NVARCHAR(2) = '' , -- @Name AS NVARCHAR(50) = '' , -- @DateOfBirth AS NVARCHAR(12)= '' SELECT TOP 100 ISNULL(CU.FirstName, 'Unknown') AS FirstName , ISNULL(CU.LastName, 'Unknown') AS LastName , ISNULL(CU.City, 'Unknown') AS City , ISNULL(CU.Postcode, 'Unknown') AS Postcode , CO.Name , CL.CompanyID , ISNULL(CN.Country, 'Unknown') , CU.CustomerID , CASE WHEN CU.PrimaryCustomer = '1' THEN 'Yes' ELSE 'No' END AS [Primary] , ISNULL(CU.MiddleName, 'Unknown') AS MiddleName , ISNULL(REPLACE(CONVERT(NVARCHAR, DateOfBirth, 103), '/', '-'), 'Unknown') AS DateOfBirth , CASE WHEN CU.Active = '1' THEN 'Active' WHEN CU.Active = '0' THEN 'Inactive' ELSE 'Unknown' END AS ActiveStatus FROM crm.dbo.Customer CU JOIN crm.dbo.CompanyLevel CL ON cl.CompanyLevelID = cu.CompanyLevelID JOIN crm.dbo.Company CO ON co.CompanyID = cl.CompanyID LEFT OUTER JOIN dbo.Country CN ON CN.CountryCode = CU.Country WHERE ( @FirstName = '' OR FirstName LIKE @FirstName + '%' ) AND ( @LastName = '' OR LastName LIKE @LastName + '%' ) AND ( @City = '' OR City LIKE @City + '%' ) AND ( @PostCode = '' OR Postcode LIKE @PostCode + '%' ) AND ( @Country = '' OR CN.Country = @Country ) AND ( @Name = '' OR CO.Name LIKE @Name + '%' ) AND ( @DateOfBirth = '' OR @DateOfBirth = CONVERT(DATE, DateOfBirth, 103) ) I've tuned this search query and it is as optimised as possible. I have looked to see what the app is running and it is literally this stored proc with various parameters. I have run the proc with same parameters in SSMS and it is blazing quick sub second. However when the app is running it, it is really slow or hangs. How do i rule out that it is the database that is the issue? i'm not very familiar with the various diagnostic DMVs in SQL or understand if what i'm looking at is high etc. so could someone help me and i'll post stats etc.? Thanks! I have run which i found WITH Waits AS (SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER', 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES', 'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK') ) SELECT W1.wait_type AS WaitType, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum ]]]
BTW, what is the output of the following SELECT qs.plan_handle, a.attrlist FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est CROSS APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + ' ' FROM sys.dm_exec_plan_attributes(qs.plan_handle) epa WHERE epa.is_cache_key = 1 ORDER BY epa.attribute FOR XML PATH('')) AS a(attrlist) WHERE est.objectid = object_id ('dbo.CustomerAdvancedSearch') AND est.dbid = db_id() I guess there could be two plans for the same procedure.
@jhowe - It seems that you are experiencing what @Kev Riley has correctly identified as parameter sniffing. SQL server has decided on a plan for the first time the sproc was called and this is optimal only for searches with very similar / identical values as the first call. When you issued sp_recompile the plan that was in cache was kicked out and the next call of the procedure was then compiled and ran really fast. I would bet that a subsequent call with completely different parameter values would result in really slow execution. The idea that @Usmann Butt offered up: `OPTION(RECOMPILE)` will effectively stop SQL Server from caching a plan. This will result in each call of the procedure to generate a one-time plan that fits exactly to the parameters supplied. This will mean that you have a much better chance of getting a good execution plan and faster execution. However, you have to consider the downside of compiling a plan each and every time the stored procedure is called. Compiling is not a trivial part of execution and can be quite taxing on the server, especially if the stored procedure is called very often. Compiling is especially CPU intensive and if you are calling the procedure hundreds of times a second, you may well run into performance problems from compiling. You also need to consider that compiling may end up taking more time than execution with an "average" plan. Another alternative to `OPTION(RECOMPILE)` is to use `OPTIMIZE FOR` supplying values for each variable that make for a plan that is consistent in execution (guaranteeing an average execution rather than ultra-fast and ultra-slow) or even `OPTIMIZE UNKNOWN`. These hints were designed to tackle the issue you were having and are a step up from `OPTION (RECOMPILE)` which is a little bit of a sledgehammer solution to parameter sniffing. If you have any more questions, let us know.
Please run sql profiler and find the query execution is same or different. What i am feeling is the query to needs to be corrected. if you can reply back with the query execution time for both from SSMS and EXE with same parameters and values, then we can proceed further.