August 7, 2017 at 11:30 pm
Hi all,
We have a database that is bloated with years of developement and was then used as the base for a new project as they wanted to use some of the tables etc. and just copied the entire database. This has resulted in a project with a database that has hundreds of tables and thousands of stored procedures, most of which are never used. (I inherited this, I didn't do it) I would like to strip out as much as I can of the objects not used/required.
In about a month we have another major release and will be doing full end to end testing on the test system. I see this as a good time to log any table, stored procedure, view or function that is used, and use that list to export all those objects to a new DB (there will be less to export to a new DB than there will be to delete, it is seriously bloated)
I know that I can get a list of stored procs used since last server restart from sys.dm_exec_query_stats but I was wondering if there was something I could run that would simply log each table, SP etc during the period of our end to end testing. I understand that it would add to server load and possibly slow things down a bit, but as this is on a dedicated testing system, that isn't an issue.
Thanks in advance
Brendan
August 8, 2017 at 6:21 am
stardockmagic - Monday, August 7, 2017 11:30 PMHi all,
We have a database that is bloated with years of developement and was then used as the base for a new project as they wanted to use some of the tables etc. and just copied the entire database. This has resulted in a project with a database that has hundreds of tables and thousands of stored procedures, most of which are never used. (I inherited this, I didn't do it) I would like to strip out as much as I can of the objects not used/required.
In about a month we have another major release and will be doing full end to end testing on the test system. I see this as a good time to log any table, stored procedure, view or function that is used, and use that list to export all those objects to a new DB (there will be less to export to a new DB than there will be to delete, it is seriously bloated)
I know that I can get a list of stored procs used since last server restart from sys.dm_exec_query_stats but I was wondering if there was something I could run that would simply log each table, SP etc during the period of our end to end testing. I understand that it would add to server load and possibly slow things down a bit, but as this is on a dedicated testing system, that isn't an issue.Thanks in advance
Brendan
If you weren't on SQL Server 2008, I'd recommend Extended Events. However, since you're on 2008, the best tool for the job is Trace. Here's a great article on how to properly set up Trace to capture the queries on your server. You'll need to capture both rpc_complete and sql_batch_complete. Be prepared for the amount of data that you'll be getting. Make sure you somehow roll off the files & data so that you don't fill whatever drive you're using. Don't use the same drive as your databases, logs, or OS on the server.
That will get all the queries that are being run on the system. What you can't easily get in any way that is both reliable and accurate is the exact list of which tables or views that are being accessed. You'll have to cross reference between the queries and those objects for an accurate list.
"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
August 8, 2017 at 4:50 pm
Hey SSC Guru, thanks for the reply. I was worried that Trace would be my only option. That said, I posted in 2008 as that is what the server is that we are currently on (many of our customers are on 2008 so we stay on 2008 to ensure compatability), but I can certainly spin up a 2012 or later server to do the end to end testing specifically for this cleaning up job. If I were to run on a later version, what would extended events be able to do for me?
Thanks again
Bendan
August 8, 2017 at 4:51 pm
Hey SSC Guru, thanks for the reply. I was worried that Trace would be my only option. That said, I posted in 2008 as that is what the server is that we are currently on (many of our customers are on 2008 so we stay on 2008 to ensure compatability), but I can certainly spin up a 2012 or later server to do the end to end testing specifically for this cleaning up job. If I were to run on a later version, what would extended events be able to do for me?
Thanks again
Bendan
August 8, 2017 at 8:13 pm
stardockmagic - Tuesday, August 8, 2017 4:50 PMHey SSC Guru, thanks for the reply. I was worried that Trace would be my only option. That said, I posted in 2008 as that is what the server is that we are currently on (many of our customers are on 2008 so we stay on 2008 to ensure compatability), but I can certainly spin up a 2012 or later server to do the end to end testing specifically for this cleaning up job. If I were to run on a later version, what would extended events be able to do for me?Thanks again
Bendan
Basically the same thing, just using Extended Events which are more lightweight and safer than Trace. The core issue is that there's still no way to just see which table or index is accessed. You can see the queries, but that's all.
"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 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply