未加星标

T-SQL Tuesday #86: Enhancement Requests

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

T-SQL Tuesday #86: Enhancement Requests
On the second Tuesday of each month, the SQL Server universe all comes together to blog about a topic. Known as T-SQL Tuesday and started by Adam Machanic ( Blog | @AdamMachanic ), this month’s T-SQL Tuesday topic is hosted by friend and fellow MCM Brent Ozar . The theme that Brent has chosen for this month is “SQL Server Bugs & Enhancement Requests”. Specifically, Brent wants us to visit connect.microsoft.com , find an interesting request, and blog about it. Now Brent, this is a fantastic topic for a T-SQL Tuesday. I’m really looking forward to seeing what others blog about. I plan on upvoting those I like to get the attention of the SQL Server team at Microsoft. For what it’s worth, I think that this should be a yearly event to do with T-SQL Tuesday! My favorite connect item

The connect item that I want to blog about is one that I submitted a few months back. Now, I’ve submitted a few enhancement requests over time ( see this link ,) and there are two current ones that I’m really keen on. However, Brent only wants us to talk about one, so let’s explore what my favorite one is. It can be found at this link .

Track object last used data

At almost every place that I’ve worked (and many that I’ve consulted at), I’ve been asked to figure out what objects in the database are no longer being used. I’ll bet that you’ve been asked this also. While we can tell when objects were created, it’s not so easy to determine when they were last used.

Now, there are a couple of DMVs that can help us out some. Specifically, sys.dm_exec_procedure_stats will show us information about the procedures that are in the plan cache. In BOL there is some information about what this DMV does:

The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached.

Another DMV that can be helpful is sys.dm_exec_index_usage_stats , which shows us various types of index operations and when they were last performed. However, this data is only available as long as the database is online when the database is shutdown or detached (or when the SQL Server service is shutdown), this data is lost.

In both of these DMVs, the data is not persisted. Now, we can persist this data by running a job every minute or so to write this information out to a table. However, this only tells us a little bit of the picture: when procedures and indexes are used. If an index is being used, then the table (or indexed view) that the index belongs to has been utilized.

What is missing is all of the other objects: triggers, functions, non-indexed views, user-defined data-types, etc. In order to look for these, you would have to get an extended event session to look at all of the sql commands going on, and then to parse the code to find out what objects are being used. Yuck.

My proposal

What I have proposed is to add two columns to sys.objects: last_used_time_user and last_used_time_system. Two columns are proposed to be consistent with how the sys.dm_exec_index_usage_stats DMV tracks usage by user or system objects. I also suggest adding these columns to sys.indexes. For sys.indexes, this could just be the max of the appropriate columns from the sys.dm_exec_index_usage_stats DMV.

I would imagine that tracking all of this information would cause a performance hit, so this should not be on all the time. I suggest an ALTER DATABASE command (ALTER DATABASE TRACK OBJECT USAGE = ON/OFF) to control whether this information is being collected (now if they can figure out a way to do this where the overhead is minimal, just ignore this command).

Taking this further?

Would it be advantageous to know whether a column is being used in a table? Do you have a column in a table that you believe is obsolete? Have you left it in the table because you’re afraid of breaking the application somewhere? So how about adding these same columns to sys.columns? I’d suggest another ALTER DATABASE command to control whether column usage is being tracked (ALTER DATABASE TRACK COLUMN USAGE = ON/OFF).

Summary

SQL Server can make it easier for us to know whether objects (or even columns) are being used. Having this information will make it easier for us to eliminate obsolete stuff hanging around. This stuff takes up space (including backups) and increases time for maintenance.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerSAGE
分页:12
转载请注明
本文标题:T-SQL Tuesday #86: Enhancement Requests
本站链接:http://www.codesec.net/view/523345.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(64)