Isn't my SQL Server hardware sufficient for my server load? Why does it keep getting slow?

Posted on 2006-03-22
Last Modified: 2009-12-16
I've been facing this problem very often. Our SQL Server 2005
occassionally becomes pathetically slow and I dont have any clue what's
wrong with it. I cannot associate it with any specific change in work
load on the server. It generally happens during peak load hours, on one
day whereas it has been working perfectly fine before that. There is no
pattern that I've been able to identify the slow speed with.

Previously we were on SQL server 2000 and only recently (1 week back)
we have moved to SQL Server 2005 and but there has been no improvement
in situtation. It is still the same.

First of all, I'd want to know if my hardware configuration is
sufficient for my work load.

My Server Configuraiton Is

   HP Proliant ML 370G4 HotPlug
   Intel 7520 Motherboard
   Intel Pentium 4 HT Xeon 3.2 GHZ wtih 2MD L2Cache
   4 x 73GB 15K SCSI Hard Disks in Raid 0 (For Data Files)
   1 x 73GB 15K SCSI Hard Disk  (for log files)
   3 GB Of RAM

My Database size is around 70 GBs and Transaction log around 2-4 GB

WorkLoad on the server
   Around 100 users who are entering transactions in the system
   Around 30-40 users who are generating some basic level of reports

I've configured a separate server on which we are using log shipping to
transfer the data continually from the main server and all the larger
reports are executed on this server. This we did long back to reduce
the IO load of our server.

When, the server gets slow, I've generally observed that the no of
PageIOLatches  and waittime due to the same is very high. On googling
for the waittype, i found that it is an indicator of bottleneck on
physical disk and that's why I've configured my disks on Raid -0
inspite of being aware that it is very prone to corruption.

Also, whenever the server becomes slow, we generally try to identify
the quieries who are doing lots of reads/writes and try to optimize it
further using indexes and for a few queries we at times are able to
reduce no of reads from over 100000 to below 1000. It has generally
helped in past and things work fine for few days, but problems start
coming again after some time. And this time, inspite of all our
optimizations of query, things are not in control.

I've pasted the output of this query, if it can be of any help. This is result over when server last restarted 2-3 hour back.
   select * from sys.dm_os_wait_stats where wait_time_ms>0 order by
wait_time_ms desc

PAGEIOLATCH_SH  4717118 58799562        15031   529859
LAZYWRITER_SLEEP        45396   19436250        1265    27484
SQLTRACE_BUFFER_FLUSH   4853    19412734        4156    4328
OLEDB   19537   15340812        2929921 0
LCK_M_SCH_S     617     13117687        72093   13234
SOS_SCHEDULER_YIELD     3831317 12906359        500     12905359
PAGEIOLATCH_EX  437932  8174250 15328   51906
LCK_M_IS        228     5121406 78359   3781
LCK_M_S 736     4727500 125218  937
LCK_M_IX        165     2356000 131046  203
LCK_M_SCH_M     127     939953  78421   46
SLEEP_TASK      263369  886828  203     370625
SLEEP_BPOOL_FLUSH       104285  655890  171     38843
RESOURCE_SEMAPHORE_QUERY_COMPILE        244     570703  32859   328
ASYNC_NETWORK_IO        50270   568046  6093    24000
WRITELOG        36811   436843  8171    20390
LCK_M_U 77      407937  37078   78
BACKUPIO        3856    337093  2015    1359
LCK_M_X 71      310265  191312  46
IO_COMPLETION   29731   166125  390     875
MSQL_XP 1590    106625  1781    0
PAGELATCH_EX    109487  87843   984     84531
LOGMGR_RESERVE_APPEND   39      39000   1000    0
PAGEIOLATCH_UP  2504    23250   421     265
LATCH_EX        34      13859   4265    15
LOGBUFFER       3108    12328   671     328
THREADPOOL      389     7406    78      0
MISCELLANEOUS   17      3593    296     0
PAGELATCH_SH    231     2593    875     203
PAGELATCH_UP    25      1796    1593    0
SQLTRACE_LOCK   53      1234    484     46
ASYNC_IO_COMPLETION     1       578     578     0
SLEEP_SYSTEMTASK        1       515     515     0
CHKPT   1       515     515     0
BACKUPTHREAD    14      390     234     0
LATCH_SH        45      312     46      46
LCK_M_IU        1       125     125     0
CMEMTHREAD      64      93      31      78
MSSEARCH        14      62      62      0
RESOURCE_SEMAPHORE      2       31      15      0

Also, if any other information/statistics are required, I can try to
find the same.

I'd be grateful for any help/suggestions to help me out of this
problem. Our company is suffering badly due to this problem and we are
willing to take any expert's services as well to resolve this problem.

Puneet Agarwal
Question by:daffodilsoftware
    LVL 15

    Assisted Solution

    I presume you are doing daily backups.  If not then do that.

    I've also found that updated statistics can make a big difference.  Here is a link that discusses how SQL Server uses the statistics.

    LVL 27

    Expert Comment

    Do you have the dbs set to AUTOGROW and/or autoshrink? This link may help with if that could be one of the causes:

    LVL 15

    Expert Comment

    Good point ptjcb!  If autogrow is set as a percentage that can cause problems.  

    Author Comment


    We've been doing backups every 4 hours.

    And i have run updatestatistics after upgrading the database to 2005.


    AutoShrink is Disabled on the database and the problem can not be due to autogrow since the rates of inserts in the database is not very high. Primarily the load on the server is due to reads. Also, I've been monitoring the requests being executed on the server using
      select *  from sys.dm_exec_sessions  and had there been autoshrink/grow command running, it would have shown there.
    LVL 21

    Expert Comment

    How much of the RAM is SQL Server using?  High disk reads is a sign of memory pressure

    Author Comment


    Our SQL server is using 1.7 Gig of RAm as shown is Task Manager. Though the system has 3 gig of ram.
    LVL 21

    Accepted Solution

    I'm not sure if this has a negative impact on SQL 2005, but can you add the /3gb switch to the Boot.ini file?  IIRC, SQL 2005 standard edition can use as much memory as the O/S can show it.  Are you using standard edition?

    Author Comment

    I've done the changes and will need to wait for tomorrow to see if it helps. Do you think I need to enable AWE support or just adding /3gb switch will allow sql server to perform optimum use of available memory on the system (3GB).
    LVL 21

    Expert Comment

    AWE doesn't help until you are over 4gb ram as I recall

    Please also remember....I am a SQL 2000 person.  Verify any advice I give you for 2005

    Author Comment

    I managed to solve the problem.

    It was primarily due to sql server updating the statiscs by sampling just 2% of the rows leading to wrong estimates for msot of the queries. It works perfectly fine when i used dbcc reindex instead of sp_updatestats.

    Also, I've managed to configure my sql server to make use of spare ram in the system by using the /3gb switch.

    Thanks to all for the help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now