SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 SQL Server Performance
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

vpkamath
Starting Member

USA
1 Posts

Posted - 03/25/2004 :  11:59:41  Show Profile
I have a web-based application written in SQL Server, VB 6 (COM) and ASP

Over a period of time (1 week) the application tends to slow down (not drastic) at which point I start/stop SQL Server, kill dllhost. This betters the performance.

I have looked at the following

Profiler, to see if there are any SPs that are taking a long time
CPU usage
Memory usage
Network Bandwidth Utilization
We use SQL XML, hence looked at the SQL XML queue length
Some more parameters in Performance Monitor, like Disk Read / Write times

also on the Application Server (ASP) followed the same

I did not find any peaks or anything that is eating up resources most of the resource usages are between 20 to 40% range

Is there something I have missed?
Can we say with the above information that SQL Server is fine (no tuning required) and it has to be COM or the front-end code that is messed up?

My understanding is that a slow down is caused due to lack of resources, but in this case the CPU and memory usages are way below the 50% mark.

I have read some of the performance tuning tips on this site but before I try some of those I want to know a way to identify where the problem is. Right now I am fighting a battle with an unknown enemy.

Thanks,
Vaibhav

tkizer
Almighty SQL Goddess

USA
36685 Posts

Posted - 03/25/2004 :  12:51:10  Show Profile  Visit tkizer's Homepage
I would suggest opening up Performance Monitor/System Monitor to determine if you have any hardware bottlenecks. Use sql-server-performance.com for a reference of which counters to analyze and what the maximum thresholds are. Once you determine that you do not have a hardware bottleneck, you should run SQL Profiler for a couple of hours. Then send the trace file/table into the Index Tuning wizard. See if it recommends anything to be deleted or added. Do you have duplicate indexes? Such as Index1 is Column1, Column2. Index2 is Column1. Index2 is considered to be a duplicate and it should be deleted. Also, are your statistics updated? Are your indexes fragmented (DBCC SHOWCONTIG)? If so, run DBCC DBREINDEX.

Tara
Go to Top of Page

gwhiz
Yak Posting Veteran

USA
78 Posts

Posted - 03/25/2004 :  12:55:08  Show Profile
Have you tried updating statistics and rebuilding indexes, if so does it help?

Also the fact that you killing dllhost and this helps leads me to think a com object may be to blame.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000