Sunday, March 25, 2012

Create CLUSTERED Index first then NON CLUSTERED indexes

We might have heard that always we need to create our CLUSTERED index first then NONCLUSTERED indexes.

Why is that? What would happen if NONCLUSTERED indexes are created first and then we create the CLUSTERED index?

If you create NONCLUSTERED indexes first and then CLUSTERED index internally ALL NONCLUSTERED indexes on that table would get recreated. On a big table this might take for ever to create the CLUSTERED Index itself.

Example:

In the sample shown in blog post titled "Query tuning using SET STATISTICS IO and SET STATISTICS TIME" we had created couple of NONCLUSTERED indexes alone. Now, let us assume we need to create a CLUSTERED index for that table on ProductID column.

First enable SET STATISTICS PROFILE ON so that we can see the profile information of the scripts we are going to execute. Then execute the below script:

--Script to create CLUSTERED index on ProductID column
CREATE CLUSTERED INDEX [ix_productId] ON [dbo].[tblTest]
(
[ProductID] ASC
)
GO

During this process it would have recreated both of these NONCLUSTERED indexes 
  1. [nc_ix_manufacturing_dt] & 
  2. [nc_ix_productName]


Friday, March 23, 2012

Declaring VARCHAR without length

Do you find anything wrong with this script?

CREATE PROCEDURE uspProcedureName
      @param1 VARCHAR
AS
....
....

If you aren't sure may be you should read this post completely without fail :)

All this while I was thinking that it is a well known issue until last week I saw a stored procedure something similar to the one shown above. Who ever created that stored procedure hasn't bothered to specify the length. Before jumping into the explanation of why we should SPECIFY THE LENGTH ALWAYS let us do a small exercise to understand this better.

Guess the results:

Try to answer what would be the output before checking the result.

--Declaring a variable without specifying the length
DECLARE @strSentence VARCHAR
SET @strSentence = 'Rajinikanth is always the NO 1 hero of India'
SELECT @strSentence

Expected Output: Rajinikanth is always the NO 1 hero of India
Actual Output: R

--While CASTing / CONVERTing
--The given string has 36 characters.
SELECT CONVERT(VARCHAR, '123456789123456789123456789123456789');
SELECT CAST('123456789123456789123456789123456789' AS VARCHAR);

Expected Output: 123456789123456789123456789123456789
Actual Output: 123456789123456789123456789123

As you could see for Variables the default length for VARCHAR is 1. When used within CONVERT/CAST the default length for VARCHAR is 30. 

So if we don't specify the length ourself these are the default values SQL Server uses - which means the data what we would be expecting to get stored in the database would have got silently truncated without our knowledge. These are one such tricky issues which if we aren't aware would mean we have to spend hell a lot of time debugging our scripts at a later stage.

Now to answer the first question on what is wrong with this script let us create a sample table and a stored procedure similar to that and see what it does.

--Sample table
CREATE TABLE dbo.tblDemo
(
ID INT,
FirstName VARCHAR(30)
)
GO

--Stored Procedure to Insert data into the sample table
CREATE PROCEDURE dbo.uspInsertData
@ID INT,
@FName VARCHAR -- See there is no length specified here
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.tblDemo VALUES (@ID, @FName)
END
GO

--Test
EXEC dbo.uspInsertData 10, 'Vadivel'
GO
SELECT ID, FirstName FROM dbo.tblDemo
GO

The output would be just 'V'. Once again the data has got truncated silently. So ALWAYS SPECIFY THE LENGTH OF THE STRING WITHOUT FAIL.


--Clean Up
DROP PROC dbo.uspInsertData;
DROP TABLE dbo.tblDemo;

Wednesday, March 21, 2012

Query tuning using SET STATISTICS IO and SET STATISTICS TIME

Often I find people aren't making use of the benefit of SET STATISTICS IO and SET STATISTICS TIME while trying to tune their queries.

Bottom-line is we want our queries to run as fast as possible. One of the challenges we face is not all environments which we would be working on are similar. The configuration, loads et al would be different between our Development box, Staging box, Production box etc., So how can we measure whether the  changes which we do really improves the performance and it would work well in other environmentts as well?

Let's try to understand few basics before seeing some code in action. For any query to be executed by SQL Server it uses many server resources. One such is "Amount of CPU resources it needs to run the query". This information would remain almost the same (There might be minimal changes in milliseconds) between executions. Another SQL resource which it needs for executing a query is IO. It would first check the Memory/Data Cache (Logical Reads) for the availability of the required data. In case the data is not available then it goes to Disk (Physical Reads) for fetching that data. So if queries are going to take more CPU and IO resources then the query is going to perform slower. So our goal should be to (re)write queries which use "Less CPU and IO resources".

Theory sounds good but how would we measure whether the changes we make to our queries are increasing or decreasing CPU/IO resource usages? This is where SET STATISTICS IO and SET STATISTICS TIME commands would come handy to us.

Things to look for in SET STATISTICS IO while tuning the queries: 

1. Scan Count - Number of times the underlying table in the query was accessed. If your query is not having any JOINs then you can ignore this count itself. Instead if your query is having JOINs then this is something which you need to watch out for during the course of your query tuning process. Needless to say, if this count goes down during your tuning then it's a good sign.

2. Logical Reads - This is the best piece of information to rely on. This count says how many PAGES it has to read from DATA CACHE to provide the desired result. This information is something which doesn't change between multiple executions. So we can rely on this output item while tuning the query. Fewer the Logical Reads better the performance would be.

Here I would like to elaborate a bit on Logical Reads. For whatever query we run SQL Server would check the DATA CACHE first for availability of data. In case required data pages are not available in DATA CACHE then it would go to the physical disk (Physical Reads) to fetch those DATA PAGES and move it to DATA CACHE.
So it means for SQL Server to execute a given query it requires all data to be available in the DATA CACHE first.
Apart from these two, the rest of them in SET STATISTICS IO are not useful for us while working on tuning the queries.

Things to look for in SET STATISTICS TIME while tuning the queries:

1. CPU Time - This tells us how much CPU time was used to execute the query. This information would be reliable. Though we could see slight variations in this number each time we run in the same box or different machines but it would be very minimal.

Always look for the "SQL Server Execution Times:" which occurs immediately after (x rows affected) line in the output.

There is another output item "Elapsed Time". This tells us the total amount of time it took from start to end. Elapsed time is not useful for query tuning as this number can vary drastically based on server load.

How to enable these commands?

By default, both SET STATISTICS IO and SET STATISTICS TIME would be disabled. So to enable them make use of either one of these options.

Option 1. Tools > Options > Query Execution > SQL Server > Advanced > Click on the check-box besides these commands.

Edit: We can also do this - Query > Query Options > Advanced.

Option 2. We can make use of the SQL command to do it.

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

..... Your query comes here .....

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

Examples


Though this post is not about explaining how to tune queries thought would share some basic examples to show case these commands in action.

--Setting up a Sample table for demonstration purpose
CREATE TABLE tblTest
(
   ProductID INT IDENTITY,
   ProductName VARCHAR(50) NOT NULL,
   Manufacturing_Date DATETIME NOT NULL
)
GO

Let us make use of our Random records generator UDF to populate some test data into this table.

--Populating test data
INSERT INTO tblTest
SELECT
   dbo.udf_StringGenerator('A', 50),
   GETDATE() + (RAND() * (39 * 365))
GO 100000

Important: Always before you start tuning your query for performance run the below two commands.

**Please be advised not to use these commands in the PRODUCTION environment directly. Use these in Development or Test Environment ONLY**

DBCC DROPCLEANBUFFERS -- Clear SQL Server Data Cache
DBCC FREEPROCCACHE -- Clears SQL Server Procedure Cache

Example 1: Let's write a query to list total count of all products whose names start with 'ab'.

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

--I have 146 records in my table matching this condition.
--It would be different for you as we have generated random records.
SELECT COUNT(*) FROM tblTest WHERE ProductName LIKE 'ab%';

/*
(1 row(s) affected)
Table 'tblTest'. Scan count 1, logical reads 971, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 48 ms.
*/

There is no index on ProductName column. So lets create a non-clustered index on that column and then check the query again.

CREATE NONCLUSTERED INDEX [nc_ix_productName] ON [dbo].[tblTest]
(
[ProductName] ASC
)
GO

--Lets check again!
SELECT COUNT(*) FROM tblTest WHERE ProductName LIKE 'ab%';

/*
(1 row(s) affected)
Table 'tblTest'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
*/

So by creating appropriate index we are able to bring down the CPU time and Logical Reads.


Example 2: Let's write a query to list total count of all products whose manufacturing year is 2012.

--I have 1923 records in my table matching this condition. 
--It would be different for you as we have generated random records.
SELECT COUNT(*) FROM tblTest WHERE YEAR(Manufacturing_Date) = 2012

/*
(1 row(s) affected)
Table 'tblTest'. Scan count 1, logical reads 971, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 33 ms.
*/

There is no index on Manufacturing_Date column as well. So lets create a non-clustered index on that column and then check the query again.

CREATE NONCLUSTERED INDEX [nc_ix_manufacturing_dt] ON [dbo].[tblTest]
(
   [Manufacturing_Date] ASC
)
GO

--Let's check it again!
SELECT COUNT(*) FROM tblTest WHERE YEAR(Manufacturing_Date) = 2012

/*
(1 row(s) affected)
Table 'tblTest'. Scan count 1, logical reads 237, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
   CPU time = 32 ms,  elapsed time = 37 ms.
*/

There is only a slight improvement and wait a minute the existing query is NOT SARGable (Check out "What does Avoid NON SARGable means?") So lets rewrite the query and make it SARGable.

--Lets rewrite the query to make the query SARGABLE.
SELECT COUNT(*) FROM tblTest
WHERE Manufacturing_Date > '20111231' and Manufacturing_Date < '20130101';

/*
(1 row(s) affected)
Table 'tblTest'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
*/


SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

So always while performance tuning queries make use of SET STATISTICS IO and the SET STATISTICS TIME commands. Within it watch out for Logical Reads, the CPU time and/or Scan Count to know whether your query tuning steps are really improving or decreasing the performance.

Tuesday, March 20, 2012

Find the last day of the month

Prior to SQL Server 2012 we can make use of DATEADD function to find the last day of a month for the provided date.

DECLARE @dtTempDate DATETIME
SELECT @dtTempDate = GETDATE()

In SQL Server 2005 or 2008 these are couple of ways by which we can get the desired

--Option1
SELECT DATEADD(DAY, -1, DATEADD (MONTH, MONTH (@dtTempDate) , DATEADD (YEAR,  YEAR (@dtTempDate) - 1900, 0 )))

--Option2
SELECT DATEADD( DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(@dtTempDate), @dtTempDate)))

--Option3
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dtTempDate) +1, 0))

Now in SQL Server 2012 there is a new Date and Time function named EOMONTH.
EOMONTH ( start_date [, month_to_add ] )

Solution using EOMONTH which would work only in SQL Server 2012 and above:

SELECT EOMONTH (@dtTempDate) AS [Current Month Last Date]

--here the second parameter tells how many MONTHS to add to the given input
SELECT EOMONTH (@dtTempDate,1) AS [Next Month Last Date]
SELECT EOMONTH (@dtTempDate, -1) AS [Previous Month Last Date]



Beware EOMONTH might throw up wrong results when used in Date Range queries. Let me try to explain it using an example.

CREATE TABLE tblSales
(
SalesDate DATETIME,
Orders INT
)
GO

INSERT INTO tblSales VALUES ('2012-03-19 00:00:00.000',10)
INSERT INTO tblSales VALUES ('2012-03-20 07:13:54.420',1)
INSERT INTO tblSales VALUES ('2012-03-31 00:00:00.000',100)
INSERT INTO tblSales VALUES ('2012-03-31 00:00:01.001',200)
INSERT INTO tblSales VALUES ('2012-03-31 07:12:29.123',500)
GO


Assume current date is 19th March 2012. We need to list all records between 19th March to End of that Month. i.e., 19th March 2012 TO 31st March 2012

In prior versions of SQL Server: Using BETWEEN would show inaccurate results as it takes only till mid night of 31st March 2012. The below query would list only the first 3 records and it would ignore the Orders 200 and 500 which is obviously wrong.

SELECT SalesDate, Orders FROM tblSales
WHERE SalesDate BETWEEN '20120319' AND '20120331';


So to get proper result we need to make use of something like SalesDate > 19th March AND SalesDate is < April 1st.


SELECT SalesDate, Orders FROM tblSales
WHERE SalesDate >= '20120319' AND SalesDate < '20120401';

Using EOMONTH in SQL Server 2012:

--This would translate exactly to the query which we had written for prior version using BETWEEN
--So what does it mean? Its going to display wrong results!!

SELECT SalesDate, Orders FROM tblSales
WHERE SalesDate BETWEEN '20120319' AND EOMONTH('20120319');


--Add a day to the output of EOMONTH to fetch the accurate result
SELECT SalesDate, Orders FROM tblSales 
WHERE SalesDate >= '20120319' AND SalesDate < DATEADD(DAY, 1, EOMONTH('20120319'))



Monday, March 19, 2012

Download and Install SQL Server 2012 Product Documentation

When you install SQL Server 2012 by default it installs only the Help viewer and its related components. It doesn't install any help documentation locally and user has to go online for checking any reference. But that concept won't work for me as I have installed SQL Server 2012 in a laptop which doesn't have internet connection at all. 

Download SQL Server 2012 Product Documentation

So I was searching for 'downloadable version of SQL Server 2012 product documentation' and I found it here - http://www.microsoft.com/download/en/details.aspx?id=347

I downloaded SQLServer2012Documentation.exe which was approx 204 MB and ran the exe which created a folder named "SQLServer2012Documentation" and extracted the following:
  • Folder named "Packages" which had 25 CAB files (cabinet files)
  • Four HTML document (book1, book2, book3, product1)
  • One MSHA file (HelpContentSetup.msha)
So over all 30 items are extracted.

Installing the SQL Server 2012 Product Documentation

1. Goto Start > Program files > SQL Server 2012 >> Documentation & Community >> Manage Help Settings
2. Click on the link "Choose online or local help" and then choose "I want to use local help" before clicking on OK button.
3. Click 'Install content from disk' and browse to the directory where you unpacked the downloaded EXE.
4. Select the HelpContentSetup.msha file (MSHA = Microsoft Help Asset File) and click Open. 
5. Click Next.
6. There will be 3 option listed: Books Online, Developer Reference, Installation and besides each of them there would be an 'Add' link. 
7. Just click on the 'Add' link next to the documentation you want to install and click on 'Update' button.
8. Continue option 7 until you have installed all the documentation you wanted.

 


Adventure Works database for SQL Server 2012

At last during the weekend I was able to download and install SQL Server 2012 standard edition in my laptop. Next to get started I was looking for sample database for SQL Server 2012. Using the search string "adventureworks 2012 download" I found the below download link.

  • Adventure Works DB for SQL Server 2012 - http://msftdbprodsamples.codeplex.com/releases/view/55330
  • Actually it downloads the DATA file named AdventureWorks2012_Data.mdf and the size when I downloaded was 189 MB.
  • Copy the DATA file to the location where you want it to be stored in your hard drive and then make use of that path in "FILENAME" attribute within CREATE DATABASE syntax.

CREATE DATABASE AdventureWorks2012
ON
(
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\DATA\AdventureWorks2012_Data.mdf'
)
FOR ATTACH_REBUILD_LOG;



Reference: CREATE DATABASE syntax for SQL Server 2012