Which factor impacts the Transactions/Sec in SQLServer 2005. What could be the reason to get less Transactions/Sec.
After some code changes in the stored procedure, the Transactions/Sec rate has come down.
View Complete Post
We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause. If we use a non-parametrized query (but keep the WHERE
clause the same) the query performs much faster and, in fact, executes a different plan. Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005. Obviously, we'd like to use parametrized queries to prevent SQL injection
but the slow perf is unacceptable. We need the LIKE to support wildcard scenarios. If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries. We're wondering, however, is there another option that would
give us the perf without sacrificing safety? I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...
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!!!
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
We have SQL SERVER 2005 SP3 Enterprise 32-bit running on a windows server 2003 Enterprise 32-bit with 32GB of RAM and 8 dual core processors. Our CPU, RAM, and I/O are always through the roof and the application is always running slow. We use AWE for buffer
cache the maximum memory is 28GB, the PAGEIOLATCH_SH is always high and the procedure cache is always around 700mB. Durring production hours we have over 6000 connections to sql server (500 users). What is wrong here? Please help. Thank you.
i would like to ask what the difference between sql server 2000 and 2005
I am using google transliteration to display the text in Hindi/Tamiletc.I tried to store the text appear as Hindi from textarea in an nvarcharfield of database (SQL Server 2005) , the text saved as "???????".After retireving text from the same it display the text as "???????".Can anyone help me how to store and retrieve the actual data from SQLServer 2005?I am using ASP.net,VB.Net and SQL Server 2005
I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.
I have two SQL Server instances installed.
1- UserLT (this is sql 2000)2- UserLT\SQL2005 (this is SQL 2005 named instance)
But when i try to add a database to my VS website project I get the following error:
Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925
I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)
and changed the "SQLEXPRESS" to "USERLT\SQL2005".
But I still get the same error message. Any ideas how i can resolve this issue?
sql serve 2005 enterprise edition is required to work with microsoft enterprise blocks
Hi All,i use the Ajax Control Toolkit HTMLEditor in VS 2008 web-project and save the formated text/content in Sql-Server 2005 - it works fine!
Now, i want print the formated text in a Report, with CrystalReport...??? is this possible?
How do i handle the process? i thing, i see the forest based on all the trees not more...
Can anyone help me? how to do?
SQL Server 2005 gathers a lot of useful data that you can use to identify areas where database performance can be improved. Here's what you need to improve performance.
MSDN Magazine January 2008