SQL Server : Application is Slow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

SQL Server 6.5 \ SQL Server 7.0

SQL Server : Application is Slow

Author

Topic

johnvarghese83cek
Starting Member

7 Posts

Posted - 2007-08-01 : 18:06:09

Pls Somebody help me out..
SQL Server application is very slow.
1. No blocked processes.
2. No Active transactions
Pls help me out.

- John

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-01 : 18:12:36

Is the CPU high?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-01 : 18:14:41

What version of SQL Server are you running? What OS are you running?

Does Performance Monitor show hardware bottlenecks? Does SQL Profiler show slow-running queries? Are you receiving table scans for your queries?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-01 : 18:23:13

this thread is gonna be a good one.

-ec

johnvarghese83cek
Starting Member

7 Posts

Posted - 2007-08-01 : 22:39:41

Hi

1. SQL SERVER VERSION 7.0 - 7.00.1063
2. Operating System - Microsoft Windows 2000 Version 5.0 - SP 4)
3. RAM - 3.67 MB
4. Task Manager - sqlservr.exe
a) Mem Usage - 1.6 MB
b) VM Size - 1.6 MB
5. I have ran a trace on the server
a) I can see a lot of lines with green texts. I donot understand much. What I understand through the reads and duration parameter in the trace file is the events are taking more duration for simple read and writes..
b) I have checked the processes . There are no blocked processes.
c) Also no long running open transactions.
d) I have checked the tables that the queries are using. I have done a rebulit of indexes on that table too..
e) Checked the performance monitor, Confused what to check..
i) Found that pages/sec are too high - Max - 160 and Avg - 4.28
ii) Processor time - Max 30.0 Avg - 12.5
iii) Avg disk queue length - Max 16 avg - 1.8

Please guide me, in a proper manner step by step, with commands instructed..
I am doing a lot of things and I am confused... What to do !!
Please start from Step 1. I am ready to go from the basics.
Help me regarding this and thanks for the quick reponse.

- John

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-01 : 23:25:32

3.67mb memory on the server? Do you mean 3.67GB? What kind of disk does the server have? How did you place db files on disk? Did you check slow query's execution plan?

johnvarghese83cek
Starting Member

7 Posts

Posted - 2007-08-02 : 00:00:29

I am sorry my mistake... yes its all in GB and not in MB.
We place as SQL Server files only in the form of .mdb and .ldf database files ...... in the hard disk.
Here there is an application that is connecting to the production server. The User here is complaining for slower application response from the server.

johnvarghese83cek
Starting Member

7 Posts

Posted - 2007-08-02 : 09:30:54

Hi guys,

Can any one please help me from step 1, if some user finds that an application is slow. What we do ?

I will do one by one, Thus we may be able to find something !!

Thanks a lot in advance,
John

johnvarghese83cek
Starting Member

7 Posts

Posted - 2007-08-02 : 11:17:36

I can see always green text when ever I ran a trace in the server.
Green text are in the lines where the event class is connect or disconnect.. always...
Any clue

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 11:54:39

When was the last time you reindexed all your tables on the production DB?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

johnvarghese83cek
Starting Member

7 Posts

Posted - 2007-08-02 : 15:55:38

I didn't reindex all the tables in the production, but only those I found out in the workload trace.
I did it 08/01/2007 : 18:06:09

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 16:15:30

If you can analyze the trace info, identify the procs taking longest time (for me anything over 1 sec is long), look into their query plans see if there's any bookmarklookups or scans or even if the query is not using an available index. See if you can "cover" some of the columns from the query in the index.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

johnvarghese83cek
Starting Member

7 Posts

Posted - 2007-08-02 : 17:50:59

Ok, I am analyzing the trace information.. I will get back to you with more information..
Also I am analyzing things in Peformance monitor, the processor time taken by the sqlserv process.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-02 : 23:57:58

What kind of disk does the server have? Did you put data files, log files and tempdb on their own disk array?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources