BArmstrong
Member
I've had the good fortune to beta test the next release of PatWel's ObjectBrowser. This came about clear out of the blue:
Ours is a smallish shop and developers wear many hats, including the CNC and DBA flavors. Not having a dedicated DBA, some aspects of db maintenance have gone without the proper attention, so recently we obtained IDERA's Defrag Manager to help out.
Here's the thing: we have noticed in the past that sometimes the SQL schemas for tables and indexes can get out of sync with the OW table specs. ...So, our manager has made this stipulation: before a defrag (reindex) window is run on a production database, first the OW spec must be compared to the SQL table schema. Then, after the maintenance window, the 'after' SQL spec must be compared to the 'before' SQL spec to assure no indexes were lost.
Now, I've been a long-time user of Everest Software's OneAssist, which is an excellent product. However, I discovered that (at least, in our system) it occasionally misreported some minor aspects of the OW table specs, namely that sometimes the ascending or descending sort was not reported correctly. Too minor to worry about? Not in my case, because I also discovered a cute little undocumented feature of OW: the SQL Primary Key created by OW ALWAYS has all columns sorted ascending. But, if the defined Primary Key in OW has a mixture of ascending and descending sorted columns, OW creates a second extra index in SQL Server with the sort mixture. (BTW if all columns are descending, there is no extra index. Go figure.) So, with my requirement of matching index counts before the defrag operation can be turned loose, I had to have an accurate analysis, allowing for the occasional extra index situation.
Figuring I had little to lose by asking, I emailed PatWel ([email protected]) to see if a command-line feature was in their future plans for ObjectBrowser. It was less than half an hour when Craig Welton of PatWel replied: "Thanks for your feedback. You are the second person this week to ask for the same thing. It will be the next thing I work on. Would you be willing to help test it?"
I really felt that here was a guy that cared about the users. I immediately replied that I would be honored to do so, and it wasn't long before he sent me his first pass. Because he had added this feature, I was able to develop a little VB.NET program that calls his program and then reads that output, counting the indexes and creating a SQL script file with which to load into a SQL table. Then, with other SQL stored procedures, I gather the SQL index counts, and compare them to the OW index counts by table.
What could have been a recurring drudge of a manual job can now be totally automated.
Kudos and thanks to Craig Welton and PatWel.
Ours is a smallish shop and developers wear many hats, including the CNC and DBA flavors. Not having a dedicated DBA, some aspects of db maintenance have gone without the proper attention, so recently we obtained IDERA's Defrag Manager to help out.
Here's the thing: we have noticed in the past that sometimes the SQL schemas for tables and indexes can get out of sync with the OW table specs. ...So, our manager has made this stipulation: before a defrag (reindex) window is run on a production database, first the OW spec must be compared to the SQL table schema. Then, after the maintenance window, the 'after' SQL spec must be compared to the 'before' SQL spec to assure no indexes were lost.
Now, I've been a long-time user of Everest Software's OneAssist, which is an excellent product. However, I discovered that (at least, in our system) it occasionally misreported some minor aspects of the OW table specs, namely that sometimes the ascending or descending sort was not reported correctly. Too minor to worry about? Not in my case, because I also discovered a cute little undocumented feature of OW: the SQL Primary Key created by OW ALWAYS has all columns sorted ascending. But, if the defined Primary Key in OW has a mixture of ascending and descending sorted columns, OW creates a second extra index in SQL Server with the sort mixture. (BTW if all columns are descending, there is no extra index. Go figure.) So, with my requirement of matching index counts before the defrag operation can be turned loose, I had to have an accurate analysis, allowing for the occasional extra index situation.
Figuring I had little to lose by asking, I emailed PatWel ([email protected]) to see if a command-line feature was in their future plans for ObjectBrowser. It was less than half an hour when Craig Welton of PatWel replied: "Thanks for your feedback. You are the second person this week to ask for the same thing. It will be the next thing I work on. Would you be willing to help test it?"
I really felt that here was a guy that cared about the users. I immediately replied that I would be honored to do so, and it wasn't long before he sent me his first pass. Because he had added this feature, I was able to develop a little VB.NET program that calls his program and then reads that output, counting the indexes and creating a SQL script file with which to load into a SQL table. Then, with other SQL stored procedures, I gather the SQL index counts, and compare them to the OW index counts by table.
What could have been a recurring drudge of a manual job can now be totally automated.
Kudos and thanks to Craig Welton and PatWel.