May 31, 2012 at 2:29 pm
I've recently inherited the DBA responsibility for a SQL box. My plan is to get off of it ASAP but until this is done, the developer working on one of the apps is seeing better performance when using the SA login (they have rights to it currently and don't get me started) versus the application login. The SA login runs a set of statements in a few seconds where the app login in runs for over 30 seconds. I ran a trace on the database and the SQL is running in the same amount of time but the app login is showing an Audit Logout in the 5,000 range where as SA is less than 50. I suspect it's something to do with the connection pooling but not sure what I can do to resolve this. The code is exactly the same (so I'm told) and the only thing that's changing is the connection string information between the runs.
Anyone have any ideas on this?
May 31, 2012 at 3:27 pm
A stab in the dark, but does it have something to do with Ownership Chaining (http://msdn.microsoft.com/en-us/library/ms188676(SQL.105).aspx)?
Your profiler trace should tell you if the data is being called through a view or direct table access, or if the call is coming from a stored procedure.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 31, 2012 at 3:29 pm
Very simple queries, they just return 1 column from a single table.
May 31, 2012 at 3:32 pm
Do you have auditing turned on?
May 31, 2012 at 3:38 pm
Nope; it's set to None.
May 31, 2012 at 4:45 pm
Strange. If you give another login, perhaps a db_owner, but not sysadmin, is there a change?
Any chance of client side tracing through ADO?
May 31, 2012 at 4:52 pm
I tried that this afternoon and it improved performance but still was not as fast as the SA login. It';s the oddest thing I've ever seen and was convinced the issue was with the app until I saw the trace with the Log off durations in it.
To give a little more information, the app is running two queries with an outer loop feeding a parameter on an inner loop. The delays I'm seeing are on the inner query; the outer is comprable.
Not familiar with a trace through ADO. What would this entail?
June 1, 2012 at 7:54 am
Found at least a partial solution. Adding "Integrated Security=SSPI" to the connection string in the app brought the timing down from 5 to 1 second on the inner queries. Still not as good as the SA login but liveable.
June 1, 2012 at 8:38 am
That's odd. usually the login shouldn't be slowing it down, especially in SQL 2000. There must be something that's not happening in sa, but can't think about it.
If you can, I'd open a case with MS. This is certainly an odd one.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply