Transcript Deliver, Manage and Control Optimal Database Performance

The 101 things not to do on SQL Server
By Bryan Oliver
SQL Server Domain Expert
Copyright © 2006 Quest Software
101 Not Yet but Getting There
• I don’t have a 101 items in my list yet but the number
is closing fast.
• 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
Applications
•
•
•
•
•
Exchange
Small Business Server
Oracle
Backup Exec that installs SQLXpress
The list goes on and on and on ..
2
Monitoring Software
• Antivirus
• Performance Monitoring
3
Drivers
• Third Party Device Drivers
• Disk Drivers
• CPU Accelerators
4
Memory
•
•
•
•
SQL Server Configuration
AWE
PAE
Virtual Memory
5
Disk Drives
•
•
•
•
•
Size
Raid configuration
Non Raid 1 System Drive
Inadequate Cooling
Inadequate fault tolerance
6
Network
•
•
•
•
Configuration
Response Time
DNS Issues
DMZ Protection
7
Database Configuration
•
•
•
•
•
•
•
•
•
•
Not enough File Groups for disk activity
Normalization of Database
Poor indexing of Tables
Foreign key relationships
Lack of naming standards
Choice of datatypes
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.
8
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
• Use of Cursours
• Locking Issues
• Overuse of Temp Tables
• Query Plan examining
• Making Databases Case Sensitive
9
T SQL Development
•
•
•
•
•
•
•
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
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
10
DBA Pratices
•
•
•
•
•
•
•
•
•
•
•
Failing to Test Backups
Backing up to same Disk
Not monitoring File Growth
Index Defragmentation
Hard Disk Defragmentation
SQL Statistics
Failing to use Perfmon to collect performance metrics
Throwing more hardware at a problem
Reliance upon end users to notify you of problems
Allowing applications to connect using sa.
Making production changes without testing them in a qualityassurance environment just because it's easier and saves time.
11
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
•
12
SQL Agent & Services
• User Account
• Important scheduled jobs fail without intervention
13
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.
•
14
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. .
15
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.
16
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
17
Find out more
• Check these out:
–
–
–
–
www.quest.com
www.SQL-Server-Performance.Com
www.SQLServerCentral.com
http://www.microsoft.com/sql/2008/default.mspx
18
Q&A
• Send questions to me at: [email protected]
• Send broader technical questions to: [email protected]
• For sales questions, go to: www.quest.com
THANK YOU!
19