Chronicles from Giai

I share a forum after some months and it's not my favourite thing. I report my recent experiences about installation. A couple of days ago I downloaded Windows Server 2003 R2 and succesfully installed it. Right bottom on my desktop screen an icon shows that: Windows Server 2003, Enterprise Edition Evaluation Copy, Build 3790 (Service Pack 1). I guessed it is a copy with already installed its Service Pack 1. Then I searched for possible upgrades in Windows Server 2003 R2 home page and found nothing. I could have stopped here, but I had an already downloaded WindowsServer2003-KB889101-SP1-x86-ENU.exe copy for normal WindowsServer2003 and installed also this Sp. System has installed it without errors and I'm going well. I should have asked an expert before doing that, but I hate to wait for answers: that's why I don't like forums. By the way, can anyone tell me if WindowsServer2003-KB889101-SP1-x86-ENU.exe is good for Windows Server 2003 R2? I can reinstall everything, as I have all downloads on CD-ROM & DVD. Then today I've downloaded Microsoft SQL 2005: 3 hours for 950 MB. I installed succesfully it. I downloaded also Northwind and Pubs database scripts and attached them to SQL 2005. I can browse tables of Northwind and Pubs. One thing: even if online guide is very complete, it takes 10 seconds to load, quite a lot. Another thing: now I find in my computer also a copy of Microsoft Visual Studio 2005, just after having downloaded SQL 2005. Is it a complete version of Microsoft Visual Studio 2005, or what?

I'm glad you were able to install. Sorry that it took so long to download, but we're now bundling more into the product than we were with SQL Server 2000.

For your 10 seconds to load question, we'll need to get an answer from the Database Engine team.

For your question about Microsoft Visual Studio 2005: The edition you get with SQL Server 2005 is really just an empty shell that other components can install into. The Business Intelligence Developer Studio (BIDS) feature in Tools installs itself as a package into Visual Studio. If you don't already have Visual Studio on your machine, the empty Visual Studio shell is installed.

Good luck with everything.

-Jeffrey

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