Vanishing Parallelism

Jul 23, 2005

I have a function that returns a table of information about
residential properties. The main input is a property type and
a location in grid coordinates. Because I want to get only a
certain number of properties, ordered by distance from the
location, I get the properties from a cursor ordered by distance,
and stop when the number is reached. (Not really possible to
determine the distance analytically in advance.) The cursor also
involves joins to a table of grid coordinates vs. postcodes (the
properties are identified mainly by postcode), and to a table
that maps the input property type into what types to search for.

Opening the cursor typically results in the creation of six to
eight parallel threads, and takes approx 1 second, which is about
half of the total time for the function.

Recently the main property table grew from 4 million to 6.5
million records, and suddenly the parallelism is lost. Taking
the identical code and executing it as a script gives parallelism.
Turning it into a SP that inserts into a #temp table and then
selects * from that table as the last statement also gives
parallelism. But when it's in the form of a function, there is
only one thread -- and the execution time has gone from ~2 sec
to ~8 sec. I updated the statistics on the table, but still
no parallelism.

I could turn it into a SP easily enough, but that would involve
a change to the C++ program that calls it, which takes a while
to get through the pipeline. In the meantime, is there some way
to induce the optimizer to use parallelism? It used to.

View 3 Replies


ADVERTISEMENT

Vanishing Query

Dec 14, 2006

thats funny sometimes i perform a select query and itll work then other times a category would disapear from results , strange thing is during those times when i edit that field that disapears to be any random word that doesnt exisit, itll still seccusfuly run just not showing that field

e.g

select fname,sname
from family

sometimes will only show

fname...

and if i change it to

select fname, gibirish
from family

note: gibirsh is any made up word

it wont be affected it wont fail and itll still just return fname

View 1 Replies View Related

Case Of The Vanishing Password

Apr 11, 2008



Hi,
I have been breaking my head over this for a day now.
I created a new OleDB connection Manager
Choose SQL server Authentication
Give the user name and password, choose Save password
Click Test Connection, the connection is successfull
I reopen the OleDB connection manager, Password is gone and when i click test connection it doesnt obviously work

I have tried all protection levels for the package, yet none of them have helped.
Kindly help me with this issue.

Regards,
Suraj

View 11 Replies View Related

Database Tables Etc Vanishing?

Dec 15, 2005

Hi

I create a database in SQL Express using the Microsoft SQL Server Management Studio Express, with tables etc.

I connect to it from my VB Express .NET application. Any changes I make, like adding a new table from VB .NET Express causes all the tables to 'vanish' when viewing the Database in the Microsoft SQL Server Management Studio Express, the database is still listed there. Does anyone know why?

Also it cannot be edited there again it gives an error?

Also if I create a DB in VB .NET Express the database does not appear in the Microsoft SQL Server Management Studio Express, which makes backups etc not available. It is saved to a drive location, but not the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData location, any ideas what is happening?

Regards.

Larry_t

View 3 Replies View Related

Parallelism On The Fly

Apr 20, 2006

I know you can change the max degree parallelism server wide, but can you do it on the fly for one query? I know... trust the query processor but when I turn it off for this one sp, my query goes from 3 seconds to 0 and I got this ex-MS guy in here telling me there is a way, but he does not remember how.

I want him to simplify the sp or have his project's DBA do it, and I even offered to take a hack but.... you know.

View 2 Replies View Related

Sqlserver's Parallelism

Oct 29, 2001

Does anyone know about sqlserver's Parallelism.
a query without parallelism takes much less time as the one with parallelism, in my case it's 6 times faster without parallelism. If that's the true.
What do we need parallelism for?
Any ideas
Thanks

View 2 Replies View Related

Max Degree Parallelism

Dec 15, 2006

hi,i've set 'max degree of parallelism' to 1 because some sql request hanged.Now when i connect, how can i set the parallelism to 4 for a session.Is there a command like this :'alter session set max degree of parallelism 4' ?ThanksPaul

View 6 Replies View Related

Parallelism Question

Jul 20, 2005

If SQL Server is designed for multi processor systems, how can runninga query in parallel make such a dramatic difference to performance ?We have a reasonably simple query which brings in data from a few nonecomplex views. If we run it on our 2x2.4Ghz Xeon server it takes 6minutes plus to run. If we run this on the same server withOPTION(MAXDOP 1) at the end of the same query it takes less than asecond.Examining the execution plan, the only difference I have been able tosee is that parallelism is taking up 96% of the run time when usingtwo processors. This drops when using the one so a sort takes up thevast majority of the time for the query to run.OK, so running in parallel should mean that it's run in various partsand then 'joined up' later for performance gains, but how can it getit so wrong (timewise) ?If this is the case, will I see a significant difference changing ourserver to use a single processor, which seems completely the wrongapproach (or should I do this on each query in each app - eek) ?Do we have a problem that we don't know about that causes it to takethis long ?What can we do ? Ideally, using both processors would seem to bepreferrable.

View 2 Replies View Related

Parallelism In SSIS

Oct 16, 2006

Hi,



I would just like to confirm something with you guys...



Am I correct in saying that you dont need multiple connections to the same DB in a SSIS package in order to achieve parallel processing across multiple SQL tasks. In other words, I have 2 SQL tasks executing different stored procedures on the same DB that I want to run in parallel. They should be able to share one connection and still process in parallel, correct?



With that in mind, would the processing be faster if they each had their own connection?



Thanks in advance.

View 1 Replies View Related

Problem On Parallelism

Mar 16, 2007

after running query at first time working all processes

but later 2-3 sec. working only one

SQL 2005

Hewlett Packard DL580 (16 processes)

What is ideas?

View 6 Replies View Related

Max Degree Of Parallelism Value

Sep 2, 2015

I have SQL Server Version:-

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
 
This is just an UAT server which has OS and hardware detail below:-

OS :- Windows Server 2008 R2 Standard
SP:- SP1
Processor :- Intel(R) Xeon(R) CPU  X5650 @2.67GHz 2.66 GHz
RAM : - 4 GB
Bit - 64 bit

I want to set the value to max degree of parallelism, what value should i configure for the same?

Below is the snap property of SQL instance >> Processor

View 3 Replies View Related

Deadlocks Involving Parallelism

Jul 23, 2005

We're experiencing a large number of deadlocks since we began runningSQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyperthreading intel processors. We don't have the same problem on Dell6650's w/o the hyper threading. If I turn off the parallel queryprocessing option the deadlocks stop. I've tried all of the suggestionsfrom the Microsoft Knowledge Base under the following link -http://support.microsoft.com/?kbid=837983The only suggestion that actually yielded results was turning offparallel query processing but I don't want to give up what should be aperformance advantage if it wasn't for the deadlocks. Query tuning andindex tuning hasn't helped. Any suggestions? I haven't applied SP4yet. I'm wondering if anyone has seen the same problem resolved withSP4.*** Sent via Developersdex http://www.developersdex.com ***

View 4 Replies View Related

SQL 2000 Parallelism - Is It Worth It?

Jul 23, 2005

Hi,I have a sql 2000 server with 8 processors, server settings are asdefault. I read on Technet that it is good practise to remove thehighest no. processors from being used for parallelism, correspondingto the no. of NICs in the server. One of our 3rd party developers hasrecommended only allowing one processor to be used as there is aperformance hit by the server working out which processor to use. Doesanyone have a definitive answer to this? I suspect he's wrong but I'dlike some hard evidence if possible, thanks.Kev.

View 3 Replies View Related

How To Achieve Partition Parallelism?

Jun 16, 2006

Hi,

Is it possible to achieve partition parallelism in SSIS? What I am asking is, In DataStage, if I load some data like 'data reader -> trans1 -> trans2 -> destination' (and assume that I have 4 nodes configured), the tool divides the data into 4 different datasets and executes the package as 4 instances. This way the data load is very fast. Is it possible in SSIS?

Of course we can divide the dataset and load them thru multiple instances? But then dividing the dataset will differ for every load and so we need to modify the package all the time. Even if we divide the dataset, I am not sure 4 instances will run in 4 different nodes or in a same node? So anybody has any idea about it?

Thanks.

View 4 Replies View Related

Some Strange Behavior With Parallelism

Jul 7, 2006

Hi,

In my package I have a source, a script component to make some changes to that and a destination. To speed up the process, within a data flow, I have created 6 copies of the above components and running them in parallel. Each source takes different set of data. I have divided the data using the record no such that, each set will read 1million records.

Now, my question is, though each pipleline is supposed to process exactly 1million records, they are not running at the same speed. For example, 1 pipeline completes processing all 1million records whereas another pipeline processed only 250000 records in that time. I don't see any reason for why one should run slow while another is running fast considering that both are doing the same thing?

Do you have any idea about this?

Thanks.

View 6 Replies View Related

How To Set Parallelism For A Specific User

May 6, 2008

Hi,

actually a sever has a parallelism of 4 I would like to set the parallelism for a specific user to 2 without changing the code of the users application.

Is this possible.

As far as I understand with plan guides you just provide sql statements.
Need I to find all queries from the user, and add plan guides for all the queries, or tis there a more elegant way to do it.

br
fari

View 1 Replies View Related

SSIS SCript Transform Parallelism

Oct 26, 2007



Hi,

I need some urgent help on a conceptual question.

Requirement:
I have a simple package with one dataflow task. In that I need to read from a sql table and for every row in that table loop through n times and generate new output rows based on certain conditions (which are best evaluated in custom script as they are rather complex). Hence, if I have 100 rows in the table as my input, I may end up with 100*n rows as output.

My Design:
To implement this I have used an OLE DB Source which outputs to a Script Transform (ST). In the ST I intend to loop through in custom code and generate new rows using the .AddRow feature when I need new rows. This ST then feeds into another OLE DB Destination which writes the data to the table. Simple!
I am using the default buffer settings. All I have tweaked is the Synchronous... property on the script transform (otherwise I do not get to the Output0Buffer within the script!).

Problem:
I wish to do as much as possible in parallel. So I would expect the OLE DB Source to provide more than one row at a time to the script transform and that should process more than one input row simultaneously. It seems the script componenet is serializing input, so it seems to take one row at a time from the OLE DB source, loop through and process in the script transform).

AM I RIGHT IN THINKING THAT THE SCRIPT TRANSFORM IS EXECUTING THE INPUT IN A SEQUENTIAL MANNER?
CAN I PARALLELISE THIS?
If so, how?

Thanks,
Tirthankar

View 3 Replies View Related

Being Able To Stop Parallelism In A C++ Program Calling SQL Server

May 7, 2007

Hi All,

I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.



One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.



However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.



Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.



Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?



Thanks in Advance.



Peter Nolan

www.peternolan.com

View 4 Replies View Related

Integration Services :: Creating Parallelism By Executing Many Dtexecs

Sep 1, 2015

I'm currently looking at refactoring an existing, large SSIS 2012 implementation that consists of about 55 projects and 360+ packages. The ETL framework that is in use has a "main" control package that reads from a database table and determines which packages are ready to execute (based on some dependency logic) and then uses an Execute Process task within a loop that calls dtexec with the arguments:  /C start Dtexec /SQL "Some Package Path" /SERVER "someserver" 

This design allows the loop to execute a package and then immediately iterate because it doesn't wait for the package to respond (aka complete with a failure or success) so it can quickly kick off as many packages are ready to execute. A SQL Agent job is used to call this package every few minutes so that it can pick up any packages that have had their dependencies satisfied since the last execution and kick those off.It's a a clever design but has some problems such as decentralized exception handling (since the parent package is unaware of what is happening in the "asynchronous" dtexec calls.My biggest concern is that by executing packages, not with the Execute Package Task but with the Execute Process Task, and spinning up many dtexecs, the framework is not leveraging SSIS's ability to handle threading, memory consumption, etc. across all running packages and executables because it is simply unaware of them. It's essentially like using an Execute Package Task with the ExecuteOutOfProcess property set to true.

View 6 Replies View Related

SQL Server 2008 :: Relation Between Max Degree Of Parallelism And Number Of CPU Cores

Jun 12, 2015

I have got a question on max degree of parallelism and CPU cores.

If max degree of parallelism = 1, this signifies that sql will use serial execution plan (unless u change it in query level with MAXDOP hint). In serial plan, will the query use all CPU cores (say in my server I have 16 core processors)?

If in serial execution plan only one thread works, then what the other threads doing ? Idle (I may have a defined max server worker thread = 32767(by default)

Unable to create a relationship between this parameters.

View 3 Replies View Related

SQL Server 2008 :: MAXDOP And Cost Threshold For Parallelism Settings?

Jul 2, 2015

Referencing an article regarding MAXDOP and cost threshold for parallelism from Brent Ozar's website: [URL] .....

We have a 2 physical CPUs that are 4 cores each with hyper threading enabled. When looking through the task manager, under the performance tab, I see 16 CPU threads.We have set the MAXDOP value is set at 4.

Reading further, cost threshold for parallelism setting is recommended at 50 to start with.

Our setting is at the default 5.

View 2 Replies View Related

Performance ? - Whether Or Not To Leverage Parallelism (cpu/disk/time Trade-offs)

May 29, 2008



Depending on the way I write a query, I come up with these 2 stats.
Is there a sure winner in this race, keeping in mind the overall health of the server?
(I'm not sure of the specs of the server, as I can't log on to it :/ but are there any sql variables that would show cpu speed and # of cpus?)

I almost am leaning towards the single cpu query because of lower resources used -
or are most of the "reads" in the parallel'd query not read directly from the HD, but using the Table Spool created internally (query plan shows it)?

CPU Reads Writes Duration
Parallel: 200k 3.2m 2400 62s
Solo: 79k 1.1m 600 79s

View 9 Replies View Related

SQL Server 2012 :: MAX Degree Of Parallelism Option Not Working In Resource Governor?

May 29, 2014

I am running a query on SQL 2012 Server with the Resource governer setup for my account to have Max DOP option set to 1.

The query still runs in about 1 minute and the execution plan still considers parallelism.

When I explicitly mention the OPTION (MAXDOP 1) , the query runs in 6 seconds.

How can i tell by querying DMV's whether my query is using parallelism or not?

View 2 Replies View Related

SQL 2012 :: Cost Threshold For Parallelism - Subtree Cost

Jul 3, 2014

I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved