Feeds:
Posts
Comments

2015 in review

The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 490,000 times in 2015. If it were an exhibit at the Louvre Museum, it would take about 21 days for that many people to see it.

Click here to see the complete report.

EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null is one of the new error messages noticed in SQL Server 2012 and above. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS shipped with SQL Server 2012.

Lets discuss this in detail:
Message Number: 11553

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null.

Error Generation:
Let me create a sample stored procedure to generate this error.

Use AdventureWorks2014
GO
--DROP PROCEDURE usp_Sample
--GO
CREATE PROCEDURE usp_Sample
AS
SELECT  
  [BusinessEntityID]
, [FirstName]
, [MiddleName]
, [LastName]
FROM [HumanResources].vEmployee ORDER BY BusinessEntityID 
GO
EXEC usp_Sample
GO

Error number 11553.1.1

You can see that the above stored procedure is executed successfully and it has ONE result set.

Lets try to execute it using WITH RESULT SETS clause.

USE AdventureWorks2014 
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
  [First Name] Name NOT NULL,
  [MiddleName] Name NOT NULL,
  [LastName] Name NOT NULL
));
GO
--OUTPUT
Msg 11553, Level 16, State 1, Procedure usp_Sample, Line 22
EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #3 in result set #1, and the corresponding value sent at run time was null.

Ooops…… I am unable to execute it properly. It returned the error message.

Error number 11553.1.2

Resolution:
Why this error ? Because, I specifically mentioned in the RESULT SETS that none of the columns should return NULL and I did not realize that stored procedures return some NULL values in the middle name column and due to this I got this error. So, when you mention any column as a NOT NULL in RESULT SETS, please make sure that the stored procedure must NOT return NULL VALUE for that particular column. Lets re-write the RESULT SET and this time we need to mention, middle name column is NULL as shown below.

USE AdventureWorks2014 
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
  [First Name] Name NOT NULL,
  [MiddleName] Name NULL,
  [LastName] Name NOT NULL
));
GO
--OUTPUT

Error number 11553.1.3

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, along with the nullable type, make sure that the data that comes from stored procedure should match with the nullable type. In other words, if you define any column in RESULT SETS clause as NOT NULL column then make sure that stored procedure MUST NOT return NULL value in that particular column to avoid such errors.

Resource Governor is one of the best features shipped with SQL Server 2008. We use this feature to handle SQL Server workload and resource allocation. However, if you go into further details of this feature, you will be shocked to see the level of control it gives you to control over CPU, memory on the basis of incoming requests from the application/user etc. But I have been hearing a very common QUESTION since SQL Server 2008 and that is, can we control physical IO using resource governor and the ANSWER is YES, you can do it in SQL SERVER 2014 & Above. But How ?

In SQL Server 2014, we have an additional control in resource governor namely CONTROL PHYSICAL IO. In other words, if you would like to restrict a user/application to use limited physical IO, you can restrict it with this additional control. You can implement this additional control in resource governor by just setting up two options which is MIN_IOPS_PER_VOLUME & MAX_IOPS_PER_VOLUME.

Let me demonstrate how to control physical IO in SQL Server 2014 step by step.

Step 1:
First of all, lets create a pool as usual and restrict its MAX_IOPS_PER_VOLUME limit to 50 ONLY, which means that whatever set of users / applications will be used, this pool cannot exceed 50 Physical IO.

 USE master 
 GO
--DROP RESOURCE POOL Sample_Pool_Restrict_IO;
--GO
CREATE RESOURCE POOL Sample_Pool_Restrict_IO WITH
(
       MAX_IOPS_PER_VOLUME = 50,
       MIN_IOPS_PER_VOLUME = 1
);
GO

Step 2:
Once we created the pool, lets create the workload group. This is also a usual step while configuring resource governor.

 USE master 
 GO
 --DROP WORKLOAD GROUP Sample_Workload_Group_Restrict_IO
 --GO
 CREATE WORKLOAD GROUP Sample_Workload_Group_Restrict_IO 
 USING Sample_Pool_Restrict_IO;
 GO

Step 3:
Let’s, create a test user, later on I will demonstrate how resource governor will restrict physical IO for this test user.

 USE master
 GO
 CREATE LOGIN dba WITH PASSWORD = 'imran.1234@';
 GO
 ALTER SERVER ROLE [sysadmin] ADD MEMBER [dba]
 GO

Step 4:
Create a classifier function and define if the above created user is the current user. Then classifier will assign the restricted IO workload group to this user. Then each time this user tries to use physical IO, it  CAN’T go beyond 50. SQL Server will restrict this user up to 50.

USE MASTER;
GO
--DROP FUNCTION dbo.fnIOClassifier
GO
CREATE FUNCTION dbo.fnIOClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
       DECLARE @GroupName SYSNAME
          IF SUSER_NAME() = 'dba'
            BEGIN
              SET @GroupName = 'Sample_Workload_Group_Restrict_IO'
           END
       ELSE
       BEGIN
              SET @GroupName = 'default'
       END
       RETURN @GroupName;
END
GO

Step 5:
Let’s assign the classifier function (created in the above step) to resource governor and reconfigure the resource governor to implement new settings.

USE master; 
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnIOClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Step 6:
Now, we are ready to test the new feature (Control Physical IO). To test the feature, let’s open performance monitor, add counter and then select resource pool stats object, further select Disk read IO/sec and add default and custom Pool (Sample_Pool_Restrict_IO) created in step 1 and press ADD BUTTON as shown below.

Resource Governor 1.0

Step 7:
Now, we setup performance monitor to test the physical IO control feature via resource governor. Lets login with any user except the one created in step 3 and run the given below script and view the results in performance monitor. In my case, I logged in with sa and executed the script.

USE AdventureWorks2014
GO
DBCC DROPCLEANBUFFERS
GO
EXEC sp_MSforeachtable 'SELECT * FROM ?'
GO

Resource Governor 1.1

Note: In the above results, you can easily view that the DISK READ IO/SEC reached the maximum of 122 and it may go far from it as well because we did not restrict DISK READ IO/SEC for sa user.

Step 8:
This time lets login with ‘dba’ user which we created in step 3 and execute the same script which we executed in the above step.

USE AdventureWorks2014
GO
DBCC DROPCLEANBUFFERS
GO
EXEC sp_MSforeachtable 'SELECT * FROM ?'
GO

Resource Governor 1.2

Note: In the above results, you can easily view that the DISK READ IO/SEC reached the maximum of 50 and why is it not going beyond 50 is because dba user has been restricted by resource governor using physical IO control feature.

Conclusion:
As you can see above that this feature really gives DBA’s more control on physical IO and it would be very handy for the DBA’s where they have serious problems with I/O which can be from users / applications.

Earlier, I have written a blog post about how to split a single row data into multiple rows using XQuery. Today, I came across a situation where I had to split a single column data into multiple columns using delimiter.

Lets me create a sample to demonstrate the solution.

Sample :

USE TEMPDB 
GO
DROP TABLE [dbo].[tbl_Employee]
GO
CREATE TABLE [dbo].[tbl_Employee](
      [Employee ID] INT IDENTITY(1,1) ,
      [Employee Name] VARCHAR (100) NOT NULL)
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name]) 
VALUES ('Andreas Berglund T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name]) 
VALUES ('Sootha Charncherngkha T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name]) 
VALUES ('Peng Wu')
GO

--Browse the data
SELECT * FROM dbo.[tbl_Employee]
GO

Split single column into multiple columns.1.0

Solution :
Given below is the solution, where we need to convert the column into xml and then split it into multiple columns using delimiter. You can use any delimiter in the given below solution. In my sample, I used a blank space as a delimiter to split column into multiple columns.

USE TEMPDB 
GO

DECLARE @delimiter VARCHAR(50)
SET @delimiter=' '  -- <=== Here, you can change the delimiter.
;WITH CTE AS 
( 
    SELECT 
        [Employee ID], 
        [Employee Name],
        CAST('' + REPLACE([Employee Name], @delimiter , '') + '' AS XML) 
        AS [Employee Name XML]
    FROM  [tbl_Employee] 
)
SELECT 
     [Employee ID], 
     [Employee Name],
     [Employee Name XML].value('/M[1]', 'varchar(50)') As [First Name],
     [Employee Name XML].value('/M[2]', 'varchar(50)') As [Last Name],
     [Employee Name XML].value('/M[3]', 'varchar(50)') As [Middle Name]
 
FROM CTE 
GO

Split single column into multiple columns.1.1

Let me know if you come across this situation and its solution.

Earlier this week, my team & I were working on financial reports and we developed some giant scripts in order to generate report data. Once we were done with the report, we came to know that we did not implement dynamic sorting in the report. Ooopssss !!!. Now do we need to re-write the query and convert into dynamic query ? :( Of course NOT.
What you can actually do is, write a conditional case and make it dynamic sort but it will increase the size of your query depending upon how many conditions you have.

In today’s post I will implement the dynamic sorting using CHOOSE function & CASE Statement. First of all, I will find the column number using CASE Statement and then pass the Column order number to CHOOSE function to actually sort the column. It will actually reduce the size of your query.

Given below is the script which will dynamically sort the query.

Use AdventureWorks2014
GO

DECLARE @SortCoulmnName VARCHAR(50) = 'OrderDate';
DECLARE @SortColumnNumber AS INT 

SET @SortColumnNumber = CASE 
When @SortCoulmnName='SalesOrderID' THEN 0 
WHEN @SortCoulmnName='OrderDate' THEN 1
WHEN @SortCoulmnName='DueDate' THEN 2
WHEN @SortCoulmnName='ShipDate' THEN 3 
ELSE 0
END
-- By Default, it will sort on first column

SELECT SalesOrderID
,OrderDate
,DueDate
,ShipDate 
,Status
FROM
Sales.SalesOrderHeader
ORDER BY 
CHOOSE(@SortColumnNumber,SalesOrderID,OrderDate,DueDate,ShipDate) DESC
GO

dynamic order by 1.1

Let me know if you came across this issue and its solution as well.

Few days ago, I created some users in my development environment and gave them access to use my development database. After sometime, I received a complaint that when they run a normal query it is working fine but when they try to run a query along with execution plan (Ctrl + M), SQL server generates given below error.

Let me explain this error in detail :

Message Number: 262

Severity : 14

Error Message: SHOWPLAN permission denied in database ‘AdventureWorks2012’.

Error Generation:

Let me create a sample to demonstrate this error.

USE AdventureWorks2012
GO

--Create a user
CREATE USER Imran WITHOUT LOGIN;
GO
--Grant select permission to Imran user
GRANT SELECT ON [HumanResources].[Department] TO Imran;
GO

-- Press Ctl+M to Include Actual Execution Plan
EXECUTE AS USER = 'Imran';
SELECT * FROM [HumanResources].[Department] ;
REVERT;

Msg 262, Level 14, State 4, Line 12
SHOWPLAN permission denied in database ‘AdventureWorks2012’.

ShowPlan error.1.1

Ooopps…… I am unable to show the actual execution plan (Ctrl + M) of the select query as shown above.

Resolution:

The resolution is very simple, actually, once you created the user you did not grant SHOWPLAN access to the USER (Imran). Lets give this user the SHOWPLAN access. Given below is the script.

Grant SHOWPLAN access

USE AdventureWorks2012
GO
GRANT SHOWPLAN TO Imran
GO
-OUTPUT

Command(s) completed successfully.

Now, the SHOWPLAN access has been granted, lets re-run the above query with execution plan and it works as shown below.

ShowPlan error.1.2

Conclusion :

Remember, whenever you want any user to include the execution plan in the query, you MUST give him SHOWPLAN access.

Follow

Get every new post delivered to your Inbox.

Join 6,681 other followers