| Author |
 Topic  |
|
|
Curt
Moderator
USA
6652 Posts
Status: offline |
 Posted - 07/12/2012 : 11:21:40 PM
|
Talking to Michael here about someo of the really bad things you can do by mistake when you take over a SQL server.
I figured I would start out with a few I know about and let the more advanced guys chime in. Let's just not run out of disk space.
1. Don't truncate a log file without backing it up.
2. It should be an extreme case to have to shrink a log file. Like the drive is filling up and you will crash if it grows, so don't shrink them every night.
3: Don't shrink your database to get disk space back every night. If you remove a great amount of data, then perhaps it's reasonable to do some but the database needs some head room as well.
4: Do not consider the tempdb of no consequence and leave it on the system partition or slow disk. It's one of the most important databases in the system.
5: Don't delete your current full backup while your creating a new one.
6: Don't Store the backups on the data drive.
7: Don't expect a DPM backup to commit data from the Transaction logs to disk.
Well, that should get us started. I await the rest of my SQL guys here to chime in.
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
JamesNT
Moderator
USA
3150 Posts
Status: offline |
Posted - 07/12/2012 : 11:50:53 PM
|
1. Don't place SQL Server on a dynamic virtual disk.
2. Do NOT create a database with a NEXTID table. I will personally break both of your arms if you do.
3. If you need to clear a table really fast, use TRUNCATE. Not DELETE.
4. Do Not place multiple values in one field of a table. Each value should go into its own field and fields should be atomic. If you don't want NULL values, use Boyce-Codd Normal Form to normalize out to a vertical table.
5. Nested Select statements are nice, but be aware of their cost.
6. Beware the differences between IN and EXISTS.
7. Never be afraid to tell a vendor that you think their database sucks. It probably does.
JamesNT |
James Summerlin
www.jamessummerlin.com |
 |
|
|
sthein5
Seasoned But Casual Onlooker
USA
65 Posts
Status: offline |
Posted - 07/13/2012 : 10:49:25 AM
|
1. When coding, think of the big picture. You don't have to put everything into a function. It's ok to have to have the same code in several procs. (e.g. the function ends up in the WHERE clause)
2. Table variables are nice but use them sparingly.
3. Code with a result-set mentality.
4. When virtualizing SQL Server, do you due diligence and run your IO tests.
5. Know the difference between ISNULL and COALESCE. You can see some weird data type issues.
6. When you setup SQL Server, set the maximum server memory setting. A good general rule is to allow SQL to have 80% of the available memory.
7. Maintenance plans are a good, quick way to get a backup startegy in place for your server. You don't have to have super, ninja, next-level code for you backups (unless you really want that )
8. Bribe your server guys with lunch every once in a while...They can be your best friends for getting things done quickly. This includes your VM guys too. |
Stacy Hein
Where the press is free and all men can read, all is safe. - Thomas Jefferson |
|
|
|
JamesNT
Moderator
USA
3150 Posts
Status: offline |
Posted - 07/13/2012 : 12:35:21 PM
|
Let me clarify my point 7 as it looks ambiguous. When I say the vendor's database, I'm not talking about Microsoft SQL Server or mySQL or whatever - the engine itself. I'm talking about the actual database the vendor built using SQL Server that their product stores its data in. The way the vendor normalized their tables, did triggers, that sort of thing. That's what sucks.
JamesNT |
James Summerlin
www.jamessummerlin.com |
Edited by - JamesNT on 07/13/2012 12:35:36 PM |
|
|
|
Wiseman82
Old Timer
United Kingdom
520 Posts
Status: offline |
Posted - 07/14/2012 : 10:16:53 AM
|
* Don't give developers access to the production system or use your production system as a DEV/QA environment.
* Don't use a sysadmin or DBO account for your application - grant only the minimum required permissions.
* Don't use dynamic SQL with concatenated user input. Write sql injection safe dynamic SQL using parameters.
* Don't use inappropriate data types.
* Don't forget to implement a backup/recovery plan and daily/weekly/monthly checks routines.
* Don't embed SQL code in the application.
* Don't use full recovery model without scheduled transaction log backups
* Don't use SELECT * FROM. Always specify a column list.
* Don't shrink your database files. (At least not on a regular basis)
* Don't autogrow by tiny increments and don't rely on autogrow in production environments. Leave room in your database files and monitor this space.
* Don't forget to monitor database/application performance counters.
* Don't use SQL Profiler against a production database server. Use a server side trace or extended events and be careful what you capture and for how long.
* Don't use database tuning advisor against a production database.
* Don't schedule maintenance activities to occur during busy periods.
* Don't believe everything you read. Try things out for yourself.
* Don't write scalar value functions that include data access and use these in your select statement. These functions are executed once per row which is usually very bad for performance. Also avoid other RBAR (Row By Agonizing Row) coding practices (cursors, loops etc) and learn to think in sets.
* Don't write multi-statement user defined functions if you can implement an inline user defined function instead.
* Don't forget to format your SQL code for readability and include plenty of comments.
* Don't use 1/3/4 part names. Ideally objects should be referenced using 2 part names (e.g. schema.object). If you need to reference objects in other DBs, you might want to consider using synonyms.
* Don't stop learning. There is always more to learn. |
David Wiseman
MCSE (2000/2003), MCSA (2003), MCDBA, MCITP:Database Administrator
 |
|
|
|
Mark Minasi
Chief cook and bottle washer
USA
10658 Posts
Status: offline |
Posted - 07/16/2012 : 10:20:02 PM
|
| I am NEVER letting you guys see my SQL code.<g> |
Mark
tweetin' at mminasi |
|
|
|
Curt
Moderator
USA
6652 Posts
Status: offline |
Posted - 07/17/2012 : 12:02:53 AM
|
I saw a "select *" in a trace of a inhouse developed application at a client last night.
I was surprised.
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
|
JamesNT
Moderator
USA
3150 Posts
Status: offline |
Posted - 07/18/2012 : 12:08:18 AM
|
Mark,
I would LOVE to see your SQL code. There must be so many opportunities in there to pay back all the wonderful things you have done for me! <grin>
JamesNT |
James Summerlin
www.jamessummerlin.com |
|
|
| |
Topic |
|
SQL DON'Ts
