Solved

SQL Server Degrading on Write

Posted on 2016-09-29
13
60 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 17

Expert Comment

by:Pawan Kumar Khowal
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 45

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 17

Accepted Solution

by:
Pawan Kumar Khowal 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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 17

Expert Comment

by:Pawan Kumar Khowal
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 45

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

17 Experts available now in Live!

Get 1:1 Help Now