SQL Server – Operator – Top – Top Expression (0)

Background

Earlier today I found myself pressing to make sure I had done right by a query.

 

Query Plan

Query Plan – 01

Here is the original query Plan

Image

queryPlan_computeScaler_HashMatch_20180517_1139AM [clipped]

Explanation

  1. I know that I don’t really want a Hash Match
    • Took care of the Hash Match by reducing the query from two tables to a single table
    • There are a few novel ways to do so, and will cover that later

 

Query Plan – 02

Here is the query Plan once we got rid of the secondary table

Image

queryPlan_computeScaler_HashMatch_20180517_1143AM [clipped]

 

Top ?

I was stuck at the Top Operator for a very long time

Explanation

  1. Rowcount
    • Do I have a set rowcount somewhere
    • Is my environment introducing a constraint for maximum number of records to “touch
    • Is my edition of SQL Server throttling performance
  2. Top
    • Do I have a top N clause somewhere

 

Operator – Top – Default

Overview

Here is what our Top Operators looks like when we do not have “set rowcount” set and we do not have an actual TOP Clause.

Image

Explanation

  1. Actual Number of Rows :- 65
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

 

Operator – Top – “Set rowcount”

Overview

What if we add an actual set rowcount

Image

 

Explanation

  1. Actual Number of Rows :- 2
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

Operator – Top – “Select TOP N”

Overview

Here is what we see when we add a “Top 1” Clause.

Image

Explanation

  1. Actual Number of Rows :- 1
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (1)

 

Other Things

Overview

I was stuck and so tried other things; such as

  1. Newer version of SQL Server ; v2017 to be exact
  2. Took out the insert into and performed a simple select

Could not reproduce…

 

Craig Freedman ( MSFT )

Finally goggled on the right terms and read what Craig Freedman has to say.

The particular post that I will be quoting is:

ROWCOUNT Top
Link

  1. TOP Operator
    • If you’ve looked at any insert, update, or delete plans, including those used in some of my posts, you’ve probably noticed that nearly all such plans include a top operator.
  2. SET ROWCOUNT
    •  It is a ROWCOUNT top. It is used to implement SET ROWCOUNT functionality.
  3. Why doesn’t SQL Server add a ROWCOUNT top to select statements?
    • SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan.  Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans.  For instance, if we add a unique index to our table, the update plan becomes substantially more complex
  4. Placement
    • By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.

 

Martin Smith

The good thing about blogging and allowing comments is that people can come back and provide helpful feedback.

Here is one from Martin Smith:

Martin Smith
December 29, 2012 at 8:15 am

In 2012 it looks like this operator is only added to plans run under “SET ROWCOUNT” of other than zero. As far as I can discern it is added in to the set_options used as a plan cache key.

SQL Server Versions

Here are the versions of SQL Server where you will be able to reproduce the Top (0) Operator preceding data effecting operators :-

  1. 2005
  2. 2008-R2

Dedication

Thankfully I have a far better grasp courtesy of two able men, Craig Freedman & Martin Smith.

WMI – Error – “0x8004100E”

Background

Trying to steal and use someone else’s code does not do well for me.

Here I am having stolen a code, but stuck with bad characters and an error message.

Code ( Original )


OPTION EXPLICIT

Dim strComputer
Dim URL
Dim objWMIService
Dim colItems
Dim objItem


strComputer = "."

URL="winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10"

Wscript.Echo "URL is "& URL

set objWMIService = GetObject(URL)

Set colItems = objWMIService.ExecQuery("SELECT * FROM SecurityCertificate",,48)

For Each objItem in colItems

	Wscript.Echo ""

	Wscript.Echo "SecurityCertificate instance"
	Wscript.Echo ""
	Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
	Wscript.Echo "FriendlyName: " & objItem.FriendlyName
	Wscript.Echo "IssuedBy: " & objItem.IssuedBy
	Wscript.Echo "IssuedTo: " & objItem.IssuedTo
	Wscript.Echo "Name: " & objItem.Name
	Wscript.Echo "SHA: " & objItem.SHA
	Wscript.Echo "StartDate: " & objItem.StartDate
	Wscript.Echo "SystemStore: " & objItem.SystemStore


Next

 

Error

Error Image

Error Message


(null): 0x8004100E

Trouble Shooting

WMI Code Creator

Download WMI Code Creator from here.

Extract

Extract the compressed ( zip) file.

Run

Run the file.

Namespace

Looked at the NameSpaces…

They are version specific.

The one I want is root\Microsoft\SqlServer\ComputerManagement12.

But, the one referenced in the code is root\Microsoft\SqlServer\ComputerManagement10.

On this particular box I am on SQL Server v2014.

And, so ComputerManagement12 is SQL 2014.

While ComputerManagement10 is SQL Server 2008.

 

Code ( Revised )

Code revised for v2014.

 

OPTION EXPLICIT
On error resume next

Dim strComputer
Dim baseURL
Dim sqlServerVersion
Dim URL
Dim objWMIService
Dim colItems
Dim objItem
Dim query

strComputer = "."

baseURL="winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\"
rem comment out version information
rem sqlServerVersion="ComputerManagement10"
sqlServerVersion="ComputerManagement12"

URL=baseURL & sqlServerVersion

set objWMIService = GetObject(URL)

if (Err.Number <> 0) then

	Wscript.Echo "GetObject failed on " & URL
	Wscript.Echo "Error Number is " & CSTR(Err.Number)
	Wscript.Echo "Error Description is " & Err.Description

	Wscript.Quit
	
end if

query = "SELECT * FROM SecurityCertificate"

Set colItems = objWMIService.ExecQuery(query,,48)


if (Err.Number <> 0) then

	Wscript.Echo "ExecQuery failed on " & query
	Wscript.Echo "Error Number is " & CSTR(Err.Number)
	Wscript.Echo "Error Description is " & Err.Description

	Wscript.Quit
	
end if

For Each objItem in colItems

	Wscript.Echo ""

	Wscript.Echo "SecurityCertificate instance"
	Wscript.Echo ""
	Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
	Wscript.Echo "FriendlyName: " & objItem.FriendlyName
	Wscript.Echo "IssuedBy: " & objItem.IssuedBy
	Wscript.Echo "IssuedTo: " & objItem.IssuedTo
	Wscript.Echo "Name: " & objItem.Name
	Wscript.Echo "SHA: " & objItem.SHA
	Wscript.Echo "StartDate: " & objItem.StartDate
	Wscript.Echo "SystemStore: " & objItem.SystemStore


Next