SQL Server Performance - 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

Old Forums

CLOSED - General SQL Server

SQL Server Performance

Author

Topic

vpkamath
Starting Member

1 Post

Posted - 2004-03-25 : 11:59:41

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

38200 Posts

Posted - 2004-03-25 : 12:51:10

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

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2004-03-25 : 12:55:08

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources