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 Thursday, September 05, 2013 7:42 AM
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 @ 5:11 AM
Points: 3,308, Visits: 2,352
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.




Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1491790
Posted Thursday, September 05, 2013 7:42 AM
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 @ 5:11 AM
Points: 3,308, Visits: 2,352
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.



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1491792
Posted Friday, September 06, 2013 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:52 PM
Points: 27, Visits: 89
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?
Post #1492215
Posted Friday, September 13, 2013 9:03 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
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.
Post #1494611
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse