When you see something not behaving the way normal way, either it must be magic or in software terms it is an error or bug. This blog was inspired by someone who caught me unaware with this challenge that it took me by surprise. I didn’t know how to solve it and it took me close to 10 mins before realizing what was happening behind the scenes. Let us see the puzzle about Why does sp_spaceused Show No Values?
I have not done any sort of trick in the below screen shot. It is real table and the output is real too.
Did you see something strange here? Even though the table has values, the sp_spaceused is not recording any values. When I first saw this, it surely looked like some bug.
Now if I had to ask for your help to help me, what would be your response? Why is the output showing like this?
Have you ever encountered such strange behavior with SQL Server? What would you do when such things happen? If this is a bug, I highly recommend you to please go to the Connect site and do a search. On a side note, do let me know if you ever visited this site. Do read about enhancement requests on this site, this is the best way to know what doesn’t work and what are people working on.
Hint: Coming back to the puzzle above. Do you know the various types of tables you can create? I think I am giving away the answer already.
Here is the code to build this example.
USE MASTER GO DROP DATABASE IF EXISTS [InMem_OLTP] GO -- Create the database CREATE DATABASE [InMem_OLTP] ON PRIMARY ( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 30MB) LOG ON ( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB) GO -- Add Inmemory Filegroup USE InMem_OLTP GO ALTER DATABASE InMem_OLTP ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE InMem_OLTP ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt') TO FILEGROUP [InMem_OLTP_InMemory] -- Let us create the table srtuctures USE InMem_OLTP GO CREATE TABLE Products ( ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY NONCLUSTERED HASH (ProductID) WITH (BUCKET_COUNT = 10000), ProductName VARCHAR(25) ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); GO INSERT INTO Products VALUES (2, 'SQLAuthority'); GO SELECT * FROM Products GO sp_spaceused Products GO USE master GO DROP DATABASE [InMem_OLTP] GO
Do drop your guesses and understanding via comments below.
Reference: Pinal Dave (https://blog.sqlauthority.com)