[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1159
  • Last Modified:

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

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
CIO
DARC Ltd.
India
0
daffodilsoftware
Asked:
daffodilsoftware
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
DonKronosCommented:
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.

http://www.sql-server-performance.com/statistics.asp

0
 
ptjcbCommented:
Do you have the dbs set to AUTOGROW and/or autoshrink? This link may help with if that could be one of the causes:
http://support.microsoft.com/?id=315512

0
 
DonKronosCommented:
Good point ptjcb!  If autogrow is set as a percentage that can cause problems.  
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
daffodilsoftwareAuthor Commented:
DonKroos,

We've been doing backups every 4 hours.

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

ptjcb,

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.
0
 
Kevin3NFCommented:
How much of the RAM is SQL Server using?  High disk reads is a sign of memory pressure
0
 
daffodilsoftwareAuthor Commented:
Kevin3NF

Our SQL server is using 1.7 Gig of RAm as shown is Task Manager. Though the system has 3 gig of ram.
0
 
Kevin3NFCommented:
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?
0
 
daffodilsoftwareAuthor Commented:
Kevin3NF,
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).
0
 
Kevin3NFCommented:
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
0
 
daffodilsoftwareAuthor Commented:
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now