| Author |
Topic |
|
mojavewheeler
Starting Member
3 Posts |
 Posted - 2014-05-30 : 17:31:10
|
| Hi guys I'm having a problem with a slow sql server, it used to be lightning speed but now seems to be slowing down down down...The first thing I thought of were the indexes, but after running DBREINDEX on each table in the database it appears that the majority of the indexes still have high rates of "Extent Scan Fragmentation", about of 30-40% on average, some higher than 50%.Here's a typical result before and after running DBREINDEX:DBCC SHOWCONTIG scanning 'OnlineBookingOrders' table...Table: 'OnlineBookingOrders' (1625525320); index ID: 1, database ID: 8TABLE level scan performed.- Pages Scanned................................: 13919- Extents Scanned..............................: 1789- Extent Switches..............................: 3815- Avg. Pages per Extent........................: 7.8- Scan Density [Best Count:Actual Count].......: 45.60% [1740:3816]- Logical Scan Fragmentation ..................: 38.42%- Extent Scan Fragmentation ...................: 88.93%- Avg. Bytes Free per Page.....................: 5281.3- Avg. Page Density (full).....................: 34.75%DBCC execution completed. If DBCC printed error messages, contact your system administrator.Table OnlineBookingOrders successfully reindexedDBCC SHOWCONTIG scanning 'OnlineBookingOrders' table...Table: 'OnlineBookingOrders' (1625525320); index ID: 1, database ID: 8TABLE level scan performed.- Pages Scanned................................: 5234- Extents Scanned..............................: 656- Extent Switches..............................: 655- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.85% [655:656]- Logical Scan Fragmentation ..................: 0.06%- Extent Scan Fragmentation ...................: 38.11%- Avg. Bytes Free per Page.....................: 600.5- Avg. Page Density (full).....................: 92.58%DBCC execution completed. If DBCC printed error messages, contact your system administrator.And here's the command I used: dbcc dbreindex (OnlineBookingOrders, '', 90)From my understanding, the Extent Scan Fragmentation should be close to zero. What else can I do? BTW I am running SQL Server 2005 but the database itself has a compatibility mode set to 2000 only. Should I try increasing the compatibility mode? Will this break anything?Thank you guys so much for your help! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-30 : 17:57:03
|
| I would take a step back to determine where the bottleneck is. What are the waits showing? PerfMon? Blocking? Missing indexes? I would bet money that fragmentation is not causing a performance issue.Do not change the compatibility level. Yes it can break things. Changing the compatibility level would ideally mean a full regression test be performed for the application(s).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
mojavewheeler
Starting Member
3 Posts |
Posted - 2014-05-30 : 19:19:51
|
| Hi Tara,Thanks for the reply. I have not performed any other analysis other than looking at the fragmentation. I'm a developer by trade vs. a dba. Can you recommend where I would start, either with an application or the sql tools? I'm not familiar with perfmon or blocking? Some web pages load quick while others are being delayed. It would be great to be able to narrow this down. What would you recommend? I'll take your advice and not fiddle with the compatibility level that's for sure. Thank you again. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-02 : 15:04:17
|
| Aside from good advice already given - do you run a regular maintenance on the SQL Server - including a statistics update. Have the response times got slower because workload has increased or has the response time slowed down with increase workload?Have any changes occured recently ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
mojavewheeler
Starting Member
3 Posts |
Posted - 2014-06-03 : 10:55:57
|
| I have not run a statistics update. The load of the server has not changed. The server itself has relatively low usage. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-03 : 14:12:10
|
| Your first priority is to identify the cause(s) of the slower performance. Sometimes this can be detailed work , sometimes trial and error. A simple method to use for emergency situations is outlined in this post - http://www.sqlserver-dba.com/2008/08/rapid-tuning.htmlIf you don't have a regaular maintenance procedure , I'd suggest you progress with implementing. Particuarly if data changes regularlyJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-03 : 16:29:50
|
While I agree with the update stats recommendation, it does not seem like that's the issue here if DBCC DBREINDEX didn't help. Well I suppose it would depend on the sampling though. You could try a FULLSCAN. quote:
DBCC DBREINDEX rebuilds statistics automatically during the rebuild of the indexes.
What version of SQL Server are you using? And which service pack and hotfix/CU? (Run "SELECT @@ VERSION" minus the space after @@)Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-04 : 00:43:30
|
| A FULL SCAN for UPDATE STATISTICS might be useful under the circumstances. Although this shouldn't replace root cause analysis. Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
Fragmentation Problems