SQL SERVER – Performance Test – sqlcmd vs SSMS

The primary reason, I keep on writing blog is all of you who keep on asking an interesting question as I publish new blog posts. Yesterday I wrote about SQL SERVER – Performance Test – oStress vs SSMS and later the same day I have received an email from my clients of Comprehensive Database Performance Health Check. The question was about Performance Test – sqlcmd vs SSMS.

I was very clear yesterday that oStress is not the utility to do any T-SQL operation in SQL Server, it is just a tool provided to use for the testing. However, my client wanted to know about the performance difference between sqlcmd and SSMS. Now sqlcmd is definitely a good tool to do various sqlcmd operations. While I personally prefer PowerShell, I have nothing against sqlcmd.

sqlcmd vs SSMS

SSMS is built for developers to write queries and DBAs to manage the database, whereas sqlcmd is built to automate test runs and maintenance tasks. They are built for absolutely different purposes and visions but we can for sure consider comparing the performance of them. Now let us run a simple test.

I will first create a test table in the database and will insert some data into the table. Right after that, I will run the same test in sqlcmd. We will compare various scenarios and compare the performance.

Set up

First, create a simple table in the database. I have kept the setup same as the earliest tests.

USE [SQLAuthority]
GO
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](500) NOT NULL DEFAULT ('I hope you have a great day!')
) ON [PRIMARY]
GO

After every test we will truncate the table by running the following command:

TRUNCATE TABLE [SQLAuthority].[dbo].[TestTable]

SSMS Test

Now let us run the insert query in SSMS and measure the time taken to run the query.

SET NOCOUNT ON
INSERT INTO [SQLAuthority].[dbo].[TestTable]
VALUES(DEFAULT)
GO 10000

The query above will run for 5 seconds on my machine. It took a more amount of time in my earlier test as well here.

sqlcmd Test

Now let us run the insert query in sqlcmd and measure the time taken to run the query. While we can measure the time of the sqlcmd with the help of a clock, I have used a very simple method to display the current time on the screen to measure the time of running the query.

First, open the command prompt and enter the following command to bring up the sqlcmd.

sqlcmd -S "Quick\SQL19" -d SQLAuthority -E

Next, run the following command. Please make sure that you have entered one extra line the command so the last GO statement runs automatically.

SET NOCOUNT ON
SELECT GETDATE();
GO
INSERT INTO [SQLAuthority].[dbo].[TestTable] VALUES (DEFAULT);
GO 10000
SELECT GETDATE();
GO

When we ran the above sqlcmd query it actually took around 4 seconds of the time.

Summary – sqlcmd vs SSMS

It is very clear that sqlcmd is executing the insert queries much faster than SSMS. Please understand that the real use of the sqlcmd is not for running your routine queries. It is used to automate your maintenance tasks and reparative operations on SQL Server. With that said in our tests sqlcmd was really a clear winner.

With that said, I am still going to use SSMS for my query developments and daily routine operations.

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