October 21, 2015 at 3:33 am
I am making all the environments in sync as far as database indexes are concerned .
My approach -
Compared index differences using 3rd party tool
Now deleting the indexes which are not used much and present in only few environments
At the same time , creating the missing indexes manually one by one which are not present in some instances while all other environments have it
Is there a simpler and better approach to this ?
Thanks
October 21, 2015 at 8:34 am
Not really.
I'm confused by "different environments" though. Are we talking about Development, Testing and Production as "different environments"? Or are we saying, I've got one database on five different production servers, "different environments"?
If the first thing, then it sounds like there's something up with your deployment processes. In addition to cleaning things up, I'd focus on the process to ensure that you have a clearly defined method for getting things from Dev into Production through Test. You shouldn't have large variations on indexes (or any other object) except as part of known release and deployment processes.
If the second thing, your approach becomes a little more problematic. Are these databases identical in terms of use and data set? If so, then yeah, clean up the indexes. It doesn't make sense that there would be variations. Or, are these databases used different ways by different applications with different sets of data and the implied differences in statistics? Then, making them all the same may not actually make sense. Maybe an index was added to ServerA that wasn't on ServerB because a different set of queries are run against ServerA. If you're in this situation, more understanding is necessary before you just start dropping indexes. Also, I would strongly recommend you capture a script of all the indexes on all the different servers just in case you need to recreate them.
"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
October 22, 2015 at 2:15 am
Not really.
I'm confused by "different environments" though. Are we talking about Development, Testing and Production as "different environments"? Or are we saying, I've got one database on five different production servers, "different environments"?
[highlight=#ffff11]I mean servers in different environments as in dev test prod and dr [/highlight]
If the first thing, then it sounds like there's something up with your deployment processes. In addition to cleaning things up, I'd focus on the process to ensure that you have a clearly defined method for getting things from Dev into Production through Test. You shouldn't have large variations on indexes (or any other object) except as part of known release and deployment processes.
If the second thing, your approach becomes a little more problematic. Are these databases identical in terms of use and data set?
[highlight=#ffff11]Yes applications using them and how the databases are being used are quite identical , hence these changes [/highlight]
If so, then yeah, clean up the indexes. It doesn't make sense that there would be variations. Or, are these databases used different ways by different applications with different sets of data and the implied differences in statistics? Then, making them all the same may not actually make sense. Maybe an index was added to ServerA that wasn't on ServerB because a different set of queries are run against ServerA. If you're in this situation, more understanding is necessary before you just start dropping indexes. Also, I would strongly recommend you capture a script of all the indexes on all the different servers just in case you need to recreate them.
[highlight=#ffff11]Can you help me with a sweet short script to script out all the indexes on a table before making the changes . Believe this would be quite helpful . Thanks for that [/highlight]
October 22, 2015 at 4:03 am
Sorry. I don't have something like that at my finger tips. I generally keep everything in source control and do all deployments from there. If you do a search online there are scripts out there that will do what you need.
Since you're talking about Dev/Test/Prod environments, I strongly recommend you examine your deployment processes. You really shouldn't be seeing crazed variation between those environments such that you don't know what belongs where.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply