Tuesday, January 31, 2012

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

Why create something new when there is something very good and free on the market already!

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. The Solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs. I designed the Solution for the most mission-critical enterprise environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the Solution, which has been a Gold and Silver winner in the 2011 and 2010 SQL Server Magazine Awards. The Solution is free.

I would recommend this script to anyone. Take a look at his website.

Monday, January 30, 2012

Filtered indexes

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Filtered indexes can provide the following advantages over full-table indexes:

Improved query performance and plan quality

A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

Reduced index maintenance costs

An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

Reduced index storage costs

Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Here is a small example of using Filtered Indexes from BOL.

use AdventureWorks2008

select *
  from Sales.SalesOrderHeader

-- create a duplicate table
  into Sales.SOHdup 
  from Sales.SalesOrderHeader

-- check the table
select *
  from Sales.sohdup

--check if there are any indexes
sp_helpindex 'Sales.SOHdup'

-- create a non clustered index

create nonclustered index idx_NC_SOID on Sales.SOHdup(SalesOrderID)

sp_helpindex 'Sales.SOHdup'

-- using DMV
  FROM sys.dm_db_index_physical_stats ( DB_ID(N'AdventureWorks2008')
                                      , OBJECT_ID (N'Sales.SOHDup')
                                      , NULL
                                      , NULL
                                      , 'detailed');

sp_spaceused 'Sales.SOHdup'
  FROM sys.indexes
 WHERE object_id = object_id('Sales.SOHDup')

-- EP means Execution Plan

--select all records and see the EP
SELECT salesorderID
  FROM Sales.sohdup

-- now we want to create a filtered index for the following query
SELECT salesorderID
  FROM Sales.sohdup 
 WHERE salesorderID >= 58659

--first drop the exiting index

-- create a filtered index
 WHERE salesorderID >= 58659

-- now see the usage on index in the EP
SELECT salesorderID
  FROM Sales.sohdup 
 WHERE salesorderID > 58659

-- now observe the size and stats for the filtered index

SELECT i.[object_id]
     , i.name
     , i.index_id
     , p.partition_number
     , p.rows             as [#Records]
     , a.total_pages * 8  as [Reserved(kb)]
     , a.used_pages * 8   as [Used(kb)]
  FROM sys.indexes as i
 INNER JOIN sys.partitions as p ON i.object_id = p.object_id
                               AND i.index_id = p.index_id
 INNER JOIN sys.allocation_units as a ON p.partition_id = a.container_id
 WHERE i.[object_id] = object_id('Sales.SOHDUP')
-- AND i.index_id = 1 -- clustered index
 ORDER BY p.partition_number

Try on large tables on your environment and see how it can improve your performance.

Friday, January 27, 2012

Long running queries

One of the big challenges of a DBA is to maintain application’s performance.

 Increasing data on regular basis is often the cause for performance degrades. In most of the cases end users report the slowness in the application, only then DBA’s/Developers jump in and begin the optimization exercise.

Ideally, DBA’s/Developers supporting the application should be the first in identifying the performance problems and should proactively optimize the faulty/poor code.

Well to acheive this there should be a way to identify the queries taking long time. SQL server has been evolving greatly. SQL server 2005 and above are shipped with very helpful DMV (Dynamic management views). These views expose performance related statistics.

Here is the query using couple of such DMV’s .
This query returns top 10(configurable) slow performing queries.

-- Execute the query inside target database

 SELECT TOP 10 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds
                           ,  qs.total_elapsed_time / 1000000.0 AS total_seconds
                           ,  qs.execution_count
                           ,  SUBSTRING (qt.text, qs.statement_start_offset / 2,
                                                            WHEN qs.statement_end_offset = -1
                                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                   ELSE qs.statement_end_offset
                                                       END - qs.statement_start_offset)/2) AS individual_query
                          , o.name AS object_name
                          , DB_NAME(qt.dbid) AS database_name
   FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
     LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

Thursday, January 19, 2012

Password Audit for SQL Server Logins

This time I am going to talk about password audit for SQL Server. Weak or Blank passwords are an invitartion to trouble. It results in a security risk. Everybody knows the importance of Strong and Complex password but as they say it’s always easy to preach but difficult to practice, we tend to make use of weak and sometimes blank passwords.

How can we avoid it.

Regular Password Audits is the solution. As a SQL DBA we must do a regular audit and remind the users to change their passwords to something more complex. For understanding Password Auditing, you need to understand the built-in function PWDCOMPARE

This function hashes a password and compares the hash to the hash of existing password. You must be wondering why are we again hashing, SQL Server stores the Hash of password for SQL Logins and hashing is only one side, un-hashing or decrypting the hashed data is not at all possible. This function is available from SQL Server 7.0 to SQL Server 2012, means you can make use of this function in all SQL Server versions.

The function takes 3 parameters Clear_test_password, Password_Hash and Version where the first two are mandatory and the third one is optional and obsolete but it is there for backward compatibility. It returns 1 if the hash of the clear_text_password matches the password_hash parameter, and 0 if it does not.

Note: PWDCOMPARE function is not a threat against the strength of password hashes because the same test could be performed by trying to log in using the password provided as the first parameter.

Permissions required: PWDENCRYPT is available to public and CONTROL SERVER permission is required to examine the password_hash column of sys.sql_logins.

1. Identifying logins that have no passwords.

The following example identifies SQL Server logins that have no passwords.

SELECT [name] FROM sys.sql_logins WHERE PWDCOMPARE('', password_hash) = 1;

2. Searching for login names wich have the same value for passwords.

This code identifies the login where the user has given the same text as login and password, for example Login ButerJ is a login with password ButerJ

SELECT [name] FROM sys.sql_logins WHERE PWDCOMPARE([name], password_hash) = 1;

3. Seaching for common passwords

To search for common passwords that you want to identify and change, specify the password as the first parameter. This will check for a password specified as password.

SELECT [name] FROM sys.sql_logins WHERE PWDCOMPARE('P@ssw0rd', password_hash) = 1;

Wednesday, January 18, 2012

The usage of ISNULL and NULLIF

Explanation of the NULLIF:

Syntax:  NULLIF ( expression1, expression2)

NULLIF returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Following is good example of NULLIF and CASE from BOL:

USE AdventureWorks;

    , MakeFlag
    , FinishedGoodsFlag
    , NULLIF(MakeFlagFinishedGoodsFlagAS 'Null if Equal'
 FROM Production.Product
WHERE ProductID < 10;


    , MakeFlag
    , FinishedGoodsFlag
    ,'Null if Equal' CASE
                         WHEN MakeFlag = FinishedGoodsFlag
                           THEN NULL
                         ELSE MakeFlag
WHERE ProductID < 10;


Explanation of the ISNULL:

Syntax: ISNULL( check_expression, replace_expression )

Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

Following is good example of ISNULL from BOL:

USE AdventureWorks;GO

  FROM Production.Product;

An interesting observation is that NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.

Introduction to the MERGE statement in SQL Server 2008

The MERGE statement is a new feature in SQL Server 2008, it provides an efficient way to perform multiple DML operations in one statement. In previous versions of SQL Server, you had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now, using the MERGE statement you can include the logic of such data modifications in one statement that even checks when the data is matched the just update it and when unmatched then insert it. One of the most important advantages of the MERGE statement is all the data is read and processed only once. In previous version three different statement has to be written to process three different activities (INSERT, UPDATE, DELETE), however using the MERGE statement all update activity can be done in one pass of the database table. This is quite an improvement in performance of the database query.

Syntax of the MERGE statement is as following:

[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]  target_table [ WITH ( <merge_hint> )] [ [ AS ] table_alias ]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHES [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED [ BY SOURCE ] [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]


Let's create a Product table and a Product_Used table and insert some records.

,Product_Name VARCHAR(20)
,Used CHAR(1)
VALUES (1, 'Opel','Y')
VALUES (2,'Ford','N')
VALUES (3,'Volvo','Y')
VALUES (4,'BMW','Y')
VALUES (5,'Audi','Y')
VALUES (1,100)
VALUES (2,200)
VALUES (3,300)

In my example I will consider three main conditions while we merge this two tables.

  1. Delete records who are no longer used
  2. Update In_Stock add 100 to each if internal if record exists
  3. Insert the record if record doesn't exists

Now I will write the MERGE process for tables created earlier. I will make sure that we will have our three conditions discussed above are satisfied.

MERGE In_Stock AS t1
USING (SELECT Product_ID, Used FROM Products) AS t2
ON t1.Product_ID = t2.Product_ID
MATCHED THEN UPDATE SET t1.In_Stock = t1.In_Stock + 100

The select before the MERGE statement looks like this:

Product_ID  Product_Name         Used
----------- -------------------- ----
1           Opel                 Y
2           Ford                 N
3           Volvo                Y
4           BMW                  Y
5           Audi                 Y

(5 row(s) affected)

Product_ID  In_Stock
----------- -----------
1           100
2           200
3           300

(3 row(s) affected)

The select after the MERGE statement looks like:

Product_ID  Product_Name         Used
----------- -------------------- ----
1           Opel                 Y
2           Ford                 N
3           Volvo                Y
4           BMW                  Y
5           Audi                 Y

(5 row(s) affected)

Product_ID  In_Stock
----------- -----------
1           200
4           100
3           400
5           100

(4 row(s) affected)

We can see that 5 statements have been executed, the In_stock for Product_ID 2 has been deleted. The In_Stock for Product_ID 1 and 3 have been updated. The In_stock for Product_ID 4 and 5 have been inserted.

There are two very important points to remember while using the merge statement:

  • Semicolon is mandatory after the MERGE statement.
  • When there is a match clause used along with some conditions, it has to be specific first among all other WHEN MATCH clause.

The MERGE statement is a very handy improvement for T-SQL developers who have to update the database tables with complicated logic. The MERGE statement also improves the performance of the database as it passes only once through the data.

Now I will write the MERGE process for tables created earlier. I will make sure that we will have our three conditions discussed above are satisfied.

Tuesday, January 17, 2012

Some simple datetime functions to find a specific date

Right now I am going to give some examples that I have been using a lot to determine a specific date in SQL Server.

The most used and I think everyone should know is the function to get the date for today.


2012-01-17 12:54:33.167

What day was it yesterday?

SELECT  DATEADD(d, -1,  GETDATE()) AS 'Yesterday'

2012-01-16 12:54:33.167

Select the first day of the current week.

SELECT DATEADD(wkDATEDIFF(wk0GETDATE()), 0AS 'First Day of Current Week'

First Day of Current Week
2012-01-16 00:00:00.000

Select the last day of the current week.

SELECT DATEADD(wkDATEDIFF(wk0GETDATE()), 6AS 'Last Day of Current Week'

Last Day of Current Week
2012-01-22 00:00:00.000

Select the first Day of the current month.

SELECT DATEADD(mmDATEDIFF(mm0GETDATE()), 0AS 'First Day of Current Month'

First Day of Current Month
2012-01-01 00:00:00.000

Select the last day the the current month.

SELECT DATEADD(ms-3, DATEADD(mm0DATEADD(mmDATEDIFF(mm0GETDATE()), 0))) AS 'Last Day of Current Month'

Last Day of Current Month
2012-01-31 23:59:59.997

Select the first day of the current year.

SELECT  DATEADD(yy,  DATEDIFF(yy, 0,  GETDATE()),  0) AS 'First Day of Current year'

First Day of Current Year
2012-01-01 00:00:00.000

Select the last day of the current year.

SELECT  DATEADD(ms-3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) +1, 0)))  AS 'Last Day of Current year'

Last Day of Current Year
2012-12-31 23:59:59.997

Select the first day of the last year.

SELECT  DATEADD(yy-1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))  AS 'First Day of Last year'

First Day of Last Year
2011-01-01 00:00:00.000

Select the last day of the last year.

SELECT  DATEADD(ms-3, DATEADD(yy0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))  AS 'Last Day of Last year'
Last Day of Last Year
2011-12-31 23:59:59.997

Making a backup from the database or only a backup from the data

In my environments I have been asked to make a backup from a database in the test environment because the developer wanted to test a script that they have made. This script deletes,  updates and/or inserts rows in one or more table. If it only concerns one or more tables with only a few records inside. Then I usual advised the to make a backup from the data inside their own database.

     INTO test_table_save
  FROM test_table

This makes a copy of the table test_table into the table test_table_save. It doesn't copy any trigger, constraints or indexes on that table.

If the script has run you can use the following command to get back to the original state.


INSERT INTO test_table
      FROM test_table_save

This makes the developer independent from a DBA and it gives him/her more flexible.

Determine when SQL Server was started

When ever SQL Server is started or restarted the tempdb gets recreated. So to find out when SQL Server was last started, we can use the the query below.

SELECT created_date FROM sys.databases where [name] = 'tempdb'

There are some other ways to find out when SQL Server is last started. When SQL Server starts all the tasks it initiate entries for their login in the sysprocesses table. We can use their login time in this table to determine when SQL Server was last started.

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1

SELECT min(login_time) FROM master.dbo.sysprocesses

SELECT login_time FROM master.dbo.sysprocesses WHERE spid =1

If you are running SQL Server 2005 or higher, you can use a Dynamic Management View to determine the number of milliseconds have been elapsed since SQL Server have been started.

USE master

SELECT TOP 1 sample_ms AS Milli_Seconds_Since_Start
   FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

SELECT TOP 1 sample_ms / 1000 AS Seconds_Since_Start
  FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

SELECT TOP 1 sample_ms / 60000 AS Minutes_Since_Start

  FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

SELECT TOP 1 sample_ms / 3600000 AS Hours_Since_Start
  FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)

If you are running SQL Server 2008 or higher, you can also use this query:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

An other way is by using sp_readerrorlog.

EXEC sp_readerrorlog 0,1,'Copyright (c)'

This approach is not useful if the SQL Server Error Log is recycled manually.

Sunday, January 1, 2012

Untrusted constraints

When developers want to load a lot of data quickly, they usual disable constraints and/or keys to get a better performance. By it self this is a good practice, But when they are done the only enable them again. Behind the scenes SQL Server will check them and everything looks good again. But for constraint the developers sometimes don't use the correct statement to do this.

They use:


This leaves the constraint in a state called untrusted. The bad part about this is, that this can have a big impact on the performance on queries. SQL Server doesn't use untrusted constraints in his query optimizer anymore.
The way to enable the constraints correctly and tell SQL Server to recheck all the data that has been loaded is:

To resolve this problem you can run my following script on your databases, it checks if there are untrusted constraints and creates the correct statement to fix the problem.
     + db_name()
     + '].['
     + s.name
     + '].['
     + o.name
     + i.name
  FROM sys.foreign_keys i
 INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id        = s.schema_id
 WHERE i.is_not_trusted = 1

     + db_name()
     + '].['
     + s.name
     + '].['
     + o.name
     + i.name
  FROM sys.check_constraints i
 INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id        = s.schema_id
 WHERE i.is_not_trusted = 1