Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a
DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn rows, a simple
SELECT TOP 1 FROM TheBaseTable returns no data - having taken 6m 38s to run, using 3845 reads, 155 physical reads, and over 400K ms CPU, so it gives the appearance of doing something.
Oddly, compatibility mode is at 2008 upon restore. Changed it to 120 and page verification to
Fixed the SQL Login SID (only 1 orphan). The login has db_owner, db_accessadmin, and db_securityadmin role membership, so it doesn't appear to lack privileges, but belts-and-braces-style it now also has db_datareader and db_datawriter memberships.
sp_updatestats because the DMVs show rows but
SELECT statements return zero.
Is it possible that the client's having set the compatibility down to 2008 with there being a CCSI in place has messed up the metadata?
Has anyone heard of anything like this, please?
Any ideas on how to see the data?
Here's the table - I see nothing fishy...
CREATE TABLE dbo.tbl_HistoryByType( InventoryID int NOT NULL, SalesTypeID int NOT NULL, ReturnFlag bit NOT NULL, SalesDate datetime NOT NULL, SeasonID int NULL, SalesQuantity float NOT NULL, TotalRevenue float NOT NULL, TotalCost float NOT NULL, TotalOriginalRetail int NULL ) ON [PRIMARY] GO CREATE CLUSTERED COLUMNSTORE INDEX CCSIX_tbl_HistoryByType ON dbo.tbl_HistoryByType WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY] GO
Hope someone can lend a hand as I've never seen anything like this in over 10 years with SQL Server...!
I have absolutely no idea why compatibility would be set to 2008, especially when we are to evaluate the DB code and performance.
Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
Jun 11 2015 19:18:41
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
Sadly, the original system is behind iron bars, so to speak. I'll inquire about any kind of select-only access. Strangely enough, I ran an
ALTER TABLE...REBUILD and after
DBCC CHECKTABLE scored zero rows. 1Bn+ "disappeared" Will look at applying the latest CU, too - this server is heavily shared, so will have to schedule it.