Database Growing creating slowness

[latitude800]

I have a database that processes all my online transaction. Startind yesterday the database is growing extremly large, slowing the website and up to the point the when members trying to login to the website it doesn't authenticated. I have shrinked the database log files, then everything goes back to normal, website runs fine, but after 3 or 4 hours website starts running slow again, check database log files and has growing very large again. srinked then works fine. What would be the most effective way of identifying the issue, using profiler then tracing the problem, (bottleneck). Any help would be very appreciate it.

Jimmy

 

[Catadmin]

The most effective way to trace what is happening on SQL Server is to use Profiler. You can use it to track errant queries, Procs, etc.

What you should check first, though, is at what rate you have your user db data & log files to grow at. Also check the growth rate of the TempDB. Try increasing the growth rate first on the user db, then on the temp db to see if that resolves the problem. It could be your slowness is due to the server not growing the DBs large enough when it does the autogrow.

You might also want to run Windows Performance Monitor to verify you aren't having other issues like RAM, Processes & Processor problems and that you aren't experiencing a denial of service attack. More than likely, though, if the box itself responds fine but SQL Server is having problems, it's the database files growth size or a badly written query that won't stop running.

Hope that helps!



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[icemel]

According to Sajal Dam:

SQL Server Performance Killers, worst offenders first

Poor indexing
Inaccurate stats
Excessive blocking/deadlocks
Poor query design
Poor db design
Excessive fragmentation
Nonreusable execution plans
Frequent recompiliation for exe. plans
Improper use of cursors
Improper configuration of db log
Ineffective connection pooling

He recommends using System Monitor Tool to monitor utilization of o/s resources, etc - memory, disk, processor, and network

A large database indicates high levels of activity! Perhaps you should look at your activity during high growth periods and see where your bottlenecks are.

 

[icemel]

p.s. I personally use Quest tools for monitoring or ad-hoc investigation when users complain about performance