By: Rajendra Gupta || Related Tips:More > SQL Server Management Studio

Problem

SQL Server Management Studio is a graphical tool to configure, monitor and perform administrative tasks for SQL Server. This graphical tool makes the task much easier, but do you know what queries SQL Server runs when we perform certain tasks using SSMS? SQL Server also sends certain information back to Microsoft; how can we view that information for the queries we are running?

In this tip, we will explore how can we view and capture the queries running by SQL Server while doing tasks in SSMS and telemetry information for the queries we run in SSMS.

Solution

In one of my previous tips, New Features in SQL Server Management Studio v17 , we saw that SSMS is now a separately installed component and is not part of the SQL Server set up. SSMS 17.x contains many exciting new features, you can explore some of these features in the SQL Server Management Studio tip category .

Once we connect to an instance in SSMS, go to View > Output or use the shortcut key (Ctrl+Alt+O).


SQL Server Management Studio Output Option for Object Explorer Queries and Telem ...

Once you click on Output, it opens up an output window having two drop-down options:

Object Explorer Telemetry

Let's explore these options.

SQL Server Management Studio Object Explorer Data

One of the new features introduced in SSMS v17.x is that we can now see the queries, which SQL Server is running after clicking on an option in SSMS. Previously we had to run Profiler or an Extended Events session to capture the queries running. If we work with the GUI, normally we do not give much attention to the queries that are running behind the GUI, but this is an interesting feature to see the queries executing in the background.

Object Explorer shows the query text and elapsed time of the SQL queries needed to expand nodes in Object Explorer. This also gives information about the event timestamp and logs the begin and end query event.

Let's look at a few examples while expanding nodes in SQL Server.

Example: Connect SQL Instance

Once we connect to a SQL Server instance, it logs that information in Object Explorer as shown below:


SQL Server Management Studio Output Option for Object Explorer Queries and Telem ...

We can see the start of an event and it gives the below information:

Begin Query event with timestamp and URN End Query event with timestamp and URN Elapsed time in milliseconds Query

The URN (uniform resource name) refers to the underlying SQL Management Object and consists of an XPath-style hierarchy. We can copy the query and run it in SSMS as well to get detailed information.

create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver
if exists (select 1 from sys.all_objects where name = 'dm_os_host_info' and type = 'V' and is_ms_shipped = 1)
begin
insert #SVer select t.*
from sys.dm_os_host_info
CROSS APPLY (
VALUES
(1001, 'host_platform', 0, host_platform),
(1002, 'host_distribution', 0, host_distribution),
(1003, 'host_release', 0, host_release),
(1004, 'host_service_pack_level', 0, host_service_pack_level),
(1005, 'host_sku', host_sku, '')
) t(id, [name], internal_value, [value])
end
SELECT
CAST(
serverproperty(N'Servername')AS sysname) AS [Server_Name],
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' AS [Server_Urn],
CAST(null AS int) AS [Server_ServerType],
0 AS [Server_IsContainedAuthentication],
(@@microsoftversion / 0x1000000) & 0xff AS [VersionMajor],
(@@microsoftversion / 0x10000) & 0xff AS [VersionMinor],
@@microsoftversion & 0xffff AS [BuildNumber],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
CAST(ISNULL(SERVERPROPERTY(N'IsXTPSupported'), 0) AS bit) AS [IsXTPSupported],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
( select Value from #SVer where Name =N'host_platform') AS [HostPlatform],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled]
ORDER BY
[Server_Name] ASC
drop table #SVer
SQL Server Management Studio Output Option for Object Explorer Queries and Telem ...
Example: Expand Database Nodes

If we expand database nodes in SSMS to see the details of databases, the below query is logged in Object Explorer.


SQL Server Management Studio Output Option for Object Explorer Queries and Telem ...

Here is the query:

SELECT
dtb.name AS [Database_Name],
'Server[@Name=' + quotename(CAST( serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@Name=' + quotename(dtb.name,'''') + ']' AS [Database_Urn],
dtb.containment AS [Database_ContainmentType],
dtb.recovery_model AS [Database_RecoveryModel],
ISNULL(suser_sname(dtb.owner_sid),'') AS [Database_Owner],
case
when dtb.collation_name is null then 0x200
else 0
end |
case
when 1 = dtb.is_in_standby then 0x40
else 0
end |
case dtb.state
when 1 then 0x2
when 2 then 0x8
when 3 then 0x4
when 4 then 0x10
when 5 then 0x100
when 6 then 0x20
else 1
end
AS [Database_Status],
dtb.compatibility_level AS [Database_CompatibilityLevel],
ISNULL(dmi.mirroring_role,0) AS [Database_MirroringRole],
ISNULL(dmi.mirroring_state + 1, 0) AS [Database_MirroringStatus],
drs.database_guid AS [Database_DatabaseGuid],
CAST(case
when SERVERPROPERTY('EngineEdition') = 6 then cast(1 as bit)
else cast(0 as bit)
end
AS bit) AS [Database_IsSqlDw],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_read_only AS [ReadOnly],
dtb.name AS [Database_DatabaseName2]
FROM master.sys.databases AS dtb
LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id
LEFT OUTER JOIN sys.database_recovery_status AS drs ON drs.database_id = dtb.database_id
WHERE
(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=0 and CAST(isnull(dtb.source_database_id, 0) AS bit)=0)
ORDER BY
[Database_Name] ASC Example: Expand Security and Logins

When we expand the security section to view logins, the below information is logged.


SQL Server Management Studio Output Option for Object Explorer Queries and Telem ...

Here is the query:

SELECT
log.name AS [Name],
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Login[@Name=' + quotename(log.name,'''') + ']' AS [Urn],
log.create_date AS [CreateDate],
CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType],
log.is_disabled AS [IsDisabled]
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')
ORDER BY
[Name] ASC Example: Viewing Error Lo

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLERPSQL ServerSNUUTFU
tags: AS,Server,SQL,dtb,Database,host,Name,CAST,log
分页:12
转载请注明
本文标题:SQL Server Management Studio Output Option for Object Explorer Queries and Telem ...
本站链接:https://www.codesec.net/view/580631.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(15)