December 10, 2010 at 12:46 pm
I support an application with an Access front-end and SQL Server database on the back-end. The database was partially built with a framework-like tool that created auxillary tables, views, and triggers for each base table. None of these objects are used. In my inital script to clean this bloating, I can get the number objects from 41499 to 9738 (i.e. 7223 user tables to 938, with similar reductions in views, constraints, and triggers).
What can I use to measure the performance of the app and database before the cleaning to compare it to after?
Also, there are similar number of schemas that look useless, but I am hestant to drop them. How can I tell if they are used?
TIA
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 10, 2010 at 1:52 pm
On the schemas, you can check the schema_id column in sys.all_objects.
On performance, I'd run perf mon for a bit before and after, and see what the numbers look like.
Honestly though, if the objects aren't in use, it's likely they aren't affecting performance at all. What they'll affect is database size, backup size, and the complexity of maintenance tasks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2010 at 11:49 am
Thanks for the response.
Right-on about the schemas. Out of 607 total, 3 are used. I'm still suspect though, I don't want to removed unused schemas like db_owner, db_accessadmin, etc. I'll leave those in and only remove the ones I know are user-created.
As far as the performance, I'm mostly thinking about the Access front-end. The Access files being version 2003 adp's (should have mentioned that before) have to load the table or query listing everytime it refreshes defintely have a lag in loading 7000+. I'm hoping that would be an improvement.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 13, 2010 at 12:58 pm
Hadn't thought of that, with regards to Access doing that. Yeah, that makes sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2010 at 3:02 am
If your Access front end is using a server-side cursor to do the table refresh, then you do need to look at reducing the number of tables in the server database.
However, you could change your Access application to use a client-side cursor. If you first link only the few tables that are needed by Access, then your refresh will only try to re-link these tables, and will neither know or care about the thousands of other tables that may be on the server.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply