SQL 2008 R2 'wall of weird' submission

  • 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

  • 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.

  • 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

  • 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'

  • Here's the connection string used globally by all CRM users:

    Application Name=*****;Data Source=******;integrated security=SSPI;Database=******;Connection Timeout=0

  • 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]

  • 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

  • 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

  • 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