SQL Profiler shows high duration on unknown TextData

Question

I have been profiling my web app's connection to the sql server for the last 24 hours. Out of 1.226.709 batchcompleted I have 36 with the duration over 0.1 second.

Out of the 36 I have 4 unknown statements. And here I hoped some of you could help me tell what these are about and from where they come?! :)

The first concerns me a bit, cause it have a duration over 1 second.

(The trace were saved to a table in the database, which is why all the durations are in microseconds)

TextData:       SELECT 'Server[@Name=' + quotename(CAST(          serverproperty(N'Servername')         AS sysname),'''') + ']' + '/Collation[@Name=' + quotename(cl.name,'''') + ']' AS [Urn], cl.name AS [Name], CAST(COLLATIONPROPERTY(name, 'CodePage') AS int) AS [CodePage], CAST(COLLATIONPROPERTY(name, 'LCID') AS int) AS [LocaleID], CAST(COLLATIONPROPERTY(name, 'ComparisonStyle') AS int) AS [ComparisonStyle], cl.description AS [Description], CAST(COLLATIONPROPERTY(name, 'Version') AS int) AS [CollationVersion] FROM sys.fn_helpcollations() cl ORDER BY [Name] ASC
CPU:            93
Reads:          0
Writes:         0
Duration:       1013301
ApplicationName Microsoft SQL Server Management Studio - Transact-SQL IntelliSense

TextData:       select  IndA.name, IndA.index_id, IndA.fill_factor, IndA.is_padded, IndA.is_disabled,IndA.is_hypothetical, IndA.allow_row_locks, IndA.allow_page_locks,IndA.spatial_index_type, IndB.bounding_box_xmin, IndB.bounding_box_ymin, IndB.bounding_box_xmax, IndB.bounding_box_ymax, IndB.level_1_grid, IndB.level_2_grid, IndB.level_3_grid, IndB.level_4_grid, IndB.cells_per_object, ind_col.index_column_id, col.name as column_nName, ind_col.key_ordinal, ind_col.is_descending_key, ind_col.column_id, st.no_recompute from (sys.spatial_indexes IndA left outer join sys.spatial_index_tessellations IndB on IndB.object_id = IndA.object_id and IndB.index_id = IndA.index_id) inner join(sys.internal_tables IntT inner join sys.stats st on st.object_id = IntT.object_id and st.stats_id = 1) on IntT.parent_id = IndA.object_id and  IntT.parent_minor_id  = IndA.index_id inner join sys.index_columns ind_col on ind_col.object_id = IndA.object_id and ind_col.index_id = IndA.index_id inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id where IndA.object_id = object_id(N'dbo.rapports') order by IndA.index_id, ind_col.key_ordinal
CPU:            109
Reads:          216
Writes:         0
Duration:       148322
ApplicationName Microsoft SQL Server Management Studio

TextData:       select col.name, col.column_id, st.name as DT_name, schema_name(st.schema_id) as DT_schema, col.max_length, col.precision, col.scale, bt.name as BT_name, col.collation_name, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol, col.is_identity, case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.seed_value) end, case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.increment_value) end, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl')) as IsIdNotForRepl, col.is_replicated, col.is_non_sql_subscribed, col.is_merge_published, col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name, schema_name(robj.schema_id) as Rul_schema, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name, schema_name(dobj.schema_id) as def_schema, CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as is_FullTextCol, col_name(col.object_id, ftc.type_column_id) FT_type_column, ftc.language_id as FT_language_id, case when(cmc.column_id is null) then null else cmc.definition end as formular, case when(cmc.column_id is null) then null else cmc.is_persisted end as is_persisted, defCst.definition, COLUMNPROPERTY(col.object_id, col.name, 'IsDeterministic') as IsDeterministic, xmlcoll.name as xmlSchema_name, schema_name(xmlcoll.schema_id) as xmlSchema_schema, col.is_xml_document, col.is_sparse, col.is_column_set from sys.columns col left outer join sys.types st on st.user_type_id = col.user_type_id left outer join sys.types bt on bt.user_type_id = col.system_type_id left outer join sys.objects robj on robj.object_id = col.rule_object_id and robj.type = 'R' left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join sys.default_constraints defCst on defCst.parent_object_id = col.object_id and defCst.parent_column_id = col.column_id left outer join sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id left outer join sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and ftc.column_id = col.column_id left outer join sys.xml_schema_collections xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where col.object_id = object_id(N'dbo.rapports') order by col.column_id
CPU:            215
Reads:          6895
Writes:         0
Duration:       142054
ApplicationName Microsoft SQL Server Management Studio

TextData:       SELECT dtb.name AS [Name], dtb.database_id AS [ID], CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject], CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible], dtb.collation_name AS [Collation], dtb.name AS [DatabaseName2] FROM master.sys.databases AS dtb ORDER BY [Name] ASC
CPU:            15
Reads:          324
Writes:         0
Duration:       111258
ApplicationName Microsoft SQL Server Management Studio - Transact-SQL IntelliSense

Extra info!

I run a script every night to backup the database. I have no idea if the 4 statements have nothing to do with that. The statement for the backup is the following:

TextData:   declare @DBName varchar(200)  declare @Filename varchar(1000)  declare @Description varchar(1000)    DECLARE AllDatabases CURSOR FOR  SELECT name AS Name  FROM MASTER.DBO.SYSDATABASES  WHERE [NAME] NOT IN('MODEL', 'tempdb')    OPEN AllDatabases    FETCH NEXT FROM AllDatabases INTO @DBName    WHILE @@FETCH_STATUS = 0  BEGIN    print ''  print ''  print 'Backing up database ' + @DBName    -- Backup the database  set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak'  set @Description = 'Full backup of database ' + @Filename  BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT      FETCH NEXT FROM AllDatabases INTO @DBName  END    CLOSE AllDatabases  DEALLOCATE AllDatabases
CPU:        219
Reads:      2569
Writes:     83
Duration:   4478260

Oh yeah and... I'm not used to profiling my SQL Server. Are there any tips you can give? :) I have of course been googling the topic.

Answer 1

This same query is also responsible for hangs of SMSS when changig from one server to another in object explorer. Just kill the process running this query and on goes the show.

Answer 2

After adding the ApplicationName column you can see that the queries are being run by SSMS to gather metadata about the database. SSMS needs all sorts of metadata to display databases, objects, logins and so on (you can see the reference to Intellisense too), so it's completely normal to see it querying the server. Unless you have a performance problem of some kind that you can directly link to these queries from SSMS, I would forget about them.