SQL SERVER – How to use Procedure sp_user_counter1 to sp_user_counter10

There are many performance counters available in SQL Server which can be used to monitor various parameters of SQL Server engine. Have you ever been into a situation where you want to see value in performance counter for a query which you have returned? Imagine a situation where you want to look at fragmentation or numbers of rows in a table over a period of time. And you want to plot the values against standard performance counters available.

SQL Server had this feature from a long time. I noticed it recently while working with one of my friend. He was showing me the impact of a number of database vs the number of threads in the AlwaysOn availability group. He showed me graph and my first question was – how did you plot the number of databases with that counter? The answer was – using a user counter. I did further research and learned something new so sharing that with you in the form of this blog.

There are ten system procedures which are available in SQL Server, which allows user to set specific counter values. They are sp_user_counter1, sp_user_counter2… till sp_user_counter10. That’s why they are called user settable counters. Below is the screenshot of Performance Monitor. (Start > Run > PerfMon)

These counters can be populated using stored procedures. Counter Object name and stored procedure names are same. This means the values for “User Counter 1” can using sp_user_counter1, values for “User counter 2” can using sp_user_counter2 and so on.

Here is a quick example to demonstrate the usage.
-- Drop database, if already present
USE MASTER
GO
IF EXISTS (
SELECT *
FROM sys.databases
WHERE NAME = 'SQLAuthority'
)
BEGIN
ALTER DATABASE
SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE;
DROP DATABASE SQLAuthority;
END
GO
-- Create New Database
CREATE DATABASE SQLAuthority
GO
-- Create Objects Needed for Demo
USE SQLAuthority
GO
CREATE TABLE TrackMe (i INT)
GO
-- while loop to populate the data and populate the counter
SET NOCOUNT ON
DECLARE
@NumberOfRows INT=0,@NumberOfRows_2 INT=0
WHILE (@NumberOfRows<25)
BEGIN
INSERT INTO
TrackMe VALUES (1), (2), (3)
SELECT @NumberOfRows = COUNT(*) FROM TrackMe
EXECUTE sp_user_counter1 @NumberOfRows        -- 3, 6, 9, 12...
WAITFOR delay '00:00:02'
SELECT @NumberOfRows_2 = @NumberOfRows*2
EXECUTE sp_user_counter2 @NumberOfRows_2    -- 6, 12, 18, 24...
WAITFOR delay '00:00:02'
END
-- loop finished reset the counters
EXECUTE sp_user_counter1 0
EXECUTE sp_user_counter2 0

Here is the Performance counter graph where I have captured the counters.

Let me explain little logic of the script. My script is plotting number of rows in the table as counter1 and double of that as counter2. There is a delay of 2 seconds added between each counter so that we can see steps. Once the number of rows in the table are around 50, I am ending the loop and resetting the values to zero. That’s when you are seeing a drop at the end.

If you look at the procedure, it is straight forward. Believe me, here is the code.

CREATE PROCEDURE sys.sp_user_counter1 @newvalue INT AS
DBCC
setinstance ('SQLServer:User Settable', 'Query', 'User counter 1', @newvalue)

Which means, we can also call undocumented command DBCC SETINSTANCE directly to set value for the counters.

Can you think of more innovative use of drawing value in performance monitor?

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – How to Create Stored Procedure in MySQL

MySQL supports Stored Procedures which can execute set of codes by applying relevant business logics. In this post we will see how to create a stored procedure and execute it.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

Suppose you want to create a stored procedure which will accept item_description and returns the total sales_amount for the enire period, you can do it as shown below

DELIMITER $$
CREATE PROCEDURE Test.usp_get_sales
(
param_item_description VARCHAR(100)
)
BEGIN
SELECT
item_description,SUM(sales_amount) AS sales_amount FROM items NATURAL
JOIN sales
WHERE item_description=param_item_description
GROUP BY item_description;
END;
$$
DELIMITER
;

Note that the creation of the stored procedure starts with setting the Delimiter $$. The default delimiter for MySQL statements are semicolon so in order to instruct the MySQL engine about the start and end of the stored procedure block, you need to use a different delimiter (which in this case $$ is used).

Now you can execute a stored procedure using CALL keyword as shown below.

Execution 1

CALL usp_get_sales('Television');

When you execute the above code, the result is

Item_description sales_amount
Television 3600.00

Execution 2

CALL usp_get_sales('laptop');

When you execute the above code, the result is

Item_description sales_amount
laptop 6800.00

Note: The parameters do not start with @ like we use in SQL Server. So in order to differentiate between the actual column name and parameter name, the name param_item_description is used. You may need to use different naming conventions as you like.

I have previously written a similar article here: MySQL – How to Create Stored Procedure.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people -

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

The above query will return results something very similar to the following:

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

Now over here when you scroll down you will notice Port Details.

It is that easy to find the port of the second instance of SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Retrieve Maximum Length of Object Name with sp_server_info

A very common question which I often receive is -

“How big my table name can be?”

The answer is simple 128.  You can figure this out by executing stored procedure sp_server_info on SQL Server.

Execute following stored procedure in SQL Server.

EXEC sp_server_info

It will return following resultset.

attribute_id attribute_name attribute_value
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2012 - 11.0.3000.0
10 OWNER_TERM owner
11 TABLE_TERM table
12 MAX_OWNER_NAME_LENGTH 128
13 TABLE_LENGTH 128
14 MAX_QUAL_LENGTH 128
15 COLUMN_LENGTH 128
16 IDENTIFIER_CASE MIXED
17 TX_ISOLATION 2
18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
19 SAVEPOINT_SUPPORT Y
20 MULTI_RESULT_SETS Y
22 ACCESSIBLE_TABLES Y
100 USERID_LENGTH 128
101 QUALIFIER_TERM database
102 NAMED_TRANSACTIONS Y
103 SPROC_AS_LANGUAGE Y
104 ACCESSIBLE_SPROC Y
105 MAX_INDEX_COLS 16
106 RENAME_TABLE Y
107 RENAME_COLUMN Y
108 DROP_COLUMN Y
109 INCREASE_COLUMN_LENGTH Y
110 DDL_IN_TRANSACTION Y
111 DESCENDING_INDEXES Y
112 SP_RENAME Y
113 REMOTE_SPROC Y
500 SYS_SPROC_VERSION 11.00.3000

In the result set you can notice there is a row with information TABLE_LENGTH and the value is 128. There are few other information related to server is also listed in the result set.

Though, a regular tablename can be 128 character long, a temporary table cannot be longer than 116 character. You can read the detailed blog post over here Maximum Allowable Length of Characters for Temp Objects is 116 – Guest Post by Balmukund Lakhani.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Select Columns from Stored Procedure Resultset

It is fun to go back to basics often. Here is the one classic question:

“How to select columns from Stored Procedure Resultset?”

Though Stored Procedure has been introduced many years ago, the question about retrieving columns from Stored Procedure is still very popular with beginners. Let us see the solution in quick steps.

First we will create a sample stored procedure.

CREATE PROCEDURE SampleSP
AS
SELECT
1 AS Col1, 2 AS Col2
UNION
SELECT
11, 22
GO

Now we will create a table where we will temporarily store the result set of stored procedures. We will be using INSERT INTO and EXEC command to retrieve the values and insert into temporary table.

CREATE TABLE #TempTable (Col1 INT, Col2 INT)
GO
INSERT INTO #TempTable
EXEC SampleSP
GO

Next we will retrieve our data from stored procedure.

SELECT *
FROM #TempTable
GO

Finally we will clean up all the objects which we have created.

DROP TABLE #TempTable
DROP PROCEDURE SampleSP
GO

Let me know if you want me to share such back to basic tips.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to INSERT data from Stored Procedure to Table – 2 Different Methods

Here is a very common question which I keep on receiving on my facebook page as well on twitter.

“How do I insert the results of the stored procedure in my table?”

This question has two fold answers – 1) When the table is already created and 2) When the table is to be created run time. In this blog post we will explore both the scenarios together.

However, first let us create a stored procedure which we will use for our example.

CREATE PROCEDURE GetDBNames
AS
SELECT
name, database_id
FROM sys.databases
GO

We can execute this stored procedure using the following script.

EXEC GetDBNames

Now let us see two different scenarios where we will insert the data of the stored procedure directly into the table.

1) Schema Known – Table Created Beforehand

If we know the schema of the stored procedure resultset we can build a table beforehand and execute following code.

CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
INSERT INTO #TestTable
EXEC GetDBNames
-- Select Table
SELECT *
FROM #TestTable;

The disadvantage of this code is that if due to any reason the stored procedure returns more or less columns it will throw an error.

2) Unknown Schema – Table Created at Runtime

There are cases when we do know the resultset of the stored procedure and we want to populate the table based of it. We can execute following code.

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.GetDBNames')
-- Select Table
SELECT *
FROM #TestTableT;

The disadvantage of this code is that it bit complicated but it usually works well in the case of the column names are not known.

Just note that if you are getting error in this method enable ad hoc distributed queries by executing following query in SSMS.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

I will be interested to know which of the above method do you use in your projects? and why?

Click to Download Scripts

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Here is one of the very common question I receive on SQLAuthority Facebook page. I usually answer them on Facebook but this one I find it very interesting so I decided to answer here.

“There are few of the stored procedure when I try to execute they return following message. Would you please explain what does it mean and is it alright to receive them?

The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.”

Very interesting question and before I explore it further let us answer execute following stored procedure.

-- First Stored Procedure
CREATE PROCEDURE TestSP
AS
DECLARE
@MyFirstParam INT
-- Additional Code
RETURN (@MyFirstParam)
GO

Now execute the stored procedure with following code.

EXEC TestSP
GO

When you execute the stored procedure it will return following message:

The ‘TestSP’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

If you notice this message, it is clear that this message is due to only one reason – we have declared a variable inside the scope of the Stored Procedure and we have assigned no value to the same procedure before we return the same variable. When any variable is not assigned any value it is by default assumed as a NULL. By Design stored procedures are not allowed to return the NULL value. Hence the value is automatically converted to zero (0) and returned.

We can verify above logic by running the following code:

DECLARE @TestVar INT
EXEC
@TestVar = TestSP
SELECT @TestVar TestVar
GO

You can see that when you run above code, instead of returning NULL value we get value of Zero (0) and in the message field we get above message.

Alternate Code

To avoid this situation you can modify above stored procedure to not return the value null. You can use ISNULL function before returning the values back from the stored procedure.

CREATE PROCEDURE TestSP
AS
DECLARE
@MyFirstParam INT
-- Additional Code
RETURN (ISNULL(@MyFirstParam,0))
GO

Above change in the code will automatically covert the NULL to Zero (0) and it will not show the above message.

Which one is better?

Now the question is which method is better – to use ISNULL and covert it to Zero or let SQL handle this and message show up. Personally I prefer to use ISNULL as that gives me clear idea and there is no ambiguity in my code. I will not have to write additional code to handle NULL when it shows up.

What is your opinion? Please share in the comment area.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)