Solved

SQL Server Degrading on Write

Posted on 2016-09-29
13
63 Views
Last Modified: 2016-10-19
I am curious.  If you have a table with 1 clustered index and 5 non-clustered indexes.  After a period of time the write to the table starts degrading.  What would cause it?  How would you troubleshoot it?  Fix it?
0
Comment
Question by:CipherIS
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41822837
Fragmentation will be the issue.

You need update the stats on the table . You also need to rebuild the indexes.

 

--

UPDATE STATISTICS tableName

--

 

Open in new window


--

USE DBName
GO
ALTER INDEX ALL ON TableName REBUILD
GO

--

Open in new window


Pawan!!
1
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41823054
After a period of time the write to the table starts degrading.  What would cause it?
If you're seeing to many page splits that means your Clustered Index is not being well chosen.

If you also facing read performance then it means you're not reindexing regularly and you should start to do so.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41823394
Use this free tool to analyze the fragmentation:

https://www.idera.com/productssolutions/freetools/sqlfragmentationanalyzer

It will show you the indexes in red, yellow and green meaning high, medium and low fragmentation. The ones in red you need to RBUILD the ones in yellow you need to REORGANIZE and the ones in green are good. You can also see them with this queries:

-- Ola Hallengreen version used also in his checkindex procedure
--/*
if object_id('tempdb..#idx_frg') is not null
	drop table #idx_frg
SELECT 
	DB_NAME(DB_ID()) AS DatabaseName,
    schemas.[name] AS SchemaName,
    objects.[name] AS ObjectName,
    indexes.[name] AS IndexName,
    objects.type_desc AS ObjectType,
    indexes.type_desc AS IndexType,
    dm_db_index_physical_stats.partition_number AS PartitionNumber,
    dm_db_index_physical_stats.page_count AS [PageCount],
    right('____'+cast(round(cast(dm_db_index_physical_stats.avg_fragmentation_in_percent as decimal(8,2)),2) as varchar),6) AS AvgFagmentationInPercent
into #idx_frg
FROM 
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
	INNER JOIN sys.indexes indexes 
		ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] 
		AND dm_db_index_physical_stats.index_id = indexes.index_id
	INNER JOIN sys.objects objects 
		ON indexes.[object_id] = objects.[object_id]
	INNER JOIN sys.schemas schemas 
		ON objects.[schema_id] = schemas.[schema_id]
WHERE 
	objects.[type] IN('U','V')
	AND objects.is_ms_shipped = 0
	AND indexes.[type] IN(1,2,3,4)
	AND indexes.is_disabled = 0
	AND indexes.is_hypothetical = 0
	AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
	AND dm_db_index_physical_stats.index_level = 0
	AND dm_db_index_physical_stats.page_count >= 1000
	--and indexes.[name]='location'
	--and objects.[name]='Photo'
order by 
	DatabaseName,
	SchemaName,
	ObjectName,
	IndexName
select 
	*, 
	case 
		when replace(AvgFagmentationInPercent,'_','') between 10.00 and 29.99 then 'Reindex' 
		else 'Rebuild' end as 'Action' 
from 
	#idx_frg where replace(AvgFagmentationInPercent,'_','')>10.00 
order by 
	AvgFagmentationInPercent desc

--*/	



-- IDERA tool version
/*	
SELECT 
	i.name, 
	i.type, 
	dmf.page_count*8 AS 'size', 
	ROUND(dmf.avg_fragmentation_in_percent,0) AS avg_fragmentation_in_percent, 
	i.fill_factor 
FROM 
	sys.dm_db_index_physical_stats (DB_ID('StreetScapeOperations'), null, null, null, 'limited') dmf 
	INNER JOIN sys.indexes i 
		on 
			i.object_id		= dmf.object_id 
			AND i.index_id	= dmf.index_id
where 
	dmf.alloc_unit_type_desc = 'IN_ROW_DATA'
--*/

Open in new window


If you want this task taken care of automatically with now headaches then you shoul duse Ola Hallegre's IndexOptimisation procedure and SQL job, also free and extremely simple to install and use:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
1
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41823813
Existing fragmentation would likely not cause write slowdowns, rather than tends to cause read slowdowns.

You need to verify that you have the best clustering key column(s) for the table, that the correct fillfactor is specified, and that you don't have nonclustered indexes you don't need and/or that have too many INCLUDEd columns.

Are the main table columns expanded later, esp. those columns that have nonclus indexes on them?  For example, is a varchar(100) column, say, left empty on initial insert, then updated later to 60  bytes?  That type of thing can force page splits and/or use of overflow, which can harm performance as it occurs.

Also, if index compression is available, often that can help tremendously.
1
 
LVL 1

Author Comment

by:CipherIS
ID: 41824777
Is there a way to resolve the index issue in SQL Server?  I thought there was a menu item to help with this.
0
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41824805
For that go ahead and rebuild the index.

Go to SSMS -> DB -> TableName -> Indexes -> Choose Index -> Right Click -> Rebuild Index.

Enjoy !
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 41826231
@CipherIS

No offense but you accepted the least recommendable solution. This will not solve your problem going further but only for now. In couple of weeks your problem will come back. Also you seemed to have ignored previous solutions that would have solved the problem for good!
1
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41826254
Yes, We should have Index and Stats Maintenance Plan in place.

Basically a script which will run over a weekend and will rebuild all the indexes of all the tables and update column stats.

Please let me know if you need those scripts.

Enjoy !!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41826300
Not over the week-end but daily. The beauty of Ola Hallegren's procedure is that it looks for fragmentation and will only apply the right command depending on that without wasting resources on indexes that need no treatment at all. And will also take care of the stats as well in one simple step. Because of this you can schedule daily index optimization maintenance without the risk of pressure or overload on resources.  SQL Maintenance Plans don't have that ability unless you use extra scripts on top of it.

I have stated that in my post and if the asker would have read the details in the link he would have understood even better. Instead he chose to go with the simplest approach but not the best by far. It is his right, though. :)
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41847686
@Zberteoc

So what is the solution?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41847790
So what is the solution?
At least myself and Zberteoc already told what is the solution.

My comment: "If you also facing read performance then it means you're not reindexing regularly and you should start to do so."

Zberteoc's: "If you want this task taken care of automatically with now headaches then you shoul duse Ola Hallegre's IndexOptimisation procedure and SQL job, also free and extremely simple to install and use:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html"
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41848269
The solution is that you should put in place a regularly scheduled index optimization maintenance plan and not do it manually. I recommended you Ola Hallegren solution for reasons I explained.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 41850649
Ok.  Thanks for the explanation.
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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

23 Experts available now in Live!

Get 1:1 Help Now