Solved

MS SQL Server - running slow

Posted on 2008-06-17
12
485 Views
Last Modified: 2012-06-22
Hi All,

I was wondering if anyone knows how to speed up a database in MS SQL? - if at all possible?
Is there a way to optimise/index a current db so that queries will run faster?

Slightly new to advanced MS SQL Administration - so any help would be really usefull :-)


Thanks!
-Craig
0
Comment
Question by:chouckham
12 Comments
 
LVL 25

Expert Comment

by:slam69
ID: 21801492
set up a trace using the sql profiler and then have the database tuning advisor analyse the trace and make recommendation son which indexes and statsistic need updating
0
 
LVL 25

Assisted Solution

by:slam69
slam69 earned 75 total points
ID: 21801499
check ou tthe following guide with a basic example for how to use the advisor

http://technet.microsoft.com/en-us/library/ms166575.aspx
0
 
LVL 2

Assisted Solution

by:climbingjaffa
climbingjaffa earned 75 total points
ID: 21801583
With all due respect the Database tuning Advisor doesn't work against temporary tables so if you use them then it won't be of much help....

If you have the server up with awhile i.e. you haven't restarted it recently you could look at using the Index DMV's to identify any missing index's that could be applied ...check out the following as a starter

http://www.sqlservercentral.com/scripts/Index+Management/31821/

You should become familar with the DMV's on query stats aswell personally i think thier a very usful tool to have in your belt.....


Also
Monitor CPU usage ..... Deadlocks....resource contention...I/O etc see where your getting hit. Then you can ask more specfic questions. Sometimes its hardware and not the database design thats the problem
0
 
LVL 2

Expert Comment

by:climbingjaffa
ID: 21801594
On a side if you have alot of Dynamic SQL you should consider turning on 'Forced Parameterization'
0
 
LVL 25

Expert Comment

by:slam69
ID: 21801603
With a ll due respect user states that they are new to sql server administration and therefore start at the beginning, if they are new to teh system then getting complicated by going into temp tables etc is going to get confusing very fast, the tuning advisor is built in, easy to use and gives good insight into how indexes and statstics can increase speed of your db before goinmg into looking at bottlenecks, IO activity, query execution plans etc.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 3

Author Comment

by:chouckham
ID: 21801719
Thank you guys!

I appreciate all of your posts! :-)

I will attempt the basics first as outlined.. then move onto the next steps -  i had already considered the hardware to be a problem - i.e. RAID 5 set up etc.. (which isnt ideal) increase in RAM (as this is also fully used) and CPU usage seems to be extremly high whilst running a custom SQL query.

I will let you know the outcome and then we can try other things.


Thanks,
-Craig
0
 
LVL 25

Expert Comment

by:slam69
ID: 21801909
for the queries running high overheads check teh query execution plan an option within the management studio and where searches occur see ifthere are any full table scans as these create high overheads by scanning complete tables instead of indexes
0
 
LVL 2

Expert Comment

by:climbingjaffa
ID: 21803277
Fair enough i'm just pointing out that if you use temp tables the tuning advisor will not be of any use thats all...i didn't intend to sound like an ass, sorry if i did.
0
 
LVL 25

Expert Comment

by:slam69
ID: 21803364
wasnt meant to cause offence mate!! your comment def holds credence and good information just taking into account users level of expertise affects where you pitch yoru repsonse sometimes ;o) Always good having extra mins on a problem though ;o)
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 75 total points
ID: 21803742
first, you have to check the (sql) server configuration:
* RAM vs Max Memory setting
* CPU => parallel query execution
* Database Recovery mode vs backup (log) strategy
* Disk layout vs database file locations

next, per database you can optimize by using
* filegroups
* indexes

do you need details (apart from what you can google around :) ) ?
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 75 total points
ID: 21804886
There is a good word document to download from Microsoft - it shows the different problem areas and things to look out for, have attached it for you...

You will need to collect some empirical measurements, and you can gather these from both MS SQL and from runing performance monitor. For fine tuning looking at queries thats when you start using detailed tools such as Database Tuning Advisor and so forth.

You do need to take into account which versions you have (both sql server and windows), there are a few differences as to how much memory etc... And where you have your databases, Wndows Virtual Memory, temp files etc...

let us know if you want more specific pointers... Bare in mind that it is potentially a big area to contemplate...

You can also look at "Monitoring and Tuning for Performance" topic in SQL Server 2005 Books Online

There are a series of fairly easy to read "intro" documents from Brad McGeHee example : http://www.sql-server-performance.com/tips/performance_monitor_cpu_counter_p1.aspx  - there are a few good articles there, and on technet...






SQL2005-TShootPerfProbs.doc
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now