Monthly Archives: January 2018

List All Extended Properties For All Objects In A Database

— By Lori Brown @SQLSupahStah

I was recently looking at a way to easily identify if any objects in a database were In-Memory. Since many times I am tasked with taking on systems that I have never seen before, I have learned that it is important to have ways of checking to see if some of the more specialized features of SQL are in use. With this information, I am better prepared to manage a new SQL instance without inadvertently causing issues or ignoring something that should be on my DBA radar. So, finding all things that are In-Memory is important since more and more of my clients are using newer versions of SQL.

I can easily find tables and indexes that are column store by using this query

— Tables with columnstore indexes — SQL 2012 +

select t.name as TablesWithColumnstoreInx, i.name as ColumnStoreIndex

from sys.indexes i

inner join sys.tables t

on i.object_id = t.object_id

where i.type = 5 or i.type = 6

But I was kind of stumped to find a good way to identify natively compiled stored procedures since as far as I could tell, those are identified only through extended properties. After doing some research, I found someone who had the idea to load up all of the extended properties into a table so that it could be joined to the sys.objects table to pass object_id’s into the OBJECTPROPERTYEX function and thereby returning a list of all extended properties for all objects. Unfortunately, I cannot find the original forum post that was so helpful again and am unable to provide a hat tip to the correct person. But, here is what I have settled on:

USE WideWorldImporters

GO

— Find extended properties on objects

CREATE TABLE #ObjectPropertyEXList (PropertyName nvarchar(50), PropertyAppliesTo nvarchar(256), PropertyDesc nvarchar(3000));

 

INSERT INTO #ObjectPropertyEXList (PropertyName, PropertyAppliesTo, PropertyDesc)

SELECT [PropertyName] = N’BaseType’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Identifies the base type of the object. When the specified object is a SYNONYM, the base type of the underlying object is returned. Nonnull = Object type Base data type: char(2)’ 

UNION ALL SELECT [PropertyName] = N’CnstIsClustKey’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’PRIMARY KEY constraint with a clustered index. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’CnstIsColumn’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’CHECK, DEFAULT, or FOREIGN KEY constraint on a single column. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’CnstIsDeleteCascade’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’FOREIGN KEY constraint with the ON DELETE CASCADE option. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’CnstIsDisabled’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’Disabled constraint. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’CnstIsNonclustKey’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’PRIMARY KEY constraint with a nonclustered index. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’CnstIsNotRepl’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’Constraint is defined by using the NOT FOR REPLICATION keywords. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’CnstIsNotTrusted’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’Constraint was enabled without checking existing rows. Therefore, the constraint may not hold for all rows. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’CnstIsUpdateCascade’, [PropertyAppliesToType] = N’Constraint’, [PropertyDesc] = N’FOREIGN KEY constraint with the ON UPDATE CASCADE option. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsAfterTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’AFTER trigger. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsAnsiNullsOn’, [PropertyAppliesToType] = N’Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view’, [PropertyDesc] = N’The setting of ANSI_NULLS at creation time. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’ExecIsDeleteTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’DELETE trigger. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’ExecIsFirstDeleteTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’The first trigger fired when a DELETE is executed against the table. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsFirstInsertTrigger’, [PropertyAppliesToType] = N’Trigger‘, [PropertyDesc] = N’The first trigger fired when an INSERT is executed against the table. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsFirstUpdateTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’The first trigger fired when an UPDATE is executed against the table. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsInsertTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’INSERT trigger. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsInsteadOfTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’INSTEAD OF trigger. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’ExecIsLastDeleteTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’Last trigger fired when a DELETE is executed against the table. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsLastInsertTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’Last trigger fired when an INSERT is executed against the table. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsLastUpdateTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’Last trigger fired when an UPDATE is executed against the table. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’ExecIsQuotedIdentOn’, [PropertyAppliesToType] = N’Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view’, [PropertyDesc] = N’Setting of QUOTED_IDENTIFIER at creation time. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’ExecIsStartup’, [PropertyAppliesToType] = N’Procedure’, [PropertyDesc] = N’Startup procedure. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’ExecIsTriggerDisabled’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’Disabled trigger. 1 = True 0 = False Base data type: int’    

UNION ALL SELECT [PropertyName] = N’ExecIsTriggerNotForRepl’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’Trigger defined as NOT FOR REPLICATION. 1 = True 0 = False Base data type: int’    

UNION ALL SELECT [PropertyName] = N’ExecIsUpdateTrigger’, [PropertyAppliesToType] = N’Trigger’, [PropertyDesc] = N’UPDATE trigger. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’ExecIsWithNativeCompilation’, [PropertyAppliesToType] = N’Transact-SQL Procedure’, [PropertyDesc] = N’Procedure is natively compiled. 1 = True 0 = False Base data type: int Applies to: SQL Server 2014 through SQL Server 2017.’  

UNION ALL SELECT [PropertyName] = N’HasAfterTrigger’, [PropertyAppliesToType] = N’Table, view’, [PropertyDesc] = N’Table or view has an AFTER trigger. 1 = True 0 = False Base data type: int’    

UNION ALL SELECT [PropertyName] = N’HasDeleteTrigger’, [PropertyAppliesToType] = N’Table, view’, [PropertyDesc] = N’Table or view has a DELETE trigger. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’HasInsertTrigger’, [PropertyAppliesToType] = N’Table, view’, [PropertyDesc] = N’Table or view has an INSERT trigger. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’HasInsteadOfTrigger’, [PropertyAppliesToType] = N’Table, view’, [PropertyDesc] = N’Table or view has an INSTEAD OF trigger. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’HasUpdateTrigger’, [PropertyAppliesToType] = N’Table, view’, [PropertyDesc] = N’Table or view has an UPDATE trigger. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsAnsiNullsOn’, [PropertyAppliesToType] = N’Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view’, [PropertyDesc] = N’Specifies that the ANSI NULLS option setting for the table is ON, meaning all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsCheckCnst’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’CHECK constraint. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsConstraint’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Constraint. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’IsDefault’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Bound default. 1 = True 0 = False Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’  

UNION ALL SELECT [PropertyName] = N’IsDefaultCnst’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’DEFAULT constraint. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsDeterministic’, [PropertyAppliesToType] = N’Scalar and table-valued functions, view’, [PropertyDesc] = N’The determinism property of the function or view. 1 = Deterministic 0 = Not Deterministic Base data type: int’

UNION ALL SELECT [PropertyName] = N’IsEncrypted’, [PropertyAppliesToType] = N’Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view’, [PropertyDesc] = N’Indicates that the original text of the module statement was converted to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users without access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at run time. 1 = Encrypted 0 = Not encrypted Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsExecuted’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Specifies the object can be executed (view, procedure, function, or trigger). 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsExtendedProc’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Extended procedure. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’IsForeignKey’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’FOREIGN KEY constraint. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’IsIndexed’, [PropertyAppliesToType] = N’Table, view’, [PropertyDesc] = N’A table or view with an index. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsIndexable’, [PropertyAppliesToType] = N’Table, view’, [PropertyDesc] = N’A table or view on which an index may be created. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsInlineFunction’, [PropertyAppliesToType] = N’Function’, [PropertyDesc] = N’Inline function. 1 = Inline function 0 = Not inline function Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsMSShipped’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’An object created during installation of SQL Server. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’IsPrecise’, [PropertyAppliesToType] = N’Computed column, function, user-defined type, view’, [PropertyDesc] = N’Indicates whether the object contains an imprecise computation, such as floating point operations. 1 = Precise 0 = Imprecise Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsPrimaryKey’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’PRIMARY KEY constraint. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsProcedure’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Procedure. 1 = True 0 = False Base data type: int’    

UNION ALL SELECT [PropertyName] = N’IsQuotedIdentOn’, [PropertyAppliesToType] = N’CHECK constraint, DEFAULT definition, Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view’, [PropertyDesc] = N’Specifies that the quoted identifier setting for the object is ON, meaning double quotation marks delimit identifiers in all expressions involved in the object definition. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsQueue’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Service Broker Queue 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsReplProc’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Replication procedure. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’IsRule’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Bound rule. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’IsScalarFunction’, [PropertyAppliesToType] = N’Function’, [PropertyDesc] = N’Scalar-valued function. 1 = Scalar-valued function 0 = Not scalar-valued function Base data type: int’   

UNION ALL SELECT [PropertyName] = N’IsSchemaBound’, [PropertyAppliesToType] = N’Function, Procedure, view’, [PropertyDesc] = N’A schema bound function or view created by using SCHEMABINDING. 1 = Schema-bound 0 = Not schema-bound Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsSystemTable’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’System table. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsSystemVerified’, [PropertyAppliesToType] = N’Computed column, function, user-defined type, view’, [PropertyDesc] = N’The precision and determinism properties of the object can be verified by SQL Server. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsTable’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsTableFunction’, [PropertyAppliesToType] = N’Function’, [PropertyDesc] = N’Table-valued function. 1 = Table-valued function 0 = Not table-valued function Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsTrigger’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Trigger. 1 = True 0 = False Base data type: int’    

UNION ALL SELECT [PropertyName] = N’IsUniqueCnst’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’UNIQUE constraint. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’IsUserTable’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’User-defined table. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’IsView’, [PropertyAppliesToType] = N’View’, [PropertyDesc] = N’View. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’OwnerId’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’Owner of the object. System_CAPS_noteNote The schema owner is not necessarily the object owner. For example, child objects (those where parent_object_id is nonnull) will always return the same owner ID as the parent. Nonnull = Database user ID of the object owner. NULL = Unsupported object type, or object ID is not valid. Base data type: int’

UNION ALL SELECT [PropertyName] = N’SchemaId’, [PropertyAppliesToType] = N’Any schema-scoped object’, [PropertyDesc] = N’The ID of the schema associated with the object. Nonnull = Schema ID of the object. Base data type: int’  

UNION ALL SELECT [PropertyName] = N’SystemDataAccess’, [PropertyAppliesToType] = N’Function, view’, [PropertyDesc] = N’Object accesses system data, system catalogs or virtual system tables, in the local instance of SQL Server. 0 = None 1 = Read Base data type: int’  

UNION ALL SELECT [PropertyName] = N’TableDeleteTrigger’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a DELETE trigger. >1 = ID of first trigger with the specified type. Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableDeleteTriggerCount’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’The table has the specified number of DELETE triggers. Nonnull = Number of DELETE triggers Base data type: int’  

UNION ALL SELECT [PropertyName] = N’TableFullTextMergeStatus’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Whether a table that has a full-text index that is currently in merging. 0 = Table does not have a full-text index, or the full-text index is not in merging. 1 = The full-text index is in merging. Applies to: SQL Server 2008 through SQL Server 2016.’ 

UNION ALL SELECT [PropertyName] = N’TableFullTextBackgroundUpdateIndexOn’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’The table has full-text background update index (autochange tracking) enabled. 1 = TRUE 0 = FALSE Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’  

UNION ALL SELECT [PropertyName] = N’TableFulltextCatalogId’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’ID of the full-text catalog in which the full-text index data for the table resides. Nonzero = Full-text catalog ID, associated with the unique index that identifies the rows in a full-text indexed table. 0 = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’  

UNION ALL SELECT [PropertyName] = N’TableFullTextChangeTrackingOn’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has full-text change-tracking enabled. 1 = TRUE 0 = FALSE Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’  

UNION ALL SELECT [PropertyName] = N’TableFulltextDocsProcessed’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Number of rows processed since the start of full-text indexing. In a table that is being indexed for full-text search, all the columns of one row are considered as part of one document to be indexed. 0 = No active crawl or full-text indexing is completed. > 0 = One of the following: The number of documents processed by insert or update operations since the start of full, incremental, or manual change tracking population. The number of rows processed by insert or update operations since change tracking with background update index population was enabled, the full-text index schema changed, the full-text catalog rebuilt, or the instance of SQL Server restarted, and so on. NULL = Table does not have a full-text index. Base data type: int Note   This property does not monitor or count deleted rows. Applies to: SQL Server 2008 through SQL Server 2016.’

UNION ALL SELECT [PropertyName] = N’TableFulltextFailCount’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’The number of rows that full-text search did not index. 0 = The population has completed. >0 = One of the following: The number of documents that were not indexed since the start of Full, Incremental, and Manual Update change tracking population. For change tracking with background update index, the number of rows that were not indexed since the start of the population, or the restart of the population. This could be caused by a schema change, rebuild of the catalog, server restart, and so on NULL = Table does not have a Full-Text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’ 

UNION ALL SELECT [PropertyName] = N’TableFulltextItemCount’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Nonnull = Number of rows that were full-text indexed successfully. NULL = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’ 

UNION ALL SELECT [PropertyName] = N’TableFulltextKeyColumn’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’ID of the column associated with the single-column unique index that is part of the definition of a full-text index and semantic index. 0 = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’  

UNION ALL SELECT [PropertyName] = N’TableFulltextPendingChanges’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Number of pending change tracking entries to process. 0 = change tracking is not enabled. NULL = Table does not have a full-text index. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’    

UNION ALL SELECT [PropertyName] = N’TableFulltextPopulateStatus’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’0 = Idle. 1 = Full population is in progress. 2 = Incremental population is in progress. 3 = Propagation of tracked changes is in progress. 4 = Background update index is in progress, such as autochange tracking. 5 = Full-text indexing is throttled or paused. 6 = An error has occurred. Examine the crawl log for details. For more information, see the Troubleshooting Errors in a Full-Text Population (Crawl) section of Populate Full-Text Indexes. Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’  

UNION ALL SELECT [PropertyName] = N’TableFullTextSemanticExtraction’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table is enabled for semantic indexing. 1 = True 0 = False Base data type: int Applies to: SQL Server 2012 through SQL Server 2016.’

UNION ALL SELECT [PropertyName] = N’TableHasActiveFulltextIndex’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has an active full-text index. 1 = True 0 = False Base data type: int Applies to: SQL Server 2008 through SQL Server 2016.’   

UNION ALL SELECT [PropertyName] = N’TableHasCheckCnst’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a CHECK constraint. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableHasClustIndex’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a clustered index. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableHasDefaultCnst’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a DEFAULT constraint. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableHasDeleteTrigger’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a DELETE trigger. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’TableHasForeignKey’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a FOREIGN KEY constraint. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableHasForeignRef’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table is referenced by a FOREIGN KEY constraint. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableHasIdentity’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has an identity column. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableHasIndex’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has an index of any type. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableHasInsertTrigger’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Object has an INSERT trigger. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’TableHasNonclustIndex’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’The table has a nonclustered index. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’TableHasPrimaryKey’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a primary key. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableHasRowGuidCol’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a ROWGUIDCOL for a uniqueidentifier column. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableHasTextImage’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a text, ntext, or image column. 1 = True 0 = False Base data type: int’  

UNION ALL SELECT [PropertyName] = N’TableHasTimestamp’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a timestamp column. 1 = True 0 = False Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableHasUniqueCnst’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a UNIQUE constraint. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableHasUpdateTrigger’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’The object has an UPDATE trigger. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableHasVarDecimalStorageFormat’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table is enabled for vardecimal storage format. 1 = True 0 = False’  

UNION ALL SELECT [PropertyName] = N’TableInsertTrigger’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has an INSERT trigger. >1 = ID of first trigger with the specified type. Base data type: int’    

UNION ALL SELECT [PropertyName] = N’TableInsertTriggerCount’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’The table has the specified number of INSERT triggers. >0 = The number of INSERT triggers. Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableIsFake’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table is not real. It is materialized internally on demand by the Database Engine. 1 = True 0 = False Base data type: int’   

UNION ALL SELECT [PropertyName] = N’TableIsLockedOnBulkLoad’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table is locked because a bcp or BULK INSERT job. 1 = True 0 = False Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableIsMemoryOptimized’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table is memory optimized 1 = True 0 = False Base data type: int For more information, see In-Memory OLTP (In-Memory Optimization). Applies to: SQL Server 2014 through SQL Server 2016.’  

UNION ALL SELECT [PropertyName] = N’TableIsPinned’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table is pinned to be held in the data cache. 0 = False This feature is not supported in SQL Server 2005 and later versions.’  

UNION ALL SELECT [PropertyName] = N’TableTextInRowLimit’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has text in row option set. > 0 = Maximum bytes allowed for text in row. 0 = text in row option is not set. Base data type: int’

UNION ALL SELECT [PropertyName] = N’TableUpdateTrigger’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has an UPDATE trigger. > 1 = ID of first trigger with the specified type. Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’TableUpdateTriggerCount’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has the specified number of UPDATE triggers. > 0 = The number of UPDATE triggers. Base data type: int’ 

UNION ALL SELECT [PropertyName] = N’UserDataAccess’, [PropertyAppliesToType] = N’Function, View’, [PropertyDesc] = N’Indicates the object accesses user data, user tables, in the local instance of SQL Server. 1 = Read 0 = None Base data type: int’  

UNION ALL SELECT [PropertyName] = N’TableHasColumnSet’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Table has a column set. 0 = False 1 = True For more information, see Use Column Sets.’  

UNION ALL SELECT [PropertyName] = N’Cardinality’, [PropertyAppliesToType] = N’Table (system or user-defined), view, or index’, [PropertyDesc] = N’The number of rows in the specified object. Applies to: SQL Server 2012 through SQL Server 2016.’ 

UNION ALL SELECT [PropertyName] = N’TableTemporalType’, [PropertyAppliesToType] = N’Table’, [PropertyDesc] = N’Specifies the type of table. 0 = non-temporal table 1 = history table for system-versioned table 2 = system-versioned temporal table Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.’    

;

 

–List all object properties for a given object

— Uncomment filters to find specific things

SELECT

op.PropertyName

, o.*

FROM sys.objects o

cross apply #ObjectPropertyEXList op

WHERE OBJECTPROPERTYEX(o.object_id, op.PropertyName) = 1

— AND op.PropertyName = ‘ExecIsWithNativeCompilation’ — uncomment this to look for a specific property

— AND o.type = ‘P’ — uncomment this to look for a specific object type

— AND o.name = ‘Colors_Archive’ — uncomment this to look for a specific object by name

ORDER BY o.name

 

/* List of object types

 

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint,FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

U = Table (user-defined)

V = View

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

UQ = UNIQUE constraint

X = Extended stored procedure

ET = External Table

*/

 

DROP TABLE #ObjectPropertyEXList

 

I have verified that all extended properties that are loaded into #ObjectPropertyEXList are from the most recent list on Microsoft (https://docs.microsoft.com/en-us/sql/t-sql/functions/objectpropertyex-transact-sql ) and have also tried to provide some extra ways to filter for data so read the comments carefully.

Here are my results when I look only for objects that are natively compiled:

Blog_20180125_1

And here are the results when I list all extended properties on a single object:

Blog_20180125_2

Hey look!! The Colors_Archive table is a temporal table!! Good to know!!

If the person who was so helpful in creating the extended properties table will let me know who they are, I will be happy to extend proper credit where it is due. I really could not find the source again despite several tries.

Hopefully you, the reader, will find this helpful. If you make improvements and would like to pass them along, please feel free to let me know and I will update this post with your ideas.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

 

Inconsistent Tempdb Growth

— by Ginger Keys

Tempdb database is a system database that is shared across all databases and all connections in SQL Server.   As a general rule, I configure all my client’s servers to have multiple tempdb files with consistent sizes and consistent auto growth set according to best practices. The number of tempdb files created equals the number of cores or 8, whichever is lower. And the size set for each file depends on each individual SQL instance to accommodate a typical workload.

The Problem

Recently during a routine review of a client’s server, I noticed in the SQL Server Logs that there were messages indicating I/O pressure for the tempdb database.

Blog_20180111_1

I also noticed in Windows Explorer that my tempdb files were no longer uniform in size. In fact the first data file had grown way out of proportion compared to all the other data files:

Blog_20180111_2

After double-checking the database properties, I confirmed that these files should have grown in consistent amounts:

Blog_20180111_3

So what happened to cause this inconsistent growth and how should I fix it?

Tempdb is used (and can grow) when users explicitly create objects like temp tables, variables, cursors, or stored procedures. Tempdb is also used when the database engine creates work tables for sorts or spooling, and row versioning operations.

It’s often difficult to diagnose what happened on a SQL Server after the fact. There are many resources online to determine what is currently using tempdb but not a lot of information from processes that may have run days ago.

What Happened?

First, I want to see what is currently using tempdb to find out if there are any processes using all that space in the database. The TSQL statements in the following links are helpful in determining who is currently using tempdb:

https://littlekendra.com/2009/08/27/whos-using-all-that-space-in-tempdb-and-whats-their-plan/

http://www.sqlservercentral.com/scripts/tempdb/72007/

https://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/

If you find there are plans using the tempdb causing substantial growth it is probably a good opportunity for some performance tuning.

In my situation there was nothing significant currently using tempdb, so my issue happened prior to my discovering the problem. Since I was not able to see any active queries creating temp tables, stored procs, cursors, or variables, I wanted to see if there were row versioning operations occurring.

If snapshot isolation is enabled on a database, performance can improve because it eliminates locking on the underlying tables. However in order to do this SQL puts the data (as it existed at the start of the transaction) into a temp table with row versions for each transaction, so that all queries in the transaction see the same version (or snapshot) of the database. This can take up a large amount of space in tempdb, depending on the size of the tables in the database being queried.

Run this statement to find any user databases with snapshot isolation enabled:

select * from sys.databases

where (snapshot_isolation_state = 1 or is_read_committed_snapshot_on = 1)

and database_id > 4

My client had a few databases with snapshot isolation enabled, so we had a conversation about the reasoning behind these settings. However it was still unclear as to why the tempdb grew and why the files grew out of proportion. As stated earlier, unless you are actively collecting performance data for this type of behavior, it is often very difficult to troubleshoot the cause of the problem after the fact. So what do we do about it?

Solution

When your tempdb files grow beyond the original size set in the properties, and they grow out of proportion, the preferred and best solution is to grow the files to a uniform size, as long as there is room on your disk. This is the preferred solution because if the tempdb files grew, it apparently needed that much room to perform the tasks that caused it to grow. You can adjust the size of your tempdb files using the GUI, or you can run the following statement for each data file:

USE master

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, SIZE = 2000MB)

If there is not enough space on your drive and you need to resolve the issue right now, shrink the tempdb files down to a uniform size. Keeping your tempdb data files the same size helps to avoid page contention issues.

This can be done through the GUI by selecting each individual data file and specifying the desired size, or you can run this statement for each data file:

USE tempdb

GO

DBCC SHRINKFILE (tempdev, 1000)

GO

 

If for some reason the data files don’t shrink to the size you specified, try running this

DBCC FREEPROCCACHE

then execute the SHRINKFILE statement again. Do this during a maintenance time of course and not during production time while users are connected.

Prevention and Best Practice

Put your tempdb on its own volume if possible. If your tempdb is on the same drive as your user database files and that drive runs out of space, it will be difficult to restart SQL Server. If your tempdb is on its own drive you can utilize all of the space for your tempdb files and also reduce I/O contention.

Create as many data files as you have processors, up to a maximum of 8. (Note that dual-core CPU is the same as 2 CPUs). This will reduce storage contention and is more scalable. There is much debate on the necessity of this practice…this is a general guideline.

Make each data file the same size. This helps to avoid SGAM page contention issues, and will allow for optimal proportional-fill performance. Set the size of your data files large enough to accommodate a typical workload. And if you have tempdb on its own volume, size your files to use the majority of the space on the volume. This way SQL doesn’t have to stop activity in order to grow the files.

Set the autogrowth to a fixed amount (not percentage) and to a reasonable size. If the threshold value is set too low compared to the amount of data written to tempdb, the files will have to expand too often. During autogrowth the database is unavailable, which will cause user transactions to have to wait until the growth process completes. Note – every time SQL server instance is restarted, the tempdb will be recreated to the original size specified in the database properties.

Enable Trace Flag 1117 – If you are not yet using SQL Server 2016 and are on an earlier version, enabling trace flag t1117 will ensure that your tempdb files all grow when any of the files reach the autogrow threshold. (Starting with SQL Server 2016 this flag is unnecessary.) Trace flag 1117 is a global setting, and will impact every database in your instance, not just tempdb. You can enable the trace flag by going to SQL Server Configuration Manager > SQL Server Services > Right click on SQL Server (MSSQLSERVER) > Properties > Startup Parameters > enter –t1117 and click Add. You must restart the service for the change to take effect.

Blog_20180111_4

Finally, you can minimize tempdb utilization by practicing the following:

  • Avoid SORT_IN_TEMPDB option
  • Avoid unnecessary cursors
  • Avoid spooling (CTEs referenced multiple times)
  • Avoid using MARS
  • Don’t enable snapshot isolation unless there is a very compelling reason to do so
  • Avoid using triggers for bulk operations
  • Avoid using LOBs as local variables

Reference: https://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!