Crazy Slow SQL

Question

Crazy Slow SQL

Tracy Bilcik

Valued Member

Points: 57
More actions
June 30, 2006 at 9:04 am

#168819

Slow performance after upgrading to SQL 2005 using Microsoft Dynamics Great Plains.
We have checked everywhere we can think of. We are in desperate need of ideas. This is what we have done so far;

New server has been installed. Small Business Server 2003
All of the appropriate sp's were put on the Great Plains Data then we went to SQL 2005.
Put on the SQL 2005 sp1
Installed SQL using Domain account and the mixed mode
ODBC on server is SQL Server connects just great
ODBC on workstations (installed from sql server 2005 disk) native client - connect just fine.
We have 3 gig of ram, 2 gig of data
Turned off sharepoint, turned off all monitoring. When you enter a transaction, for instance, it hesitates on the next document number, lookups are slow.
Ran all my maintenance on data that is recommended by Microsoft
Check catalog
check database
reindex
shrink
update stats
I can run this at my office on a smaller system smaller that this. 2 processors
Intel xeon 3.4 GH
They have installed Norton on the workstations, AVG on the server. There is a group policy that has the windows firewall turned on. I went to services and turned it off. I turned all of the above off and it was still slow.
I then installed an instance that was local account not domain account. Just thought I would try. Nothing.
ODBC Tracing is not on.
Can someone please help us PLEASE.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

How much memory is SQL Server actually using? How are the disks configured and what are the placements of the filegroups? Is IIS running on the box? Is the NIC set to the correct speed (don't laugh, I've been bitten by that one)? Does a profiler trace reveal acceptable execution plans for the slow transactions?

Just the first questions that come to mind....

And then again, I might be wrong ...
David Webb

Tracy Bilcik Valued Member Points: 57 More actions · Answer 2

First a memory issue on the sql server

In properties when the server is first installed it places maximum memory at

2147483648

What unit is this. The properties page say maximum memory in MB so if I have 3 gig of ram, what should I put.

3 gig is 3072 MB

3 gig is 3221225472 bytes

What should do I restrict my memory at Bytes or MB?

What do you mean how are the disks configured? (don't laugh)

IIS is running

NEC speed - You don't laugh, How do I check that.

I ran profiler. When the max memory of was reached I received this

-2147483649 then 2487483648 the this 'pppppppppppppppppppp' That is the only odd thing I can see.

Tracy

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

Unless you have to, I wouldn't run IIS on a SQL Server box because they'll both try to get as much memory as they can. Was IIS running on your old box?

I wouldn't change the memory setting just yet. Can you tell how much memory the SQL Server is actually using as opposed to other processes on the box? Is the swap rate on the box high?

Are the disks attached to the server or are you using a NAS or SAN? How many disks do you have? Are they in a raid configuration? Are all the database filegroups on the same physical disk? Are they on the same disk as the system swap file? If you check perfmon, are the disks busy?

TO check the NIC:

Start / settings/ control panel / network connections

right click on the connection and choose properties. You should see the adapter and be able to click 'configure' to see the settings. Check with your networking folks to see what it should be set to.

And then again, I might be wrong ...
David Webb

Tracy Bilcik Valued Member Points: 57 More actions · Answer 4

Memory usage for SQL is 929196K

However, the network guys have installed exchange server on the same server. It is at 570905K

This is a problem right?

David Webb-CDS SSCoach Points: 17398 More actions · Answer 5

Yes. Best practice is to run ONLY SQL Server on the server. Anything else will lend itself to contention on disk, memory, or both.

And then again, I might be wrong ...
David Webb

Keith Risman SSC Veteran Points: 287 More actions · Answer 6

If you run sp_monitor you should get a baseline and perhaps enough information to determine it you need to go onto more specific IO stats.

It will provide CPU%, number of connections, IO stats, packets recieved/ packets sent, read, and write info etc.

lydia shaba SSC Rookie Points: 44 More actions · Answer 7

Is this on a san configuration as i have a similar problem and it turned out to be a network card io problem....also I strongly believe running exchange and iis on the same server as sql server is bound to cheat sql on memory resources..

Tracy Bilcik Valued Member Points: 57 More actions · Answer 8

We did figure it out, we reinstalled the odbc drivers on the workstations and reinstalled the Dynamics GP application and it seems to be working fine. Not sure what happened there because it was happening on all the workstations.

Thanx For All You Help

Tracy Bilcik