Large Queries Slower in SSMS – SQL Server

Try SET STATISTICS XML ON before you run your query. That will return the plan in XML format without trying to render it in SSMS. You could then copy and paste the text into a file. From there, you'll get the plan in XML format, and you can open it up in a tool like SQL Sentry Plan Explorer.

The difference in logical reads leads me to believe that the two executions are getting different plans. This could be because:

• there are different set settings between the app that is calling the procedure that profiler is picking up and your instance of SSMS.
• you have a different default schema (you really should say exec dbo.SM_SP...).
• see this post for more details

You can see if there are two or more different plans for the procedure using something like this, which will also identify if this is caused by either (or both) of the two most likely factors for different plan choice:

SELECT p.plan_handle, p.usecounts, p.size_in_bytes, 
  set_options = MAX(CASE a.attribute WHEN N'set_options' THEN a.value END),
  dflt_schema = MAX(CASE a.attribute WHEN N'user_id'     THEN a.value END)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.objectid = OBJECT_ID(N'dbo.SM_SP_MAILEVENTNAMES')
AND a.attribute IN (N'user_id', N'set_options')
GROUP BY p.plan_handle, p.usecounts, p.size_in_bytes;