Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I got reports from users that the access speed has dramatically slowed down, when they try to access the database.

Perfmon states:

Cpu ~20% usage.

RAM (3,5gb) 2.1gb in usage, 1.7GB for SQL

HDD Disk avg read is above 22 up to 40 and has 104GB free.

Everything has been fine for the past 3 weeks until now.

How do i troubleshoot this? Does it do any good to de-fragment the database?

share|improve this question
    
Can you post any of these results to the question? Have you made sure nothing else is taking up the memory? –  TylerShads Oct 4 '11 at 12:35
    
There was nothing else i could really see that the server is running. (im new to sql). –  Seb Oct 4 '11 at 12:48
    
You might want to consider running a SQL Profiler trace report. I suspect your users are running a query that is very disk-intensive or has many CTE's (Common Table Expressions) that require full completion before the rest of the query is executed. You could also use the "Display Estimated Execution Plan" button on their queries yourself to see where the most expensive part of the query is. –  Daniel Mallott Oct 4 '11 at 12:56
1  
So, waht changed? Lots of new data? Yoru disc totally looks overlaoded to me to start with. And you have very low memory too. –  TomTom Oct 4 '11 at 12:58
    
Also, how much memory is being paged to disk? How many processor cores do you have? How many people are trying to run queries concurrently? –  Daniel Mallott Oct 4 '11 at 13:10

1 Answer 1

You need to get a feel for what's happening in the DB. Check current activity to see what is being run against the system - there may be big queries running, statements holding lots of locks, blocking etc.

You could also examine some sample statements that are now running slowly to look at the actual execution plans & statistics - are they hitting the right indexes or generating table scans?

Are you indexes heavily fragmented?

Has anything major happened recently like a big growth in data volume after a bulk import or similar?

Update:

Index fragmentation is internal to SQL Server (and a separate issue from physical file fragmentation at the OS level). You should look at putting in place a regular schedule to deal with fragmentation.

Brent Ozar has done a good series of posts on it http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/. It includes a link to a good scrupt defaging indexes that need it. Alternatively if you could setup a maintenance plan to do it but note the limitations mentioned in the article.

At a minimum you need to consider the impact of these maintenance tasks running against a live system - the ideal is to schedule the work to run out of business hours if possible.

share|improve this answer
    
checks all indexs.. for fragmentation. fran 3364 - 4170 806 av 4170 ca 20% is over 10% defragmented in the database. using the script from this website: blog.sqlauthority.com/2008/03/27/… –  Seb Oct 12 '11 at 9:34
    
Yes it looks very fragmented. What is the best way to defrag the database? defrag the files in windows using Contig? (from Sysinternals) Or in the database? –  Seb Oct 12 '11 at 9:39
    
@Seb Extra info added to answer. –  Chris W Oct 12 '11 at 10:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.