SQL Server running Extremely Slow

[handlebars]

We are running SQL server with a web front end product.

The system is a helpdesk management system and recently, it has slowed to a crawl when querying the database from either the web or the back end tables.

In terms of data, we have around 10,000 end users, 50 analysts and currently around 1000 call requests.

At the moment there are only about 25 analysts logged into the system at any one time.

Even trying a search/find on the underlying end user table (9000 records) takes around 5 minutes!

Looking at taskmanager, it the server does not seem to be running at too high a cpu % and ram available is 1.5 Gb.

One thing that I have noticed is that the application log is filling up within minutes from SQLserver, does this mean the the transaction log is also filling up this quick?

 

[vongrunt]

Is everything always slow, or only some helpdesk features crawl at snail pace?

My first guess is about concurrency issues (read committed, many users attacking the same table simultaneously) and loose application design. Web apps are also known to generate excessive SQL queries... but again, this depends on design and technology used.

For starters, run profiler and see what is going on.

 

[hmckillop]

I would guess locking/blocking is possibly an issue, but the key as vongrunt has already pointed out is identifying where does the problem actually lie.
If you run SQL profiler and can identify the slow running procs/code.
Possible problems causing this are nearly endless but things like Isolation Level (some mid-tier code defaults isolation to serialisable which can cause concurrency problems), length of transactions, where transactions are managed i.e. is it DTC or is the transaction managed at Db level in the procs etc.
If the Application log is filling up what is being logged?

"I'm living so far beyond my income that we may almost be said to be living apart