?
Solved

Performance Analysis of SQL 2005 database

Posted on 2010-01-13
6
Medium Priority
?
244 Views
Last Modified: 2012-05-08
hi

i have a SQL production server that has started to get quite slow when using various parts of the front end application. Tasks that used to take a second to commit now take 5-6 seconds.

Can anyone advise on
1) good general steps on performance analysis
2) any features/configs to look out for that may be enabled that cause performance issues
3) anything server specific that i could change etc

i am running sql 2005 on server 2003 ent

0
Comment
Question by:meteorelec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 70

Expert Comment

by:KCTS
ID: 26302141
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 26302191
why don't you go for perfmon to find out the bottleneck?

have a look at following links:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

http://207.46.16.252/en-us/magazine/2007.10.sqlcpu.aspx

http://blogs.technet.com/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx

apart from these, you can see profiler to find out what is going on with your server, may be your indexes are not properly maintained and you need to update the stats.

0
 
LVL 11

Expert Comment

by:rajvja
ID: 26302194
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:meteorelec
ID: 26302495
cheers guys, ill have a read at these and see how i get on

ill be back with questions im sure!
0
 
LVL 2

Author Comment

by:meteorelec
ID: 26314959
ok, i ran perf mon for a day or so , and have alot of facts and figures
 
the stats that stand out immediatly are
1)PhysicalDisk(1 D:)\Avg. Disk Queue Length - max is getting up to 187 to as low as 0
2)PhysicalDisk(1 D:)\ % Disk Time 17386.9988397381 to as low as  0
 
disk D is where the sql database is installed, are these numbers maybe normal enough for daily operation? or are they a sign of an issue?
0
 
LVL 2

Author Comment

by:meteorelec
ID: 26322334
got the issue!
 
 there is 4 databases running on the server, each had full recovery mode enabled which as soon as i switched to simple recovery mode i am back at full speed

performance monitor helped greatly as the stats reflected immedialty a difference when i disabled the logging

cheers for the help guys!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've always wanted to allow a user to have a printer no matter where they login. The steps below will show you how to achieve just that. In this Article I'll show how to deploy printers automatically with group policy and then using security fil…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question