Learn SQL Server from Microsoft’s Own Code

By: Jeffrey Yao | Comments (2) | Related: More > Database Administration

Problem

I am a junior DBA with a few years of work experience, I usually improve myself by reading books/forums/blogs, attending seminars / training sessions / user groups etc., are there any other ways I can further improve myself?

Solution

As a DBA, we all need to keep sharpening our skills and knowledge, and especially when new releases of SQL Server products come out. Other than the normal ways of learning, I find it is very helpful to look at how Microsoft has done something that we DBAs need to handle.

In this tip, we will look at a few ways (with examples) about how Microsoft handles some common DBA tasks, and what we can learn from Microsoft.

Review SQL Server System Object Code

One common question we have is “what is the largest table (in terms of rows, or used space) in this database”? I remember that my first version is to loop through each table in a cursor and use sp_spaceused to get each table’s row size, data size and index size and dump the data to a temp table. There is nothing wrong here, the only problem is poor performance when handling a few hundred to thousands of tables in a database.

So I checked the code in sp_spaceused and looked through the code

exec sp_helptext 'sp_spaceused'

I found the following section:

My tables are regular tables without full text indexes or XML indexes, so with the understanding of how Microsoft calculates a table’s space/rows, I quickly came up with a query to find needed information for all tables as follows:

use AdventureWorks2016 -- change to your own db
SELECT [table]=schema_name(t.schema_id)+t.name, 
   reservedpages = SUM (s.reserved_page_count),
   usedpages = SUM (s.used_page_count),
   pages = SUM (
      CASE
         WHEN (s.index_id < 2) THEN (s.in_row_data_page_count + s.lob_used_page_count + s.row_overflow_used_page_count)
         ELSE 0
      END
      ),
   [rowCount] = SUM (
      CASE
         WHEN (s.index_id < 2) THEN s.row_count
         ELSE 0
      END
      )
FROM sys.dm_db_partition_stats s
inner join sys.tables t
on s.object_id = t.object_id
group by t.schema_id, t.name
order by [rowcount] desc;

The performance improved hugely compared with looping through each table and then executing sp_spaceused. Here is part of the output for the AdventureWorks2016 database:

It is also interesting and no surprise to find that with different SQL Server version releases, Microsoft has to change some system stored procedures accordingly. For example, sp_helpindex is different even between SQL Server 2012 and SQL Server 2014 because in SQL Server 2014, there is hash index for memory-optimized tables while not in SQL Server 2012.

Review SQL Server Code from GUI operation

Every SQL Server DBA must have done tasks by right-clicking an object in SQL Server Management Studio (SSMS), and then clicking the pop-up menu. Actually, by looking at what SQL Server is doing underneath the GUI, we can learn a lot, and use many of the techniques directly in our automated administration scripts.

Let’s first start SQL Server Profiler, and connect to our to-be-monitored SQL Server instance. We only need to set up a trace like the following (only monitoring two events and the [TextData] column is needed):

Once the trace is running, we can right-click on the SQL Server instance, and then click ‘Properties’ in the pop-up menu (as shown below).

We will get the following SQL Server instance property window:

Notice on Profiler output below there are lots of events happening. As we click each Page in the above Server Property window, we can see additional events happening in the Profiler window.

In my test environment, I get 45 events (rows) in the Profiler trace if I click each Page.

If we look at each row, we may learn how SQL server is responding to our action. For example, we can see SQL Server will check whether I have the necessary permission by running:

SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');

I can also see the following code has been run:

declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @ErrorLog nvarchar(512)
declare @ErrorLogPath nvarchar(512)
 
select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) from master.sys.database_files where name=N'master'
select @LogPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
select @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex('\', reverse(@ErrorLog)))
 
declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
 
SELECT
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
N'Windows' AS [HostPlatform],
N'\' AS [PathSeparator],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
CAST(SERVERPROPERTY('IsPolyBaseInstalled') AS bit) AS [IsPolyBaseInstalled]

This code is about various properties of the SQL Server instance. I actually borrowed it directly when I was working on a SQL Server instance inventory project, in which I log SQL Server instance properties for each SQL Server instance in my company.

Review Code via PowerShell + SMO

SQL Server Management Object (SMO) has literally a few hundred objects (see BOL reference), and each object may have dozens of properties and methods. They are an excellent source for learning how Microsoft does its work. I will demonstrate two examples here. Please note, I am using SQL Server 2016 with the PowerShell SQL Server module installed.

Find out a login’s user mapping in each database. Here is the PowerShell code, which I get a login named [xyz] in my named instance [localhost\sql2016]:

import-module sqlserver;
$login = get-item -path sqlserver:\sql\localhost\sql2016\logins\xyz;
#start the sql profiler
$login.EnumDatabaseMappings();

In Profiler, I can see the following RPC:Completed event captured:

Here is the full T-SQL statement captured (I removed a few blank lines for better reading):

exec sp_executesql N'
create table #loginmappings( LoginName sysname NULL, DBName sysname NULL, UserName sysname NULL, AliasName sysname NULL )
declare @db_name nvarchar(512)
declare crs cursor local fast_forward
   for ( select name from sys.databases where 1 = has_dbaccess(name)) 
open crs 
fetch crs into @db_name
while @@fetch_status >= 0 
begin 
   set @db_name = quotename(@db_name)
   exec(''use '' + @db_name + '' INSERT #loginmappings select suser_sname(u.sid), db_name(), u.name, null from sys.database_principals AS u where suser_sname(u.sid) is not null'')
   fetch crs into @db_name
end 
close crs
deallocate crs
SELECT
logmap.LoginName AS [LoginName],
logmap.DBName AS [DBName],
logmap.UserName AS [UserName]
FROM
sys.server_principals AS log
INNER JOIN #loginmappings AS logmap ON logmap.LoginName=log.name
WHERE
(log.type in (''U'', ''G'', ''S'', ''C'', ''K'') AND log.principal_id not between 101 and 255 AND log.name <> N''##MS_AgentSigningCertificate##'')and(log.name=@_msparam_0)
drop table #loginmappings
',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'xyz'

From this T-SQL script, we can see Microsoft create a cursor for accessible databases and then query's sys.database_principals for non-orphaned database users, i.e. suser_sname(u.sid) is not null, and puts the result into a temp table, and later uses this temp table to join with sys.server_principals to find all user mappings for [xyz], i.e. @_msparam_0=N’xyz’.

So now we know more about how a login’s user mappings are found. We can literally copy the code and create our own stored procedure.

Now let’s look at some properties of a SMO object. Find a database size and available space.

Here is the PowerShell script:

#requires -version 4.0 
import-module sqlserver -DisableNameChecking;
#find the db size and space available for database [mssqltips] 
$db = get-item -path sqlserver:\sql\localhost\sql2017\databases\mssqltips;
#start the sql profiler
$db | select size, spaceavailable;

From Profiler, we get:

Here is the full T-SQL captured (formatted by me for better reading):

SELECT
( SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) 
) AS [DbSize],
(SUM(a.total_pages) + ( SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) 
             FROM sys.database_files AS df WHERE df.type = 2 
            )
) AS [SpaceUsed]
FROM sys.partitions p 
join sys.allocation_units a on p.partition_id = a.container_id 
left join sys.internal_tables it on p.object_id = it.object_id

I borrowed this code too for my SQL Server instance inventory collection script.

Summary

Reviewing how SQL Server exposes itself in various scenarios is a good way to learn SQL Server “internals”, and it may give us lots of valuable free code to use/borrow.

Of the three ways mentioned above, I really like the “SMO + PowerShell” way as SMO almost exposes all aspects of SQL Server through object properties and methods.

To me, Microsoft’s own source code is a great source of nutrition for us to grow our SQL Server knowledge tree.

Next Steps

I recommend you write a PowerShell script together with a SQL Server trace (server-sided) or XEvent to run through a few typical SMO objects (such as Server, Database, Table) for their properties / methods and automatically log the T-SQL statements that are run when those properties and methods are invoked. It will be a good learning experience.

You may also read some of these other good tips to see how you can improve your knowledge about SQL Server:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips

Comments For This Article

Tuesday, December 26, 2017 - 4:56:20 PM - jeff_yao	Back To Top (74485)
Thanks Tom, Glad and humbled to hear your opinion.

Tuesday, December 26, 2017 - 11:13:20 AM - Tom	Back To Top (74476)
An absolutely outstanding article. Thank you.