September 1, 2016 at 2:47 am
Hi,
I have 1 query, which as below
select [USERNAME] ,[JOBNAME] ,[STEPNBR] from [dbo].[JobStep]. All these three columns are used to form cluster index.
The operator cost of execution of query 1 is around 227.551 and it took about 11 seconds to returned all rows.
Now, I create noncluster columnstore index on these 3 columns. The operator cost of execution of query 1 is around 1.20209 and it took about 13 seconds to returned all rows.
This seems to me as little contradictory, cost being high and execution time lesser. Does the cost have a relationship with the time ?
September 1, 2016 at 4:17 am
Not really. Cost is a measure of how expensive the query is, not how long it runs. A long time ago (~1995 or earlier), cost was calibrated as how long the query took on a particular piece of reference hardware, but it's not a time any longer.
And if you're trying to figure out how long the query takes, keep in mind that SSMS is very bad at displaying data, the grid is slow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2016 at 4:31 am
what it mean by 'expensive' ? Can give some sample ?
September 1, 2016 at 7:43 am
Gail, does it have to do with things like parallel processing? Much higher CPU cost to get faster throughput?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2016 at 8:05 am
sqlbaby2 (9/1/2016)
what it mean by 'expensive' ? Can give some sample ?
What is needed (CPU, IO) to run the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2016 at 8:07 am
The Dixie Flatline (9/1/2016)
Gail, does it have to do with things like parallel processing? Much higher CPU cost to get faster throughput?
Maybe, maybe not, no idea without seeing the OP's execution plan. Probably the 11/13 seconds is more to do with SSMS displaying the data than the time the query takes to run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2016 at 8:47 am
Actually, I was referring to why the definition of "expensive" changed, not the OPs current speed.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2016 at 12:01 pm
I never said anything about the definition of expensive. In fact, I used the word only to give an idea of what a query cost is
If you mean the origin of the cost, it was calibrated as a time on a particular piece of reference hardware at the MS labs during the development of SQL Server 7. Something that took 1 second on a particular piece of hardware around 1995 definitely won't take 1 second on current hardware, and that's ignoring all the changes in SQL since version 7.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply