Does SQL 2000 use multiple processors for inserts?

That's the question
Hi
Never see it use more than one. Usually with Inserts, the disk subsystems
can not cope and not the processor.
Each client connection is capable of simultaneous inserts, but each
connection will not span multiple processors.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"hello" <hello@.discussions.microsoft.com> wrote in message
news:3A3D5594-8155-4178-A484-52C0B6A94328@.microsoft.com...
> That's the question
sql

Does SQL 2000 use multiple processors for inserts?

That's the questionHi
Never see it use more than one. Usually with Inserts, the disk subsystems
can not cope and not the processor.
Each client connection is capable of simultaneous inserts, but each
connection will not span multiple processors.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"hello" <hello@.discussions.microsoft.com> wrote in message
news:3A3D5594-8155-4178-A484-52C0B6A94328@.microsoft.com...
> That's the question

Does SQL 2000 use multiple processors for inserts?

That's the questionHi
Never see it use more than one. Usually with Inserts, the disk subsystems
can not cope and not the processor.
Each client connection is capable of simultaneous inserts, but each
connection will not span multiple processors.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"hello" <hello@.discussions.microsoft.com> wrote in message
news:3A3D5594-8155-4178-A484-52C0B6A94328@.microsoft.com...
> That's the question

Does MS SQL 2000 support multiple processors?

We are in the process of moving our SQL servers to a SAN
environment and decided to upgrade our servers to 4
processors. Therefore, does anyone know if MS SQL 2000
supports multiple processors?Depends on the edition. See BOL: Maximum Capacity Specifications for the
complete OS\Edition matrix. For four processors, you can use any of the
Windows Server OS editions and Standard or Enterprise Edition SQL Server
2000.
From the performance side, SQL does very well with multiple processors.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Cory" <crubicco@.yahoo.com> wrote in message
news:04ae01c3883d$882e63b0$a301280a@.phx.gbl...
> We are in the process of moving our SQL servers to a SAN
> environment and decided to upgrade our servers to 4
> processors. Therefore, does anyone know if MS SQL 2000
> supports multiple processors?

Does it increase performance to use multiple datafiles for large databases

If you've large databases (above 200GB/file). Does it help to have multiple smaller files (For e.g four 50GB files)? Does it improve performance. Also if a single table (size 200gb) is alloted to a file group which has one datafile. Does it help to have such filegroup with multiple files (fore e.g 4 files of 50GB each). My thought was if SQL Server is looking for certain data (assuming the file is not very fragmented) it is better for SQL Server to load up only the specific file that corresponds to the data, instead of loading up an entire 200gb file.

Moving to engine forum|||In general, the size of the data files does not really matter that much, as SQL Server will not load an entire file. SQL Server uses an offset in a file to go to a particular page (8K chunk of data) that it needs to load.

What really matters is the placement of files on disks, and the type of disks you use. If you have a single large file that is backed up by a RAID array, it does not really matter much. However, if you use ordinary (non-RAID) disks, multiple files might help, as you can place different files on different disk. Especially, placing the non-clustered indexes on different disks than clustered indexes will help.

Also, placing tempdb on (one or more) different disks, and placing the log files on different disks will help performance.

If you need more information, could you explain the problem you are trying to solve in a litte bit more detail.

Thanks,

Marcel van der Holst
[MSFT]|||

A lot of this depends on what kind of storage you have, whether is direct attached storage or a SAN, and if its a SAN, what kind of SAN it is. Generally speaking, Microsoft recommends that you have one TempDB file per physical CPU (and dual-core CPUs count as physical CPUs here), and one data file for each physical CPU. Doing this can help with allocation bottlenecks. Some SANs (such as 3PAR) make this somewhat moot, since they spread a file among all of the physical disks. Having multiple small data files can make backups and restores easier to manage.

You want your data files on one set of disks, log files on another set of disks, and TempDB on another set of disks.

Does it increase performance to use multiple datafiles for large databases

If you've large databases (above 200GB/file). Does it help to have multiple smaller files (For e.g four 50GB files)? Does it improve performance. Also if a single table (size 200gb) is alloted to a file group which has one datafile. Does it help to have such filegroup with multiple files (fore e.g 4 files of 50GB each). My thought was if SQL Server is looking for certain data (assuming the file is not very fragmented) it is better for SQL Server to load up only the specific file that corresponds to the data, instead of loading up an entire 200gb file.

Moving to engine forum|||In general, the size of the data files does not really matter that much, as SQL Server will not load an entire file. SQL Server uses an offset in a file to go to a particular page (8K chunk of data) that it needs to load.

What really matters is the placement of files on disks, and the type of disks you use. If you have a single large file that is backed up by a RAID array, it does not really matter much. However, if you use ordinary (non-RAID) disks, multiple files might help, as you can place different files on different disk. Especially, placing the non-clustered indexes on different disks than clustered indexes will help.

Also, placing tempdb on (one or more) different disks, and placing the log files on different disks will help performance.

If you need more information, could you explain the problem you are trying to solve in a litte bit more detail.

Thanks,

Marcel van der Holst
[MSFT]|||

A lot of this depends on what kind of storage you have, whether is direct attached storage or a SAN, and if its a SAN, what kind of SAN it is. Generally speaking, Microsoft recommends that you have one TempDB file per physical CPU (and dual-core CPUs count as physical CPUs here), and one data file for each physical CPU. Doing this can help with allocation bottlenecks. Some SANs (such as 3PAR) make this somewhat moot, since they spread a file among all of the physical disks. Having multiple small data files can make backups and restores easier to manage.

You want your data files on one set of disks, log files on another set of disks, and TempDB on another set of disks.

Does anyone know MS SQL 2005 support multiple instances?

Hello everyone,

Does anyone know SQL 2005 server support multiple instances?

if It does, can you tell me how to create another new instance?

Thanks in advance!!

Stephen

It does it by naming the instances.

To install another one run setup and on the instances page of the setup change from "default" instance to a named instance. and heh presto it will install a new instance.

Does a transaction automatically rollback on error?

When I write code for a multiple statements transaction do I need to check'if @.@.ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @.ERROR > 0 ?

If you start a transaction (with BEGIN TRANSACTION) you must end it with a COMMIT or ROLLBACK. So, yes, you should check the @.@.ERROR value after each pertinent statement and explicitly ROLLBACK the transaction if there is an error. I am not sure if there were any changes in this area for SQL Server 2005.|||In SQL 2005, you could do:

BEGIN TRY
.....
COMMIT
END TRY
BEGIN CATCH
.....
ROLLBACK
END CATCH

The good thing is you could put multiple SQL statementsin the TRY block. But there are some limitations too. check out books on line.