Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008 R2 'wall of weird' submission Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 11:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 291, Visits: 316
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

Post #1349846
Posted Friday, August 24, 2012 11:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1349848
Posted Monday, August 27, 2012 11:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 291, Visits: 316
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
Post #1350492
Posted Monday, August 27, 2012 11:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 291, Visits: 316
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'
Post #1350493
Posted Monday, August 27, 2012 11:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 291, Visits: 316
Here's the connection string used globally by all CRM users:

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

Post #1350499
Posted Monday, August 27, 2012 11:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:19 PM
Points: 9,294, Visits: 9,491
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1350508
Posted Monday, August 27, 2012 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1350514
Posted Monday, August 27, 2012 9:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 291, Visits: 316
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
Post #1350680
Posted Monday, August 27, 2012 10:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:32 PM
Points: 219, Visits: 789
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.
Post #1350684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse