在过去的24小时里,我一直在分析我的web应用程序与sql server的连接。在完成的1.226.709批中,我有36个持续时间超过0.1秒。
36个语句中有4个未知语句。在这里,我希望你们中的一些人能帮我告诉我这些是关于什么以及它们来自哪里?:)
第一个让我有点担心,因为它的持续时间超过1秒。
(跟踪被保存到数据库中的一个表中,这就是为什么所有持续时间都以微秒为单位)
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
额外的信息!
我每天晚上运行一个脚本来备份数据库。我不知道这四个表述是否与此无关。用于备份的语句如下:
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
哦,是的,还有…我不习惯分析我的SQL Server。你能给我一些建议吗?:)我当然一直在谷歌这个话题。
添加ApplicationName列后,您可以看到查询正在由SSMS运行,以收集有关数据库的元数据。SSMS需要各种元数据来显示数据库、对象、登录等等(你也可以看到对智能感知的引用),所以看到它查询服务器是完全正常的。除非你有某种性能问题,你可以直接从SSMS链接到这些查询,否则我会忘记它们。
当在对象资源管理器中从一个服务器切换到另一个服务器时,这个相同的查询还负责SMSS的挂起。只需终止运行此查询的进程,然后继续执行。