Diving deep into SQL-Server 2005
My colleague Anko Duizer has created a rather extensive list of do’s and dont’s about SQL-Server. There are some tips in there that are incredibly important, and perhaps he can illuminate some of those in the future. Like why you should avoid the use of GUIDs, you must not prefix stored procedures with ‘sp_’ and more like these.
I’ve recently took a glimpse at a PowerPoint slide-deck by Rob Howard about the performance of sites. For all asp.net sites (site, forum, blogs, etc), blogs.msdn.com, dotnetnuke.com and aspinsiders.com, they have only two web servers and one database server running. Rob shows for example, how their physical database server is running on three separate RAID0 configurations, one for OS, one for data and one for logs. The performance gain was incredible once implemented. This is also one of the tips Anko mentions, between the 46 others. Just nice to know how to boost performance of your database server.
Anyway, if you want to know all this yourself, you can start with taking a really deep dive into SQL-Server with Bob Beauchemin. Immediately after DevDays 2006 here in The Netherlands, he’s going to present a class at our Class-A office in Woerden. For more information about the class, click here.
Dennis is an independent consultant and software architect who loves building distributed systems and the challenges they bring. What he learns he tries to share via numerous articles, blogposts, presentations and workshops.
Dennis, this has nothing to do with Sql 2005.
All the things you mention are valid for almost every database.
Therefore there is nothing new about it…..
You can see the SQL Server performance Website for very good explanations of the do’s and dont’s.
However I am GLAD at least 1 developer is staying away from the GUID’s in the future…
( have a look at my blog https://bloggingabout-linux.azurewebsites.net/blogs/wellink/archive/2004/03/15/598.aspx
Read the comments and read who posted them…( you should know some of them)
Then RE-Evaluate those persons with this new knowledge….)
Patrick W.
Patrick,
I’ve read your blogpost about ‘guid is not always good’.
I agree with you that you should not put a clustered index on a GUID column. However, this does not mean that you never should use a GUID as primary key. As long as your primary key GUID is not a clustered index, I think there’s nothing wrong with it.
I also think that the statement that is in the BoL (haven’t verified it) should be ‘a primary key creates a clustered index by default’, instead of automatically. You can always override this clustered option.
The biggest problem of using a GUID as a clustered primary key index, is, that a clustered index determines the fysical order of your records. Since a GUID has no logical incrementing sequence, this means that with every insert, your clustered index will have to be rebuilt, and since the clustered index determines the way the records are ordered within that table, those records will have to be ‘repositioned’ as well. That’s the primary reason why a GUID can detoriate the performance.
So, I would say, if you use a GUID, be sure that you *never* put a clustered index on that GUID-column.
Well if you have read the ENTIRE postings…
somebody put a script there showing that guids WILL affect the search times as well ( i dont recall exactly but about 35 % faster if you use int), this is completely logical since a guid is 4 times as big as a int.