473,326 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Extended stored procedure performance tuning

We have an application that is based on several extended stored
procedures. When we run our application in house, or when most other
customers run it, they see performance of about X transactions per
second. One customer is seeing performance of about X/5, and I'm
having a hard time troubleshooting it. The performace bottleneck has
been narrowed to the execution of the extended stored procedures. Does
anyone know of tuneable SQL Server parameters that may specifically
affect the performance of extended stored procedures. I know the
procedures get run by a scheduler. Is there some way the priority or
frequency of the scheduler can be modified? Thanks for any advice.

Jul 23 '05 #1
3 2124
Are there lots of concurrent users in this one customer? This KB article
might be interesting
to you:

http://support.microsoft.com/?kbid=836839

--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
<bs******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
We have an application that is based on several extended stored
procedures. When we run our application in house, or when most other
customers run it, they see performance of about X transactions per
second. One customer is seeing performance of about X/5, and I'm
having a hard time troubleshooting it. The performace bottleneck has
been narrowed to the execution of the extended stored procedures. Does
anyone know of tuneable SQL Server parameters that may specifically
affect the performance of extended stored procedures. I know the
procedures get run by a scheduler. Is there some way the priority or
frequency of the scheduler can be modified? Thanks for any advice.

Jul 23 '05 #2
Are you using synchronization objects inside the XP?
Loopback connections?

Ge***@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.

<bs******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
We have an application that is based on several extended stored
procedures. When we run our application in house, or when most other
customers run it, they see performance of about X transactions per
second. One customer is seeing performance of about X/5, and I'm
having a hard time troubleshooting it. The performace bottleneck has
been narrowed to the execution of the extended stored procedures. Does
anyone know of tuneable SQL Server parameters that may specifically
affect the performance of extended stored procedures. I know the
procedures get run by a scheduler. Is there some way the priority or
frequency of the scheduler can be modified? Thanks for any advice.

Jul 23 '05 #3
Also check if you customer is running fibers or threads? exec sp_configure
'lightweight pooling'
Verify size of the MemToLeave area, maybe your customers runs with /3GB on
or off, of uses the -g startup parameter to set the MemToLeave area size in
SQL Server

Ge***@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.

"Gert E.R. Drapers" <gertdATsqldevDOTnet> wrote in message
news:42***********************@news.xs4all.nl...
Are you using synchronization objects inside the XP?
Loopback connections?

Ge***@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no
rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.

<bs******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
We have an application that is based on several extended stored
procedures. When we run our application in house, or when most other
customers run it, they see performance of about X transactions per
second. One customer is seeing performance of about X/5, and I'm
having a hard time troubleshooting it. The performace bottleneck has
been narrowed to the execution of the extended stored procedures. Does
anyone know of tuneable SQL Server parameters that may specifically
affect the performance of extended stored procedures. I know the
procedures get run by a scheduler. Is there some way the priority or
frequency of the scheduler can be modified? Thanks for any advice.


Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Michael Trosen | last post by:
Hi Everyone, I hope someone can help, I'm pretty new to pro*c programming. I have the following application setup: a pro*c program calls a stored procedure and recieves a cursor back: the...
3
by: Matik | last post by:
Hi, I alredy tried to search this problem in last posts, but I couldn't find the answer. I try to access via Extended SP the method in a dll. I registered the dll as a ExSP, with a name of...
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
1
by: smauldin | last post by:
What is the overhead of using extended stored procedures? I created a table with 500,000 rows. 1) I ran a select on two columns and it runs in about 5 seconds. 2) I ran a select on one column...
1
by: scanner2001 | last post by:
Here is what I would like to be able to do, Have an SQL extended stored procedure to do file copy, delete, and rename(move) procedures. I have written the code in C# to do this, in the form of a...
23
by: Bonj | last post by:
Hi I'm looking to write an extended stored procedure, the job of which will basically to read data from one table, process it using a COM object, and write (insert) rows out to another table. I...
0
by: negmat | last post by:
Does anyone know how exactly the sub-tree cost is calculated and whether it should be used as an indication of query/stored procedure performance? I used to think (although I could never find a...
0
by: devkumar | last post by:
I have a SP having 5 tables out of which 3 are temporary tables. This stored procedure is used to insert millions of records into one of the table. The problem is that its taking long time to execute...
1
by: raviva | last post by:
Hi, This is Ravi again. We have some jobs running in Oracle 9i. We have that stored procedures in TOAD. Right now we are expreriencing a degration in the performance of the procedure. It is taking...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.