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 5, 2013 7:42 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 4,611, Visits: 4,067
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
String Splitting with True Performance
Best practices on how to ask questions
Post #1491790
Posted Thursday, September 5, 2013 7:42 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 4,611, Visits: 4,067
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
String Splitting with True Performance
Best practices on how to ask questions
Post #1491792
Posted Friday, September 6, 2013 7:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 51, Visits: 205
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: 2 days ago @ 7:18 AM
Points: 115, Visits: 826
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