Extended Stored Procedure Performance Tuning
Jul 23, 2005
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.
View 3 Replies
ADVERTISEMENT
Jan 9, 2008
hi
how should i monitor performjance of stored procedure and sql statements. i want to know that how much cpu time a query or stored procedure is taking??
r there any system table which give these information
please give suggestion ASAP
thanx
Maneesh
View 1 Replies
View Related
Jul 20, 2005
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 thatI can improve the performance speed of this SP.In the next couple of weeks I will work on preparing SQL statementsthat will create the tables, insert sample record and run the SP.I would hope people will look at my SP and give me any hints on howI can better write the SP.In the meantime, after looking at the SP briefly, my first observations are:1- use SET NOCOUNT ON2- avoid using UNION statements3- use WITH (NOLOCK) with each SELECT statement4- avoid using NESTED Select statements5- use #temp tables6- avoid renaming tables in SELECT statements, for example SELECT * FROMtblClients CAm i correct to consider the above 6 points as valid things in terms ofcausingperformance problems?I would appreciate any comments/helpThank you very much
View 12 Replies
View Related
Nov 22, 2000
Hi
Is there any good books for Query Tuning and Stored procedure Tuning
Thanks
View 1 Replies
View Related
Jul 23, 2005
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 and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.
View 1 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Apr 12, 2005
Could someone who has done it before be kind enough to post a short example of how to make a call from an extended stored procedure to a .NET DLL? Or even direct me to an example, or tell me that this is possible / not possible, it would help.
Thanks,Brian
View 4 Replies
View Related
Jun 25, 2002
I am looking for examples written in VB. Thanks ALOT!!
View 1 Replies
View Related
Oct 15, 2001
is it possible to view the source of an extended stored procedure.
I know that it is a dll program
but i want to know how works one of my extented stored proc
View 1 Replies
View Related
Sep 11, 2007
Hello,
i have created one C# DLL
xp_hello.dll
Its having below code in Class1.cs
namespace xp_hello
{
public class Class1
{
public string xp_hello()
{
string strReturn = string.Empty;
strReturn = "Chirag Patel";
return strReturn;
}
}
}
i compiled that DLL and kept it in C drive.
now i am trying to create Extended stored procedure using
sp_addextendedproc 'xp_hello', 'c:xp_hello.dll'
its showing successful creation.
Now i am trying to Execute it with
DECLARE @txt varchar(33)
EXEC xp_hello @txt OUTPUT
now its showing below message.
Msg 17751, Level 16, State 0, Procedure xp_hello, Line 1
Could not find the function xp_hello in the library xp_hello.dll. Reason: 127(The specified procedure could not be found.).
can anyone please help me out in this?
Thanks
View 4 Replies
View Related
Jan 31, 2005
Hello,
I have a question regarding the extended SPC 'xp_cmdshell'.
Basically I want to determine the username and userdomain in a stored procedure and what I know is that you can get this information in a "DOS-Box" with 'Set username' or 'Set userdomain'.
But if I use the above mentioned extended stored procedure in the following way:exec master..xp_cmdshell 'set username'I don´t get any resultset.
Does anyone know why I get different results depending on the fact if I call the 'set'-command in a "DOS-Box" or with the appropriate stored procedure?
Thank you for any help
View 4 Replies
View Related
Mar 25, 2002
We are on Windows 2000, SQL Server 2000, and a SAN. Our data and backups are located on the Storage Area Network.
I am attempting to restore a database with a backup on the SAN through Enterprise Manager. I selected 'From Device', 'Selected Devices'. SQL appears to hang. After looking at Current Activity, I have identified that it is running 'EXECUTE master.dbo.xp_fixeddrives 2'.
Are there any known problems with SQL Server 2000 and SAN? Has anyone encountered this error?
Thanks
Kim
View 3 Replies
View Related
Nov 19, 2001
Hi Everybody,
I have created an Extended Stored Procedure without any error in 'master' database, i.e. sp_addextendedproc @functname = xp_dump, @dllname = 'xp_dump.dll'. In xp_dump.dll, I have written a function with xp_dump name. I have developed this DLL in VB with the following sample code:
Function xp_dump() As Boolean
Dim con As New ADODB.Connection
Dim auid As String
auid = "999-99-9999"
con.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=*********;Initial Catalog=pubs;Data Source=WASSERVER"
con.BeginTrans
con.Execute ("INSERT INTO table999(au_id,au_lname,au_fname,phone,address,cit y,state,zip,contract) SELECT '" & auid & "',au_lname,au_fname,phone,address,city,state,zip, contract FROM table999 WHERE au_id='213-46-8915'")
con.CommitTrans
con.Close
End Function
But when I am trying to EXECUTE the same in Query Analyzer the following error is found:
ODBC: Msg 0, Level 16, State 1
Cannot find the function xp_dump in the library xp_dump.dll. Reason: 127(The specified procedure could not be found.).
Kindly help me to resolve my problem.
Thanking you.
Michael.
View 2 Replies
View Related
Sep 21, 2001
I have installed Standard Sql 2000 .
However i don't find the ex stored Procedures xp_sqlinventory and sp_sqlregister ..
Can anyone Point out here i can get them?
Thanks
View 1 Replies
View Related
Feb 17, 2004
Hi
Can i be able to use the DLL which is created in VB for Extended Stored Procedures.
if so how?
View 1 Replies
View Related
Mar 15, 2004
Hi,
I made some extended strored procedures
and they goes well when they are placed at
c:windowssystem32 or the same derectory to
sqlservr.exe which is usualy in C:Program Files
Microsoft SQL ServerMSSQLBinn.
I thought it's OK wherever they are placed
as long as I put them to the system path.
But actually they didn't work at all
when they were placed to the other path
that I had newly added as a system path.
Here is the contents of 'path' valiable:
%SystemRoot%system32;%SystemRoot%;%SystemRoot%Sy stem32Wbem;C:WINDOWSSystem32AmiClick;C:Progra m FilesMicrosoft SQL Server80ToolsBINN:C:Program Files/INC
I just added only "C:Program Files/INC",
placed the extended stored procedures there,
then called them through a trigger.
Can Somebody tell me what was wrong?
Please help me if you have any informations about this issue.
regards,
Takanobu Maekawa
View 11 Replies
View Related
May 3, 2004
Does anyone know why the Extended Stored Procedure API (formerly Open Data Services) headers and libraries (e.g. srv.h, srvdbtyp.h, opends60.lib) are not in the new Platform SDK?
Older versions of the Platform SDK had these tools included, but I installed the new Platform SDK and they're not included. Is there another SDK that I need to download and install now?
View 2 Replies
View Related
Jul 20, 2005
Hi allIve got a problem I was hoping someone may be able to help with.Im calling an extended stored procedure provided by a third party(master..xp_OrderHeader).This xp requires 3 inputs and is supposed to provie one output.When I call it in SQL Query Analyser all runs OK and I get a column result(single result) titled "Output_Info" with a value of say 300051Here is an example callexecute master..xp_OrderHeader @CustID, @TodayDate, @OrderID,@Output_Info OUTPUTI would have thought that my variable @Output_Info would hold the output,but all I get is NULL?Any ideas what Im doing wrong. Seems bizarre that the XP Ive been providedis displaying a result (be it a coumn I haven't named) ... but I can get itinto my variable for use.CheersCraig
View 1 Replies
View Related
Jul 12, 2007
Hi,
I am using vs2005, vc++, sql2005 and trying to write a complex extended stored procedure.
It seems I must use switch clrafe for my executable to deploy to sql.
But with clrafe, I can no longer use such c code tools such as typedef structures and pointer manipulation!
But I need to do these things to translate a contiuous block of binary data into a series of field/value pairs to be written to the sql server!!! Doing so externally would mean a severe drop in throughput: going back to the server for each field instead of all as one block!
Is there a technique, or pragma command to allow "unmanaged" c code to reside in a sql extended stored procedure?
View 15 Replies
View Related
Feb 28, 2008
Hello Friends.....
I want to ask that What is mean by Extended Sored Porcedure and when can it be useful ?
Where can be it useful in our day to day SQL Seveloper's activites ?
waiting for reply....
View 4 Replies
View Related
Jun 1, 2007
Hi I have written a small C++ Extended Stored Procedure
#include "CPPDLL.h"
#include <windows.h>
#include <Srv.h>
#include <stdio.h>
#define XP_NOERROR 0
#define SRV_MAXERROR 20000
#define CMDSHELL_ERROR SRV_MAXERROR + 2
BOOL APIENTRY DllMain( HMODULE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
return TRUE;
}
int xp_Easy(SRV_PROC * srvproc)
{
srv_sendmsg(srvproc, SRV_MSG_INFO , CMDSHELL_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0, "This is how easy XP's can be!", SRV_NULLTERM);
srv_senddone(srvproc, SRV_DONE_MORE, 0, 0);
return(XP_NOERROR);
}
When I register the DLL in SQL server 2005 and execute it. I get the following error message
Msg 17750, Level 16, State 0, Procedure xp_Easy, Line 1
Could not load the DLL C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnCPPDLL.dll, or one of the DLLs it references. Reason: 14001(This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem.).
Does anyone know why this is happening.
Thanks
Reeves
View 1 Replies
View Related
Nov 24, 2006
Hi ,
Any Body can help in knowing, How i can migrate the Extended Stored Procedure from one server to another.
View 1 Replies
View Related
Nov 15, 2007
HiI've heard 2 things recently, can I confirm if their true/false?(1) If you have a stored procedure and you want to optimise it you cancall exec proc1,you could also use define/set for each of the variables and copy thecode into query analyser,this then makes it easier to tune. However the optimiser worksdifferently for these variables than it does for variables passed intothe query via exec and will produce a less optimalplan(2) There is a different optimiser used in query analyser than thatused otherwise? A colleaguehad a problem where a stored procedure called from dotnet code wasrunning slowly butone run from query analyser via exec, with exactly the same arguments,was running quicklyta
View 1 Replies
View Related
Oct 3, 2007
Hi
I have a stored procedure in SQL server 2005. It works fine when I execute it from the Management Studio.But when executing it from ASP.NET code like this:
..... Of course more code is executed before this call .....int retVal = this.odbcCreateDataBaseCommand.ExecuteNonQuery();
retVal is -1. But -1 doesn't really tell me what the problem is?
Is there anyway to get extended error information so I can figure out whats going wrong?
(The stored procedure was working fine in SQL server 2000 before I upgraded to SQL server 2005. I use .NET Framework 1.1 and ODBC Sql Native Client to access the 2005 server.)
Regards
Tomas
View 1 Replies
View Related
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
Jan 25, 2006
Hi,First I would like to apologize for cross posting in three groups asI did not know which one would be the appropriate group. If some onepoints me to the correct one I will use that in the future.I am trying to findout the scalabilty of an user written extendedstore procedure. I have created a dll using a C program(modified/plagarized from some of the examples) . The main function ofthis extended SP is to act as a passthru to connect to an third partyODBC driver. All this is supposed to do is take the passthru sqlstatement, userid, passsword and connect to a remote server (IBM 3090Mainframe in our case) using a system ODBC connection and pass thereturned result set back to the stored procedure calling this extendedSP. I am trying to find out the answers for the following questions.1. What are the limitations of this approach.2. What would happen say if 2,000 concurrent calls per minute are madeto the extended SP from the web app.3. What would happen if this continued for say 4 hours. Will the memoryusage increase to point that will cripple the server assuming there isno memory leak in the dll.4. Are there any connection pooling concerns that I should payattention to specifically from an Extended SP point of view.5. Apart from compiling the dll using the "MultiThread" option should Ibe using other options to make sure the dll is threadsafe.SQL server Environment :OS - Windows 2000 Advanced Server SP4SQL - SQLServer 2000 Enterprise edition SP3Hardware - 8 way 2 node cluster with 6Gb RAMAny help regarding this is greately appreciated.Prahalad
View 3 Replies
View Related
Aug 10, 2007
Hi All,
Is there any way that I can create a dll file for Extended stored procedure(SQL server) in C# ?
I an able to create that in VC++. in VC++ there is DLLMain which serve as entry point and a paramter can be passed to it using Extended stored procedure. But since C# does not have any such entry point. Hence is there any way that I can achieve this?
Any pointers to this will be highly appreciated.
Thanks
Sid
View 9 Replies
View Related
Mar 12, 2007
Can anyone of you help me in finding online - resources to write extended stored procedure for Sql Server 2000 using C#.
Thank you,
Andy Rogers
View 1 Replies
View Related
Jul 20, 2005
Hello all,I have spend quite some time now but don't manage tofind out how to have the srv_describe function workingproperly for target NUMERIC(x,x) and NUMERIC types.Already tried several alternatives like e.g.// ...PVOID pvdata1;strcpy((char*)pvdata1, "12345.6789");wsprintf(colname, "Score");srv_describe(srvproc, 1 /* column #1 */, colname /* column name */, SRV_NULLTERM /* column name ending */, SRVNUMERIC, (DBINT)sizeof(DBNUMERIC), SRVNUMERIC, (DBINT)sizeof(DBNUMERIC), pvdata1);srv_setcoldata(srvproc, 1, pvdata1);srv_setcollen (srvproc, 1, strlen((char*)pvdata1));// ...but obviously does not work, I get an empty resultset.I could only manage by converting the numeric valueto string from C and then sending only strings but theSQL side expects actually a number(x,x) and is a customer, in which case, I would never propose to be casting thething from TSQL.Any ideas how to do it? The documentation and examples isquite limited in this area.Thanks in advance,Best Regards,Giovanni
View 2 Replies
View Related
Oct 5, 2007
We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.
Do I just have to control the permissions of these procedures tightly?
Thanks In Advance
MPM
View 1 Replies
View Related
Jul 13, 2007
Hi,
web searches give no end of how extended stored procedures can only be written in C++ ( or maybe vb also) .
And that extended stored procedures should be abandonded in favour of CLR framework procedures.
And how most articles explain how to convert ESPs to CLR procedures!!!!!
But I need to pass a non-discript block of binary data, extract pieces of data identified by its offset into the block, data type inferred by offset; into data to be written to the SQL database. These offsets are determinede by mapping (C UNION) to C typedef structures.
This cannot be done by managed code, therefore cannot be done by C++ CLR.
It is also ill suited for C# .
Sounds like a job for C++ extended stored procedure.
But how does one create and deploy an ESP with Visual Studio 2005? All wizards seem to insist on CLR.
Help!?
Boyd
View 2 Replies
View Related
Jun 5, 2007
I have created an extended stored procedure (that executes a DLL file) in SQL Server 2005 Express. When I execute this procedure the execution enters an infinite loop and hangs there. When I execute the same procedure on SQL Server 2000 it executes correctly. I wonder if the problem relates to SQL Server 2005, since no problem occurs with SQL Sever 2000. And if so, how could I repair it?
Best Regards,
Ramy
View 2 Replies
View Related
