Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Performance Analysis of SQL 2005 database

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
meteorelec
Asked:
meteorelec
1 Solution
 
KCTSCommented:
0
 
RiteshShahCommented:
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
 
rajvjaCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
meteorelecAuthor Commented:
cheers guys, ill have a read at these and see how i get on

ill be back with questions im sure!
0
 
meteorelecAuthor Commented:
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
 
meteorelecAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now