Poor stored procedure performance on multicore comp.


    Sponsored Links


  • 1. SQL max amount of memory
    What is the max amount of memory that SQL 2K can use and how do i set it... i know about the /3GB in the boot.ini file Thanks, BC
  • 2. SQL Svr 2K remote help
    I have MSSQL 2K installed on a Win2k server. No problems for users to connect to it that are on this mini-network. However, I have a user that is off-site, doesn't connect to my network in any form/fashion, but wants to be able to connect to my SQL server via TCP/IP and be able to run queries and whatever on the SQL server. I know this is possible, but how? any resources and/or websites would be greatly appreciated. Thanks
  • 3. Non System Disk Error Upon Restart of Installation
    After I install SQL Enterprise 2000 on my server I get the non system disk error upon restart usually associated with having a floppy in your A drive upon reboot. I didn't configure every service on my server but I didn't know if that was the problem. The installation said it was complete, but when I restarted I received this message. Any idea why?
  • 4. Installing SQL Server 2000 on Server 2003
    I want to install SQL Server 2000 Enterprise on Windows Server 2003 Small Biss. Standard, but when I do, I receive the error message "SQL Server 2000 SP2 and below isn't supported by this version of Windows. To run the program, click Continue. For more information, click Details." I click continue (as all microsoft articiles claim you should do), And I Get a screen that claims I can only install the client. I do not wish to install just the client. I don't have an install CD version of SQL Server 2000 that already has Service Pack 3 (SP3) applied, and I can't install SP3 untill I install SQL Server 2000. I have searched microsoft and the internet for an answer, but cannot find one. For now I have downloaded and installed the 120day demo, but I must come up with an answer for this customer soon. help! marshall

Poor stored procedure performance on multicore comp.

Postby Piotr Gawkowski » Wed, 14 Nov 2007 19:50:46 GMT

SQL 2005 Dev. Ed. 32 bit on Server 2003 R2 SP2 32bit. I have a problem with 
the poor performance of the stored procedure. Regardless to the quality of 
the procedure - at the same database (copied with back-up and restore) on 
old machines (e.g. Athlon XP 1.8) the procedure executes in 15 seconds (CPU 
usage during the execution is 100%) - the same on my Sempron 2.6.... The 
same procedure on two dual-core Opteron 280 (4 cores) with 8GB RAM and RAID5 
on four SATA disks the same procedure executes in ... over 3 minutes. During 
the execution CPU utilization is constatnly around zero, IO is also idle... 
I've searched already and found out that I'm not the only one having similar 
problem but still haven't found the solution... I tried to change the CPU 
and IO affinity of the SQL but it didn't changed anything... I 've also 
tried to switch on and off protocols allowed for database connection - no 
change was observed... Same problem I've faced on another multicore computer 
(8 dual core processors...)... If anyone can help - thanks in advance...
Piotr Gawkowski 

Re: Poor stored procedure performance on multicore comp.

Postby Russell Fields » Wed, 14 Nov 2007 22:43:59 GMT


You did not mention adjusting the degree of parallelism, so perhaps you can 
try that.  The following command would turn off parallelism, so that each 
request would only use one processor.

sp_configure 'max degree of parallelism', 1;

A rule of thumb that has been offered in the past is to set your max degree 
of parallelism to the number of physical processors.  So, for your two 
dual-core Opteron 280, you would set parallelism to 2.  You can read this 

If you want to be carefult about reconfiguring your server, you can 
experiment with this on a per query basis by adding the query hint OPTION 
(MAXDOP 1) to your queries.

I have seen this make a dramatic difference.


Similar Threads:

1.Poor performance when executing stored procedure


I have a stored procedure which simply does a

Select count(*) from sometable where ID=@SomeID

whereas @SomeID is a parameter of this sp. Since the table is tiny (5 rows) and only has a couple of columns execution should be pretty fast. Oddly though it takes the sp almost 2-3 sec. return the result. Does anybody know what could be the reason for this behaviour? It seems that query analyser waits a bit before executing the sp. I understand that the optimizer has to compile the sp first, put it into its memory and than is able to execute to sp. Is there any possibility that sql server got confused and wants to compile the sp before every execution?
Do you have any idea what else could be the reason for this strange behaviour?

Thank you! Best regards


2.Loading user's stored procedure via SMO - very poor performance problem


I've tried to load SPs from AdventureWorks via SMO in a for loop.

SMS shows 9 procedures (user defined objects). SMO 
Database.StoredProcedures.Count reports more then 1200 SPs in total.

I want to load user made SPs only. It's waste of time to go through more 
then 1200 objects each time to load just few. How? Can I, somehow, make 
distinction between sys SPs in database and user defined? 
StoredProcedure.IsSystemObject is not useful, I still have to iterate 
through all 1200+ SPs.

Same question for views. I guess principle would be same, if any.

Thanks in advance,
Brad Jones 

3.comp.databases , comp.databases.theory , comp.databases.ms-sqlserver

Are there any tools to have the primary conversion of ISAM database
structure to SQL 

Server [relational]?


- Kedar Agarkar

4.[comp.databases.informix] retrieving Stored procedure with SQL IN INFORMIX

On Fri, 27 Jun 2003 09:54:13 -0400, Lionel Girard wrote:

Myschema does the same thing in ESQL/C and there are not spaces
compressed out.  The procedure text is EXACTLY as it was entered!  Must
be something that Delphi or your ODBC driver is doing.

Art S. Kagel

> Hi, i'm currently working on a Procedure extractor from delphi/BDE
> plateform , and i do it with this request passed to INFORMIX :
> strQuery:='SELECT sysprocedures.procname, sysprocedures.procid, ' +
> 'sysprocbody.datakey, sysprocbody.data, sysprocbody.seqno ' + 'FROM
> sysprocedures, sysprocbody ' + 'WHERE sysprocedures.procname=''' +
> txtProcStoc.Text + ''' ' + 'AND
> sysprocedures.procid=sysprocbody.procid ' + 'AND
> sysprocbody.datakey=''T'' ' + 'ORDER BY sysprocbody.seqno ASC;';
> This give me the entire code of procedures, but there is a bug
> sometimes. When sysprocbody.data ends or begin with a space, it forget
> it in tables, so i can't get it back when i extract the procedure.
> For example, a procedure as a line like this :
> 	DEFINE myvar CHAR(2);
> and it is segmented like this in table sysprocbody
>  ..........DEFINE|myvar................
> The space is lost in text but INFORMIX seems to keep it somewhere
> because it can restore it with DBAccess. How can i find it and where? Do
> you have an idea?
> Really thanks for your help

5.Extremely Poor Query Performance - Identical DBs Different Performance

6. Very Poor Performance - Identical DBs but Different Performance

7. Poor Performance - Identical DBs Different Performance

8. Poor Performance - Identical Databases Different Performance



Who is online

Users browsing this forum: No registered users and 48 guest