November 12, 2014 at 8:08 am
Hallo Everyone
I have a small database of CMS system which does not have any primary key, relations or cluster indexes. few none cluster indexes has been created from vendor. i have seen that page fragmentation of few tables exceed 90% where page fullness ist between 70 and 85%. Database tables contains maximum of 30 to 50 thousand rows with maximum of 10 columns in each table. Database is really very simple.
I am using idera fragmentation analyser which show me fragmentation where index name is null.
my questions are following?
1) why a cluster index is necessary? does it improve the performance of select statements?
2) What effect it has if we have one cluster index but many none cluster indexes? according to following blog it can even create extra performance problems instead of solving it.
http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key
3)How good is to create a cluster or none cluster index on GUID ID field?
I am using SQL Server Maintenance plan to rebuild indexes every day but still fragmentation grows up quite rapidly (in couple of hours again over 90%).
i have created cluster index on each table and it reduce fragmentation even under 5% but later i have deleted cluster indexes as i dont know eitherit really improve the performance.
Thanks in advance
Regards
Zahid
November 12, 2014 at 8:58 am
zahid.nawaz (11/12/2014)
Hallo EveryoneI have a small database of CMS system which does not have any primary key, relations or cluster indexes. few none cluster indexes has been created from vendor. i have seen that page fragmentation of few tables exceed 90% where page fullness ist between 70 and 85%. Database tables contains maximum of 30 to 50 thousand rows with maximum of 10 columns in each table. Database is really very simple.
I am using idera fragmentation analyser which show me fragmentation where index name is null.
my questions are following?
1) why a cluster index is necessary? does it improve the performance of select statements?
Yes a clustered index is necessary on most tables. It does generally help performance. Sometimes more than others.
2) What effect it has if we have one cluster index but many none cluster indexes? according to following blog it can even create extra performance problems instead of solving it.
http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key
It depends. It's not the Clustered index but instead the quantity of other indexes. The NC indexes have a part of the clustered index in each of them for lookups. So a CI can impact the size of those NCIs. There is a cost for inserts/updates/deletes with indexes, but that has to be offset with how much reading you do. In other words, you need to know your data.
3)How good is to create a cluster or none cluster index on GUID ID field?
It is a terrible idea to put a clustered index on a guid.
I am using SQL Server Maintenance plan to rebuild indexes every day but still fragmentation grows up quite rapidly (in couple of hours again over 90%).
i have created cluster index on each table and it reduce fragmentation even under 5% but later i have deleted cluster indexes as i dont know eitherit really improve the performance.
Problem 1 is that you are using the maintenance plan which is more of a brute force attack.
Problem 2 is that you have Clustered indexes on GUIDs. You want to be fighting fragmentation issues all day, then use a Clustered Index on a GUID. GUIDs cause larger indexes and cause excessive fragmentation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 12, 2014 at 9:10 am
zahid.nawaz (11/12/2014)
Hallo EveryoneI have a small database of CMS system which does not have any primary key, relations or cluster indexes. few none cluster indexes has been created from vendor. i have seen that page fragmentation of few tables exceed 90% where page fullness ist between 70 and 85%. Database tables contains maximum of 30 to 50 thousand rows with maximum of 10 columns in each table. Database is really very simple.
I am using idera fragmentation analyser which show me fragmentation where index name is null.
my questions are following?
1) why a cluster index is necessary? does it improve the performance of select statements?
2) What effect it has if we have one cluster index but many none cluster indexes? according to following blog it can even create extra performance problems instead of solving it.
http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key
3)How good is to create a cluster or none cluster index on GUID ID field?
I am using SQL Server Maintenance plan to rebuild indexes every day but still fragmentation grows up quite rapidly (in couple of hours again over 90%).
i have created cluster index on each table and it reduce fragmentation even under 5% but later i have deleted cluster indexes as i dont know eitherit really improve the performance.
Thanks in advance
Regards
Zahid
I suppose the first question is your CMS system a third party system or is it something your company has developed in-house? Typically third party systems licensing doesn't let you change things like that in the database.
If you can change things, then yes clustered indexes definitely are a good thing in SQL Server:
http://www.sqlservercentral.com/search/?q=clustered+index&t=a&sort=relevance
Some quick answers to your questions:
1) If clustered index columns are aligned with how the table is most frequently accessed, like a primary key, then they can definitely improve performance. Clustered indexes guide how your data is physically stored in the table so a good clustered index can mean fewer reads need to take place to get the results for a query.
2) Having many non-clustered indexes performance will vary depending on the table. Non-clustered indexes can improve performance in several cases such as frequently used foreign key columns, "covering indexes" that include columns used in a query, and columns that are frequently used in a WHERE clause of your queries. Some things to watch are if the non-clustered indexes are actually being used or not:
http://msdn.microsoft.com/en-us/library/ms188755.aspx
and make sure that you don't have redundant indexes on the same columns.
3) GUID or UniqueIdentifier columns are well known for creating fragmentation problems in clustered indexes. Typically you want your clustered indexes on columns that are ever increasing in value, such as an identity column. There are many good resources here on sqlservercentral.com and across the web for improving your approach to designing indexes.
November 12, 2014 at 10:15 am
zahid.nawaz (11/12/2014)
I am using SQL Server Maintenance plan to rebuild indexes every day but still fragmentation grows up quite rapidly (in couple of hours again over 90%).
This means you are regularly doing inserts in the middle of a page in the index(es) causing page splits. You can help re-mediate that by adjusting the fill factor on your indexes to leave more free space on the pages.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2014 at 4:37 am
zahid.nawaz (11/12/2014)
2) What effect it has if we have one cluster index but many none cluster indexes? according to following blog it can even create extra performance problems instead of solving it.
Every nonclustered index you add creates additional work when you add/edit/delete data within the table since the values stored in those indexes will have to modified in addition to the data stored with the clustered index (or the heap for tables that don't have a cluster, and I'm with Jason, most, read 99%, should have one). So, yes, you want to be careful about adding indexes. That's not to say you can't add them, but you want to ensure that you only add the right ones.
People usually make three errors here. First, they run the Database Tuning Advisor and then do everything it says to do without looking at what it said. This leads to lots of indexes that have the same key columns, the columns that define the index, not counting any columns using the INCLUDE word. Second, they look at the missing index DMVs and apply every one of them without looking at, or thinking about, what they're doing. This also leads to lots of indexes with duplicate key columns. Third, putting an index on every single column in the table. This leads to tons of indexes that are never used.
If you've done any of these, you need to very carefully go back and reconsider each of the indexes you've created to understand if it duplicates another index. I would strongly suggest very carefully eliminating non-clustered indexes that have repeating key columns, especially ones that have the first column in the key being the same.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 13, 2014 at 5:20 am
Hi Everybody
Thanks a lot for useful information. now i have understand the CI and NCI better than before.
@chris-2 Harshman: yes its a third party CMS system (Sitecore).
@SQLRNNR: why maintenance plan is brute force attack? any alternatives to auto rebuild only needed indexes? one thing what come in my mind is to create a stored procedure to rebuild selective only those indexes where fragmentation is over 30%. SP can be configured to call through Server agent task.
if any one have any script which can do this stuff, than most welcome.
@jack-2 Corbett: what do u mean about to adjust fill factor? should it not be 80% of page?
Kind regards
Zahid
November 13, 2014 at 5:44 am
I'd suggest taking a look at the Minion scripts[/url] by the Midnight DBA team. That gives you a lot more control than the maintenance plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 13, 2014 at 6:44 am
zahid.nawaz (11/13/2014)
Corbett: what do u mean about to adjust fill factor? should it not be 80% of page?
You state that the pages are 80% full when you check fragmentation, but that doesn't mean that your fill factor is set at 80%. What does this query return:
SELECT
OBJECT_SCHEMA_NAME(I.object_id) AS schemaName,
OBJECT_NAME(I.object_id) AS tableName,
I.name AS indexName,
I.fill_factor
FROM
sys.indexes AS I
WHERE
OBJECTPROPERTY(I.object_id, 'IsMSShipped') = 0;
You should also read this article, http://msdn.microsoft.com/en-us/library/ms177459(v=sql.105).aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2014 at 7:37 am
zahid.nawaz (11/13/2014)
@SQLRNNR
: why maintenance plan is brute force attack? any alternatives to auto rebuild only needed indexes? one thing what come in my mind is to create a stored procedure to rebuild selective only those indexes where fragmentation is over 30%. SP can be configured to call through Server agent task.if any one have any script which can do this stuff, than most welcome.
I say brute force because the Maint Plan in SSMS comes with no intelligence in it at all. It forces a rebuild of all indexes whether it is needed or not.
As for a better method, there are any number of good scripts on the internet available for download that will perform many times better than a maint plan. The same as Grant, I would recommend Minion Reindex.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2014 at 7:40 am
Here is the result of Fill Factor query.
schemaName, tablename, indexname, fillfactor
dbo Items NULL 0
dbo Items ndxID 80
dbo Items ndxName 80
dbo Items ndxParentID 80
dbo Items ndxTemplateID80
this is just one table, all other tables show similar result. if they have any non cluster index, than it has a fill factor of 80%
Note that currently table does not have any Cluster Index or primary key etc. i am really interested to know the first entry with fillfactor of 0%. what is this? why it does not have any index name? is this some thing when a table is saved as heap, it has a null index default?
Thanks in advance
Regards
November 13, 2014 at 7:56 am
zahid.nawaz (11/13/2014)
Here is the result of Fill Factor query.schemaName, tablename, indexname, fillfactor
dbo Items NULL 0
dbo Items ndxID 80
dbo Items ndxName 80
dbo Items ndxParentID 80
dbo Items ndxTemplateID80
this is just one table, all other tables show similar result. if they have any non cluster index, than it has a fill factor of 80%
Note that currently table does not have any Cluster Index or primary key etc. i am really interested to know the first entry with fillfactor of 0%. what is this? why it does not have any index name? is this some thing when a table is saved as heap, it has a null index default?
Thanks in advance
Regards
The one with no name is the table (heap) itself and should have an index_id = 0.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 14, 2014 at 8:41 am
It can be easy to get stuck in making sure that a process works, and lose sight of the reason you are doing the work.
In general eliminating fragmentation is a 'good thing' because this tends to improve performance. However, trying to do this with vendor applications, particularly ones that use GUIDS as primary keys can be a difficult job and may not gain you much.
With a CMS such as SiteCore, I would want to look at the actual performance issues and what can be done to fix these issues, and not try to implement a general solution such as regular index rebuilds.
Try to work out what is the most pressing performance issue and look at solving that. Repeat this process until your users are happy with performance. This is called a Pareto Analysis.
It may be that you can solve some or all of your problems by adding an index to a table. But be aware of what others have said about adding indexes that duplicate each other or which are unlikely to be used by SQL Server.
Also, as this is a supported vendor application, ask the vendor for help - that why you pay for support. SiteCore may already know the best solution to some of the problems you are having.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 14, 2014 at 3:13 pm
Grant Fritchey (11/13/2014)
First, they run the Database Tuning Advisor and then do everything it says to do without looking at what it said. This leads to lots of indexes that have the same key columns, the columns that define the index, not counting any columns using the INCLUDE word. Second, they look at the missing index DMVs and apply every one of them without looking at, or thinking about, what they're doing. This also leads to lots of indexes with duplicate key columns. Third, putting an index on every single column in the table. This leads to tons of indexes that are never used.If you've done any of these, you need to very carefully go back and reconsider each of the indexes you've created to understand if it duplicates another index. I would strongly suggest very carefully eliminating non-clustered indexes that have repeating key columns, especially ones that have the first column in the key being the same.
Not too mention the valid indexes it will usually drop for you!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 17, 2014 at 3:38 am
Thanks everybody for sharing your knowledge and experience. Today i have installed minion re-index script and run the jobs for my database, it did't improve too much. Comparing to maintenance plan minion is good because it only rebuild or reorganize needed indexes.
i am not sure either minion can decide which index need to be rebuild and which just need to be reorganized?
But the problem remain same, as data in tables is stored as heap, with few NC indexes, fragmentation remain for few tables between 50-90%. i already asked Sitecore support, why don't they have CI on table, they said with too many insert, delete and update operations they had with CI performance drawbacks instead of a boost. Therefore they removed CI on current version of Sitecore 🙁
Ok, Database have now some Fragmentation issues but the real problem leis somewhere in Data Caching. i think if Sitecore implement intelligent data caching, they can overcome lot of performance issues.
Now my question is how it would be if i create a second ID Field integer in tables with auto increment, and create CI on this ID Field.
It will change a table from heap to CI Ordered Storage. insert or delete will be organized at the right place in cluster index. i am sure it will reduce the fragmentation but how it will react in Long Run?
i know adding new field in tables of a vendor application is not a good idea because of maintainability etc. but i can at least share my experience to Sitecore and make a suggestion.
Thanks and kind regards
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply