Index use - COUNT(*) vs COUNT(1)

The question whether to use COUNT(*) or COUNT(1) has been doing the rounds on the internet again, the question this time is whether or not COUNT(*) forces the optimiser to scan all rows or not.

I'm going to query Sales.Orders table of the WideWorldImporters sample database and use a where clause that should take advantage of a non-clustered index on CustomerID. 

Firstly I'll run a select on everything and I can see the following query returns 107 rows:

SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
WHERE CustomerID = 832

Now lets try replacing the select everything with count(1) and this time I will include the actual execution plan.

SELECT COUNT(1)
FROM [WideWorldImporters].[Sales].[Orders]
WHERE CustomerID = 832

The execution plan using COUNT(1) has used a seek on the Index and we can see from the operator properties that only 107 rows were read:

Now I'll try a COUNT(*) and I have added a OPTION (RECOMPILE) hint to ensure a brand new execution plan is used:

SELECT COUNT(*)

FROM [WideWorldImporters].[Sales].[Orders]

WHERE CustomerID = 832

OPTION (RECOMPILE)

The execution plan is identical, even though we're using COUNT(*) the optimiser can still seek on the relevant index for the where clause in the query:

Just to double check; our index seek operator has the same number of rows read as well:

In terms of what index can be used COUNT(*) and COUNT(1) are identical and both will use an index seek if the optimiser deems it the most efficient method and COUNT(*) definitely does not force a scan.

SQL Server Nuts and Bolts: Memory Clerks DMV

Memory clerks provide an interface between components and the memory nodes that can be used to allocate memory and are also used by the memory nodes to track allocation and we can use the Dynamic Management View sys.dm_os_memory_clerks ourselves to see how memory is being used within our SQL instance by running the following query:

SELECT [type],SUM(pages_kb) / 1024 AS MemoryMb

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY MemoryMb

The query will return the top clerks based on Mb of memory used in SQL Server. Ideally we'd expect to see MEMORYCLERK_SQLBUFFERPOOL as the largest allocation which is essentially our pages in memory. 

We can also test buffer pool allocation by (on a very suitable test system well away from production) by clearing down our buffer pool using the DBCC DROPCLEANBUFFERS command, this is what the top 5 rows are on my machine from the above query:

MEMORYCLERK_XTP         78

MEMORYCLERK_SOSNODE 43

MEMORYCLERK_SQLSTORENG 27

MEMORYCLERK_SQLCLR 21

MEMORYCLERK_SQLGENERAL 16

Now if I run a couple of queries on my test database such as the following to bring some pages into memory:

SELECT * FROM [WideWorldImporters].[Sales].[Invoices];

SELECT * FROM [WideWorldImporters].[Sales].[Orders];

When I rerun my DMV query I can see that there is now around 100Mb in my buffer pool as a result of my queries being executed.

MEMORYCLERK_SQLBUFFERPOOL 100

MEMORYCLERK_XTP                 78

MEMORYCLERK_SOSNODE         43

MEMORYCLERK_SQLSTORENG         27

MEMORYCLERK_SQLCLR         21

We also need to aware that our maximum server memory configuration setting will restrict the amount of memory available to the SQL Server memory components. 

This is a quick example of how easy it is to use Dynamic Management Views to expose detailed information on what is happening in our SQL Server instance and over the next few posts I'll look at some of memory related features of SQL Server.

Nuts and Bolts picture from iStockPhoto: 29,882 Nuts And Bolts Stock Photos, Pictures & Royalty-Free Images - iStock (istockphoto.com)