Toad World® Forums

IN MEMORY tables show 2 or 3 times in explorer


#1

SQL Server 2016 with newest beta SQL Server .

All tables with IN MEMORY option shows up 2 or more times in the object explorer

xx_004.jpeg

regards

Mette


#2

Via SQL Profiler I got the it executes the following statement:

– Specific to SQL Server 2012.

– Used in TableListTrl.

– This SQL file contains some Toad specific syntax, such as {{Quote()}} and
– bind variables are represented with a : prefix instead of the usual @ character.

USE Production_internal_Soak_BiTool_DW;

SET NOCOUNT ON;
– sys.dm_db_index_usage_stats requires View Server State permission and is causing many issues.

– DECLARE @IndexUsageStatsPermission BIT;
– SET @IndexUsageStatsPermission = 0;

– IF EXISTS
– (SELECT 1
– FROM fn_my_permissions (‘sys.dm_db_index_usage_stats’, ‘object’)
– WHERE permission_name LIKE ‘%SELECT%’)
– BEGIN
– SET @IndexUsageStatsPermission = 1;
– END
begin try
SELECT DISTINCT

obj.name COLLATE database_default as [Name],
sch.name COLLATE database_default as [Owner],
CASE
WHEN obj.is_ms_shipped = 1
THEN ‘S’
ELSE obj.type
END as [Type],
ISNULL(obj.is_filetable, 0) as [IsFileTable],
CAST(CASE
WHEN dsidx.type = ‘PS’
THEN 1
ELSE 0
END AS bit) as [Partitioned],
ISNULL(obj.is_memory_optimized, 0) as [IsMemoryTable]
,obj.create_date
, (select count(name) from sys.columns cols where cols.[object_id] = obj.[object_id]) as [ColumnCount]
, CASE WHEN EXISTS (SELECT 1 FROM fn_my_permissions (‘sys.dm_db_index_usage_stats’, ‘object’) WHERE permission_name LIKE ‘%SELECT%’)
THEN (SELECT stat.last_user_update from sys.dm_db_index_usage_stats stat
where stat.[object_id] = idx.[object_id] and stat.index_id = idx.index_id and stat.database_id = DB_ID() )
ELSE null end as [Last Updated], obj.modify_date, case
when(obj.is_memory_optimized = 1) then
ISNULL((SELECT SUM(spart.row_insert_attempts) - SUM(spart.row_delete_attempts)
FROM sys.dm_db_xtp_object_stats spart
WHERE(spart.object_id = obj.object_id)), 0)
else
ISNULL((SELECT SUM(spart.rows)
FROM sys.partitions spart
WHERE(spart.object_id = obj.object_id) AND
(spart.index_id < 2)), 0)
end as [RowCount]
,ISNULL(eprop.[value], ‘’)

FROM
sys.tables obj

INNER JOIN sys.schemas sch ON sch.schema_id = obj.schema_id
INNER JOIN sys.indexes idx ON (idx.object_id = obj.object_id) AND
(idx.index_id < 2 or idx.type = 7)
LEFT OUTER JOIN sys.data_spaces dsidx ON dsidx.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.extended_properties eprop on obj.[object_id] = eprop.major_id and eprop.[name] COLLATE database_default = ‘MS_Description’ AND eprop.minor_id = 0

WHERE
1 = 1
AND (LOWER(obj.name) LIKE N’%frontpage%’)

ORDER BY
sch.name COLLATE database_default ASC, obj.name COLLATE database_default ASC;


If I change TO A SELECT DISTINCT it works

The query returns 2 plus rows if a table as more index types - here we have id=0 and a idtype=7 (noclustered hash)

regards

Mette


#3

Hi MetteS,

we can reproduce your issue, and aslo create defectID TSS-1456 for this.

Thanks,

Michael


#4

I just loaded the new beta - this problem still exists in here
It is indeed very annoying

best regards Mette

2017-09-11 11:42 GMT+02:00 michael bounce-michael@toadworld.com:

RE: IN MEMORY tables show 2 or 3 times in explorer

Reply by michael
Hi MetteS,

we can reproduce your issue, and aslo create defectID TSS-1456 for this.

Thanks,

Michael

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for SQL Server - Beta Forum notifications altogether.

Toad for SQL Server - Beta Discussion Forum

Flag this post as spam/abuse.


Mette Stephansen

Miracle Expert ServicesMiracle A/S
Borupvang 2C, 2750 Ballerup

53 74 72 26