SQL Slowness

[kgreer]

We are running Microsoft SQL 2000 sp4 on a machine. Lately we have been hearing on how slow some of the process on our intranet site is.

If we try to run a store procedure within query analyzer, the store procedure never finishes.

To fix the problem we stop and restarted the SQL services, then you are able to run the store procedure.

Can someone tell me what SQL could be doing to lock up? Or what I can look at.

Thanks

 

[ptheriault]

What kind of maint jobs are you running on the database?
Your going to need to find out how fragmented your tables are.
Run the following.

--DBCC SHOWCONTIG 
sp_MSforeachtable @command1="print '?' dbcc showcontig ('?')"

Check the Extent scan Fragmentation. The higher the number the worse fragmentation is.

If you don't already have one, create a maint job to reindex the tables. You should also update statistics.

Also,
run

sp_who2

Look for blocking. Blocking will things down.




- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Some other things to look at.

Open up perf mon on the server and check the following.

CPU - % processor time
CPU - Interupts / sec

Memory - Buffer Manager page life expectancy. - should be above 300.

Memory Pages/sec
Memeory Page faults/sec

Disk Phsyical/logical disk counters
Disk % disk time read/write

SQL counters
SQL Buffer manager - page life expectancy,
page reads & writes /sec



- Paul
- Database performance looks fine, it must be the Network!

 

[kgreer]

We are running the perf mon. Were can we find the Buffer Manager page lif expectancy?

 

[ptheriault]

It will be in the SQL BUFFER MANAGER

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

If all the hardware is good, look under Management for a job that "runs forever" and doesn't seem to be stopping. Also, you can use profiler to track down what part of a particular SP is giving you problems if it is only the one that you're having trouble with.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[kgreer]

We start having slow downs, once we start the services and restart them, everything looks like it start to run ok. Then after time it begins to slow down again.

 

[ptheriault]

Is this a dedicated SQL Server?
Do you have other Programs running on the same server?
How much memory does the server have and how much is dedicated to SQL? What did you find from perf mon? What was the max page life expectancy? Is there one process that seems to use all the CPU? Run sp_who2 take a look at CPUTime for each SPID?

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLBill]

Another place to look....open Enterprise Manager, expand until you see Management. Expand that to see Current Activity. Expand that. Click on Locks/Processes. Look for little red blocks. Those are indications of processes being blocked or blocking. Next, click on Process Information. Find the SPIDs from the blocked/blocking information and see what is happening.

-SQLBill

Posting advice: FAQ481-4875

 

[kgreer]

The server is has 8 gig of memory, the SQL is setup to dynamically use memory. The computer aslo has 8 CPU's. When I look for lock jobs, I am returning nothing with a little red blocks. If this is a memory leak, what else can we do?

 

[SQLBill]

You probably aren't using all the memory. I believe you need to enable AWE. And set the 3GB switch.

From BOL:
SQL Server 2000 Enterprise Edition introduces support for using Microsoft Windows® 2000 Address Windowing Extensions (AWE) to address approximately 8GB of memory for instances running on Windows 2000 Advanced Server

Run this:

sp_configure 'awe', 1
reconfigure with override

Then stop and start the SQL Server services so it will take affect. You can check to see if it is enabled by running just sp_configure. Scroll down to see AWE ENABLED. You should see:
0,1,1,1 if it is enabled
0,1,0,0 if it is not enabled
0,1,1,0 if it's enabled, but the services weren't restarted.

You might have to add /3GB into the boot.ini file, but I'm not sure.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Further on that: From the BOL

Before you configure Windows 2000 for AWE memory, consider the following:

To enable Windows 2000 Advanced Server or Windows 2000 Datacenter Server to support more than 4 GB of physical memory, you must add the /pae parameter to the boot.ini file.


To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to support a 3-GB virtual address space, you must add the /3gb parameter to the boot.ini file. This allows user applications to address 3 GB of virtual memory and reserves 1 GB of virtual memory for the operating system.

-SQLBill

Posting advice: FAQ481-4875

 

[timscronin]

You may also look at using sql profiler, to determine what might be your worst offender. if you look at

http://www-sql-performance.com

they have some good articles on using the profiler product to determine WHAT is causing the problem, since you have already determined you have one.

 

[SQLBill]

Forgot about this:

To use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege.

-SQLBill

Posting advice: FAQ481-4875

 

[ptheriault]

Also,
In order to use more than 2 gb of mem you must be using SQL 2000 Enterprise edition. Standard can only use 2 gb max

- Paul
- Database performance looks fine, it must be the Network!