[SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS

[SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS

This morning I found myself digging into some indexes and viewing execution plans and I couldn't for the life of me figure out why things were coming back as a Index Scan versus an Index SEEK.

Upon running the same query in both SQL Editor and SSMS, SQL Editor shows an Index Scan, where SSMS shows an Index Seek. Again, same exact query.

Any ideas why that would be?

SSMS is picking up my Clustered PK index, and SQL Editor is picking up on a non clustered index that doesn't even contain the column in question.

*Edit: The SSMS Execution plan and the SQL Assistant execution plan agree WITHIN SSMS. But the Execution Plan in SQL Editor does not.

I think DBCC FREEPROCCACHE cleared up some of the confusion here. Now SQL Editor agrees with SSMS. I must have just been working on a cached plan.

Any chance you had different settings in effect for SSMS and SQL Editor that resulted in different plans? ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULL and co. are notorious for causing such mayhem.

	gemisigo wrote:
	Any chance you had different settings in effect for SSMS and SQL Editor that resulted in different plans? ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULL and co. are notorious for causing such mayhem.

Nothing in my code windows.. whatever the defaults are for SSMS/SQL Editor are in those regards maybe...

Probably. SQL Server Management Studio has ARITHABORT set to ON, while apps connecting using ADO or ODBC usually default to ARITHABORT OFF, if I recall correctly.

	gemisigo wrote:
	Probably. SQL Server Management Studio has ARITHABORT set to ON, while apps connecting using ADO or ODBC usually default to ARITHABORT OFF, if I recall correctly.

That makes sense but they seem to be matching up now. I think it was a cached plan.. but it really threw me off this morning.

You might want to read >>this<< if you haven't yet. I learned a lot from it.

	gemisigo wrote:
	You might want to read >>this<< if you haven't yet. I learned a lot from it.

Thanks I'll check it out.

Is there anywhere in SQL Assistance to set defaults for sessions like ARITHABORT that you've found?

*Edit; Though your link suggests turning it on as long as ANSI_WARNINGS is ON.

None that I know of. But Connections in DB Options have a Custom Settings section. Perhaps that part? SysOp?

	gemisigo wrote:
	None that I know of. But Connections in DB Options have a Custom Settings section. Perhaps that part? SysOp?

The context help on that suggests it's for connection string customization. I'm unsure you can set stuff like that in there?

Probably not. We might want to file a feature request for that one then.

	gemisigo wrote:
	Probably not. We might want to file a feature request for that one then.

It might be nice to have some defaults there. There seems to be a varied opinion on whether ARITHABORT should be on or off and seems to differ depending on which versions of SQL SERVER you are connecting to.

	gemisigo wrote:
	Probably not. We might want to file a feature request for that one then.

I see you can set it in the SQL Server properties under connections.. I wonder if that's wise?

I wouldn't touch those. It'd affect everyone, even those assuming defaults would actually default to default values :)

	gemisigo wrote:
	I wouldn't touch those. It'd affect everyone, even those assuming defaults would actually default to default values :)

It just seems with how much trouble and how much talk there is about the impact (positive or negative) about ARITHABORT it would seem like it would make sense to set it on or off (depending on circumstances).