SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS
Author Message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post [SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS Reply with quote
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.
Tue Nov 08, 2016 11:57 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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.
Tue Nov 08, 2016 2:59 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
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.
Tue Nov 08, 2016 4:30 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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...
Tue Nov 08, 2016 4:32 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
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.
Tue Nov 08, 2016 4:38 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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.
Tue Nov 08, 2016 4:39 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
You might want to read >>this<< if you haven't yet. I learned a lot from it.
Tue Nov 08, 2016 4:41 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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.
Tue Nov 08, 2016 4:42 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
None that I know of. But Connections in DB Options have a Custom Settings section. Perhaps that part? SysOp?
Tue Nov 08, 2016 4:46 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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?
Tue Nov 08, 2016 4:48 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
Probably not. We might want to file a feature request for that one then.
Tue Nov 08, 2016 4:49 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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.
Tue Nov 08, 2016 4:51 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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?
Tue Nov 08, 2016 5:18 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1395

Post Reply with quote
I wouldn't touch those. It'd affect everyone, even those assuming defaults would actually default to default values :)
Tue Nov 08, 2016 5:30 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
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).
Tue Nov 08, 2016 5:31 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.