September 21, 2010 at 7:32 am
Over the last week I've had two different stored procedures from two different applications that hit two different SQL servers (both 2005 build 9.0.3054) that time out when called from a web server through ADO (timeout is 30 seconds). Both stored procedures executed in less than a second when executed through SSMS. When I tried calling the stored procedures through a command prompt using SQLCMD I saw the same results as the call through ADO. The only common bit between both stored procedures is that there is "like '%' + @pSearchString +'%' " in the where clause of both procs. One of the procs has two different searches called based on a parameter passed in to the proc one with a like and one an equals. If you search on the search with the equals the proc worked, but the like timed out. I was able to fix both issues by restarting SQL server. Does anybody know what could be going on here? Because these are happening on production servers I can't take a lot of time to debug. I don't see anything in the SQL logs other than my differential and transaction log backups during these times. I also don't see anything in the windows event viewer. Any help would be greatly appreciated.
September 21, 2010 at 10:12 am
it sounds like a poor execution plan is being created. Usually if statistics are up to date the optimizer won't choose the wrong physical join operators because the estimated row counts will be accurate. One thing you might try to fix the problem quickly is look at the execution plan in SSMS and then issue query hints to get the optimizer to create a plan like that in the future. In otherwords if the good plan uses all "inner loop joins" then you could add a query hint "OPTION( loop join )".
The like '%' + @ + '%' might be causing the optimizer to make inaccurate estimates and then use a physical join operator that is inappropriate for the actual row counts. You could try using the OPTION(OPTIMIZE FOR(@pSearchString='somenormalvalue'))
or OPTION(OPTIMIZE FOR(@pSearchString UNKNOWN)) if you are on SQL Server 2008/2008R2
September 21, 2010 at 11:23 am
The LIKE statement with a % in the front of the string will result in table or index scans, no options. That is likely why things are running slowly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2010 at 11:55 am
Thanks to both of you for your replies and I will look at both options.
I don't know if I was clear in my original post or not, but both instances executed immediately when I ran them through management studio after I captured them through profiler. External calls though were timing out (taking longer than 30 seconds). One of which I know took 5 minutes 46 seconds when I ran it using SQLCMD but through management studio executed in less than a second.
September 21, 2010 at 12:02 pm
True, the heap/table/index will be scanned with the leading wildcard, but I am wondering if the optimizer plans for a full row count or a rowcount based on an average value. Presumably a different plan is being used when it runs in one second versus longer than 30 seconds. The hint isn't to make it scan/seek any different object, because it's going to scan like you said, but rather it's to help it arrive at the right count estimates. Although, now that I am thinking about it my suggestion would have the opposite effect because instead of having an estimated rowcount of every record in the heap/cluster/index it might use a count estimate from statistics based on the optimize for value or an average if unknown.
September 21, 2010 at 12:08 pm
If you supply a value through OPTIMIZE FOR, it's going to use that value. It won't help it arrive at a better plan, just the one that it can arrive at based on that value. If the value is different and would benefit from a different plan... you're in trouble.
As to the problem, this sounds like it might be an issue with the connection settings. Assuming everything else is the same, same query on the same database on the same server with the same parameters, and the only thing changing is the connection string, then you should check the ANSI settings on the two different connections to see what's happening there. Those can, and do, result in different execution plans and therefore different query speeds.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply