Chosen datatype for Primary key field and performance questions?

Hi there,

I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.

One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.

An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.

Many tables aren't that big, so the values will fit in an int datatype.

1. Is iot a good option to change the decimals columns to an int column ?

2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?

3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?

Thanks in advance,

Greetz,

Patrick de Jong

To me decimal (18,0) does not make sense at all... if at all u need to store larger number u could have gone for BigInt. Ofcourse the index size increases. To reduce the page split the change of datatype may not be sufficient. you many need to re-look your fillfactor for the index.

Madhu

|||

Using int for an indentity column is the most common solution, but just be aware of the roughly 2.1 billion upper limit for that data type. If 2.1 billion is not large enough, most people use bigint.

Changing your fillfactor can help minimize page splits, at the cost of making your index larger. Your deadlocks are probably not caused by page splits. Its more likely that they are caused by lack of, or improper indexes. You might try running this query to see if you are seeing any blocking:

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id