SQLServer Performance to Queries
Hi,
During my working with SQL Server 2000 sometime back in a enterprise application development, we found some performance bottlenecks and tried to resolve the same by following some rules.
1.Having GUIDs as objectIds(primary key) was slowing the queries so we used int datatype instead.
2. Limit the joins in a query to 3 or 4 and denormalise the table if it needed more joins
3.Stop using Dynamic queries and use Stored procedures instead.
Are they still valid in SqlServer 2005 too?Should I follow the same rules here also?Is there any improvement in these rules?
[612 byte] By [
keshavbs] at [2007-12-24]
Point 1: in general the smaller the datatype the faster the performance so use the leanest possible datatype for any attribute...
Point 2: properly indexed tables should not have any problems with number of joins much higher then 3 or 4. Denormalizing database because you exceed some predefined number of joins is not a good rule of thumb. Take a look at your indexes first and denormalize only if there is no other way.
Point 3: always a good idea...
None of these three have changed from SQL 2000 to SQL 2005
Using GUIDs for your PK is generally a bad idea, since it will cause lots of page splits, which will increase your IO and will make your index maintenance more difficult.
In a large, busy OLTP database, having queries with more than three or four joins can definitely cause performance issues.
Stored Procedures don't necessarily perform any better than dynamic SQL, but they are more secure and much easier to maintain. I am very much in favor of using SP's, but not for perf reasons.
 oj wrote: |
| You can use newsequentialid() instead of newid() to generate a sequential guid. This will reduce page contention and split at the leaf level. |
|
However, if you're using a GUID as your clustered index (assumed if it's your PK) then each of your nonclustered indexes will be 12 bytes wider than using an INT data type (16 vs. 4) since all of your nonclustered indexes contain the key of the clustered index.
Also not sure I subscribe to the hard rule of X joins per query. That can be highly influenced by number of rows in your tables and appropriate use of indexes/data types.
