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

TSQL by Duration (Response Time) Expand / Collapse
Author
Message
Posted Thursday, November 1, 2007 10:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
1) Appropriate testing covers the "don't fix something that is slow because you may break it".
2) Besides, many performance fixes are either adding/altering indexes, defragmentation of same, updating statistics or rewriting a query to removed unnecessary temp tables, cursors, etc. Only the latter really carries any significant risk, and see one for risk management.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #417559
Posted Thursday, November 1, 2007 10:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, Visits: 683
TheSQLGuru (11/1/2007)
2) Besides, many performance fixes are either adding/altering indexes, defragmentation of same, updating statistics or rewriting a query to removed unnecessary temp tables, cursors, etc. Only the latter really carries any significant risk, and see one for risk management.


Adding/Altering indexes has significant risk.
Adding an index WILL increase the time involved for inserts. It WILL impact the locks that need applied. It COULD increase time involved for updates (+ or -).

Altering an index can also have a significant impact. For example: Say you change the order of fields in an index only to find out that Quarter-end reports that you have never seen run in your testing needed that index as written.

Another hazard of alteringing an index especially if it is a clustered index. The order of results will change if an order by is not in the sql. This could be considered an application issue, but it will still be caused by your change.

Updating statistics is a common thing, and is generally considered safe... However, there are situations when you DON'T update statistics. This of course depends on the system, but this is a BIG NO-NO in older versions of Oracle (8-9i) since they didn't do automatic statistics updates. In general, I will agree this is a safe operation, but don't be fooled into thinking this is not changing the system which can lead to unexpected results (bug).

Don't misunderstand me. I agree that a WELL performing system is a goal, and that you should strive to never hear from the user/customer. That can be prevented with monitoring and such. Good instrumentation can be used to justify a performance only change. However in general, you will find that if you propose such a change to management unless you are talking about an insane gain in performance, the answer to changing the system will be "Not at this time".
Post #417572
Posted Thursday, November 1, 2007 10:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 4,404, Visits: 6,264
Agreed on all Bob. I should have been more specific about my low-risk index stuff. I consider that to be nothing more than adding/including a column for example. Usually the biggest issue is the time it takes to actually do the alter. It may need to be performed during scheduled maint period. Adding a new single index that allows a frequently used query avoid a table scan has never in my experience led to such an increase in maintenance work that it wasn't still a big win. It is also my experience that the work to maintain a single new index and the locks involved are very, very low overhead compared to system total. Again, see my point 1. :)

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #417578
Posted Thursday, November 1, 2007 11:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, Visits: 683
I've been at both ends of the spectrum on this. I used to work for a company where any, and I mean ANY, change was required authorisation and no less than 10 days notification before it was made. The only exception to this was during a crisis situation, and then a post change request had to be made.

Now where I work, the feeling is if you can gain 10 seconds of CPU a day by re-writing a procedure, your given the green light. Only requirement is you at the least have a backout plan and have done sufficient testing. And the word sufficient is basically if more than one person did the testing and they say it is ok, it is sufficient.
Post #417581
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse