Solved

SQL Server Degrading on Write

Posted on 2016-09-29
13
67 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 28

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 47

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 69

Expert Comment

by:Scott Pletcher
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 28

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
 
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 28

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 47

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

803 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