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)
i checked the same thing on my server it works i don’t know who to upload its image to your site but honestly peaking its like bellow
The whole Database (Unalocated Spcae (1.03 MB)) (Unused 3488 KB),
while the egproducts Table Unused Space is 64 KB
is it a bug as well ??
I am not able to understand the issue here.
Well you answered it Pinal. This is a memory optimized table so it resides in memory and not in storage.
Yes, I agree with Anil. There is no disk space used, yet.
Even if you insert data, it would show the same.
Supose if we restart the server then the memory in sql server will get cleared i.e zero.so if we run the select * from products then otput must be empty.so this will be the wrong scenario .I think the In-memory tables will have a copy on disk so that when we run the select statements it will fetch the data from disk. Am is right or wrong?
In-Memory tables can be persisted on disk using SCHEMA_AND_DATA option
With having MEMORY_OPTIMIZED = ON, even thought you have DURABILITY = SCHEMA_AND_DATA nothing has been written to disk yet. …just a theory, I have not really spent much time with In Memory tables.
Well, sp_spaceused is not designed for it.