SQL Server Degrading on Write

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?
LVL 1
CipherISAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
For that go ahead and rebuild the index.

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

Enjoy !
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ZberteocCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
CipherISAuthor Commented:
Is there a way to resolve the index issue in SQL Server?  I thought there was a menu item to help with this.
0
 
ZberteocCommented:
@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
 
Pawan KumarDatabase ExpertCommented:
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
 
ZberteocCommented:
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
 
CipherISAuthor Commented:
@Zberteoc

So what is the solution?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ZberteocCommented:
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
 
CipherISAuthor Commented:
Ok.  Thanks for the explanation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.