March 18, 2012 at 6:05 am
During a discussion about virtualizing servers, I suggested we migrate just data that we knew for sure was in use. The idea instigated a riot among the developers because of the fear factor; no one knows what actually IS in use, so their intention is to move everything to the new environment.
1) How can i know how often and by who a database object (table, view, function procedure) is selected, inserted, updated, deleted or called
2) Do you know of a good resource or tool or outline that is a migration assistant in a box, so at least we have a rational basis for the riot?
thanks very much
drew
March 18, 2012 at 6:22 am
drew.georgopulos (3/18/2012)
1) How can i know how often and by who a database object (table, view, function procedure) is selected, inserted, updated, deleted or called
Monitor (extended events, profiler, DDL triggers, SQL Audit, etc) for 'long enough' and then make a decision on whether things that never show up are unused or just very seldom used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2012 at 6:28 am
To expand on what Gail already recommended:
Even though there might be objects that never show up you'll need to investigate the purpose of each of those objects. Some might be used just once a year (e.g. for annual reports or some kind of annual data aggregation or the like).
The approach is more towards excluding objects that are used for sure from the detail analysis.
March 18, 2012 at 7:02 am
thanks very much;
one follow up and a lttle whine for breakfast <g>...first the follow up
1) the limits of my knowledge dictate that profiler and triggers can have an impact in degrading performance...do SQL Audit and Extended Events have less overhead for the same bang for the buck for the task at hand?
now the whine...
the temporal dimension is the paranoia monster 'You only checked for a month...my process runs quarterly, semi annually, on demand from the CFO (slip your favorite excuse not to do it here <g>)'
i will read about SQL Audit and Extended Events. thanks for the light.
March 18, 2012 at 7:06 am
thanks very much Lutz;
I was in the throes of responding before i saw your reply...
like Meatloaf, you took the words right out of my mouth!:-D
thanks again
drew
March 18, 2012 at 7:10 am
drew.georgopulos (3/18/2012)
1) the limits of my knowledge dictate that profiler and triggers can have an impact in degrading performance...
They can, but they can also be fine. Depends what you do with them. Triggers often receive a very bad rap because they can be badly written. A simple trigger should not have a noticable effect unless the system is already maxed out. With profiler there are recommendations for running traces without huge effects.
do SQL Audit and Extended Events have less overhead for the same bang for the buck for the task at hand?
Massivly less overhead than just about anything else, but again, depends what you do with them. There are certainly settings on extended events that can cause performance degradation
the temporal dimension is the paranoia monster 'You only checked for a month...my process runs quarterly, semi annually, on demand from the CFO (slip your favorite excuse not to do it here <g>)'
Why do you think I put 'long enough' in quotes? You have to trace/monitor for long enough to catch that kind of thing. If there's a process that only runs one a year, you must trace long enough to catch what that does. How long is 'long enough'? Depends on your system and your business.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2012 at 7:14 am
ok, I think im ready.
thanks very much for the orientation.
drew
March 18, 2012 at 10:41 am
drew.georgopulos (3/18/2012)
The idea instigated a riot among the developers because of the fear factor; no one knows what actually IS in use, so their intention is to move everything to the new environment.
if your developers don't know exactly what objects are used i would be worried 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 18, 2012 at 11:54 am
To aid what everyone else has already said (especially noting that some tables are only used monthly/yearly, etc), depending on how frequently you restart your MSSQL servers you can use/tweak the query below to determine when a table was last selected from since the server was restarted. It won't give you everything you need, but may provided a decent starting point...
WITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 18, 2012 at 1:45 pm
they don't.
its a sad fact of life...there's ten years of zero pedigree spinning
March 18, 2012 at 1:46 pm
MyDoggieJessie (3/18/2012)
To aid what everyone else has already said (especially noting that some tables are only used monthly/yearly, etc), depending on how frequently you restart your MSSQL servers you can use/tweak the query below to determine when a table was last selected from since the server was restarted. It won't give you everything you need, but may provided a decent starting point...
WITH LastActivity (ObjectID, LastAction) AS(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)
Better than that!
Thanks a ton...its a mini education all by itself.
I appreciate it.
drew
March 18, 2012 at 3:08 pm
Glad it will help!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 19, 2012 at 2:10 pm
drew.georgopulos (3/18/2012)
During a discussion about virtualizing servers, I suggested we migrate just data that we knew for sure was in use. The idea instigated a riot among the developers because of the fear factor; no one knows what actually IS in use, so their intention is to move everything to the new environment.1) How can i know how often and by who a database object (table, view, function procedure) is selected, inserted, updated, deleted or called
2) Do you know of a good resource or tool or outline that is a migration assistant in a box, so at least we have a rational basis for the riot?
thanks very much
drew
There is no tool or query that will tell you what objects or datasets can be safely excluded from the new instance. Like someone suggested earlier, it is possible to query from system information views what objects have been read or written to in the recent past. However, even if a table's indexes are written to, that doesn't mean that the index or even the table are actually used by the applications; they could be updated by a scheduled maintenance plan. However, if an index hasn't been read for a complete business cycle (ex: 45 days), then you can probably drop it without breaking anything.
The best thing to do is let the developers decide what objects can be excluded and let the business decide what data (closed accounts, change history, etc.) can be excluded. As a DBA or Data Architect, your only job is to decide how best to implement the migration based on the requirements. If they say exclude nothing, then exclude nothing.
One thing you could do while planning your migrating is consider implementing sparse columns or row/page compression.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 19, 2012 at 2:57 pm
Two aspects to look at:
- most of my tables are accessed in some fashion. That said - very little of the data older than a year is ever accessed again. Just because "an object is accessed" does not mean it doesn't have a lot of unused data.
- you may consider taking on the "squeeky wheel" approach. As in - take your best shot at estimating what you can move forward and what you leave behind, but set up a process which allows you to quickly move something over after the fact. You then only move over the things people cry about after the initial port. Since the retired data is static - you probably would not need it backed up regularly (make a couple of backups of it, and put them aside) and would not need a whole lot of horsepower to access it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 19, 2012 at 3:50 pm
To be safe, I would move everything.
If/as you feel a particular object is not needed, rename it (or move to a different schema/db, if you prefer). See if any errors occur. If so, put it back :-). This should be easy since the object is still there, and the permissions are still in place (don't overlook those).
If no errors occur, leave if for <however_many> days, and then genuinely delete it when you feel confident you can so safely.
There's just no easy way to clean up an existing database.
If you can, adopt some naming conventions to help with this in the future.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply