July 14, 2010 at 8:23 am
Hi all,
I am posting this hoping that your expert advise can guide me to understand what we are experiencing and since we do not have a DBA on staff, we might be looking for someone to help us out on a consultant basis. Our setup consists on a HA Cluster comprised of two Windows 2008 Enterprise x64 servers (Active/passive) running SQL Server 2005 SP3 x64 standard edition. Both servers are configured as follows: Dual Procs quad core Xeon 3.0 Mhz, 12 GB RAM and dual 1GBs NICs. The only thing that runs on these servers is SQL. We have a vendor provided-n-proprietary application that uses SQL for the backend. The application also runs two Windows 2003 Standard 32bit on a load-balanced environment.
We have seen lately a lot of degradation in terms of responsiveness from the SQL server, especially during peak hours, however, our user base is not more than 50 users connected at the same time. The vendor indicated that we needed to upgrade the memory from 8 to 12 GB (which we did) but this has not helped, we still see the same poor performance during peak hours.
During my research, I noticed that the Memory is being used almost in total by the SQL service and even at nighttime, when there are no users, the memory usage reported by Performance monitor and Task manager, remains at 98% with SQL service being the HOG. All documentation I have read, indicates that this is normal SQL behavior and unless SQL server is constrained with the min/max memory settings, there is nothing more than can be done.
I have also detected a lot of bad coding practices on the stored procedures/functions that this application runs but before I go about critizicing our vendor, I want to make sure that at least our SQL infrastructure is set up correctly.
Also, on the same token, if someone knows a tool that will analyze the contents of a DB and provide some sort of report with all the possible bottlenecks, errors and/or recommended fixes, I will greatly appreciate it if you could tell me that as well.
Thank you all for your help and assistance.
JC
July 14, 2010 at 9:32 am
Might be useful to state whereabouts you are, seeing as you're asking possibly for a consultant.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2010 at 9:40 am
Hi Gail!
Sure, we are located in Orlando Florida.
July 14, 2010 at 9:46 am
Counts me out then. Maybe Kevin (TheSQLGuru) is interested.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2010 at 9:49 am
I appreciate that Gail!
I have used your knowledge and expertise directly and indirectly thru various tasks I have come across and if you are still interested, we could work out something for you to do the consulting remotely. Just something to think about.
July 15, 2010 at 8:49 am
GilaMonster (7/14/2010)
Counts me out then. Maybe Kevin (TheSQLGuru) is interested.
Thanks for the props Gail.
Yes, this is the type of work I do for a living (and have for coming on 15 years now). I live in the southeastern US and can do onsite work easily, although a good bit of tuning work can be done remotely. I am the only DBA at several standing clients and can provide references if you like. I will actually be in Orlando the 28/29th of July for a family visit on the way to South Florida for the SQL Saturday there. I can arrange for some onsite time then if you like.
Contact Information:
Kevin Boles
AIM: TheSQLGuru
YIM: SR20Pilot
MSM: kgboles at earthlink dot net
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 15, 2010 at 10:05 am
johnnycash (7/14/2010)
if you are still interested, we could work out something for you to do the consulting remotely. Just something to think about.
In my case it's not just remote, also the joys of international money transfers and exchange rates.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply