SQL Server 2005 performance tuning and diagnostic for slow SharePoint server 2007 database ?

Hi people,

Can anyone please suggest me what is the SQL Server 2005 command / script to identify which part of the database that is causing my SharePoint Server 2007 to runs slow ?

and what is the recommendation to make sure that it can run well again ?

Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Adam RayCommented:
Does it continuously run slow? Or does it just take a while to load the first time after it has been idle a bit?

If the latter, it may be that IIS is recycling the worker process for the sharepoint application pool. The timeout can be adjusted in IIS.

(But before you google that and start changing settings I'd read up a bit in general on optimizing sharepoint.--In my environment sharepoint is fairly small, so I didn't have to worry about any potential performance implications of changing the default settings. ) http://technet.microsoft.com/en-us/library/cc298550(office.12).aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Senior IT System EngineerIT ProfessionalAuthor Commented:
It continuously runs slow, especially when clicking on submit on the Infopath form to upload the document.
Senior IT System EngineerIT ProfessionalAuthor Commented:
ok, I've managed to find the version of my sharepoint server from Central Administration > Operations > Servers in Farm  , it is

what version is that ? I cannot find it in anywhere by Googling around ?

I'm about to apply the scheduled after hours SQL script from http://support.microsoft.com/kb/943345 but not sure if it is applicable since I cannot determine the version of my WSS 3.0

Note:  the scheduling and running of the script are included in SP2
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Adam RayCommented:
Off hand, I can't remember for sure if it says, but have you checked Add/Remove programs to see if it is listed there. I seem to remember it specifying whether it was WSS 2, WSS 3, etc. (WSS is a separate install, not a server role.)

Moreover, what version of Windows do you have on the server. If you have 2008 R2/2012 you may have Sharepoint Server Foundation 2010+ rather than "Windows Sharepoint Services"

How long of a delay are you talking about? I've used WSS 2/3 on SBS2003 and WSS 3 on a hosted platform and it was never snappy. I no longer have it up running so I can't confirm, but it seem to remember a few seconds delay was common when I was working infopath documents. My "list" with attachments ran faster but it still snappy. (I eventually made a Access front end for my "lists" because I never did like working in the sharepoint interface. (But the others using it liked the simplicity of the sharepoint site, so I kept the backend there so they could use the web interface.)

Having said all of that, my sharepoint databases were relatively small, so I never tried to do much optimizing and just lived with it. (A few hundred infopath forms, a few thousand list items, and a few hundred meg in the document library.)
Senior IT System EngineerIT ProfessionalAuthor Commented:
The Windows Serve that I have which runs the SharePoint site is Windows Server 2003 not 2008.
the database is quite big, the WSS_Content is 22 GB in size and users report that it is slow to submit Infopath form and even opening the sites.
Adam RayCommented:
It's been a while since I've used Server2003/WSS 3.0. But in my old install (On SBS 2003) I seem to remember turning off/tweaking the settings for some of the automated tasks (e.g. backup checkpoints, deleted items retention age, indexing settings) in the sharepoint administration website. (You can find it in IIS, it's not the "site designer" section of the normal sharepoint website.)

Also don't forget the basics, like making sure any back up jobs run (and complete) at night. (Continuous backup solutions that run incremental jobs during the day or online backups that run hourly sound like a good idea, but they can really kill performance.)

If you are running the 32-bit version of server 2003 (32-bit IIS, sharepiont, SQL, etc.) I saw an article that said NOT to enable the 3GB flag in the Windows bootloader or SQL instance. It, in theory, gives you access to more RAM if you have it installed, but can cause other problems in Sharepoint/SQL.
Senior IT System EngineerIT ProfessionalAuthor Commented:
ok, I can see the WSS 3.0 version from the Add/Remove program 12.0.6612.1000
so is that WSS 3.0 SP1 ?
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.