Transcript Deliver, Manage and Control Optimal Database Performance

Common Mistakes Developers Make
By Bryan Oliver
SQL Server Mentor at SolidQ
Most Common Mistake Made in Developing
SQL Server Databases
• This list is based upon experience over the last 10
plus years as both a DBA and a Developer
• It is a list that is meant to cause discussion and I will
always allow a person the right to disagree with me.
1
Database Configuration
• Not enough File Groups for disk activity
• Normalization of Database
• Poor indexing of Tables
•
• Foreign key relationships
• Lack of naming standards .
2
Database Configuration Cont.
• Choice of data types
• No Baseline or Benchmarks of each Database
• File Growth set to Auto and 10% or some inadequate
size
• Temp DB not Optimized for CPU’s
• Running SQL Server in mixed-authentication mode
with a NULL password for the systems administrator
(sa) account
T SQL Development
• Use of Adhoc SQL
• Not defining owner or schema name when calling a
SQL Object
• Lack of naming standards
• Inadequate Development Testing
• Inadequate Load Testing
4
T-SQL Development Cont.
• Use of Cursors
• Locking Issues
• Overuse of Temp Tables
• Query Plan examining
• Making Databases Case Sensitive
T SQL Development Cont.
• No or Poorly Commented Code
• Adding a Column in Production
• Defining Rows that Exceed The Max Length
• Spaces in Object Names
• Not Having An Archive Plan For Each Table
6
T-SQL Development Cont.
• Sorting by Ordinal Select field1, field2, field3 from pubs order by 1, 2
• Bad Connection Strings and Bad Info in
Sysprocesses
• Not Using Primary Keys and Clustered Indexes
• Objects Not Owned by DBO
• Using Hungarian Notation for Column Names
Performance Tuning worst practices
• System performance not a component of
requirements analysis
• No dedicated development and test environments
• No load testing
• No SQL Server maintenance
• No managerial support for high performance
• No performance monitoring and management tool
8
Ten Things a SQL Server DBA Should Never Do
• Rebuilding an index in daylight hours - this will hit disk I/O
very heavily. It is rarely useful to do this during normal working
hours, so always schedule it for the evening or overnight - that
is, during the period of lowest user activity.
• Stopping the database engine without warning - why? Lots
of frustrated users and a telephone (yours) that won't stop
ringing.
• Performing a service pack upgrade during working hours usually this involves re-starting the core database engine. Don't
do it, it'll annoy many people.
•
9
Ten Things a SQL Server DBA Should Never Do
• Running test queries against live servers - do you really
know how long they'll run for or how much disk I/O they will
demand? I thought not!
• Defragmenting the drive on which the database files sit have you ever done this to your home PC? Then you'll know
why you shouldn't do it to a live box in working hours. No, no,
no.
• Being arrogant towards developers - why? A few can be a
complete pain, but explaining to them the issues and trying to
work towards a good working compromise is usually more
productive than treating them unsympathetically. Likewise the
support guys - you need each other. Foster good relationships
with your work colleagues, in the long term it pays dividends. .
10
Ten Things a SQL Server DBA Should Never Do
• Backing up during working hours - it's all about disk I/O. It
serves the backup or it serves your users. If you have to do it,
look at differentials or transaction log backups: they take less
time and reduce dropped connections as a result.
• Executing updates against live data - you are kidding, right?
At the very least, write a script, test it against a live copy, and
backup the live database before you apply it. And if possible
have a regression script which will allow you to back out the
updates if reverting to a backup is not possible
• Performing vendor upgrades without testing or backing up
- sad as it seems, vendors don't always test their upgrades
thoroughly. Nor can they practically test against every hardware
and software configuration available out there.. So always back
up before running a patch. And if you can apply it to a test or
development server first, do it.
11
Ten Things a SQL Server DBA Should Never Do
• Not securing your database servers - run the Microsoft
Baseline Security Analyzer against your servers to find out your
vulnerabilities and get clued up on security.
• Dropping a live database and the effect on your career - I
once worked with someone who did this. He got sacked on the
spot, and rightly so. If you only engage your brain to do one
thing as a DBA, make sure it kicks off the alarm bells whenever
you issue a DROP command
12
Find out more
• Check these out:
– www.sqlbloke.com
– www.SQL-Server-Performance.Com
– www.SQLServerCentral.com
13
Q&A
• Send questions to me at: [email protected]
• Information about SolidQ at: www.solidq.com
THANK YOU!
14