Slow SQL Server?

billyboy9200
billyboy9200 used Ask the Experts™
on
We have a doctor's office and use a propitiatory program to manage our patients.  Every system in the office is hooked to a switch with goes into our router and then server.  We only have a few computers and see maybe 20-30 patients a day.

Network speed is not an issue.

Internet connectivity/speed is not an issue.

Server speed is not an issue (quad core with 8GB of memory -- only a few years old).

When we go to access our software, it loads fine.

However, when we try to pull up and manipulate client data (add appointment, add client, et cetera) it is extremely slow.  I know it runs off the SQL server on our W2K3 server and all the data is stored locally.

The company that built this has horrible tech support.

Can someone offer some "good practices" to maintain SQL database?  Something to look for....to speed up SQL access/read/write???
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Normally, you wouldn't want to mess with a third party product because they could have a problem with it. That said, if I was looking at it, I would run profiler and see what queries were taking a long time. Copy those queries to the management studio. Run the tuning wizard and copy the output to a file. Run the file to create the indexes and check performance.

You obviously would want to back up the data before doing this.
Imran Javed ZiaConsultant Software Engineer - .NET Architect
Commented:
Hi,
It is pretty difficult to state anything without knowing in details but you may find some helpfull tips on following link:
http://msdn.microsoft.com/en-us/library/ms187658(v=SQL.90).aspx

furthermore, check your database mdf file size as well as log ldf file. if ldf is too large then think to reduce its size.
secondly try to rebuild indecies
also check free space on windows drive and drive having database files.

Thanks
It is a very broad aspect. However, below are some resources to start with

http://www.sql-server-performance.com/tips/all_main.aspx

http://www.techrepublic.com/blog/datacenter/create-database-maintenance-plans-in-sql-server-2005-using-ssis/248 
(Update Stats, Rebuild Idexes would help)

Also, you could install the trial version of QuestSpotlight software that would give you a sneaky view in to the system to identify bottlenecks.
http://www.quest.com/spotlight-on-sql-server-enterprise/

Thanks,
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

lcohanDatabase Analyst
Commented:
"SQL server on our W2K3 server and all the data is stored locally."
Can you provide what SQL version/edition is used and how many databases?
If anyone has access to the SQL Server and if it is 2005 or higher you could use SQL Performance Dashboard reports to get a picture of why your server appears slow - they are easy to install and use.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc

At least you could run the statement below against each of your SQL db's to refresh statistics.

exec sp_updatestats;

lcohanDatabase Analyst
Commented:
You could also use "SQL Server 2005 Best Practices Analyzer (August 2008)" if you are running SQL 2005
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=DA0531E4-E94C-4991-82FA-F0E3FBD05E63
GlobaLevelProgrammer
Commented:
As the above mentioned you need to make sure no contract/license exists.  
That said...Do you have a DBA on premises?

if not you can outsource one...you want to be sure you have a qualified expert...sql server can be easiely messed and slow is better than now functional.

that said..if you know SQL...first thing I would do is run a query locally and time it compared to a client connection...if it runs just as slow locally compared to the client..its in fact SQL...with your post..sounds like the table structure is off..and sql is doing a full index scan/full table scan(google it)...which would slow things down..make you have Primary keys...if you have been running this system long and maintenance (as your post mentions not good it support)...your transaction logs might need to be teh issue....google it...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial