September 4, 2013 at 1:14 pm
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
September 4, 2013 at 1:18 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2013 at 1:25 pm
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?
September 4, 2013 at 1:37 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2013 at 1:46 pm
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.
September 4, 2013 at 1:58 pm
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.
September 4, 2013 at 3:17 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
September 5, 2013 at 4:23 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2013 at 7:14 am
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.
September 5, 2013 at 7:32 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2013 at 7:42 am
Tom - I've seen so many problems caused by with views on views on views in the past that I've denounced them as heretical and will never write them myself. You definitely have your work cut out for you. You're going to have to examine the performance of each view and underlying table. You're going to have to look at every UDF and figure out what they do and how. Performance improvement opportunities will present themselves.
I'm offering this piece of advice because it sounds like a real mess, I don't know what environments they have for you to play around with and you're new to your job. I don't do any changes like this on the production database until it's thoroughly tested for both results and performance. I would follow the same advice here. I don't want your first month at your new job to be your last because you brought down a production server.
September 5, 2013 at 7:42 am
Grant Fritchey (9/5/2013)
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.
Grant - Write more books; you're good at it. 😉
September 6, 2013 at 7:14 am
Have you watched the I/O of the server while the queries are running? I know that, for example, we're limited to 600Mb/s for or developers, but the backups user can top out at 700Mb/s or more. Perhaps there's an artificial cap of that kind on the user where dbo doesn't have one?
September 13, 2013 at 9:03 am
Thanks so far.
Ed: I agree with you 1000% about the layering of views. Got truly spaghetti sql to deal with here. I'm working on convincing the powers that be that we should create a database with the "answers" pre-populated. First step towards a data warehouse.
Grant: Please continue writing books!
Haven't yet found any evidence of filtering based on username, but I'm still digging.
sqlslacker: No resource governing implemented on any resources or any users.
June 2, 2018 at 8:27 am
Hello,
I have the same problem. In our company about 20 users every day runs the query (select * from MyQuery). Query has complex where conditions. For all users it takes about 10 sec, but for one of them it takes 25 min. And it happening the same on different computers and only for one user.
I tried a lot of things:
1. I have explored execution plans - they are identical
2. I have recreated user on SQL server
3. I have recreated that user in Active Directory and then on SQL server
4. I have rewritten that query and got better performance. For all users it works perfect except that one user.
Nothing helps.
I created new user in the same way as i did it recreating then problematical user. For new user this query gets result in 10 sec.
It seems like SQL server somewhere saved some information about something for that user name and always uses it... I have no idea, what kind of information and where it did it.
Maybe you have some ideas?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply