Solved

SQL Query runs slow for specific user, fast for another user.

Posted on 2012-04-05
9
906 Views
Last Modified: 2012-07-05
Our company sells a product that uses a MS SQL Server backend. Our product contains a tool that runs a query to extract billing information. Two users run this tool remotely on a terminal server. One user can run the tool and the results are returned in less than a minute. When the other user runs the same tool the query can take up to five minutes. Testing with the same query criteria by either user's account with sequential non-concurrent logins yields the same delay for this one specific user. The only difference in their account profiles is that the account that gets a fast response is a member of domain admins and the account that gets a slow response is a standard domain user. This is for one particular client running SQL Server 2008 R2 which is hosted on a dedicated server. The demand load on the SQL server is low to moderate the majority of the time. Testing with both accounts using RDP from the same computer or different computers at different locations produces the same results. I suspect the problem is with the SQL backend and possibly involves authentication. The SQL Server is set to use Windows Authentication and connection is via TCP/IP.
0
Comment
Question by:jtforde518
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 37814786
How many rows are returned in each case?
Are the two users logging on to the same terminal?
0
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 total points
ID: 37814799
Lots of questions....

- Are both users running the exact same query?
- What are the differences?  (Account number perhaps?)
- Do both queries generate the same explain plan?
- How many rows are returned for each query?
- Can the timings that you're seeing be repeated from other locations?  (Can you run both queries from your desktop?)
- Are the tables properly indexed?
- Are the index nodes balanced?  (Has there been a lot of insert/delete activity since the index was created?)
- How long have the two accounts existed on the system?
- How many rows in the "big" table are there for each account?
- Are any of the tables partitioned?

Let's start here and move forward...  :)


Kent
0
 

Author Comment

by:jtforde518
ID: 37834078
both users are using the same custom tool which runs the query. The same person is running the exact same query with the same criteria. The same data is returned. The problem is the time it takes "User B" to run the tool is almost 5 minutes while "User A" runs it and it completes in less than a minute. This occurs regardless of what computer the person tests from.
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 45

Expert Comment

by:Kent Olsen
ID: 37834142
Would still like some more detail on your database, and explain plans from both users.    :)



Do either of these two users own any/all of the objects being queried?  Are views being used over the data?

What I'm looking for is a condition where there is a table that is owned by user A and a view with the same name as User A's table that is used by user B.


user A owns the table masterdata

user B could be accessing *UserA.masterdata* via a view

CREATE VIEW Userb.masterdata AS
SELECT * from Usera.masterdata where name = 'ME';


  SELECT * FROM masterdata WHERE date < current_date - 100;

When UserA runs the query above, the table is accessed directly, using any appropriate index.

When userB runs the query, the index is used to create the subset of rows that is the result of the view.  The result is then scanned via a full table scan to return the rows that have the correct date.  If UserB has a lot of rows, this takes a lot of time.


Kent
0
 

Author Comment

by:jtforde518
ID: 37834816
User A is dbo, User B is normal user with all permissions public.
Same query every time with one user doing the testing logging in with both accounts.
Explain data does not change. Query does not change.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37835491
Please post the Execution plan in each case.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 37835510
I've got about 10 questions just a few posts up.  Can you answer them?  They really could be relevant.

Thanks,
Kent
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37835864
And I have just got 2 and I still have not got a clear answer.  :)
0
 

Author Closing Comment

by:jtforde518
ID: 38158081
Thanks for the help. I appreciate all the responses and I'm splitting the points between you. The problem was rendered moot by the implementation of a newer version of a C# .NET custom query reporting tool. The suggestions and the questions helped us pinpoint issues in our application and we modified both the backend and the query tool to correct for the problems we were having.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question