May 5, 2015 at 4:39 pm
May 5, 2015 at 5:24 pm
Nice attempt at your first blog post.
Unfortunately, there are a few questionable suggestions in there.
For example, what's the difference between these two queries:
SELECT * FROM Sales.SalesOrderHeader AS soh
LEFT JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID;
SELECT * FROM Sales.SalesOrderDetail AS sod
RIGHT JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID = sod.SalesOrderID;
Do they perform differently? Does the optimizer make different choices? Why should I change everything to LEFT JOIN?
There are some others as well. I'd strongly suggest breaking it down and researching each one. I think you'll be surprised at some of what you learn.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2015 at 6:10 pm
The one that first caught my eye is the recommendation of using BULK INSERT to insert into a view. I never do this. I'll always load into a staging table, do my cleaning and validation and then into a reporting table. I wouldn't use a view. If you want to import data, the underlying table is going to have to have column to receive the new incoming column anyway, so a view is just another object you'd have to change in addition to the table.
Another one is the recommendation to back up to a local disk. I've found that backing up directly to a SAN volume and then having the network team perform their DR and off-site tape backups is usually the best. The throughput on the SAN can also be very impressive.
The Copy Database Wizard is something I haven't really played with much lately. I always use the backup...copy...restore approach and find it to be very reliable.
May 5, 2015 at 9:17 pm
From the blog:[hr]
Joins: When coding outer joins, always order your data sources so that you can write left outer joins. Don’t use right outer joins, and never mix left outer joins and right outer joins.
I'm not sure where such "best practices" are spawned but this one is absolutely incorrect. A mixture of left and right joins frequently adds clarity to a FROM clause.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2015 at 6:03 am
Jeff Moden (5/5/2015)
From the blog:[hr]
Joins: When coding outer joins, always order your data sources so that you can write left outer joins. Don’t use right outer joins, and never mix left outer joins and right outer joins.
I'm not sure where such "best practices" are spawned but this one is absolutely incorrect. A mixture of left and right joins frequently adds clarity to a FROM clause.
Yeah, so many of the so-called "best practices" come about just because someone says it. If enough people say it enough times, people believe it. It has nothing to do with being good or not. After all, it's a best practice, so people accept it blindly without even questioning it, which irks me to my core. :w00t:
May 6, 2015 at 6:28 am
And this is a few days too early for me to reference an editorial on using the scientific method when writing blog posts, so I'll just make comments instead.
LIKE Operator: Although the LIKE operator can be useful, it can also cause a performance hit. Indexes are based on the beginning of a column, not on phrases in the middle of the column.
LIKE is not the problem here. LIKE with leading wildcards is the problem. Be specific. LIKE with only trailing wildcards is very powerful and likely to work better than LEFT(SomeCol,3) = 'ABC'
Joins: When coding outer joins, always order your data sources so that you can write left outer joins. Don’t use right outer joins, and never mix left outer joins and right outer joins.
Cite it or prove it.
Priority Boost option: In almost all cases, it is recommended to leave the priority boost option to the default value of 0. Raising the priority of SQL Server may drain essential operating system and networking functions and thereby result in a poorly performing SQL Server; in some cases it may even result in a SQL Server shutdown. If you do change the priority boost from 0 to 1, then be sure to test it thoroughly and evaluate all other performance tuning opportunities first.
lightweight pooling option: For most SQL Servers the default value of 0 for the lightweight pooling configuration option gives the best performance. In fact, changing the value from 0 to 1 may result in decreased performance. If you do change the lightweight pooling option to 1, then be sure to test it thoroughly and evaluate all other performance tuning opportunities first.
Disagree. Both of those options are deprecated, have been deprecated for several versions and were introduced to alleviate problems with processors circa 1995 that do not exist in modern processors. Neither should be used at all.
max degree of parallelism: The default value of 0 for the max degree of parallelism option works well for SQL Servers that have up to 8 processors. The performance of the SQL Server can actually degrade if more than 8 processors are used in a parallel plan. It is recommended to change the max degree of parallelism option on SQL Servers that have more than 8 processors from the default value of 0 to 8 or less.
Again, cite it or prove it.
Since SQL Server 7.0, the storage engine quality has significantly improved, reducing the need for running DBCC CHECKDB frequently. However, a full proof recovery plan includes a full restore and a DBCC CHECKDB to make sure all the portions of the recovery plan work. The frequency to run a DBCC CHECKDB often depends on your comfort level, your environment, and the importance of your data. (The authors would never say that DBCC CHECKDB is not needed. It is recommended to run it now and then as well as restoring from backups.)
Nope. Absolutely not. It's not about the quality of the storage engine, corruption is an IO subsystem problem in the vast majority of cases. CheckDB absolutely should be scheduled on a regular basis. How often depends on the backup retention. It needs to be run often enough that restore from backup is ALWAYS an option.
Oh, and no mention of backups as a way to recover from corruption. Repair is usually a last resort. It is definitely not the only option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply