File Groups and parallel options

MS SQL SERVER

    Sponsored Links

    Next

  • 1. porting Interbase to SQLServer
    We are a couple of developers and have a large Interbase db (>300 GB), and are looking into porting it to SQL Server. Anyone have any recommendations about the best way to do the port ? Is DTS the way to go? Any particular drivers recommended? Thx, Jan
  • 2. Upgrading to Enterpise Edition
    Trying to upgrade from SQL Server Standard Edtion to SQL Server Enterprise Edition and would like to do it using a '.iss' file. I have not found any documentation regarding an unattended upgrade. I have created an '.iss' file using the advance option during setup and tried using it to do the upgrade but it does not work.. Any help would be greatly appriecated. Bruce
  • 3. Install SQL Server 2000 on home network
    Hi, I'm a web developer who is trying to expand my database knowledge by learning SQL Server 2000 (I've used Access previously) for creating dynamic web pages. I have two computers, both running Windows XP Pro SP2 that are networked together through a Netgear wireless router although these two desktop computers are connected to the router through ethernet cables, not wireless. On one system I have IIS running, as well as the software I use on a regular basis, including Dreamweaver MX2004. On the other computer I've kept it bare bones and am trying to install SQL Server 2000. I've installed SQL Server with logon set as Local System Account. I should mention that I don't currently have Windows passwords set up for either machine (blank) and I have Admin privileges on both. I have not installed Service Pack 3a yet although I have the cd for that. I'm able to get SQL Server to start when I start the computer and I can create ODBC connections on the machine it's running on. What I want to do and I am unable to do is create an ODBC connection from the other computer to the SQL Server computer. I'm assume it's a log in and permissions thing but I'm a little lost. Does anyone have any good resources that address something like this step by step or does anyone have any advice for doing this? I forgot to mention that I'm using SQL Server 2000 Developer's Edition. Thank you very much! Mark
  • 4. Integrating Installation
    I'm developing an application in VB.NET which uses the SQL Server 2000 MSDE as a back end database. I'd like to be able to integrate the installation of the MSDE into the installation for the application so that only one setup kit is required when installing on a new machine. Does anyone know if or how this can be done, or where I can look for further information. Thanks in advance for any help. -- M Keeton

File Groups and parallel options

Postby Peter Held » Fri, 01 Oct 2004 21:48:03 GMT

We are running SQL server 2000 enterprise, serving multi-
GB databases, on a 2 processor hyper-threaded box.

In reading best-practices / optimizations on MSDN/TechNet, 
I came across the paragraph below.  my questions/comments 
are:
1.) we do not currently "ever" see parrallel processing.
2.) our databases currently use one file group: primary.
3.) much of the database is in memory.  what effect does 
having the data in memory have as apposed too going to the 
harddrive for retrieval - when parallelism is involved.
4.) we do some fairly complex and large queries - i.e. 
loading a data warehouse.  
5.) do the additional file groups need to be on different 
drives?
6.) Might we see some parrallel operations if we place 
some tables onto different file groups?
7.) Does anyone have experience in the payoff of doing 
this?
8.) Is the key here that if the optimizer chooses 
parallel, then having separate hardware to support the 
parallel process is best.  In other words - multiple file 
groups do not improve the chances of parallel processing.

Thank you in advance.

In order to exploit any SQL Server 2000 parallel options, 
such as parallel table scans, parallel index scans, or 
parallel JOIN operations, it is strongly recommended that 
you create additional files and file groups. SQL Server 
2000 uses drive letters to select I/O requests that can be 
run in parallel. By default, the SQL Server 2000 
application databases have only one file group, named the 
primary file group. This is where the database system 
tables are stored. Each file group has at least one file. 
Each additional data or index file should be dedicated to 
a different drive (which is a different drive letter) or 
different RAID arrays. Consequently, a parallel query 
request is supported optimally by the storage hardware, if 
each request is fulfilled by different disks or stripes.



RE: File Groups and parallel options

Postby TWlrZSBFcHByZWNodCAoU1FMIE1WUCk » Fri, 01 Oct 2004 22:15:05 GMT

Hi

Unless you have disk multiple controllers, running against seperate disk 
groups/SAN's, you do no get much benefit. Trying to fool SQL by using 
seperate drive letters for the same disk group will not help performance, and 
you might actually saturate the SCSI bus.

Quite often, having good clustered indexes helps with query parallelism as 
simpler execution plans can be used when joins are involved.

You might be surprised, your current queries might be using parallelism.

Regards
Mike





Similar Threads:

1."Object was open" OLEDB error for parallel calls to Look Up transformation Options

Hello everyone
I have an SQL Server 2005 SSIS Package which users can initiate using
when the use my IIS website.
I kick the package off from code in a separate thread.

It is used to import data and it makes repeated calls to the Look Up
transformation to validate.


If two or more instances of the package run I get the following msg:


 Message: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has
occurred. Error code: x80040E05.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"
Hresult: 0x80040E05  Description: "Object was open.".


I'm a bit stuck. I know some versions of OLE support a flag to switch
on MARS (Multiple Active Record Sets). Could this be the problem -
how
would I switch it on?


Any information very gladly received ...

2.How to execute a group of tasks in parallel

Hi everyone,
Can someone tell me how to design my DTS to import Reference Tables (those 
with Primary Keys) to run in parallel. Since each Ref. table is independent 
of each other, i figured that it's much more efficient to import them in 
parallel as appose to sequential. However, the question is, how do i design 
the DTS to do this, then depending on success/failure, it continues to import 
the rest of the tables (those with Foreign Keys).
Source : Oracle
Destination: SQL Server 2000
As a summary, what i would ideally like is as follows:
Get data from Oracle -> Create SQL Temp tables -> Import Ref. tables into 
temp tables -> Import other tables (with foreign keys) -> Insert/Update 
master tables (SQL)

Any suggestion is greatly appreciated.
Calvin

3.Parallel Dynamic Groups ?

4.Why are Parallel INSERTs slower than non-parallel when you hav

It doesn't appear to matter if the index is Cluster or not.
There aren't any other Indexs on the table.


"John Bell" wrote:

> Hi Al
> 
> You don't say what other indexes are on this table or if you have tried 
> making the PK clustered. You may also want to try splitting the clustered 
> index into a filegroup that is on multiple files over different spindles.
> 
> John
> 
> "Al" wrote:
> 
> > Hi,
> > 
> > We are using SQL2000 SP3. I'm puting data into an empty fact table of a 
> > datawarehouse. There is a non-clustered PK on the table.
> > 
> > Starting with an empty table, INSERTing 600,000 with a single select, a 
> > parallel query plan is used and it takes approx 120 sec. When using OPTION 
> > (MAXDOP 1) to "turn off" the parallel query, it takes 45 sec.
> > 
> > If we drop the PK, and do the INSERT, the parallel query plan finishes in 30 
> > sec, and the non-parallel query finishes in 44 sec.
> > 
> > I'm just trying to understand the factors at work here, so that we can 
> > always use the best approach. 
> > 
> > Thanks in advance.
> > 
> > Al

5.Why are Parallel INSERTs slower than non-parallel when you have a

Hi,

We are using SQL2000 SP3. I'm puting data into an empty fact table of a 
datawarehouse. There is a non-clustered PK on the table.

Starting with an empty table, INSERTing 600,000 with a single select, a 
parallel query plan is used and it takes approx 120 sec. When using OPTION 
(MAXDOP 1) to "turn off" the parallel query, it takes 45 sec.

If we drop the PK, and do the INSERT, the parallel query plan finishes in 30 
sec, and the non-parallel query finishes in 44 sec.

I'm just trying to understand the factors at work here, so that we can 
always use the best approach. 

Thanks in advance.

Al



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 23 guest