February 4, 2011 at 11:45 am
Here's where I'm coming from:
1. Developer writes code to meet some business requirements.
2. Code (supposedly) gets peer reviewed.
3. Code gets promoted to beta environment.
4. Code gets tested to insure it meets business requirements.
5. Code gets promoted to prod environment.
6. One week later people are standing at my desk with pitchforks and torches because "people can't do their work" and/or "clients are threatening to cancel".
At the end of the day, I'm on the hook for my stuff in addition to cleaning up someone else's code. So I've decided to document some basic performance expectations, distribute them and then enforce away. Was just curious to see if anybody else has already done this, how they've implemented it and whether it's worth the hassle I envision it being going forward.
Thanks,
_____________________________________________________________________
- Nate
February 4, 2011 at 11:55 am
Multiple simultaneous recursion testing with timers in UAT environments. Really the only way. You can't test for load interference on an unloaded system. You can guess, but you can't *know*. Not without thorough research.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 11:55 am
Honestly, there are two ways to deal with this.
First, is review all database code yourself and make sure it's properly tuned, before it goes into production.
Second, is monitor the database performance at a code level and fix things before anybody else notices they've gone wrong.
You can (should) do both.
Confio Ignite is really helpful for the second.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 1:29 pm
Thanks for the responses.
GSquared (2/4/2011)
First, is review all database code yourself and make sure it's properly tuned, before it goes into production.
Right call, but tough to implement. Where there used to be 2 prod support DBAs, 2 dedicated DB developers and a handful of part-time developers there's just me and the part-time developers.
GSquared (2/4/2011)
Second, is monitor the database performance at a code level and fix things before anybody else notices they've gone wrong.
You can (should) do both.
Confio Ignite is really helpful for the second.
Currently researching a couple 3rd party solutions, will add Ignite to the candidate list. In the past, I've had to rely on random trace data to identify performance bottlenecks.
_____________________________________________________________________
- Nate
February 9, 2011 at 5:32 am
It should be EASIER to control what goes on with FEWER people stirring the pot! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 9, 2011 at 7:34 am
One thing that may help (in the long run) is to come up with a DOs and DON'Ts list (Database standards). If you're cleaning up the code, you know the most common mistakes and how to fix them. You list them in that document, give them and their boss the document (get boss's buy in first and it'll be a lot easier), then enforce those standards with code reviews.
If you can get boss buy-in, making compliance with standards part of their yearly review goals is a sure-fire way of getting them to pay attention.
February 9, 2011 at 10:44 am
Hi,
Also use DMV, if you are using SQL Server 2005/2008, search for "find most expensive query using DMV", "performance DMV" or "bottleneck dmv" in google.
After a deploy and performing the tests check if anything popups.
Using the DMV you also can find lots of other information that can help you troubleshooting other performance problems, like indexes that are not being used.
Start by checking this links:
February 11, 2011 at 7:20 am
Brandie Tarvin (2/9/2011)
One thing that may help (in the long run) is to come up with a DOs and DON'Ts list (Database standards). If you're cleaning up the code, you know the most common mistakes and how to fix them. You list them in that document, give them and their boss the document (get boss's buy in first and it'll be a lot easier), then enforce those standards with code reviews.
About a year ago I came up with a best practices doc and the devs do somewhat adhere to them. The biggest hurdle is getting them to realize that if they're modifying legacy code to take the extra time to bring the object up to spec and not just the couple lines they're changing.
VPombeiro (2/9/2011)
Also use DMV, if you are using SQL Server 2005/2008, search for "find most expensive query using DMV", "performance DMV" or "bottleneck dmv" in google.
We're mid upgrade from 2000 to 2005 so the dynamic mgmt views aren't available to me right now. Our network engineer who was doing the upgrade quit mid-stream so we got waylaid until the new guy got in, caught up and had cycles to throw at it.
I appreciate the responses and will take them into account as I try to streamline the process.
_____________________________________________________________________
- Nate
February 12, 2011 at 10:22 am
I would recommend to use stored procedures, they can be modified without modifying the code, it's easier to change them getting the same results but modifying them in a way that get better performance.
February 14, 2011 at 4:52 am
Aristides Rapozo (2/12/2011)
I would recommend to use stored procedures, they can be modified without modifying the code
I'm assuming by code, you're referring to client-based code ala VB.Net or something?
Because, technically (at least in my environment) even T-SQL is considered code. Therefore, when a stored procedure is altered, it's still considered a code change for the purposes of the SDLC & SOX compliance.
February 14, 2011 at 7:11 am
Brandie Tarvin (2/14/2011)
Because, technically (at least in my environment) even T-SQL is considered code.
Yep, same here. Most of the heavy lifting is in stored procs (i.e. T-SQL) and is definitely considered code.
_____________________________________________________________________
- Nate
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply