SQL Clever

Here is a very useful query to distinguish the number of hits for each table, based on the index usage. This query can be useful in “housekeeping” situations, or on a performance analysis.

The information takes in considering seeks, scans and lookups and outputs the date of the last operation of each kind.

It’s important to remark that this statistics are cleaned every time the service is restarted!

SELECT Object_name(object_id),
SUM(user_seeks)       user_seeks,
SUM(user_scans)       user_scans,
SUM(user_lookups)     user_lookups,
MAX(last_user_seek)   last_user_seek,
MAX(last_user_scan)   last_user_scan,
MAX(last_user_lookup) last_user_lookup
FROM   sys.dm_db_index_usage_stats
WHERE  database_id = Db_id()
GROUP  BY Object_name(object_id)
ORDER  BY SUM(user_seeks),
SUM(user_scans),
SUM(user_lookups)

Hi guys

The virtual launch sessions of the SQL Server 2012 are already available!

http://www.sqlserverlaunch.com/

I recommend the session about AlwaysOn and ColumnStore Index.

Good learning for you

A DBA is focused in maintain a database system running, accessible and recoverable in any moment. Obviously, the data is the motivation of a database system and have to be safe in all aspects. As commonly known, a database administrator is faced with very sensitive data, and is not easy to know who is responsible for what…So what are the borders that distinguish the DBA responsibilities?

In summary, is a DBA onus maintains total attention on the following points:

• Security
  The database server security is not a meaning of anti-hacking system (only). Through the operating system is possible to reach the database server and its resources, and through the database server is possible to reach the operation system. For this reason, is extremely important the teamwork between system administrators and database administrators.
• Isolation
  This point could be a part of security, however is particularly important to isolate the access to each single database in the server, even though it is owned by the same entity.
• Recoverability
  As the data is stored in database system managed files, it is required (most cases mandatory) for the DBA to maintainto maintain a reliable backup strategy implemented, to recover the databases when is needed.
• Confidentiality
  All the applications have to be owned by someone at some point, and only these (or those) people can allow changes in the data and sending chunks of data or even the entire database.

Is normal to a DBA be faced with data corruption. It can be caused by some database engine fail, by a “wicked” DML/DDL execution or even by the application itself. To take care of data, and protect the DBA, some important rules have to be created:

• Data Changes (DML scripts)
  Is mandatory to any data change be authorized by the application’s responsible. A DBA cannot change the data without the corresponding permission. Adding to it, every data change have to be requested by the application owner and the DML scripts sent to the DBA team to be approved and executed without online modifications.
• Structure Changes (DDL scripts)
  A DDL script doesn’t change data directly, however, it can indirectly break the database integrity, corrupt data or carry performance problems in a non-distant future, because of it, a DBA’s verification before a creation or change of any database object is of high importance subject and must be followed carefully by the DBA.

The objective of this article was to demonstrate some ideas, based on experience, of possible points of protection, because as we know, the DBA is always the first in the line of fire…

Developing each of these point, will help to improve the QoS and protect the DBA team of possible mistakes.

If you have more ideias, critics or suggestions, do not hesitate to contact me!