Dm_db_stats_properties - to get statistics properties

Managing statistics is important part for developers. Many times we have seen
improper statistics screwed up the queries and queries which completes in seconds
takes hours to complete.
Monitoring statistics and updating it regularly is important for this aspect.
For this we can use  sys.dm_db_stats_properties which give better insight about
each stats for a table.

This DMV has two arguments

Object_id
Stats_id

Better we should user cross apply it with sys.stats and it will give up
in depth details about the stats.

SELECT s.object_id,

       Object_name (s.object_id),

       s.stats_id,

       s.auto_created,

       s.user_created,

       s.no_recompute,

       s.has_filter,

       s.is_temporary,

       s.is_incremental,

       d.last_updated,

       d.rows,

       d.rows_sampled,

       d.steps,

       d.unfiltered_rows,

       d.modification_counter

FROM   sys.stats s

       JOIN sys.tables t

         ON s.object_id = t.object_id

       CROSS apply sys.Dm_db_stats_properties(s.object_id, s.stats_id) d

WHERE  t.type = 'u'

PWDCOMPARE - to check predictable password

How to check week passwords?
As a strong security policy we also need that password are not predictable.
Many time we need to test this but checking all such predictable password with all logins
is very tedious task and any one will try to avoid.

Recently I had a issue where I need to check that certain predictable passwords are not used by
any login.

Here we can use pwdcompare function , which solves this purpose very easily.

It checks password with stored password hash from sys.sql_logins.

I want to check which logins are using password like qwedsa or password

SELECT *

FROM sys.sql_logins

WHERE PWDCOMPARE('qwedsa',password_hash) = 1

  OR PWDCOMPARE('password',password_hash) = 1

Once we know this logins we can ask the user to change password with strong combinations.

Online reindexing of partition.

Till now SQL 2012 its not possible to reindex a partition online.
We had two option

1. Either reindex entire table online
2. Reindex a single partition offline.

Database Audit - Example with Select statement.

SQL SERVER audit is a server feature and its used to audit server actions.
Its available in SQL 2008 and later editions.
Its using SQL Server extended events to audit server activity.
Its providing much more granularity and we can use it to audit database related events also.

How to change default databse backup location in SQL Server

We all are aware of backup in SQL Server and how important it is. But very few have paid attention at backup location. When we take backup SQL Server show folder which is assigned for default backup.
But as its in C drive , almost majority will change location while taking backup.
So its better if we change default backup location to new address.

Partial Backup

Partial Backup

We can use partial backup  while handling  large databases
Using partial backup we can take backup of only read write enabled filegroups instead of
all filegroups which are read_only.

Copy only backup option

copy_only option for backup

Its a useful feature but very few are aware of this option .

As we all are aware of backups and its different types

We are aware of backup strategies which include backup chain of full,differential and log backups

How to identify tempdb space usage

Sometimes our tempdb gets full and fills the disk. Hence it cant expand and due to its a lots of our operation either stucks or starting failing.
At this time we immediately need to find which process is taking so much space in tempdb and which type of  operation by that process is taking space to troubleshoot the issue ASAP

DMV to get SQL Server Registry information

SQL Server 2012 has new DMV sys.dm_server_srvices
This provides details in registry related to SQL server

This provides some interesting information like 
SQL Server Version/Startup Parameters/Ports/Service startup options

Easy way to get status of SQL Server Services in SQL 2012

SQL 2012 has some new DMVs.
One of them is sys.dm_server_services
It gives us details about the SQL Server services (SQL Serve,SQL Server Agent, Full Text)

IndexProperty

Recently I had problem of slow insert on tables
During optimization we changed FillFactor of indexes and that improved the performance of insert.
We change the fill factor to 50%.

Impact of FillFactor

Problem

We have a table where we are dumping data on every 5 mins
Every 5 mins we are dumping approx 0.1 to 0.2 million rows
The tables has 5 indexes and we have a complaint that dump is getting slower over the time
It was taking 40-50 seconds , which needs to come down as we had to do other process after the dump

Activity Monitor SQL Server

Activity Monitor

As a sql server user we all are aware of Activity monitor.
But in SQL Server 2008 its enhanced with more features

Script SQL Server Jobs

                As a part of backup routine or server migration we have to script jobs and then move them to other server. We all are used to SSIS for moving jobs from one server to other server
Another option is to

How to monitor replication subscription errors

 One of my client has a big setup Server for SQL Server. He also has a lot of transactional replication and monitoring its is a really problem for him. Frequently he has problem of out of sync replications or failed replication

xp_readerrorlog parameter detail

Recently we had  hardware and performance issues on SQL Server,with our client , so while monitoring the system we also looked at error log. We used xp_readerrorlog procedure.
This is really useful procedure and I feel I should share some information for this undocumented store procedures

get row count for a table SQL Server DMVs

In SQL 2000 you can get this informartion from sysindexes system table.
But we should not sysindexes in because it will be deprecated in future

map login with users

When we restore backups to new server then users in the database are not mapped with their login in the new server
Its because sid is not matching.In this case we will are used to with sp_change_users_login
But this procedure will be deprectated in future version . So instead of this we should use alter user


alter user username
with
= new username,
,


So for mapping a user test with testlogi  we will use


ALTER USER test WITH login = testlogin

DMVs instead of DBCC Opentran

Those who are using SQL Server must be aware of dbcc opentran
After 2005 edition , SQL Server  is providing reach information in DMV.
For transactions 2 DMVs are available in SQL Server

sys.dm_exec_procedure_stats DMV to get cached proc stats

We all are used to sys.dm_exec_query_stats which gives us useful information for queries running on the server.
Similarly in SQL 2008 we can get details for procedures. For this we will have to use sys.dm_exec_procedure_stats
It is supported in SQL 2008 and higher versions.