question

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 ]]] sql-server-2008sql-server-2008-r2