Database Slow Downs 1

[LCannon]

Got a real brain-twister here, I think.

I have a third party application that writes and reads from another workstation to/from two databases on separate SQL 2000 server. For the past two weeks, it has become very "sensitive" to anything else using SQL on that server. The writers of this program are telling me that the problem is too much usage on my SQL server. And by too much, they say this when I'm seeing the total usage of the 4 CPUs on this server go above 20%.

My question is, what can I do to improve SQL? Tweak it come more, etc. I'd also like to be able to see if the problem is actually with that program, but from SQL's perspective. We've pretty much stripped all other applications from that SQL server and now I'm looking into the hardware.

Any ideas?

 

[pweegar]

What are you doing as far as maint. on both the server and to the database(s) themselves?

Might be time to defrag hard drives and to shrink and re-index your databases, if you don't have jobs set up to do this automatically

 

[LCannon]

I have the default maint. plans on the databases that run every night. The server itself was defraged about a two weeks ago. I will try another defrag tonight.

Not quite sure on how to shrink and re-index the databases. I checked to see that the indexes on the tables used was there but isn't this don't by the maint. plans? If not, how can I set that up?

 

[JohnBates]

Hi LCanon

"Not quite sure on how to shrink and re-index the databases"

It may help with your performance problem and is fairly easy to set up.

Using Enterprise Manager.....

- Management --> Right Click SQL Server Agent --New Job

- Step Type: TSQL
command: DBCC DBREINDEX('name of table you want to reindex')

- The last step should perform the db shrink

DBCC SHRINKDATABASE([name of your user db])

Schedule your job to run at specified times.

I have found it is best to perform the reindex steps first, make the shrink step the last step.

I have a database that grows very large - so I perform a reindex on 3 system tables (MS....) then the shrink. I have it scheduled to run on the weekend.

Look at the largest system tables and user tables - I guess any table that has indexes might be a candidate for reindexing.

Hope this helps. John

 

[SQLBill]

Reindexing can cause problems with users as I believe the database can't be used during reindexing. Another option is DBCC INDEXDEFRAG which keeps the database in operation.

Refer to the Books OnLine (BOL) for information on DBCC REINDEX and DBCC INDEXDEFRAG

-SQLBill

 

[LCannon]

The tables are being reindexed nightly, during low usage times. There was a job on SQL running the reindex that I forgot about. It's not reindexing any system tables, though. I'll have to check into that.

Question:
I'm showing two applications whose CPU and Memory Usage figures always climbing. Wondering if that could be the problem. But I'm a bit foggy on what the CPU column is recording. I thought it was showing the CPU usage that application is using now (at the moment of the refresh of Current Activity) but the BOL doesn't seem that clear. Also, whatever my problem is, it seems to correct when I restart SQL.

Ideas?

 

[LCannon]

I've been able to get some more logging in place with the applications that are connecting to the SQL tables. According to their logs, they are showing a 2 second delay in writting a record to one of the tables.

What can cause a delay in writting a record to SQL?

 

[SQLBill]

A TRIGGER can do that. Let's say you have an INSERT trigger and you try to do an INSERT. Well first all of the trigger has to run to see if the INSERT meets any criteria/action in the trigger. If it's a long trigger the INSERT can take a while.

-SQLBill

 

[LCannon]

Dug around the SQL server and found a trigger that uses the same table at the same time. Removed it and now watching for the delay. Thanks!