Tuesday, November 17, 2015

SQL query to get the job error message from SSISDB

In SQL Server 2012 Microsoft did lot of enhancements in SSIS, especially by introducing the “Project Deployment Model”.  After this so many things got better in SSIS.  While there are so many good things about this change one thing started to irritate me and that is the error in the job history.  With this change we stopped getting the exact error messages in job history.  Instead, when we open the job history to check the reason for failure we see this message:
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report.
To get the exact job failure reason we need to go the Integration Services Catalog, right click on the Project name >> Reports >> Standard Reports >> All Executions.  These reports are very nice and detailed but every time going to these reports and trying to find the error is very irritating.  Sometimes too much information is not that good.  I have seen people making mistakes in reading these execution reports.  Most of the times we are interested in error messages only and not in other execution events.  Worst part is we can’t even copy the error message to quickly Google the error for solutions.  So I thought of doing something about it.  Every time I’m not going to open execution reports to see the error.
DECLARE @DATE DATE = GETDATE() - 7 -- This is to restrict the data for last 7 days, used in ON condition
SELECT O.Operation_Id -- Not much of use
,E.Folder_Name AS Project_Name
,E.Project_name AS SSIS_Project_Name
,CONVERT(DATETIME, O.start_time) AS Start_Time
,CONVERT(DATETIME, O.end_time) AS End_Time
,OM.message as [Error_Message]
,EM.Message_Source_Name AS Component_Name
,EM.Subcomponent_Name AS Sub_Component_Name
,CASE E.Use32BitRunTime
THEN 'Yes'
END Use32BitRunTime
,E.Executed_as_name AS Executed_By

FROM [SSISDB].[internal].[operations] AS O
INNER JOIN [SSISDB].[internal].[event_messages] AS EM
ON o.start_time >= @date -- Restrict data by date AND EM.operation_id = O.operation_id

INNER JOIN [SSISDB].[internal].[operation_messages] AS OM
ON EM.operation_id = OM.operation_id

INNER JOIN [SSISDB].[internal].[executions] AS E
ON OM.Operation_id = E.EXECUTION_ID

WHERE OM.Message_Type = 120 -- 120 means Error AND EM.event_name = 'OnError'
-- This is something i'm not sure right now but SSIS.Pipeline just adding duplicates so I'm removing it. AND ISNULL(EM.subcomponent_name, '') <> 'SSIS.Pipeline'
ORDER BY EM.operation_id DESC

A quick SQL query to get the exact error would be nice.   And as I expected, all the package execution logs and error messages are stored in the SSISDB internal tables.   I created below sql script to query the SSISDB to get the exact error messages:
This was simple script to get the errors from SSISDB instead of going to Integration Services Catalogs execution reports.  Though this query is still work in progress, there could be scope for performance improvement or adding more information.  I’m still understanding the tables in SSISDB.  I’ll keep on posting the updates if any.
Mangal Pardeshi

Saturday, November 14, 2015

Dynamic PIVOT in SQL

Today I’m going to show you how to write a PIVOT query when column names are dynamic.  A dynamic pivot is very necessary when you don’t know the column names or column names can change with the time or due to any other reason.  I’m not going to discuss the pros and cons of the dynamic sql or even the Pivot itself (in my opinion pivoting of data should be handled at the reporting level.).  So let see how to write a dynamic pivot query with a very simplified example.

First lets create some sample data:
-- create table
OrderID INT,
ProductName VARCHAR(50),
Quantity INT,
OrderDate DATE
-- insert some sample data
(1, 'Pen', 100, GETDATE()-1),
(2, 'Pen', 200, GETDATE()-2),
(3, 'Pen', 300, GETDATE()-3),
(4, 'Pencil', 500, GETDATE()-1),
(5, 'Pencil', 600, GETDATE()-2),
(6, 'Pencil', 400, GETDATE()-3),
(7, 'Eraser', 30, GETDATE()-1),
(8, 'Eraser', 20, GETDATE()-2),
(9, 'Eraser', 10, GETDATE()-3),
(10, 'Pen', 100, GETDATE()-4),
(11, 'Pencil', 500, GETDATE()-4),
(12, 'Eraser', 30, GETDATE()-4);
-- verify the data
FROM Orders

This is how our data looks like:
Requirement:  Now I want to Pivot this data on the column OrderDate.  I want to show the last 3 dates of OrderDates in the column and respective quantity of each Product under the Date.  Here is the expected output, though you need to remember that you will get different column names(dates) as my sample data itself is dynamic: 
Now first understand the challenges.  Our requirement is to show the last 3 days of OrderDate.  Today I’m writing this post that’s why last days are 11 Nov – 13 Nov, but tomorrow I’ll want my query to show the dates 12 Nov – 14 Nov.  It will continue to change.  Every time changing these dates in our query can be difficult/painful/irritating.   And that’s where dynamic SQL comes into the picture.  The main challenge is to get these ever changing column names and pass them into the PIVOT block without hard-coding and also into the SELECT clause.

Lets do one thing at a time.  First we will try to get the last 3 OrderDates and concatenate them in desired order and format.  For that I’m going to use XML PATH.  Note that, there could be other methods to concatenate the column names into a single variable.  Here is my code to get the column names dynamically into a variable:
DECLARE @ColumnNames VARCHAR(1000)

SELECT @ColumnNames = 
           ', ' + QUOTENAME(CONVERT(VARCHAR(10), OrderDate, 120)) 
           FROM Orders 
           WHERE OrderDate > DATEADD(DD, -4, GETDATE()) 
           ORDER BY ', ' + QUOTENAME(CONVERT(VARCHAR(10), OrderDate, 120)) DESC
           FOR XML PATH('') 
           ), 1, 2, '')
-- Just to check how we are getting the column names
PRINT @ColumnNames

Few notes on above query:
1. DISTINCT is needed because one OrderDate is appearing multiple times in the table.
2. TOP 100 is needed because I want to order the dates in the descending order. Since our query is actually a sub-query, TOP hack is needed. I put the number 100 just randomly, I could have simply used TOP 3 because I want only top 3 dates.  When you are not sure about the number of columns you can either put a relatively large number like 100 or TOP 100 PERCENT.
3. In WHERE clause I'm restricting the dates to last 3 dates from getdate() i.e. from "TODAY".
4. I'm also converting OrderDate to varchar and using the style 120 to get the dates in the desired format.  In my case I want the dates in yyyy-mm-dd, that's why the style 120.
This is how we get column names from above query: [2015-11-13], [2015-11-12], [2015-11-11]

Now here is our PIVOT query by using dynamic column names created above:
DECLARE @ColumnNames VARCHAR(1000)

-- This part is already explained above
SELECT @ColumnNames = 
           ', ' + QUOTENAME(CONVERT(VARCHAR(10), OrderDate, 120)) 
           FROM Orders 
           WHERE OrderDate > DATEADD(DD, -4, GETDATE()) 
           ORDER BY ', ' + QUOTENAME(CONVERT(VARCHAR(10), OrderDate, 120)) DESC
           FOR XML PATH('') 
           ), 1, 2, '')

-- Preparing our PVIOT query
SET @Query = 'SELECT ProductName, ' +
@ColumnNames + '
        ( SELECT ProductName
          FROM Orders
        ) AS M
         SUM( Quantity )
         FOR OrderDate IN ( '
+ @ColumnNames + ' ) 
       ) AS P
--Executing @Query to get the result


You may also like to see my other post on Pivot: PIVOT Multiple Columns in SQL Server

Mangal Pardeshi

Thursday, November 12, 2015

Fastest way to find the row count of all the tables in SQL

Let me show you 2 simple queries to find the row count of all the tables in a Database.

Some time back I shared a trick to quickly find the row count of a table in SQL Server.  Now lets take it to next level and try to get the row count of all the tables in a Database.  There are 3-4 ways to do that, but I’m going to show you only 2 very identical methods.

1. Using Catalog View sys.partitions:

Here is the script to find the row count of all the tables using sys.partitions.  If you are a normal developer, who doesn’t have high level of permissions on a production Servers, this is the best method for you to find the row count of all the tables quickly.
           ,SUM(P.ROWS) AS [RowCount]
FROM sys.partitions AS P
INNER JOIN sys.tables AS T


2. Using DMV sys.dm_db_partition_stats:

           ,SUM(S.ROW_COUNT) AS [RowCount]
FROM sys.dm_db_partition_stats AS S
INNER JOIN sys.tables AS T


Actually nothing to chose between 2 methods, they both are quite identical, performance wise also both are very same.  Only difference between them is permissions needed to execute them.  While catalog view sys.partitions requires membership in the public role.  Effectively even if you have only “read only” access on a database, you can still use the 1st method.  On the other hand it requires VIEW DATABASE STATE permission to query the sys.dm_db_partition_stats dynamic management view.  So if you are a normal developer “sometimes” it is difficult to get that level of permissions.

And yes, as I already mentioned previously there is a chance that you will get the approximate row count if statistics are not updated.  This applies to both the methods.

To know more about the views see:
1. sys.partitions
2. sys.dm_db_partition_stats

See my couple of other posts on similar topics:
1. Fastest way to find the row count of a table in SQL Server
2. Find the row count of temporary tables in SQL Server

Mangal Pardeshi

Friday, November 6, 2015

Disk space monitoring of SQL Server

Recently I was working on creating a custom dashboard to monitor the health of all the database servers.  One thing I wanted to add to the dashboard was “Available Disk Space” on the data drives of SQL Server instances.  And I thought I’m not going to get this information in any system table or DMV and I’ll need to get the disk space information via some window commands or something.  Then I came across this dynamic management function sys.dm_os_volume_stats ,  which provided the exact information I was looking for.


Returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server. Use this dynamic management function to check the attributes of the physical disk drive or return available free space information about the directory.

Here is the script that gives you the useful information about the total and available size of attached data volumes on SQL Server instance.
SELECT DISTINCT d.logical_volume_name AS LogicalName
,d.volume_mount_point AS DriveName
,CONVERT(NUMERIC(32,2),d.available_bytes *1.00/(1048576.0 *1024 ))AS FreeSpaceInGB
,CONVERT(NUMERIC(32,2),d.Total_Bytes *1.00/(1048576.0 *1024 )) AS TotalSizeInGB
FROM sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID) d


This script is very useful in monitoring the data volumes, tracking the growth of the data, detecting the low disk space on the data drive of SQL Server etc.  I have implemented the low disk space alert on my Servers using this script.

Mangal Pardeshi

Wednesday, November 4, 2015

Row Count of Temporary Tables

In this post I’ll show you a quick trick to find the rowcount of temporary tables in SQL Server.
Imagine a situation where you are executing a stored procedure that has many SQL queries. In the stored procedure there are few temporary tables.   Query is creating temporary tables, inserting rows into them and then performing some actions.  While this stored procedure is executing you want to track the progress of the execution.  To be more specific you want to know how many rows are inserted into a particular temporary table.  Knowing the row count of a temporary table in another sessions seems to be pretty impossible, right?   Actually it is very easy.  By using below script:
FROM TEMPDB.sys.dm_db_partition_stats AS S
Note:  To keep it simple I’m assuming you don’t have partitioned tables, anyways it is pointless to create partitions in a temporary table. 
By executing above script you can get the row count of all the temporary tables created on the server.  In my last post Fastest way to find the row count of a table I mentioned about the system DMV maintained by the SQL Server sys.dm_db_partition_stats.  sys.dm_db_partition_stats returns page and row-count information for every partition in the current database.  We also know that all the temporary tables are created in the tempdb database.  So if we execute the sys.dm_db_partition_stats in the tempdb we can get the row count of the tables in the tembdb database and also the temporary tables created by users.
BUT, there is one thing you should be aware.  When we create the temporary table we know that it gets created in the tembdb database but not with the exact same name.  SQL Server engine adds some extra (random) characters into the name.  Mostly lots of underscores followed by some number.   See the below image, I created one temporary table #T and immediately executed above script:
You just need to little smart while giving the name to temporary tables.  Otherwise by habit many users can create temporary table with same name (typically #temp or #t) then it will be difficult to know which table is created by which user.  So if you give some meaningful names while creating temporary table (e.g. #OrdersLastMonth), above script to know the row_count of temporary tables can really help you.
I hope this script of finding the row count of temporary tables in SQL Server will be useful to you.  Do let me know your feedback.
Mangal Pardeshi

Sunday, November 1, 2015

Fastest way to get the Row Count of a table

Finding the row count of a table is probably the most frequently performed task by SQL developers/programmers.  So the obvious thing we all look for is: what is the fastest way to find the row count of a table?  Of course there are few ways, I’ll discuss them in this post.  However, if you ask me the same question my immediate response without blinking an eye will be using built-in stored procedure SP_SPACEUSED.  It is very easy to use with simple syntax:
sp_spaceused your_table_name
and within a second you will get the rowcount of the given table, doesn't matter how big the table is. To know more about the SP_SPACEUSED please see: sp_spaceused (Transact-SQL)


Now there are certain things you need to keep in mind while using sp_spaceused:
1. sp_spaceused relies on DMV sys.dm_db_partition_stats to get the rowcount.  So there is a chance that you will get the approximate row count if statistics are not updated.  To get the exact count you can update the statistics using DBCC UPDATEUSAGE .  However this can take long time if the table is really huge. 

2. If the table is part of the default dbo schema then you don’t need to pass the schema name in the syntax.  However if the table is created under some different or custom schema then you need to pass the schema name also.  But if you try to execute sp_spaceused schema_name.your_table_name you will get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

This become even more difficult if schema name has your domain name as well, e.g. your table name is domain\user_name.table_name and you try to use sp_spaceused domain\user_name.table_name you will get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.

In that case you just need to pass your table name within the single quotes:
sp_spaceused 'schema_name.your_table_name'
-- or

Now coming to back some positives:  This is my favorite method despite the fact that sp_spaceused sometimes can give me approximate row count.   First reason I like sp_spaceused is, it is extremely fast, syntax is simple, I don’t need to remember some DMV names.  Another reason is, I can use it even when some DML operations are being performed on the table. For example you are inserting large number of rows in a table, query is still executing and you want to know how many rows are inserted in the table.  sp_spaceused can you give you the row count of a table even middle of a INSERT query.  Very very useful in tracking the progress of big INSERT query. 

Now having said that row count we get from sp_spaceused or the DMV sys.dm_db_partition_stats is not always accurate, personally I have never observed any difference.   Even if there is a difference I guess it is negligible. 

Now quickly discuss the couple of other methods to find the row count of a table.

Using DMV sys.dm_db_partition_stats:

This is another very quick method to get the rowcount of a table.
SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('table_name')
AND index_id < 2;

Interesting thing is even sp_spaceused relies on the same table to get the row_count.  But of course writing and remembering sp_spaceused table_name is much easier than this, right?  You can also get the row count using sysindexes, but I’m skipping that.

Using COUNT function:

Now this is the most reliable, known but also a slow method to find the row count of a table, especially if the table size  is in millions or billions.   Actually I don’t need to talk about the COUNT function, because everyone knows about it, but I’m mentioning it in this post because of a myth.  There is some misconception going around that if you write COUNT(1) instead of COUNT ( * ) you will get faster result.  That is a total myth.  Many experts have tested it, written about it and you can find many articles online comparing the performance of COUNT(1) and COUNT ( * )  and concluding that there is absolutely no performance difference between them.   Here is one such article by Beaulin Twinkle: SQL Server: count(*) or count (1) or count(”) Which is better?

Mangal Pardeshi

Thursday, October 29, 2015

LPAD and RPAD in SQL Server using FORMAT

In SQL Server 2012 Microsoft introduced a new T-SQL function FORMAT.  This function returns a value formatted with the specified format and optional culture.  This function can be very useful in formatting Numeric and Date values.  In this post I’ll show you a simple use of FORMAT function to do the padding similar to LPAD and RPAD functions in Oracle.

First lets create some sample data:

--Now insert the table Temp with some sample data:



First lets see how to do the LPAD, will talk about RPAD separately because it need some extra efforts.  Will try to do the padding till 5 digits.  Means if there is only 1 digit in column then we need to add extra 4 zeros to the left.  With FORMAT function it actually becomes very very easy.  If you know the syntax of FORMAT function =>> FORMAT ( value, format [, culture ] )  then you just need to put ‘00000’ (number of digits you want to do the padding) at the place of format. 


For the RPAD you need to do little differently.  While defining the format of ‘00000’ we need to add ‘#’ (which represent the value/column) before it:


See the combine result of above 2 queries:


For more about the function see: FORMAT (Transact-SQL)

Also check my post about new T-SQL functions introduced in SQL Server see: New T-SQL programming enhancements in SQL Server 2012

In my future posts I’ll try show more different ways we can use FORMAT function.

Mangal Pardeshi

Monday, October 26, 2015

Maximum number of Tables in a SQL Database

Recently somebody asked this question on Stackoverflow forums “How many tables we can create on a SQL Server database”.  And I thought I should write about it on my blog.   Though for normal SQL programmers this shouldn’t matter and mostly some weird people ask this question in interviews, and I don’t know why. 

Anyways, since we are talking about it let me answer this question.  Actually in SQL Server, Microsoft has not defined any limit on number of tables but there is a limit on the number of Objects in a database. 

According to Maximum Capacity Specifications for SQL Server Tables per database are limited by number of objects in a database.  Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

So if there are NO other objects created in a database you can create 2,147,483,647 number of tables in SQL Server database.

And if I’m I’m right then I guess this limit is pretty much constant since SQL Server 2005 to SQL Server 2014.

To know more about the maximum sizes and numbers of various objects defined in SQL Server components you can check out: Maximum Capacity Specifications for SQL Server

Mangal Pardeshi

Saturday, October 24, 2015

How to Calculate Running Totals in SQL Server

Back in 2009 I wrote this post about How to Calculate the Running Totals in SQL Server.  Back then(before SQL Server 2012)  this was one of the “difficult” queries to write.  We used to do that by writing a  co-related sub-query or some time even Cursor was used for calculating running totals.

Then Microsoft came up with this much awaited enhancement in OVER() clause in SQL Server 2012.  Now the OVER() is extended fully to aggregate functions like SUM, COUNT and AVG.  In simple words now we can write ORDER BY in OVER() clause for aggregate functions as well.  And this feature has made running total calculations extremely easy and efficient.

On the same line with my previous post, the different scenarios I’ll be covering in this post are -

  • Running Total for all records.
  • Running Total on each Group of Records.
  • Running Total on each Date.

Create Sample Data:

-- Create Table
( ShipId INT,
OrderId INT,
ShipDate DateTime,
Quantity INT
-- Load Sample Data
(1,1, '2015-10-01 12:06:16:820PM',10),
(2,1, '2015-10-02 04:16:16:820PM',15),
(3,2, '2015-10-01 01:26:16:820PM',20),
(4,2, '2015-10-03 02:40:16:820PM',12),
(5,2, '2015-10-04 01:05:16:820PM',15),
(6,3, '2015-10-03 05:25:16:820PM',25),
(7,3, '2015-10-04 03:50:16:820PM',50),
(8,3, '2015-10-05 07:40:16:820PM',30),
(9,4, '2015-10-06 03:30:16:820PM',20),
(10,4, '2015-10-07 04:20:16:820PM',10),
(11,5, '2015-10-05 05:05:16:820PM',35)

This is how our sample data looks like:

Case I : Running Total For all records.  Now this is the simplest case where I’ll show you how to calculate the running totals for all the records in the order of ShipDate.  If you remember old ways then we needed to write some weird looking co-related sub query to achieve that.  But this can be easily achieved now by simply SUM(Quantity) OVER(ORDER BY ShipDate).
Note: I’m assuming you know the basics of OVER() clause.

,SUM(Quantity) OVER(ORDER BY ShipDate) AS RunningTotal
Here is the result returned by above query :

Case II: Running Total on each Group of Records.
Now instead of calculating the running total for each record we will calculate the running total for each OrderId.  To do that we just need to ad PARTITION BY OrderID in the OVER clause.

,SUM(Quantity) OVER(PARTITION BY OrderId ORDER BY ShipDate) AS RunningTotal
ORDER BY OrderId, ShipDate

Case III: Running Total on each Date.
Now we will see how to calculate the Running Total for each day. For that of course we need to put ShipDate in the PARTITION BY but also need to remove the time portion from the date.  We can do that by simply converting ShipDate to DATE format.  See below query:

,SUM(Quantity) OVER(PARTITION BY CONVERT(DATE, ShipDate) ORDER BY ShipDate) AS RunningTotal

I hope you like this.  Do give me your feedback.

Mangal Pardeshi

Thursday, October 22, 2015

New T-SQL programming enhancements in SQL Server 2012

In SQL Server 2012 and 2014 Microsoft introduced many new features and enhancements. In fact there were 14 new in-built functions introduced in SQL Server 2012.  However I noticed that for some reason I don’t see many people using them.  In my company we upgraded all our servers to 2014 from 2008R2 almost a year back but I see none of the developers using them or even aware.  Also in last 6 months I interviewed many people and one of the question I asked to candidates that are you aware of any new features of SQL Server 2012/14?  While most of the people talked about the changes in SSIS, project deployment etc but very few knew about the 14 new in-built functions introduced in SQL Server 2012. 

So I thought of writing about these new in-built functions. In this post I’m not going to write the details of these function.  This is just going to be a reminder that now we have 14 more in-built functions that can help us in writing SQL code. 

Conversion functions

1. PARSE (Transact-SQL) : PARSE function converts the string expression to the requested data type. And if it fails to translate then raises an exception.

2. TRY_CONVERT (Transact-SQL): Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

3. TRY_PARSE (Transact-SQL): Returns the result of an expression, translated to the requested data type, or null if the cast fails

You might wonder what is the difference between PARSE and CONVERT or may be PARSE and TRY_PARSE; I’ll try to explain that in my next few posts.


Date and time functions

4. DATEFROMPARTS (Transact-SQL): Returns a date value for the specified year, month, and day.

5. DATETIME2FROMPARTS (Transact-SQL): Returns a datetime2 value for the specified date and time and with the specified precision.

6. DATETIMEFROMPARTS (Transact-SQL): Returns a datetime value for the specified date and time.

7. DATETIMEOFFSETFROMPARTS (Transact-SQL): Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.

8. EOMONTH (Transact-SQL): Returns the last day of the month that contains the specified date, with an optional offset.

9. SMALLDATETIMEFROMPARTS (Transact-SQL): Returns a smalldatetime value for the specified date and time.

10. TIMEFROMPARTS (Transact-SQL): Returns a time value for the specified time and with the specified precision.


Logical functions

11. CHOOSE (Transact-SQL): Returns the item at the specified index from a list of values.

12. IIF (Transact-SQL): Returns one of two values, depending on whether the Boolean expression evaluates to true or false.


String functions

13. CONCAT (Transact-SQL): Returns a string that is the result of concatenating two or more string values.

14. FORMAT (Transact-SQL): Returns a value formatted with the specified format and optional culture.

To know more about each of these new functions you can click on the names of the functions.  In my future posts I’ll try explain each of these functions in details with some examples and situation where they can be useful.


To know more about the new features introduced in the SQL Server please follow below links:

What's New in SQL Server 2012
What's New in SQL Server 2014

A Sneak Peek into the Future: What's New in SQL Server 2016

Mangal Pardeshi