May 27, 2014 at 11:18 am
Hello -
I am in the midst of some database tuning for a CRM 2011 database, and part of that effort is in attempting to get rid of what Brent Ozar's sp_BlitzIndex tool calls an 'Index Hoarder'. Quite literally - unused Non-Clustered indexes that are just taking up space on the database drive(s).
So - I wanted to see if there is any danger in removing default indexes in CRM. I am looking at usage from the standpoint of time and need (meaning since the last time the service was restarted - how frequently if at all are the indexes being used). I realize that some indexes could possibly be used for anything from Quarterly to Yearly reports, but my client has assured me that all reporting is done on another copy of the database that is shot over to another location via Replication.
Any insight into CRM's default indexes, and whether they can be deleted without worry, would be greatly appreciated. We have them all scripted out and archived (as with any piece of T-SQL code or DDL should be).
May 27, 2014 at 2:20 pm
SQL_ME_RICH (5/27/2014)
Hello -I am in the midst of some database tuning for a CRM 2011 database, and part of that effort is in attempting to get rid of what Brent Ozar's sp_BlitzIndex tool calls an 'Index Hoarder'. Quite literally - unused Non-Clustered indexes that are just taking up space on the database drive(s).
So - I wanted to see if there is any danger in removing default indexes in CRM. I am looking at usage from the standpoint of time and need (meaning since the last time the service was restarted - how frequently if at all are the indexes being used). I realize that some indexes could possibly be used for anything from Quarterly to Yearly reports, but my client has assured me that all reporting is done on another copy of the database that is shot over to another location via Replication.
Any insight into CRM's default indexes, and whether they can be deleted without worry, would be greatly appreciated. We have them all scripted out and archived (as with any piece of T-SQL code or DDL should be).
I would check with the vendor of whatever CRM you are using.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 27, 2014 at 3:49 pm
Hi Sean - Not sure what you mean by Vendor. As best I know it's Microsoft's product still, but do you perhaps mean reseller?
We do not deal with our reseller directly, so I am not sure.
May 28, 2014 at 2:24 am
If it's Microsoft's CRM (the vendor is Microsoft), then making any schema changes is a violation of the support agreements afaik, will probably result in you losing any support from MS and could cause problems with patches or upgrades.
If you insist on doing so, script all the indexes you drop and recreate them any time you need to call support or do upgrades/patches.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2014 at 7:00 am
SQL_ME_RICH (5/27/2014)
Hi Sean - Not sure what you mean by Vendor. As best I know it's Microsoft's product still, but do you perhaps mean reseller?We do not deal with our reseller directly, so I am not sure.
Just so you know, CRM is an acronym which stands for Customer Relationship Management. There are dozens of these applications out there, Microsoft Dynamics is one of them.
I agree 100% with Gail. Most vendors, including Microsoft, forbid schema changes in the service agreement. If you are unsure I would err on the side of caution and leave well enough alone.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2014 at 4:45 pm
Hi guys -
Yes - it's Microsoft's CRM (sorry for the assumption that you knew what I was asking about). Specifically - CRM 2011.
I know that SharePoint is as locked down as a program can be, but I had not seen any issues with the following (in fact - all I can find is that the following is not only recommended but can definitely boost performance for CRM):
- Non-Clustered Indexing.
- Data Compression (Row or Page).
But what I have not found is whether it would be detrimental to the CRM environment to get rid of indexing that is not being used actively by the system, that was part of the install (default indexing).
That really was my question - whether that would be safe to do or not (remove/delete them off said tables).
I have already taken Gail's recommendation into account by scripting everything out.
Thanks again
May 29, 2014 at 6:44 am
Would disabling the indexes be safer than deleting them ? If you found you needed them, enabling them would be faster than finding the code to run, and there's no risk that the scripted version is out of sync, lost, incomplete etc.
May 29, 2014 at 10:17 am
Hi homebrew01 -
The reason I need to delete them is they are taking up a massive amount of space, and I literally have thousands of indexes on this database that I have inherited. Based on usage statistics that I have been gather over the past 3 months - I can tell that some have never been accessed, while others have had little to no usage at all.
This is an OLTP front end system with a documentation store on the back end (no reporting), so I am just curious to know if anyone out there has worked with Microsoft Dynamics CRM 2011 before, and had any issues in doing this or not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply