views:

1208

answers:

7

Update: Problem solved, and staying solved. If you want to see the site in action, visit Tweet08

I've got several queries that act differently in SSMS versus when run inside my .Net application. The SSMS executes fine in under a second. The .Net call times out after 120 seconds (connection default timeout).

I did a SQL Trace (and collected everything) I've seen that the connection options are the same (and match the SQL Server's defaults). The SHOWPLAN All, however, show a huge difference in the row estimates and thus the working version does an aggressive Table Spool, where-as the failing call does not.

In the SSMS, the datatypes of the temp variables are based on the generated SQL Parameters in the .Net, so they are the same.

The failure executes under Cassini in a VS2008 debug session. The success is under SSMS 2008 . Both are running against the same destination server form the same network on the same machine.

Query in SSMS:

DECLARE @ContentTableID0 TINYINT
DECLARE @EntryTag1 INT
DECLARE @ContentTableID2 TINYINT
DECLARE @FieldCheckId3 INT
DECLARE @FieldCheckValue3 VARCHAR(128)
DECLARE @FieldCheckId5 INT
DECLARE @FieldCheckValue5 VARCHAR(128)
DECLARE @FieldCheckId7 INT 
DECLARE @FieldCheckValue7 VARCHAR(128)
SET @ContentTableID0= 3
SET @EntryTag1= 8
SET @ContentTableID2= 2
SET @FieldCheckId3= 14
SET @FieldCheckValue3= 'igor'
SET @FieldCheckId5= 33
SET @FieldCheckValue5= 'a'
SET @FieldCheckId7= 34
SET @FieldCheckValue7= 'a'

SELECT COUNT_BIG(*)
FROM dbo.ContentEntry AS mainCE
WHERE GetUTCDate() BETWEEN mainCE.CreatedOn AND mainCE.ExpiredOn
AND (mainCE.ContentTableID=@ContentTableID0)
AND ( EXISTS (SELECT *
              FROM dbo.ContentEntryLabel
              WHERE ContentEntryID = mainCE.ID
              AND GetUTCDate() BETWEEN CreatedOn AND ExpiredOn
              AND LabelFacetID = @EntryTag1))
      AND (mainCE.OwnerGUID IN (SELECT TOP 1 Name
                                FROM dbo.ContentEntry AS innerCE1
                                WHERE GetUTCDate() BETWEEN innerCE1.CreatedOn AND innerCE1.ExpiredOn
                                AND (innerCE1.ContentTableID=@ContentTableID2
                                     AND EXISTS (SELECT *
                                                 FROM dbo.ContentEntryField
                                                 WHERE ContentEntryID = innerCE1.ID
                                                 AND (ContentTableFieldID = @FieldCheckId3
                                                      AND DictionaryValueID IN (SELECT dv.ID
                                                                                FROM dbo.DictionaryValue AS dv
                                                                                WHERE dv.Word LIKE '%' + @FieldCheckValue3 + '%'))
                                                )
                                    )
                               )
           OR EXISTS (SELECT *
                      FROM dbo.ContentEntryField
                      WHERE ContentEntryID = mainCE.ID
                      AND (   (ContentTableFieldID = @FieldCheckId5
                               AND DictionaryValueID IN (SELECT dv.ID
                                                         FROM dbo.DictionaryValue AS dv
                                                         WHERE dv.Word LIKE '%' + @FieldCheckValue5 + '%')
                              )
                           OR (ContentTableFieldID = @FieldCheckId7
                               AND DictionaryValueID IN (SELECT dv.ID
                                                         FROM dbo.DictionaryValue AS dv
                                                         WHERE dv.Word LIKE '%' + @FieldCheckValue7 + '%')
                               )
                          )
                     )
          )

Trace's version of .Net call (some formatting added):

exec sp_executesql N'SELECT COUNT_BIG(*) ...'
,N'@ContentTableID0 tinyint
,@EntryTag1 int
,@ContentTableID2 tinyint
,@FieldCheckId3 int
,@FieldCheckValue3 varchar(128)
,@FieldCheckId5 int
,@FieldCheckValue5 varchar(128)
,@FieldCheckId7 int
,@FieldCheckValue7 varchar(128)'
,@ContentTableID0=3
,@EntryTag1=8
,@ContentTableID2=2
,@FieldCheckId3=14
,@FieldCheckValue3='igor'
,@FieldCheckId5=33
,@FieldCheckValue5='a'
,@FieldCheckId7=34
,@FieldCheckValue7='a'
A: 

I ran into this problem before as well. Sounds like your indexes are out of whack. To get the same behavior in SSMS, add this before the script

SET ARITHABORT OFF

Does it timeout as well? If so, it's your indexing and statistics

Steve Wright
Tried that, didn't change anything. The connection, database and server defaults are ARITHABORT ON. Trace show same connection options for both connections:-- network protocol: TCP/IPset quoted_identifier onset arithabort onset numeric_roundabort offset ansi_warnings onset ansi_padding onset ansi_nulls onset concat_null_yields_null onset cursor_close_on_commit offset implicit_transactions offset language us_englishset dateformat mdyset datefirst 7set transaction isolation level read committed
IDisposable
By didn't change anything, I mean that I added that to the SSMS and it still finish in sub-second time.
IDisposable
@Steve - Having the same problem and `SET ARITHABORT OFF` also causes SSMS to time out. So if it's indexing/statistics, how do we resolve or rebuild them?
Nelson
@Steve - So I rebuilt the indexes (http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx) and the problem went away. Looks like we need a maintenance plan (http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx#MaintenancePlan) or something similar to prevent this. Any idea why it occurs in the first place?
Nelson
A: 

It's most likely index-related. Had a similar issue with .Net app vs SSMS (specifically on a proc using a temp table w/ < 100 rows). We added a clustered index on the table and it flew from .Net thereafter.

All tables have all the indexes (of the right kind) you could ever wish for.. the plan shows nothing but happy-path plan elements.
IDisposable
A: 

I've had off-hours jobs fubar my indexes before and I've gotten the same result as you describe. sp_recompile can recompile a sproc... or, if that doesn't work, the sp_recompile can be run on the table and all sprocs that act on that table will be recompiled -- works for me every time.

Joe Davis
No stored procs in the game. Statistics updated, ALL indexes rebuilt.
IDisposable
+3  A: 

It is not your indexes.

This is parameter-sniffing, as it usually happens to parametrized stored procedures. It is not widely known, even among those who know about parameter-sniffing, but it can also happen when you use parameters through sp_executesql.

You will note that the version that you are testing in SSMS and the version the the profiler is showing are not identical because the profiler version shows that your .Net application is executing it through sp_executesql. If you extract and execute the full sql text that is actually being run for your application, then I believe that you will see the same performance problem with the same query plan.

FYI: the query plans being different is the key indicator of parameter-sniffing.

FIX: The easiest way to fix this one assuming it is executing on SQL Server 2005 or 2008 is to add the clause "OPTION (RECOMPILE)" as the last line of you SELECT statement. Be forewarned, you may have to execute it twice before it works and it does not always work on SQL Server 2005. If that happens, then there are other steps that you can take, but they are a little bit more involved.

One thing that you could try is to check and see if "Forced Parameterization" has been turned on for your database (it should be in the SSMS Database properties, under the Options page). To tunr Forced Parameterization off execute this command:

    ALTER DATABASE [yourDB] SET  PARAMETERIZATION SIMPLE
RBarryYoung
You are correct about the sp_executesql I would be surprised if that's the cause as this is ONLY statement against the server that could match the plan cache (fresh restart). I can't force a recompile on every statement my codegen makes, that would hammer the server.Can I disable parameterization somehow?
IDisposable
Parameter-sniffing doesn't have anything to do with picking up the plans for other statements it has to do with the fact that the same query with different parameters should get different plans for best performance. However, once parametrized, queries will get stuck or frozen on one plan that is bad for most (or even all) parameters.
RBarryYoung
Thanks for the information, we do NOT have Forced Parameterization turn on (I confirmed it is off for the Server and this database). As for different parameters, nope... same exact query was the ONLY values sent ever to this server. In fact, given the nature of the bug, the only values that could have changed are the strings in the middle of the (evil, ICK!!) LIKE '%' + @parameter + '%' clauses.It's something strange in the server, near as I can tell. Seems to be behaving since being rebooted.
IDisposable
If the same query, with the same values produces different execution plans depending on whether it is parametrized or not, that's parameter-sniffing because that's the definition of parameter-sniffing.
RBarryYoung
What I have not figured out is why it is using sp_executesql and parametrizing it so much, because I didn't think that simple parametrization would go that far.
RBarryYoung
A: 

Checked and this server, a development server, was not running SQL Server 2005 SP3. Tried to install that (with necessary reboot), but it didn't install. Oddly now both code and SSMS return in subsecond time.

Woot this is a HEISENBUG.

IDisposable
That's just because you flushed the query plan cache. It will come back unless you fix it.
RBarryYoung
I agree that it _should be_ because of that. However it never came back. I suspect one of the Windows Updates that installed at reboot had some effect. That or something in the Hyper-V host got happy.
IDisposable
A: 

I've seen this behavior before and it can be a big problem with o/r mappers that use sp_executesql. If you examine the execution plans you'll likely find that the sp_executesql query is not making good use of indexes. I spent a fair amount of time trying to find a fix or explanation for this behavior but never got anywhere.

Jamie Ide
Jamie: see my answer...
RBarryYoung
A: 

Most likely your .Net programs pass the variables as NVARCHAR, not as VARCHAR. Your indexes are on VARCHAR columns I assume (judging from your script), and a condition like ascii_column = @unicodeVariable is actually not SARG-able. The plan has to generate a scan in this case, where in SSMS would generate a seek because the variable is the right type.

Make sure you pass all your string as VARCHAR parameters, or modify your query to explicitly cast the variables, like this:

SELECT dv.ID
FROM dbo.DictionaryValue AS dv
WHERE dv.Word LIKE '%' + CAST(@FieldCheckValue5 AS VARCHAR(128)) + '%'
Remus Rusanu
No, sorry. Read the question again... I show the .Net trace's call and it IS passing in exactly the correct datatypes. Our access layer gets that right.
IDisposable