.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

SQL Server 2005 store procedure performance issue

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server
 

We are calling store procedure from vb.net client following are the observation.

1) sqlserver 2005  takes huge amount of time (more than 1 hour) to execute procedure. The procedure has select statement using join statement. (Generally this procedure executes and populate data in 5 seconds when it is called from .net client.)

2) If same procedure is executed from sql server management studio, procedure executes and populate data in 5 seconds (works as per expectation)

3) If we recompile procedure (i.e. open procedure in alter mode in management studio and click on execute button) ,  the procedure executes and populate data as per expectation.

4) This is intermittent issue on production machine. Most of the time procedure is executed and populate data as per expectation when called from vb.net client. But it does not give expected outout in some scenario. The frequency for occurring the issue is  once in 15 days and there is no pattern in which the issue is occurring.

 

Can any one help to resolve the issue.

 

Thanks in advance!!!

 

 

 




View Complete Post


More Related Resource Links

Decrypt the encrypted store procedure through the T-SQL programming in SQL Server 2005

  

HI ,

 

I have a encrypted store procedure in my production server .Right now I do not have the script now I want do some modification.

Please any one can help me to decrypt the store procedure it will be a great help for me.

 

For any suggestion thanks  in advance.


" Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
Click here to read my blog

SQL Server 2005 Replication - Replicated Databse Performance Issue

  

Hello,

 

I am struggling to work out a very frustrating replication issue and wondered if anyone could help me.

Here is my situation.

 

I have a virtual machine with 16gb RAM and 4 quad core 3Ghz processors and 250gb disk. This has Windows Server 2008 64 bit installed.

The machine has a SQL Server 2005 64 bit instance installed with SP3.

I have setup a publication on this SQL Server called pub1 , this publication publishes all tables/sp's /views and UDF's . I have set the article properties for the published tables as below.

- Copy Check Constraints = True

- Copy Clustered Index = True

- Copy Non Clustered Index = True

- Copy Default Value Spec = True

- Copy Collation = True

- Copy Insert, Update and Delete = True

- Copy unique key contraints = True

- Copy XML Indexes = True

Every other setting is set to false.

I have created a subscription to pub1 within the same SQL Server instance. The database has replicated everything I require without an issue.

If I run a query against the databse that I have published, it returns the query in less than 10 secs. If I run the same query against the subscription DB then this query takes over 30 mins to return.

I thought this was an issue with the indexes not replicating. From what I have selecte

Store Null Data In image datatype in sql server 2005

  

hi

i wanted to store image in sqlserver database image field and i did. But now in some cases i want to store null value in that image field please let me know how to do this using file uploader.please give complete sample code.


regards


SQL Server 2005 Performance Dashboard Reports user context

  
HI, We are planning on installing the Dashboard reports. But we'd like to know if the Dashboard is run under admin user or the login user who runs the Dashboard reports? Thanks! Kong

Kerberos issue with SQL Reporting Services 2005 on Server 2003 R2

  
Hi Guys,apologies if this is the incorrect forum, so moderators, feel free to move it to SQL/IIS/SharePoint as appropriate... [Windows Server Security moderator pushed me this direction]I have a test environment that I'm trying to get SQL Reporting Services 2005 SP3 working in integrated mode with SharePoint 2007 SP2.The environment is all in VMWare, running Server 2003 R2 x86 and is layed out like this:SERVER A:AD/DNS/DHCPSERVER B:SQL 2005 SP3 CU8SERVER C:SharePoint 2007 SP2 Dec 09 CU- Central admin on port 9000- SSP on port 9001- MySite on port 81- Main Content on port 80SQL Reporting Services 2005 SP3 CU8- Reporting Service website on port 82SERVER D:SharePoint 2007 SP2 Dec 09 CU- Central admin on port 9000- SSP on port 9001- MySite on port 81- Main Content on port 80SQL Reporting Services 2005 SP3 CU8- Reporting Service website on port 82Through the use of DNS and (SharePoint) Alternate Access Names, SERVER D is used to deliver the Main Content in SharePoint and the Reporting Service website.  SERVER C is used to deliver the Central Admin, SSP and MySite.I've set up SPN's for the SharePoint App Pools, using the following: [main content] setspn -S HTTP/SERVERA DOMAIN\AppPoolUserA setspn -S HTTP/SERVERA.FQDN DOMAIN\AppPoolUserA setspn -S HTTP/SERVERB DOMAIN\AppPoolUserA setspn -S HTTP/SERVERB.FQDN DOMAIN\AppPoolUserA [repor

SQL Server Express 2005 connection timeout issue.

  
Client: Sql Sever Management Studio Express 2005 (SSMSE) on Vista Server: Sql Server 2005 Express on Windows Server 2008 Standard When I first try to log into SSMSE from the client machine, the login operation times out. When I retry, the login succeeds almost immediately. If I close the app and re-open it, the same thing happens. This appears to be infinitely repeatable. I am able to reproduce this behavior using SQLCMD.exe. Here's the error: "Timeout expired. The timeout elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)" The technical details reference Error Number -2 What may be unique or tricky about this client's network is that it's a mixture of IPv4 and IPv6. I believe the server is configured for both, but honestly the networking side of things is outside of my expertise. Has anyone else experienced a problem like this? I'm looking for some troubleshooting tips and of course a flat-out answer :-). Thank you in advance for your time and expertise - I hope I've broken the problem down to its simplest form. Will.

SQL Server 2005 Analysis Services - Performance of Excel Pivot Tables

  
Hi, I've several cubes available for several users within my organization. I've the SQL Server 2005 and the Analysis Services running on the same machine but it has 30Gb of RAM, 8 processors and a quick enouth disk. The problems is that i've some automatic processes that "build" some excel reports and the users use pivot tables with olap connections to the cubes regularly. I've noticed that the excel "locks" when the user is drilling down the data and the server has only one processor (there are 8...) at 100%... Is there a way to make the analysis services use the maximum processing capacity that is available? The users are complaining about the slowness of the cubes... Thanks in advance Hugo

SQL Server 2005 Cluster failover issue

  
Hi All, I'm fequently experience Cluster failover issue. The cluster tries to failover and it fails back. Because of this the SQL server is getting restarted and this is a bugging issue during business hours. The following are the System event logs, Cluster resource 'SQL Server' in Resource Group 'Production' failed. The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop control. The SQL Server Agent (MSSQLSERVER) service entered the stopped state. The SQL Server (MSSQLSERVER) service was successfully sent a stop control. The SQL Server (MSSQLSERVER) service entered the stopped state. The SQL Server (MSSQLSERVER) service was successfully sent a start control. The SQL Server (MSSQLSERVER) service entered the running state. The Cluster Service brought the Resource Group "Production" online.    The following are the application event logs, The client was unable to reuse a session with SPID 131, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed [sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Native Client]Query timeout expired [sqsrvres] OnlineThread: QP is not online. The client was unable

in need of sql 2005 e-book on store procedure

  
i need e-book on sql 2005 express on STORE PROCEDURE TUTORIAL including how to use IF AND ELSE STATEMENT in SQL

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

  
I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......

ALTER PROCEDURE [QMS].[GetQueries]

@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS



SELECT [QueryID]

,[UserID]

,[QueryName]

,[Comments]

,[CreateDate]

,[UpdateDate]

,[ExpirationDate]

,[LastAccessedDate]

FROM [ReportQueries]

WHERE

[UserID] = @UserID

Issue with SAN Drive Migration in master database in clustered SQL Server 2005 SP1

  

All,

 

In our SQL Server 2005 SP1 cluster, I have to retire the existing SAN and move all my databases to new SAN drive. I have successfully moved all datafiles (user,system databases, resource) except master database.

 

Even when I change the parameters in start-up from configuration manager, I am getting error when starting up from cluster administrator / configuration manager.

 

But I am able to start the SQL Server successfully from command line (without any flags).

 

When I start the SQL Server from cluster administrator / configuration manager (after changing the parameters), I see that the start-up parameters are changing back to originalpath (old SAN) .

 

Please let me know incase you need any more information.


Issue when migrating Reporting service reports from SQL Server 2005 to SQL Server 2008

  

Hi Friends,

We are migrating reporting service reports from SQL server 2005 to SQL Server 2008 and facing below login issue.

We got a virtual machine with 2003 server and got SQL Server 2008 installed with reporting service.

I was able to publish my 2005 reports using VS 2005 easily.


1) But when I try to run the reports I am getting below errors at my website home page.

The permissions granted to user 'DomainName\MachineName' are insufficient for performing this operation. (rsAccessDenied)

I could not get why there is no user name in above error and how it says machine name does not have permissioin?

There should be at least some user id mentioned as per me.

What could be the reason of above error?

2) Also when I try to run reports using Reports website (i.e. http://server/Reports URL), I get below errro in executing the reports.

"An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'ReportSource'. (rsErrorOpeningConnection)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

Here the question is how it is picking up Anonymous logon. I thought it might be using Network Sevice acct to access reports.

What should I be doing to resolve above errors?

3) Also one more point about the s

Issue when migrating Reporting service reports from SQL Server 2005 to SQL Server 2008

  

Hi Friends,

We are migrating reporting service reports from SQL server 2005 to SQL Server 2008 and facing below login issue.

We got a virtual machine with 2003 server and got SQL Server 2008 installed with reporting service.

I was able to publish my 2005 reports using VS 2005 easily.


1) But when I try to run the reports I am getting below errors at my website home page.

The permissions granted to user 'DomainName\MachineName' are insufficient for performing this operation. (rsAccessDenied)

I could not get why there is no user name in above error and how it says machine name does not have permissioin?

There should be at least some user id mentioned as per me.

What could be the reason of above error?

2) Also when I try to run reports using Reports website (i.e. http://server/Reports URL), I get below errro in executing the reports.

"An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'ReportSource'. (rsErrorOpeningConnection)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

Here the question is how it is picking up Anonymous logon. I thought it might be using Network Sevice acct to access reports.

What should I be doing to resolve above errors?

SQL Server 2005 Express Connectivity Issue

  

I have a virtual dedicated 2003 server (standard edition) on which I installed SQL Server Express 2005 in mixed mode.  After installing the server I added the 2010 Web Developer package.  I updated the remote connections to allow TCP/IP and named pipes.  I also ensured that the TCP/IP protocol was enabled under the protocols tab.  After restarting the SQLEXPRESS service and attempting to configure a SqlDataConnection in Web Developer I got the ubiquitous error,

"A network-related or instance-specific error occured while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify the instance name is correct and that the SQL Server is configured to allow remote connections.  (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)". 

When I try using a trusted connection with sqlcmd I got this error,

"HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not  allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Log

how to create stored procedure in sql server 2005 and how to use that stored procedure in asp.net

  

Hi friends ,

       I am very newbie

I have created stored procedure and student database and also asp.net application for asp.net page

but it could not found stored procedure what is the mistake actually I don't no

Please help me



Warm Regards

Durga


Sql Server compact Edition Performance issue

  
I was  trying to insert a .wav file to sql server CE..The file is of size 1 Mb,but it takes a long time to perform the action (more than 1 min).Anybody have solution ? please help me..

How to increase performance of store procedure ?

  

Hi

I have written one store procedure for fetching huge data like this. there are nearly 2000 records. It is taking nealy 50 sec to execute. Could you tell me best method to fetch large no of

data ?


    ALTER PROCEDURE bulls_orbit.BuySellUpdateFetchData
    

     AS


    Select Id,Message,EndDate,RefId from tblBuySellUpdate Order By Id Desc


    RETURN


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend