SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 R2 'wall of weird' submission


SQL 2008 R2 'wall of weird' submission

Author
Message
Andre Ranieri
Andre Ranieri
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 379
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93163 Visits: 38955
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.

Cool
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)
Andre Ranieri
Andre Ranieri
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 379
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
Andre Ranieri
Andre Ranieri
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 379
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'
Andre Ranieri
Andre Ranieri
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 379
Here's the connection string used globally by all CRM users:

Application Name=*****;Data Source=******;integrated security=SSPI;Database=******;Connection Timeout=0
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34344 Visits: 9518
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."
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56589 Visits: 9730
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
Andre Ranieri
Andre Ranieri
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 379
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
David Moutray
David Moutray
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 895
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search