Sunday, January 25, 2015

SQL Script to find the missing indexes

Performance tuning in SQL is important exercise and index creation is an important part of it. Below script will help in finding the missing indexes. Once you create these indexes, it will help in improving the Performance.

SELECT db_name(d.database_id) dbname

, object_name(d.object_id) tablename
, d.equality_columns
, d.inequality_columns
, d.included_columns
,'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)
      + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'
      + ' ON ' + d.statement
      + ' (' + ISNULL (d.equality_columns,'')
        + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL (d.inequality_columns, '')
      + ')'
      + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
       FROM  sys.dm_db_missing_index_groups g
       join sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle
       join sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
WHERE  d.database_id =  'Database_id' and d.object_id =  d.object_id

We need to pass the id (Database_id) of the database in the above query. This id of the database can be get with the help of the below query.

select * from sys.databases

The above query will gives the id of all the databases deployed on the server in which this query is executed.

SQL SERVER - Data types

In relational database, we store data in tabular form where data is divided into columns. Each column has a name and a data type which shows what type of data is going to store in that column. Below are the data types which are mostly used in SQL server. 

CHARACTER(n)    Character string of fixed length n

VARCHAR(n)        Character string of variable length with maximum length n

VARCHAR(MAX)   Character string of variable length with maximum length                                can exceed 8000 byes

NCHAR(n)            Fixed-length Unicode string data where n defines the string                            length and must be a value from 1 through 4,000

NVARCHAR(n)     Variable length Unicode string data where n defines the                                  string length and must be a value from 1 through 4,000

NVARCHAR(MAX) Variable length Unicode string data where MAX indicates                                 that the maximum storage size is 2^31-1 bytes (2 GB)

BINARY(n)           Binary string of fixed length n

BOOLEAN             It stores TRUE or FALSE values

VARBINARY(n)    Binary string of variable length with maximum length n

NTEXT                  Variable-length Unicode data with a maximum string length                            of 2^30 - 1 (1,073,741,823) bytes

TEXT                    Variable-length non-Unicode data in the code page of the                               server and with a maximum string length of 2^31-1                                       (2,147,483,647)

IMAGE                 Variable-length binary data from 0 through 2^31-1                                          (2,147,483,647) bytes.

INTEGER Or Int   A 32-bit signed integer value having range of INTEGER is                                -2147483648 to 2147483647

SMALLINT           A 16-bit signed integer value having range of SMALLINT is                              -32768 to 32767.

BIGINT               A 64-bit signed integer value having range of BIGINT is                                   -9223372036854775808 to 9223372036854775807

DECIMAL(p,s)    Exact numerical, precision p, scale s. For Example,                                         Decimal(5,3) means 22.212

NUMERIC(p,s)   Exact numerical, precision p, scale s. (Same as DECIMAL)

REAL                  Approximate numerical, mantissa precision 7

FLOAT                Approximate numerical, mantissa precision 16

DOUBLE             Approximate numerical, mantissa precision 16

DATE                  It stores year/month/day values

TIME                  It stores hour/minute/second values

TIMESTAMP       It stores year/month/day/hour/minute/second values

XML                    It stores XML data

SQL Server - Convert() function

Convert () function in SQL Server to convert an expression from one data type to another data type

Syntax for CONVERT function
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Convert functions is usually used with date/time data type to convert a date in different formats. Below examples will show the different formats in which we can display the date/time data type using Convert function. In the below examples we use getdate() to get the current date and time of the database server.

select convert(varchar(40), getdate(), 0)

Jan 25 2015  9:36PM

select convert(varchar(40), getdate(), 101)


select convert(varchar(40), getdate(), 102)


select convert(varchar(40), getdate(), 103)


select convert(varchar(40), getdate(), 104)


select convert(varchar(40), getdate(), 105)


select convert(varchar(40), getdate(), 106)

25 Jan 2015

select convert(varchar(40), getdate(), 107)

Jan 25, 2015

select convert(varchar(40), getdate(), 108)


select convert(varchar(40), getdate(), 109)

Jan 25 2015 10:19:15:583PM

select convert(varchar(40), getdate(), 110)


select convert(varchar(40), getdate(), 111)


select convert(varchar(40), getdate(), 112)


select convert(varchar(40), getdate(), 113)

25 Jan 2015 23:22:49:453

select convert(varchar(40), getdate(), 114)


select convert(varchar(40), getdate(), 120)

2015-01-25 23:23:37

select convert(varchar(40), getdate(), 121)

2015-01-25 23:24:07.577

select convert(varchar(40), getdate(), 126)


select convert(varchar(40), getdate(), 127)


select convert(varchar(40), getdate(), 131)

 5/04/1436 11:25:23:483PM

From the above examples we can see that how we can use the Convert () function to display date time in different formats.

Tuesday, January 20, 2015

SQL Script to find the list of all the jobs failed yesterday

One of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday.

CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name  AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs  T2
ON T1.job_id = T2.job_id
WHERE  T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)