August 24, 2012 at 11:47 am
I am the developer of our in-house CRM application that runs against a database located on a single SQL 2008 R2 server.
All domain users connect to the database through SPPI / integrated authentication.
One of our managers was complaining that a report is running very slowly for him. The report is a series of in-line T-SQL statements in a Windows Forms / .NET environment.
I pulled up Profiler and started a trace; I noticed that execution of a particular T-SQL statement in the report was taking in excess of 1000 ms to execute. I then ran the same report with the same parameters under my own login and saw the same T-SQL statement executed in less than 1 ms.
I deleted the manager's terminal services roaming profile and had him log in as a new user, thinking that something might have gotten into the profile registry to cause the SQL connection issue. Unfortunately the difference in execution time for this one query (repeated thousands of times in a loop) didn't change.
What could possibly cause the exact same T-SQL statement with the exact same parameters to run so much slower for one domain user than another, when both users connect to the database with SPPI / Windows-integrated auth?
Thanks in advance for any feedback.
Andre Ranieri
August 24, 2012 at 11:52 am
Using the server-side trace, you should also be able to capture the session connection information. See if there is any difference between the sessions, what options are on and off.
August 27, 2012 at 11:02 am
The first thing I notice is that the other user seems to hover around 668117 reads for his query, whereas I typically am between 80 and 120.
His CPU time is about 800 - 1200 ms whereas mine is around 0-20.
Are there any columns or fields in SQL Profiler server side trace which you'd recommend I look at for connection type, parameters, etc?
Thanks,
Andre
August 27, 2012 at 11:07 am
Here's the T-SQL statement that has the big delta in execution time depending on the user.
The connection type for all users on our CRM is Windows integrated auth (SPPI).
Any feedback would be very much appreciated!
Andre
exec sp_executesql N'SET LOCK_TIMEOUT 15000;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT MS.ServiceID, MS.SaleAmount, MS.Service, MS.Status, MS.StatusDate, ''False'' AS Checked
FROM tblMainServices MS
INNER JOIN tblMainServices_B SB ON (MS.ServiceID = SB.ServiceID AND (SB.OriginalDate BETWEEN @BeginDate AND @EndDate) )
WHERE AccountKey = @AccountKey AND (SB.OriginalDate BETWEEN @BeginDate AND @EndDate) AND TimesRenewed = 0 AND (Salesperson = @EmployeeID OR SecondarySalesperson = @EmployeeID ) ',N'@AccountKey int,@EmployeeID varchar(8),@BeginDate datetime,@EndDate datetime',@AccountKey=324071,@EmployeeID='SPO3698',@BeginDate='2012-02-24 00:00:00',@EndDate='2012-08-25 23:59:59'
August 27, 2012 at 11:13 am
Here's the connection string used globally by all CRM users:
Application Name=*****;Data Source=******;integrated security=SSPI;Database=******;Connection Timeout=0
August 27, 2012 at 11:49 am
Use the profiler to pull up the actual query plan for your user. Compare that to your own query plan when you try to execute it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 27, 2012 at 12:08 pm
One potential difference is that the tables in the From clause do not include their schema names. Thus, if his login has a different credential than yours, it might be a problem with implicit schemas. That can cause all kinds of weird behavior, including odd performance issues.
Can you fix the query so it uses two-part names for the objects in the From clause? Like "dbo.TableName" instead of just "TableName".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2012 at 9:46 pm
Thanks, I'm adding the schema name (dbo) and will get back to you with results.
I'm starting to think this is an execution plan issue such as parameter sniffing.
As a beset practice, do you always include schema names for all queries even if the customer only uses the default dbo?
Thanks in advance for your valued feedback,
Andre Ranieri
August 27, 2012 at 10:36 pm
If you suspect parameter sniffing, add OPTION (RECOMPILE) to the end of the query. That causes the query to be "recompiled" every time it executes. I don't recommend that you leave it that way, but if the extreme variation in performance goes away when you add OPTION (RECOMPILE), that would tend to confirm that the problem is parameter sniffing.
Also, if you execute the same query with different ANSI SET options active, then SQL retains a separate query plan for each distinct set of ANSI options. This is a common source of problems where "It runs fast in SSMS, but slow in the application".
In fact, given the behavior you have described, I think it likely that something of this sort is happening. You are running your test queries in SSMS, but the user with the problems is using an ADO.NET or ASP.NET application, which has different default ANSI options.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply