January 16, 2017 at 2:22 am
Dear All,
I have a set of relatively big tables with very high reads and writes occurring on them containing data over more than 8 years.
Currently on rush hours things become very slow and lots of deadlocks and timeouts happen also long time for index rebuild.
I have an idea and need your opinion on it please, to make things simple, i will just talk about one table, my idea is to create several instances of this table and each one will hold data for a specific year ex:Table2008,Table2009 etc, then i will create a view making union of all those tables to avoid any changes in system.
Will that solution fix my issue or if there are suggestions to other solutions please do tell me.
Thanks in advance
Best Regards
Nader
January 16, 2017 at 2:51 am
nadersam - Monday, January 16, 2017 2:22 AMDear All,I have a set of relatively big tables with very high reads and writes occurring on them containing data over more than 8 years.
Currently on rush hours things become very slow and lots of deadlocks and timeouts happen also long time for index rebuild.
I have an idea and need your opinion on it please, to make things simple, i will just talk about one table, my idea is to create several instances of this table and each one will hold data for a specific year ex:Table2008,Table2009 etc, then i will create a view making union of all those tables to avoid any changes in system.
Will that solution fix my issue or if there are suggestions to other solutions please do tell me.Thanks in advance
Best Regards
Nader
Without any knowledge of your data, and taking a hint from the fact that you are considering splitting the table by date, have you tried adding your date field as the leading field in your index, and ensuring that your queries include date or date range for filtering.
January 16, 2017 at 2:59 am
Have you looked at partitioning?
- Damian
January 16, 2017 at 3:00 am
DesNorton - Monday, January 16, 2017 2:51 AMnadersam - Monday, January 16, 2017 2:22 AMDear All,I have a set of relatively big tables with very high reads and writes occurring on them containing data over more than 8 years.
Currently on rush hours things become very slow and lots of deadlocks and timeouts happen also long time for index rebuild.
I have an idea and need your opinion on it please, to make things simple, i will just talk about one table, my idea is to create several instances of this table and each one will hold data for a specific year ex:Table2008,Table2009 etc, then i will create a view making union of all those tables to avoid any changes in system.
Will that solution fix my issue or if there are suggestions to other solutions please do tell me.Thanks in advance
Best Regards
NaderWithout any knowledge of your data, and taking a hint from the fact that you are considering splitting the table by date, have you tried adding your date field as the leading field in your index, and ensuring that your queries include date or date range for filtering.
most indexes on that table have the date field included but i have a problem in changing the queries, that site has an old version of the application and still planning for upgrade but will take a long time and we can't leave them in that state till upgrade happens.
January 16, 2017 at 3:11 am
DamianC - Monday, January 16, 2017 2:59 AMHave you looked at partitioning?
not really i dont have much knowledge in that area, and all i wanted is to do that for 3 tables only, do you suggest that instead?
January 16, 2017 at 3:11 am
nadersam - Monday, January 16, 2017 3:00 AMDesNorton - Monday, January 16, 2017 2:51 AMnadersam - Monday, January 16, 2017 2:22 AMDear All,I have a set of relatively big tables with very high reads and writes occurring on them containing data over more than 8 years.
Currently on rush hours things become very slow and lots of deadlocks and timeouts happen also long time for index rebuild.
I have an idea and need your opinion on it please, to make things simple, i will just talk about one table, my idea is to create several instances of this table and each one will hold data for a specific year ex:Table2008,Table2009 etc, then i will create a view making union of all those tables to avoid any changes in system.
Will that solution fix my issue or if there are suggestions to other solutions please do tell me.Thanks in advance
Best Regards
NaderWithout any knowledge of your data, and taking a hint from the fact that you are considering splitting the table by date, have you tried adding your date field as the leading field in your index, and ensuring that your queries include date or date range for filtering.
most indexes on that table have the date field included but i have a problem in changing the queries, that site has an old version of the application and still planning for upgrade but will take a long time and we can't leave them in that state till upgrade happens.
Having the date field included in the index is not going to improve performance of reads. It needs to be in the actual index fields.
As a starting point, you want to have something like
CREATE NONCLUSTERED INDEX IndexName
ON TableName (FieldsUsedInWhere, FieldsUsedInJoin)
INCLUDE (FieldsRequiredForOutput)
Also, note that the order of the fields is also important. However, the order is very dependent on the queries that use the index.
January 16, 2017 at 3:14 am
nadersam - Monday, January 16, 2017 3:11 AMDamianC - Monday, January 16, 2017 2:59 AMHave you looked at partitioning?not really i dont have much knowledge in that area, and all i wanted is to do that for 3 tables only, do you suggest that instead?
From your description, it does not sound like partitioning will give you any improvement.
Take a look at this article as a starting point
https://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/
January 16, 2017 at 3:19 am
DesNorton - Monday, January 16, 2017 3:11 AMnadersam - Monday, January 16, 2017 3:00 AMDesNorton - Monday, January 16, 2017 2:51 AMnadersam - Monday, January 16, 2017 2:22 AMDear All,I have a set of relatively big tables with very high reads and writes occurring on them containing data over more than 8 years.
Currently on rush hours things become very slow and lots of deadlocks and timeouts happen also long time for index rebuild.
I have an idea and need your opinion on it please, to make things simple, i will just talk about one table, my idea is to create several instances of this table and each one will hold data for a specific year ex:Table2008,Table2009 etc, then i will create a view making union of all those tables to avoid any changes in system.
Will that solution fix my issue or if there are suggestions to other solutions please do tell me.Thanks in advance
Best Regards
NaderWithout any knowledge of your data, and taking a hint from the fact that you are considering splitting the table by date, have you tried adding your date field as the leading field in your index, and ensuring that your queries include date or date range for filtering.
most indexes on that table have the date field included but i have a problem in changing the queries, that site has an old version of the application and still planning for upgrade but will take a long time and we can't leave them in that state till upgrade happens.
Having the date field included in the index is not going to improve performance of reads. It needs to be in the actual index fields.
As a starting point, you want to have something like
CREATE NONCLUSTERED INDEX IndexName
ON TableName (FieldsUsedInWhere, FieldsUsedInJoin)
INCLUDE (FieldsRequiredForOutput)
Also, note that the order of the fields is also important. However, the order is very dependent on the queries that use the index.
The table is already over indexed and contain all needed indexes for different queries.
January 16, 2017 at 4:45 pm
nadersam - Monday, January 16, 2017 3:19 AMDesNorton - Monday, January 16, 2017 3:11 AMnadersam - Monday, January 16, 2017 3:00 AMDesNorton - Monday, January 16, 2017 2:51 AMnadersam - Monday, January 16, 2017 2:22 AMDear All,I have a set of relatively big tables with very high reads and writes occurring on them containing data over more than 8 years.
Currently on rush hours things become very slow and lots of deadlocks and timeouts happen also long time for index rebuild.
I have an idea and need your opinion on it please, to make things simple, i will just talk about one table, my idea is to create several instances of this table and each one will hold data for a specific year ex:Table2008,Table2009 etc, then i will create a view making union of all those tables to avoid any changes in system.
Will that solution fix my issue or if there are suggestions to other solutions please do tell me.Thanks in advance
Best Regards
NaderWithout any knowledge of your data, and taking a hint from the fact that you are considering splitting the table by date, have you tried adding your date field as the leading field in your index, and ensuring that your queries include date or date range for filtering.
most indexes on that table have the date field included but i have a problem in changing the queries, that site has an old version of the application and still planning for upgrade but will take a long time and we can't leave them in that state till upgrade happens.
Having the date field included in the index is not going to improve performance of reads. It needs to be in the actual index fields.
As a starting point, you want to have something like
CREATE NONCLUSTERED INDEX IndexName
ON TableName (FieldsUsedInWhere, FieldsUsedInJoin)
INCLUDE (FieldsRequiredForOutput)
Also, note that the order of the fields is also important. However, the order is very dependent on the queries that use the index.The table is already over indexed and contain all needed indexes for different queries.
Are all the indexes actually being used? Checkout the sys.dm_db_index_usage_stats system view. Keep in mind that you should not disable or delete any UNIQUE indexes even if they appear to be unused. For more information on the sys.dm_db_index_usage_stats system view, please see the following URL. https://www.google.com/?gws_rd=ssl#q=sys.dm_db_index_usage_stats+example
Also, if queries that use indexes that are already available seem to be slow or cause a lot of blocking, make double sure that stats on the table are being rebuilt on a regular basis.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2017 at 11:50 pm
Jeff Moden - Monday, January 16, 2017 4:45 PMnadersam - Monday, January 16, 2017 3:19 AMDesNorton - Monday, January 16, 2017 3:11 AMnadersam - Monday, January 16, 2017 3:00 AMDesNorton - Monday, January 16, 2017 2:51 AMnadersam - Monday, January 16, 2017 2:22 AMDear All,I have a set of relatively big tables with very high reads and writes occurring on them containing data over more than 8 years.
Currently on rush hours things become very slow and lots of deadlocks and timeouts happen also long time for index rebuild.
I have an idea and need your opinion on it please, to make things simple, i will just talk about one table, my idea is to create several instances of this table and each one will hold data for a specific year ex:Table2008,Table2009 etc, then i will create a view making union of all those tables to avoid any changes in system.
Will that solution fix my issue or if there are suggestions to other solutions please do tell me.Thanks in advance
Best Regards
NaderWithout any knowledge of your data, and taking a hint from the fact that you are considering splitting the table by date, have you tried adding your date field as the leading field in your index, and ensuring that your queries include date or date range for filtering.
most indexes on that table have the date field included but i have a problem in changing the queries, that site has an old version of the application and still planning for upgrade but will take a long time and we can't leave them in that state till upgrade happens.
Having the date field included in the index is not going to improve performance of reads. It needs to be in the actual index fields.
As a starting point, you want to have something like
CREATE NONCLUSTERED INDEX IndexName
ON TableName (FieldsUsedInWhere, FieldsUsedInJoin)
INCLUDE (FieldsRequiredForOutput)
Also, note that the order of the fields is also important. However, the order is very dependent on the queries that use the index.The table is already over indexed and contain all needed indexes for different queries.
Are all the indexes actually being used? Checkout the sys.dm_db_index_usage_stats system view. Keep in mind that you should not disable or delete any UNIQUE indexes even if they appear to be unused. For more information on the sys.dm_db_index_usage_stats system view, please see the following URL. https://www.google.com/?gws_rd=ssl#q=sys.dm_db_index_usage_stats+example
Also, if queries that use indexes that are already available seem to be slow or cause a lot of blocking, make double sure that stats on the table are being rebuilt on a regular basis.
Thank you Jeff i will check all this
January 23, 2017 at 2:54 pm
Look what date column is used for range selection (like BETWEEN @StartDate and @EndDate) and make that column a first one in a clustered index.
Note - changing a clustered index requires a lot of time and free disk space (about twice as much as the table occupies).
Before you proceed with it on Prod do it in Test environment on a copy of Prod.
This will give you an estimation of the outage time required to complete the task.
You'll also be able to see how effective will be the change.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply