SSMS and SOS, difference in execution times
-
Came a cross a bit of an oddity, execution times on the same system differ when run in SSMS and SOS on the same instance, not much of a difference but roughly 10%.
Anyone seen this before?
😎I'm running this on the latest version of SQL/SSMS/SOS/Win10 on a brand new i5/16Gb/SSD
- Eirikur Eiriksson - Friday, August 17, 2018 7:57 AM
There are specific default setting for queries in SSMS
It's possible some of these could influence the run time - Eirikur Eiriksson - Friday, August 17, 2018 7:57 AM
Can't say I've ever tested if I'm honest, Eirikur; I tend to use SSMS at work and SOS at home (as I use Windows 10 at work and Ubuntu 18.04 at home).
What way round are you seeing the performance swing and is it more obvious with certain queries? Is it only slower on x when you're returning a dataset, for example? I'll have a go myself afterwards and see what the speed difference is like on the work PC.
If it's worth the interest, I'll try on the Ubuntu home PC vs the home Windows laptop as well (SOS vs SSMS respectively), but that might be put down the PC specs and network.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Jonathan AC Roberts - Friday, August 17, 2018 8:07 AMEirikur Eiriksson - Friday, August 17, 2018 7:57 AM
There are specific default setting for queries in SSMS
It's possible some of these could influence the run timeThe SOS settings are almost identical and should not affect the results which are all generated on the server, I'm using my normal test harness which does all the execution on the server, collects the results there and then reports the results in a simple aggregating select query.
😎I've checked all the set options and those are the same, the isolation levels are the same, the query plans are the same, if I had any hair left on the top of my head, it would probably be in my palms right now😀
- Thom A - Friday, August 17, 2018 8:09 AM
In SOS the fastest queries are roughly 10% slower than in SSMS. I've tested this on few of my home systems on Win10, obviously cannot test on the Linux systems as those do not run SSMS. The fun thing is that if I run this against my Linux farm from a Win10 system, the results are more equal, if the SQL Server instance is on the same system as I'm running SSMS/SOS from, the results are consistently different although there are some methods where the results are more inconsistent.
😎Here is one of the tests I was running:
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE BIGINT = 2000000;
DECLARE @YEAR_RANGE INT = 10;
DECLARE @MONTH_RANGE INT = 12;
DECLARE @DAY_RANGE INT = 31;
DECLARE @FIRST_YEAR INT = 2008;/*
IF OBJECT_ID(N'dbo.TBL_TEST_DATEFROMPARTS') IS NOT NULL DROP TABLE dbo.TBL_TEST_DATEFROMPARTS;
CREATE TABLE dbo.TBL_TEST_DATEFROMPARTS
(
DFP_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_DATEFROMPARTS_DFP_ID PRIMARY KEY CLUSTERED (DFP_ID ASC)
,DFP_YEAR INT NOT NULL
,DFP_MONTH INT NOT NULL
,DFP_DAY INT NOT NULL
,DFP_FULL AS ((DFP_YEAR * 10000) + (DFP_MONTH * 100) + DFP_DAY) PERSISTED
);
-- Normal inline tally table construct, generates up to 10^9 rows.
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
-- Simple way of avoiding any conversion errors, does not cater for leap years.
,MAX_MONTH_DAYS (MN_NO,MN_MAX) AS (SELECT X.NO,X.MX FROM (VALUES (1,31),(2,28),(3,31),(4,30),(5,31),(6,30),(7,31),(8,31),(9,30),(10,31),(11,30),(12,31)) X(NO,MX))
,DATE_NUM_SOURCE (DFP_YEAR,DFP_MONTH,DFP_DAY) AS
(
SELECT
(ABS(CHECKSUM(NEWID())) % @YEAR_RANGE ) + @FIRST_YEAR AS DFP_YEAR
,(ABS(CHECKSUM(NEWID())) % @MONTH_RANGE) + 1 AS DFP_MONTH
,(ABS(CHECKSUM(NEWID())) % @DAY_RANGE ) + 1 AS DFP_DAY
FROM NUMS NM
)
INSERT INTO dbo.TBL_TEST_DATEFROMPARTS WITH (TABLOCKX) (DFP_YEAR,DFP_MONTH,DFP_DAY)
SELECT
DNS.DFP_YEAR
,DNS.DFP_MONTH
,CASE
WHEN DNS.DFP_DAY <= MMD.MN_MAX THEN DNS.DFP_DAY
ELSE MMD.MN_MAX
END AS DFP_DAY
FROM DATE_NUM_SOURCE DNS
INNER JOIN MAX_MONTH_DAYS MMD
ON DNS.DFP_MONTH = MMD.MN_NO
-- */DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TD DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @INT_BUCKET INT = 0;
DECLARE @DATE_BUCKET DATE = '2000-01-01';
DECLARE @FIRST_DATE DATE = '1899-12-31';INSERT INTO @timer (T_TXT) VALUES ('DRY RUN');
SELECT
@INT_BUCKET = DFP.DFP_YEAR
,@INT_BUCKET = DFP.DFP_MONTH
,@INT_BUCKET = DFP.DFP_DAY
FROM dbo.TBL_TEST_DATEFROMPARTS DFP
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DRY RUN');INSERT INTO @timer (T_TXT) VALUES ('DATEFROMPARTS');
SELECT
@DATE_BUCKET = DATEFROMPARTS(DFP.DFP_YEAR,DFP.DFP_MONTH,DFP.DFP_DAY)
FROM dbo.TBL_TEST_DATEFROMPARTS DFP
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DATEFROMPARTS');INSERT INTO @timer (T_TXT) VALUES ('DATEADD');
SELECT
@DATE_BUCKET = DATEADD(DAY,DFP.DFP_DAY,DATEADD(MONTH,DFP.DFP_MONTH,DATEADD(YEAR,DFP.DFP_YEAR - 1900,@FIRST_DATE)))
FROM dbo.TBL_TEST_DATEFROMPARTS DFP
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('DATEADD');INSERT INTO @timer (T_TXT) VALUES ('CONVERT CONCAT');
SELECT
@DATE_BUCKET = CONVERT(DATE,CONCAT(DFP.DFP_YEAR,CHAR(45),DFP.DFP_MONTH,CHAR(45),DFP.DFP_DAY),0)
FROM dbo.TBL_TEST_DATEFROMPARTS DFP
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('CONVERT CONCAT');INSERT INTO @timer (T_TXT) VALUES ('CONVERT ARITHMETIC');
SELECT
@DATE_BUCKET = CONVERT(DATE,CONVERT(VARCHAR(8),(DFP.DFP_YEAR * 10000) + (DFP_MONTH * 100) + DFP.DFP_DAY,0),112)
FROM dbo.TBL_TEST_DATEFROMPARTS DFP
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('CONVERT ARITHMETIC');INSERT INTO @timer (T_TXT) VALUES ('CONVERT PERSISTED');
SELECT
@DATE_BUCKET = CONVERT(DATE,CONVERT(VARCHAR(8),DFP.DFP_FULL,0),112)
FROM dbo.TBL_TEST_DATEFROMPARTS DFP
OPTION (MAXDOP 1);
INSERT INTO @timer (T_TXT) VALUES ('CONVERT PERSISTED');SELECT
T.T_TXT
,DATEDIFF(MICROSECOND,MIN(T.T_TD),MAX(T.T_TD)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION ASC;SSMS results
T_TXT DURATION
------------------- --------
DATEFROMPARTS 380268
DRY RUN 731363
CONVERT ARITHMETIC 780574
CONVERT CONCAT 1216842
DATEADD 1285931
CONVERT PERSISTED 5713584SOS results
T_TXT DURATION
------------------- --------
DATEFROMPARTS 526373
DRY RUN 774539
CONVERT ARITHMETIC 882624
CONVERT PERSISTED 886610
CONVERT CONCAT 1368991
DATEADD 1410999 - Eirikur Eiriksson - Friday, August 17, 2018 9:16 AM
Thanks Eirikur. I'm not going to get a chance to have a test till Monday now (might get a chance this weekend, but I'm off to Silverstone, so unlikely!) but I'll share what i find when I do. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Friday, August 17, 2018 9:30 AM
Thanks Tom,
plan to do more detailed testing this weekend, will post back with my findings. What is really puzzling is the fact that it is consistently different, a challenge to figure out and I like challenges
😎
What are you seeing at Silverstone? The Endurance? - Thom A - Friday, August 17, 2018 9:30 AM
I've just run your script from SSMS and SOS and I get similar performance results to you.
SSMS:T_TXT DURATION
CONVERT ARITHMETIC 532400
CONVERT CONCAT 864000
CONVERT PERSISTED 546000
DATEADD 910600
DATEFROMPARTS 267200
DRY RUN 258000
SOST_TXT DURATION
CONVERT ARITHMETIC 585000
CONVERT CONCAT 960000
CONVERT PERSISTED 601000
DATEADD 1043000
DATEFROMPARTS 290000
DRY RUN 237000 - Jonathan AC Roberts - Friday, August 17, 2018 9:45 AM
Thanks Jonathan,
your results are almost identical to mine, just trying to figure out why, your test confirms my suspicion that it is related to the tools, not my instances.
😎I'll post some more tests and results when I have time.
- Eirikur Eiriksson - Friday, August 17, 2018 9:45 AM
Yep, looking forward to it; was great fun last year. Not going to get anywhere near Alonso for a signature though, he'll be swamped by the paparazzi after his retirement announcement this week. hopefully get to see Button at least. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Sooooo..... SOS actually DOES stand for what I thought. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Friday, August 17, 2018 6:11 PM
It's a new thing, I had to look it up too.
https://docs.microsoft.com/en-us/sql/sql-operations-studio/faq?view=sql-server-2017
-
Heh... Oh no... I knew what it was. When I first saw the abbreviate when it came out, I was thinking of the old Navy abbreviation for some unknown form of brown gravy on some really badly burned toast. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply