July 7, 2011 at 6:51 am
I have a SP whose performance had deteriorated in the production environment.
The application connects using a single user, so that user has multiple connections. Furthermore, the same user is used in replication and other tasks.
We tested the SP connecting with another user, and surprise! tenth the time it took before. (1 minute versus 12 minutes).
We repeated the test several times with both users and "low load user" always takes one tenth of the other.
Is this behavior normal?
SS2008 (R2) gives some kind of resource limit for each user?
July 7, 2011 at 7:23 am
Try running a trace for each user and then compare the results...
________________________________________________________________________________
Can I ask you a rhetorical question...?
________________________________________________________________________________
July 7, 2011 at 7:28 am
The trace shows the same, just change the processing time.
July 7, 2011 at 7:33 am
Have you tried SET STATISTICS IO ON; and are they the same for each?
________________________________________________________________________________
Can I ask you a rhetorical question...?
________________________________________________________________________________
July 7, 2011 at 7:33 am
Is resource governor configured?
-- Gianluca Sartori
July 7, 2011 at 7:35 am
Gianluca Sartori (7/7/2011)
Is resource governor configured?
Beat me to it 😉 - could you provide the query and the context of the differing users...?
________________________________________________________________________________
Can I ask you a rhetorical question...?
________________________________________________________________________________
July 7, 2011 at 7:38 am
No, we only use the SQL Profiler.
Try to see if we find differences.
Thank you.
July 7, 2011 at 7:38 am
Sorry to bombard - but if you tryEXECUTE AS [YourFastUserGoesHere] - does it change the performance?
________________________________________________________________________________
Can I ask you a rhetorical question...?
________________________________________________________________________________
July 7, 2011 at 7:42 am
Gianluca Sartori (7/7/2011)
Is resource governor configured?
The Resource Governor is not set, that is: we have the default settings. (0 - 0 - 25 - 0).
July 7, 2011 at 8:05 am
trevelvis (7/7/2011)
Gianluca Sartori (7/7/2011)
Is resource governor configured?Beat me to it 😉 - could you provide the query and the context of the differing users...?
The query is somewhat complex: there is a process in c # using a datareader to get data, executing a SP.
The user who has problems is in the connection string of a WCF service hosted in IIS7. However, to rule out problems inherent in WCF / IIS, we put the same process in a Windows executable, with the same results.
The same SQL user, is used in SSIS processes, replication, and so on.
The WCF service serves approximately 80 clients, but concurrent connections rarely exceeds 10, because the application connects and disconnects for each transaction.
The user who obtained a better performance, I was just assigned to this process.
We changed the user in the connection string of the service, and improved performance as well. The new user is exclusively for the application.
July 7, 2011 at 9:26 am
Post the actual execution plans for the proc exec when run by each user.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply