SQL 2000 Parallelism - Is It Worth It?
Jul 23, 2005
Hi,
I have a sql 2000 server with 8 processors, server settings are as
default. I read on Technet that it is good practise to remove the
highest no. processors from being used for parallelism, corresponding
to the no. of NICs in the server. One of our 3rd party developers has
recommended only allowing one processor to be used as there is a
performance hit by the server working out which processor to use. Does
anyone have a definitive answer to this? I suspect he's wrong but I'd
like some hard evidence if possible, thanks.
Kev.
View 3 Replies
ADVERTISEMENT
Sep 23, 2005
Is MCDBA worth of the time and money one has to put into to acheive it?
I am talking in terms of getting a job.
Do you think after getting MCDBA you have gained sustantial knowledge about the subject?
I havent appeared for any of the MS exams as yet
but would like to start with 70 228 and 70 229.
I like reading BOL, but i guess for certfn exams you have to have a diff approach...
View 13 Replies
View Related
Feb 14, 2008
Is it worth it for performance purposes to create additional fields in table so that when a database query is made two tables don't need to be joined?
View 1 Replies
View Related
Feb 2, 2001
I currently have several databases running under SQL 7. My Shop is migrating to Windows 2000. I was wondering, does SQL 7 run as good under Win 2000 like it does under Win NT? Since we are migrating should I upgrade to SQL 2000 or should I stay with SQL 7?
Thanks.
View 1 Replies
View Related
Jun 23, 2008
SELECT * FROM TEST NOLOCK
WHERE FY_CD = DATEPART(YEAR, GETDATE())
This gives me last years - but they want last years 2007 and 2008
Any suggestions
View 3 Replies
View Related
Jun 21, 2007
Hi,
I have a question regarding a project I am working on and I'm not sure what the best way to do it may be, I am new to SSIS and would like to gain some exposure using it, but don't know if this project is overkill or even possible using SSIS.
*** PROBLEM ****
Basically I have a large number of flat files each file contains different columns and data. What I want to do is import all of the data from the flat files into corresponding tables in SQL Server. The tables in SQL server are already defined with data structure to match each file. The first 10 characters of the flat file are also the same name as the SQL table. So it seems easy enough to just import flat file to table one by one, but I cannot figure out how to match the flat file structure to each table without setting up a task for each table (there are roughly 50-60 tables). Another reason I want to do it with SSIS is these flat files have to be imported on a weekly basis.
**********************
Is it in my best interest to just create a stored procedure that basically loops through the files and imports the data? Or do you think using SSIS is the best solution? I have looked at SSIS a bit and thought maybe I could use the bulkinsert task, but it appears it cannot dynamically change due to column specs. I figured out how to loop through the flat files using the foreach task, but I could only get it to import into one SQL table. I also started looking at the dataflow task and thought about creating a script task, but I'm not sure if that would work before I spend a lot of time trying to figure that out.
Any suggestions or thoughts are appreciated.
Thank you
View 5 Replies
View Related
Oct 24, 2006
Very often during dev. I add processes that don't change the metadata of a data item (for example a new sort) yet the linieage id is changed. This causes lots of headaches down the pipe. If metadata of a data item is not changed in the pipe, why is the lineage id changed?
If lineage is not smart enough to figure out when some metadata was actually changed, should it be made smart enough? Or optional?
Ken
View 10 Replies
View Related
May 13, 2008
Hey all,
I'm trying to set up a backup schedule in SQL 2005 that will keep 5 .bak files within the folder I specify. I can get it to keep one backup only even though I specify that it is to keep 5 days worth of backups. Each backup happens at 12:00 am. I think the problem I may be having is I don't know how to get the maintenance plan to name the file mmddyyyy.bak or whatever it may need to determine that the file is 5 days old. Currently I am specifying a name of gmsqlbackup.bak. Any idea's that may help me out?
View 2 Replies
View Related
Aug 16, 2007
I have a need to query my data and return only the last 24 hours (versus last day) worth of data.
My date and time fields are called:
calc_date = the date
Calc_time= the time
RealTime = Calc_date+Calc_time as realtime
I know if I use getdate()-1 that will get me the last day, but I need the last 24 hours. Any ideas on how would I do this?
View 6 Replies
View Related
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
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
Jul 23, 2005
I have a function that returns a table of information aboutresidential properties. The main input is a property type anda location in grid coordinates. Because I want to get only acertain number of properties, ordered by distance from thelocation, I get the properties from a cursor ordered by distance,and stop when the number is reached. (Not really possible todetermine the distance analytically in advance.) The cursor alsoinvolves joins to a table of grid coordinates vs. postcodes (theproperties are identified mainly by postcode), and to a tablethat maps the input property type into what types to search for.Opening the cursor typically results in the creation of six toeight parallel threads, and takes approx 1 second, which is abouthalf of the total time for the function.Recently the main property table grew from 4 million to 6.5million records, and suddenly the parallelism is lost. Takingthe identical code and executing it as a script gives parallelism.Turning it into a SP that inserts into a #temp table and thenselects * from that table as the last statement also givesparallelism. But when it's in the form of a function, there isonly one thread -- and the execution time has gone from ~2 secto ~8 sec. I updated the statistics on the table, but stillno parallelism.I could turn it into a SP easily enough, but that would involvea change to the C++ program that calls it, which takes a whileto get through the pipeline. In the meantime, is there some wayto induce the optimizer to use parallelism? It used to.
View 3 Replies
View Related
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
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
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
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
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
Mar 20, 2008
Which sort of data (from application architecture) point of view it's worth to put in XML datatype (MS SQL 2005)?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
Mar 9, 2005
I'm not sure if this is the correct forum for this this question but I'll give it a shot.
The only db development that I have ever done is in MS Access. I have a project at work that is being accomplished in VB and I need a db engine to use as the back end. Visual studio came with a copy of MSDE. Is this tool worth using or should I invest in mySQL? Are there any advantages to using MSDE over mySQL?
View 4 Replies
View Related
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
Mar 15, 2006
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
View 1 Replies
View Related
May 21, 2005
Can you install Sql Server 2000 Developer Edition with MSDE 2000 release A already installed?
View 2 Replies
View Related
