Solved

SQL performance problems.

Posted on 2014-10-31
7
149 Views
Last Modified: 2014-11-03
We are running an application that uses a SQL database.     Users are complaining the application runs slow.    For example to just start the application it takes between 40 and 50 seconds.

We have a standalone dedicated SQL 2008 server with plenty of horse power and its not overloaded.  

The clients are running on powerful terminal severs as well.   Again with plenty of horse power and resources.

during the 40 to 50 second delay both the application server and the SQL server are not busy at all.   In fact utilization is extremely low.

As a test I installed the application on an older server using the exact same data only this time the application and the SQL server are running on the same machine.    And now bam!! it runs so fast.   start up is less the 2 seconds.

Does this mean I have problems or the application itself is the problem when opening a database on a remote server?

I have tested everything I can think of such as disabling antivirus with no luck so far.

Application support says while it would be faster running the SQL locally we should not be seeing such a huge difference but they have no real ideas on how to fix.      they mostly talk about resources which I have plenty of.

Thanks in advance for your help....
0
Comment
Question by:Zoldy2000
  • 4
  • 3
7 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40415901
Make sure your dbs is set to "auto_close off".  You want this query to return zero rows:

select * from sys.databases where is_auto_close_on = 1
0
 
LVL 2

Author Comment

by:Zoldy2000
ID: 40415906
can you tell me how to check that please?   Thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40415921
Edited that in -- please refresh me earlier comment :-).
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:Zoldy2000
ID: 40416235
thanks no change
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40419854
Idk what that means, "no change".  It was set to "on", you've changed it to "off", and it's still slow?  It wasn't set "on"?
0
 
LVL 2

Author Comment

by:Zoldy2000
ID: 40419964
sorry speed is the same.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40420123
Likely not a SQL Server issue then.  Check the communications between the servers.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MSSQL Speen Degradation 4 21
Challenging SQL Update 5 41
Is there any way to convert exponential value to number in sql server 5 38
Sql Server group by 10 26
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question