reads, clustering, etc - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

reads, clustering, etc

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-01 : 13:57:30

server: QAT on clustering server ----> 23 seconds
----------------------------------------------------
SS 2000 developer edition SP4
win NT 5.2 (3790) SP4
MeM 7935 MB
processors 4
root directory C:\program files...
use a fixed memeroy size 640 MB

reserve physical memory for sql server
minimum query memory 1024 kb

use all available processors
minimum query plan threshold for considering 5

PROFILER READS = 5234

server: MILLER ----> 3 seconds
----------------------------------------------------
SS 2000 developer edition no service pack
win NT 5.2 (3790) SP4
MeM 2047 MB
processors 4
root directory f:\MSSQL$INAQAT

dynamically configure sql server memory

use all available processors
minimum query plan threshold for considering 5
PROFILER READS = 598

----------------------------------------------------
Making story short. I got an application that hits only 1 database called RECORDS. I'm getting different duration when running an application. 23 and 3 seconds.
Same database, same objects and same application.
SERVER QAT is our staging server, means lots of databases
SERVER MILLER is just a server i just assembled, means just one database (RECORDS).

Not sure if it's because it's a clustering server that is causing the issue nor the reads. If its the reads, what is causing it? Do you think is the how the memory is configured?. Will the experts pls stand up?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 14:01:43

Are the statistics updated and indexes defragmented on the server with the longer run times? Are they both using the same execution plan?

What does Performance Monitor show for CPU% on both servers?

Why would you put the root directory on the C drive?

What are the CPU specs for both servers?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-01 : 16:40:27

Restored the database to the servers again.

Are the statistics updated and indexes defragmented on the server with the longer run times?
after restoring the backup on both servers i left them as it is. statistis are the same on both servers.
logical fragmention (100%) is different just in one table. But this fragmentation is on the server that show faster and better perfomance.

Are they both using the same execution plan?
no...wth is going on...i have used the same backup. ITS BIZARRE!!!!

What does Performance Monitor show for CPU% on both servers?
% processor TIME average, Maximum
QAT: 2.2, 16.04
MILLER: 0.78, 7.4
MILLER has lower values cause it has only one database (RECORDS).

Why would you put the root directory on the C drive?
not sure i didnt put it there. But the mdf and ldf are in a different drive. only executables are on C: drive so wont make much IO performance.

What are the CPU specs for both servers?
3.06ghz in both

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-01 : 17:46:06

Why is QAT set to use only 1 GB of memory when the server has 8 GB of memory?

CODO ERGO SUM

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-01 : 19:05:09

quote:
Originally posted by Michael Valentine Jones

Why is QAT set to use only 1 GB of memory when the server has 8 GB of memory?

CODO ERGO SUM

QAT
use a fixed memeroy size 640 mb

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 19:08:21

And then it says:

quote:

reserve physical memory for sql server 1024 kb

So what is 640? Are you talking megabytes? And 1024 kb is 1 MB. Do you mean 1 GB instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-01 : 19:33:35

sorry read again above.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 19:35:02

640 KB? No wonder it's so slow.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-01 : 20:31:32

quote:
Originally posted by tkizer

640 KB? No wonder it's so slow.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Its 640 MB.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-02 : 12:41:05

i suspect it is the clustering server configuartion which is slowing down the application. But not sure yet.

I also recreated the indexes on the tables on both servers and the application is flying now.

another thing is why the execution plan is different in the 2 servers? I restored the database with the same backup. WEIRD!

If you are an expert, dont be shy and give me some inputs.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-02 : 13:37:03

Yeah I knew 640 MB was going to be the answer, but I wanted to double check.

640 MB is very low. You should consider changing it to 2 GB. It is pretty rare to allocate less than 1 GB of memory to SQL Server. And obviously your system is feeling the pain.

I am an "expert" on performance and clustering, so I'm not sure what you are hinting at.

It is very doubtful that the clustering configuration is causing your issue. Your memory is not configured properly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-07 : 16:13:41

tkizer,
Any way to test your theory about the memory? without switching to 2GB. Cause we have several instances of SQL.

It can't be the memory cause i switched to 16MB in the MILLER server and the application is also fast. its not the memory.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 16:21:42

No, but if you've got several instances of SQL, then perhaps you need to add more physical memory.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-07 : 16:23:46

do you have anotyer way to troubleshoot this issue?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-07 : 16:39:31

Check page life expectancy, buffer hit ratio and procedure hit ratio for each sql instance in perfmon.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-07 : 19:48:42

QAT & MILLER
page life expectancy 28888, 61111
buffer hit ratio 99, 99
procedure hit ratio 73, 75

They look alike except for PLE. But they both are above 350.
They both look good.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-07 : 20:11:52

I was lookin at the current bandwidth, QAT & MILLER: 11 & 100. Could this be the issue?. Pls experts only.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 20:25:03

I don't see how the bandwidth could affect the execution plan. Bandwidth would affect sending the data back to the client. How many rows is your query returning?

But at any rate, try forcing an index via an index hint that way you can get a similar if not the same execution plan. When doing that, do both perform about the same?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-08 : 00:23:54

I don't see how the bandwidth could affect the execution plan. Bandwidth would affect sending the data back to the client. How many rows is your query returning?
Back to the client? WEll I'm testing the front application and also the SP that front application is calling.
SP returning 9 rows. Does it matter?
front end application does update, insert, select.
QAT is slower than MILLER server. I CAN feel is slow with the mouse.

But at any rate, try forcing an index via an index hint that way you can get a similar if not the same execution plan. When doing that, do both perform about the same?
I can't modify the SP cause its a vendors application. I just have to prove to him that nothing is wrong in our side. But I dunno how. If i could only figure out why QAT is slow I could prove it to him.

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-08 : 01:08:18

quote:

I'm getting different duration when running an application. 23 and 3 seconds.

How did you measure this time? Executing query in QA or SSMS? or is this the time took by the front end application to fetch the data from Database and display it in the frond end application

quote:

I was lookin at the current bandwidth, QAT & MILLER: 11 & 100. Could this be the issue?. Pls experts only.

If your answer for the above is front end, then bandwidth is the most possible candidate for the slowness.

Did you ever try executing the stored procedure in QA / SSMS? How fast or slow was it?

Thanks
Karunakaran

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-08 : 11:42:43

KARUNA,

How did you measure this time? Executing query in QA or SSMS? or is this the time took by the front end application to fetch the data from Database and display it in the frond end application

YES, FRONT END NOT QUERY

If your answer for the above is front end, then bandwidth is the most possible candidate for the slowness.
Did you ever try executing the stored procedure in QA / SSMS? How fast or slow was it?

It takes 1 second on the QAT server. But its supposed to take microseconds.
so the SP TAKES on QAT (1sec), MILLER(some ms) servers.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources