0

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?

Tablemaker
  • 1,149
  • 1
  • 11
  • 23
Seb
  • 1
  • 1
  • Can you post any of these results to the question? Have you made sure nothing else is taking up the memory? – Tablemaker Oct 04 '11 at 12:35
  • There was nothing else i could really see that the server is running. (im new to sql). – Seb Oct 04 '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 04 '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 04 '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 04 '11 at 13:10
  • @tomtom Couldnt really tell. Just got reports about everything being slow as syrup. – Seb Oct 12 '11 at 09:26
  • @tomtom I created an copied an user for the database. (Disabled the user now) hope it will make an diffrence. – Seb Oct 12 '11 at 09:34
  • @Daniel Couldnt find any sql profile trace report. Paging size is 3581 mb cpu= xeon e5320 1.87Ghz. there are about 30 ppl. – Seb Oct 12 '11 at 09:34
  • @Seb You have to run the trace yourself. You can start one by going to Tools > SQL Server Profiler. I like the TSQL template personally. And by 30 people, do you mean 30 concurrent connections? – Daniel Mallott Oct 12 '11 at 12:57

1 Answers1

0

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.

Chris W
  • 2,670
  • 1
  • 23
  • 32
  • 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: http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/ – Seb Oct 12 '11 at 09: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 09:39
  • @Seb Extra info added to answer. – Chris W Oct 12 '11 at 10:09