SQL Slowness issues

[JPL910]

Server 2003 R2 64 bit w/ SP2
32GB Ram
Quad-Core Processor
------
SQL Server 2000 Standard Edition w/ SP4
Database size 75GB
Connected users - about 200
------
O/S installed on C drive
Database installed on D
Log files located on E
Paging File on F
-------
Consistently experiencing slowness issues. Some of the issues noted by Idera SQL Manager are:
Page Life Expectancy is low
OS Average Disk Queue Length is 27.00 on D drive
My end-users are calling and screaming about how slow the system runs.

What can I do to optimize the settings to allow for SQL to use more memory and run faster/better?

Thank you in advance.

 

[gmmastros]

Is it slow all the time, or just occasionally?
Is this a problem that just recently started?
Is this a problem with a query ( or two) or is the whole system affected?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Connected users - about 200

I thought SBS is for up to 75 users?

or is it different for SQL connections as compared to domain accounts / exchange.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[JPL910]

It is never really fast but it definitely slows down quite a bit throughout the production day. There are times when everyone just hangs.
Pretty much is and has always been like this…..We just got used to the fact the system is slow.

Not using SBS.

 

[1DMF]

d'oh, my bad, going blind what with me currently chasing a bunch of system problems of my own!

Anyways, have you looked into allocating specific memory usage?

Does this help

http://msdn.microsoft.com/en-us/library/aa196734(SQL.80).aspx

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[SQLBill]

Some thoughts.

1. Run master..sp_who2 How many connections are there? You may have a bunch of connections that aren't be dropped and other users are waiting for a connection to come free.

2. How big are your tables? Are there indexes on the tables?

3. What are the users doing with the data? Are they all trying to update the same tables? If so, they could be getting locked out by one another.

4. Are the users running their own scripts or calling stored procedures? Either way, if they are just using SELECT statements (reading data) consider using the (NOLOCK) hint.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[DrSql]

SQLBill's recommendations are the right approach. I would also suggest running the profiler, filter duration > 10 Sec to start with and take the query that taking the longest start running through Index tuning advisor. Also profiler would let you capture trace for a period and you run it through advisor recommendations. Evaluate and apply those indexes. Is this a of the shelf app or in house?
Ps. perform all these tasks in your dev environment first.


Dr.Sql
Good Luck.

 

[1DMF]

Ps. perform all these tasks in your dev environment first.

- You're assuming we all have one!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PRPhx]

You might also take a look at this tool for monitoring
performance:

http://www.idera.com/Products/Free-Tools/SQL-check/

Also, what kind of maint. plan do you have in place? How often aree your indexes repaired/re-indexed?

And finally take a look here for some performance monitoring advice:

http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx

 

[JPL910]

Thanks for the tips...

I am already using Idera. The common and repeated issues are:

- Average Disk Queue Length is x.00 on drive D (varies between 7 and 50)

- Page Life Expectancy for Database is currently xx (varies between about 3 and low 100's)

As for number of connections - about 200.

Maintenance is done weekly(rebuild indexes, update statistics)

The application is third party. No control over the stored procedures or scripts.

 

[JPL910]

So here is where I am currently at:

I am upgrading/ moving the database to a new server.
What is the best way to configure it? How many hard drives should I install? How should I configure RAID? Which drives/partitions should the database and log files go on?

Thank you in advance. Your advice and expertise are greatly appreciated.

 

[1DMF]

Well I'm looking at straight mirror (raid 1), if all that will be on it is SQL & the OS.

Apparently it's faster that way.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts