The perfect database

Question

The perfect database

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 1

It's a pretty good list... that's probably why the forum code listed it 3 times. 😛

Dev...make sure your developer have a list of best practices that won't kill your performance.
Avoid cursors, functions in the where clause, minimal output, etc, etc, etc.
There are easy lists to give them.
Make sure they know they are playing with physics not just logic.
Oh, and also make sure they are creating their objects to certain standards
(naming, structure, etc...make them put a darn "create by" "modify by" and datatime columns on all tables)

Although that should absolutely be done, the fly in the ointment is that you have to do a code review of all their code. A companion fly with teeth that lurks just below the surface of the ointment where you can't see it is in the form of embedded SQL. THAT will be your biggest challenge because (apoligies to those who can do both well) 1) most GUI programmers just aren't going to be as concerned with your database as you are and they're also not going to be as good at it as you might like. Further, 2) it might not even be the GUI programmers that are writing the embedded code... it might be N-Hibernate (C) or Hibernate (Java). I just found a nasty bid of code written by N-Hibernate today... it queries 32 (some are repeats in the same query) relatively small tables to return 13 rows and takes anywhere from 25 to 55 seconds to do so. In the process, two CPU's are consumed by 60% each for a single run and to produce the required report, it takes 12 runs of the same proc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Good List by Michael. Excellent point by Jeff concerning the code review.

Review, review, and review some more the developer code.

I would like to throw in to the mix a piece of my pie in the sky server - hardware-wise. I like the ability of creating multiple filegroups with multiple files in each. When doing this, I evaluate the usage of the tables involved, check indexes, determine disk i/o (sys.dm_io_virtual_file_stats ), and place the necessary tables with their indexes into filegroups that have multiple files split across multiple san shelves (double parity RAID).

And of course we make sure that the cluster block size on the HD is 64k (but test first and then size it properly for the specific database server), and make sure the disk partitions are aligned.

http://msdn.microsoft.com/en-us/library/dd758814.aspx

http://blogs.msdn.com/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Michael Robison SSC Journeyman Points: 99 More actions · Answer 3

Thanks for the feedback, its appreciated.

I think in a perfect database as far as code review goes,

I'd like to do a few things...

One have a real-time perfomance monitor/utility for all levels...OS/HW...DB and front-end...

get a heart-beat of several factors (not just CPU, I/O, locks, waits, recompiles, paging, etc, etc, etc)...

get graphs too...this will help you visualize better, and catch patterns you might not notice.

Setup a baseline...actually a few, for different scenarios, levels, usage, etc...snapshot these. (just so you know you're not going insane when you see something off or strange)

Give them a set of base "percentages" of what is actually impacting the system. (ex...20% db, 20% OS, 60% front-end...overall)

And you can also give a periodic Object Execution Statistics report straight from MSSQL 2005 and do it periodically,

or even better, show their lead how to do it themselves. (if you want to give them the privs, of course)

Give a list of "best practices" and "tips" to the devs...make it clear and simple...not TOO ideal...or they will ignore it.

Get them allow you to give a short meeting/training...something like "this is my world" or "this is what I see" or even better "let me make you shine even more"

I send sites of interest...one for FYI...the other to show I'm not just blowing smoke and that it does matter.

Sometimes its the same subject, just a different perspect, clearer, a better tool or such

I actually keep these to easily send to others, or keep them in a reference section.

(don't assume people know or remember...just curiousity or refreshers helps make a better memory or design)

See if they can actually remember to do peer-reviews, and incorporate some of the basics into it.

Hopefully with a bit of proactive effort, this will prevent as much actual code-review later.

Actually even more Hopefully, you have enough authority/influence...or what I like to call, "oomph"

for when you do spot something that raises your eyebrows, you can just say...hey can you adjust this??

I know that this is often not in your job description, but personally I find it better than having blame the darn db,

or you getting stuck holding the bag fixing it...usually during the worst time.

Of course, this is the "perfect" database...in a perfect world too.

Michael Robison SSC Journeyman Points: 99 More actions · Answer 4

Oh...and one more thing...in a perfect environment with your perfect db...

see if they can setup a centralized best practices/tips/definitions/history/perspective area for the big app...

like a wiki for it.

The CIA is doing a great thing having one for all their projects, and sharing info in a live environment,

across depts., staff changes, etc...of course they have a "blackout" function which adjust for your security level.

But its amazing to have that so you know things ahead of time or not to recreate the wheel

or to know about issues that have reappeared, perhaps a solution already there, etc.

And yes, everyone shares editing it...of course with a brave monitor to make sure there are no hiccups.

jdurandt SSChasing Mays Points: 628 More actions · Answer 5

This is a very interesting topic of discussion, especially around how server sizing has changed to development practice 😉

Of course the point is very true - what you are running is much more important than what you are running it on in the vast majority of cases.

I have though experienced scenarios where the application was tuned as much as was feasible, and server hardware had to be upgraded to deal with the load. In that instance it was definitely disk, disk and more disk that did the trick.

As a development team leader, I try to avoid the term "code review", as well as the practice of looking over everyone else's shoulder all of the time. What works much better is to institute a "peer review" process, where any work done by any person must be eyeballed by at least one other person before being promoted to a formal testing cycle.

You would be amazed by how many comebacks are avoided by instituting this simple practice.