March 18, 2009 at 6:02 am
Hi All
I got a production database that has got more then 500 tables, out of which only a tiny percent is been used. There is a problem in identifying these tables that are not used, since there are lots of apps running, some might run once a month or even once a year, i got a BIG doubt
(i) Does these many tables in a database affect performance of the server ( there are 5 databases on this server)(in this case most tables are not been used), if yes what difference will it make if i clean all the tables in database and have only the tables that are been used.
(ii) is there any way of finding out when the table has last been used ( apart from profiler), I am using SQL 2005.
Please help me get out of this blues 😀
Thanks all in advance for your help 😀
March 18, 2009 at 7:47 am
Typically the number of tables in a database does not affect performance. It is the design of the database (indexes, etc...), query design, and number of users that typically cause performance problems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 18, 2009 at 7:56 am
Thanks for reassuring me Jack, Is there any way that i can find which tables are been used, and id i have stored the database on a LUN, will this affect when i use the LUN in a large chunk( say the mdf is 30 GB), i can understand that its been stored on pages when we go deep inside, will this size of the database have any performance issues ( when i say performance issues, the systems are working fine and i use the ways to improve as you suggested indexes etc... 🙂 , just curious to know will this make me a small difference).
March 18, 2009 at 8:00 am
I don't know of anyway, outside of profiler/trace to tell when a table is accessed. In theory, you could use the DMV's to get the SQL Statements and parse the SQL Statements to get table names, but I don't know that I'd trust that either. I believe the DMV's are "reset" when you reboot or restart the SQL Server service so you'd need to store this data somewhere as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 18, 2009 at 8:22 am
Thanks Jack for this, i think i will ask the developers to look into the codes and get me the table names, let me clear from where its started ( not blaming developers in common 🙂 )
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply