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 12»»

Same query, two users, different performance Expand / Collapse
Author
Message
Posted Wednesday, September 04, 2013 1:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:38 AM
Points: 110, Visits: 761
Hey Gurus,
I have a query that performs differently depending upon the user executing it. This was first brought to my attention because an Excel spreadsheet was taking a long time to load data (Excel 2010, Sql Server 2008 R2). The query was awful, I rewrote it and got better performance. But when the user tried running it from Excel, still had bad performance. After a bit of head scratching I saw that the connection from Excel had a username/password specified, whereas when I ran it from SSMS I was logging in using my username via windows integrated security (and I am dbo). So, I tried a few things:

1. In two windows in the same SSMS, run the query simultaneously. One as me, the other using "Execute as Login =".
2. Start up two instances of SSMS. Connect in one as me, in the other as the specified login.

In any case, when I run the query as me, it takes about 7 1/2 minutes. When I run as the specified user, it takes 12+ minutes (today's measurements. Other days when I run as me I can have it run in as little as 2 minutes, but these times have been consistent today).

In any case, I am always executing the exact same statement (SELECT * FROM a table valued function) with the same parameters. The only difference is "who" is logged in.

Why would two different user logins have different performance profiles? What should I be looking at to track this down?

Thanks in advance.

Tom
Post #1491490
Posted Wednesday, September 04, 2013 1:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
Start with execution plans. Are they the same?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1491494
Posted Wednesday, September 04, 2013 1:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:38 AM
Points: 110, Visits: 761
Sean Lange (9/4/2013)
Start with execution plans. Are they the same?


Unfortunately, the named user does not have permission for SHOWPLAN. I won't be able to change that until next week. Is there any reason to suspect they would be different plans?
Post #1491500
Posted Wednesday, September 04, 2013 1:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
Tom Bakerman (9/4/2013)
Sean Lange (9/4/2013)
Start with execution plans. Are they the same?


Unfortunately, the named user does not have permission for SHOWPLAN. I won't be able to change that until next week. Is there any reason to suspect they would be different plans?


In theory they should get the same plan but in reality if everything else is the same (query, parameters) then you have to find what is different. Thinking that would be a good place to start. Maybe somebody else has dealt with this issue but I don't remember hearing of it before.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1491513
Posted Wednesday, September 04, 2013 1:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 3,324, Visits: 2,381
Are the returned data sets the same no matter who runs the query? If you're selecting everything from a TVF, what is that function doing? Does it depend on the user who's running it to determine what data to bring back? If so, it might be returning more data for one user over another. If the username is used in a WHERE clause somewhere, it could be using a different index or even a full table scan because there's no covering index.

I know I might be reaching here, but something has to be different for that kind of a performance difference.



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1491517
Posted Wednesday, September 04, 2013 1:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:38 AM
Points: 110, Visits: 761
Ed Wagner (9/4/2013)
Are the returned data sets the same no matter who runs the query? If you're selecting everything from a TVF, what is that function doing? Does it depend on the user who's running it to determine what data to bring back? If so, it might be returning more data for one user over another. If the username is used in a WHERE clause somewhere, it could be using a different index or even a full table scan because there's no covering index.

I know I might be reaching here, but something has to be different for that kind of a performance difference.


Had not considered that, but unfortunately the result sets are identical. I'll dig into the query more (layers upon layers of views and TVFs) to see if a username is specified anywhere.

I only started at this company 2 weeks ago, so still trying to get the lay of the land.
Post #1491525
Posted Wednesday, September 04, 2013 3:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:30 PM
Points: 1,757, Visits: 2,577
Are schema names specified on the tables in the query? If not, what are the default schemas of the two users running the queries?

SQL DBA,SQL Server MVP('07, '08, '09)
I'm not fat, I'm gravity challenged.
Post #1491549
Posted Thursday, September 05, 2013 4:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 14,840, Visits: 27,315
It doesn't matter if that login doesn't have permission to show execution plans. You can query the dynamic management objects (DMO) to get the plans from cache.

But, I'll bet it has something to do with one of the table valued functions. Those things are notorious for destroying performance.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1491704
Posted Thursday, September 05, 2013 7:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:38 AM
Points: 110, Visits: 761
Grant, thanks for reminding me. The query plans are the same. The only differences between the two plan XMLs are:

<QueryPlan CachedPlanSize="1352" CompileTime="12244" CompileCPU="12238" CompileMemory="111952">
<QueryPlan CachedPlanSize="1352" CompileTime="12387" CompileCPU="12364" CompileMemory="111952">

Yes, I'm sure that the overall poor performance is due to the table valued functions (and part of the reason I was hired here is to help with a rewrite), but that wouldn't explain why the performance is so remarkably different between two different users would it?

Scott, the default schema is dbo, but there are multiple schemas involved, so generally the schema name is specified. Actually, there are multiple databases involved as well (3). I've got a query executed in one database referencing views and functions that reference views/functions/tables in other databases. It's a mess. At least they are all on one server.
Post #1491768
Posted Thursday, September 05, 2013 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 14,840, Visits: 27,315
Without knowing what's happening inside those UDF's, it's hard to say whether or not it would affect performance. I'm willing to bet money that there's some type of "security" check against data or something in there that is slower for one user than for another. Or, within the views.

Nesting views and UDFs... I really should become a consultant. I can make millions doing nothing but unwinding nested views and nested UDFs.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1491787
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse