Unfortunately, there are endless possibilities to address this....
You're going to have to really determine if the MSSQL server is the issue.
What if any SQL is running slow, selects, inserts, updates, deletes or all
of them? How large is your user database, is your temp db sized properly, is
the application well written and uses temp tables efficiently and if so, is
your tempdb on separate spindles? If not, is I/O the issue? Does performance
get worse the longer the uptime of the server? If so, is there a lot of data
being dropped and reloaded with daily/weekly batch jobs. Are you updating
statistics following batch jobs that drop and/or add data? Is rebuilding
indices in order? Did you pull the SQL, and run the same SQL outside of the
web server to see if the SQL needs tuned?
Is the host dedicated only to host your MSSQL instance? If so, and you have
determined that your SQL Server is the bottleneck by tracing the SQL and
doing some benchmarking. If it is and your host is dedicated, use dynamic
memory and boost SQL Server priority under the PROCESSOR tab and
re-benchmark. What SP are you on for MSSQL Server 2000? Is AWE the right
choice for you user database size? Some say it matters, some don't. There is
a MS KB article (can't remember number but it's an easy search) on AWE and
how it reports memory usage as well, your may not be getting correct
information through EM. Only way to tell is benchmarking. I personally find
AWE isn't all that great of an option but I have talked to a few that say it
may have helped them.
How much data is being extracted by the application, maybe the MSSQL server
is performing well, it's just taking some time to push the results back to
the web server via the network. Is the NIC/switches/webserver NIC using full
duplex?
What background processes are running on your host, antivirus, management
tools, and number of policies? These could get in the way as well. We found
that on WIN2K3, a specific version of NETBACKUP brought the server down to
its knees with MSSQL 2000 and 2005 and needed an upgrade.
If you're forcing MSSQL to verify 6GB is available at startup by setting a
specific amount of RAM, limiting the OS to 2GB of ram and using AWE, make
sure your page file is set correctly. Maybe it's paging a lot and it's the
host you're waiting on, not the MSSQL server. HA, yet another benchmarking
activity.
I also found a performance boost on some hosts after moving to SP2 for
Win2K3. Have not determined why yet, but it's interesting...
This is just a few ideas to pursue. Some may be valid for your environment
some may not. Most important thing is to determine if it's really MSSQL
server. Then you will know your going to have to dig deeper.
Hope maybe something can help you or point you in the correct direction...
Jeff
-----Original Message-----
From: Atul Thakor
[mailto:mssqldba-ezmlmshield-x92136687.[Email address protected]
Sent: Thursday, March 29, 2007 8:45 AM
To: LazyDBA Discussion
Subject: RE: site slow
You should also run a trace and look at the execution times of the
queries, as it might not be SQL at fault!
-----Original Message-----
From: Albert Frazer
[mailto:mssqldba-ezmlmshield-x96157363.[Email address protected]
Sent: 29 March 2007 12:43
To: LazyDBA Discussion
Subject: site slow
I need advice from any of you experts on this issue.
We have a box with SQL Server 2000 on back-end with website pointing to
that box. The machine has Windows 2003 Server Enterprise w/Service Pack
1, 8GB of RAM.
I enabled AWE and allocated 6GB for SQL Server.
Yet when website is extremely slow, when I look at the Task Manager on
that box SQL Server always uses only under 200MB. Any help appreciated.
Thanks,
Albert Frazer
Home Decor Products, Inc
www.hdpi.com <http://www.hdpi.com>
732-593-3637 (office)
732-570-4465 (cell)
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DISCLAIMER
Any opinions expressed in this email are those of the individual and not
necessarily the Company.
This email and any file transmitted with it, including replies and forwarded
copies (which may contain alterations) subsequently transmitted from the
company, are confidential and solely for the use of the intended recipient.
If you are not the intended recipient or the person responsible for
delivering to the intended recipient, be advised that you have received this
email in error and that any use is strictly prohibited.
If you have received this email in error please notify the Swinton Business
Systems Service Desk via email to [Email address protected] including a copy
of this message. Please then delete this email and destroy any copies of it.
Swinton Group Limited may monitor the content of all emails sent via its
network for the purposes of ensuring compliance with its policies and
procedures.
As part of Swinton Group Limited security policy, this email has been virus
scanned.
Swinton Group Limited, registered in England 756681, is connected for the
purposes of the Insurance Companies Regulations 1981 to MMA insurance plc.
Registered office: Swinton House, 6 Great Marlborough street, Manchester, M1
5SW.
Swinton Group Limited is authorised and regulated by the Financial Services
Authority.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page