Database Source Code Output

Today the development databases are inaccessible. The network team perform a migration over the weekend and now most of the development teams are unable to do their work. I've taken this opportunity to note something down. Its a mechanic I've used for a number of years when working in a Windows environment.

First, a bit of context. I spent several years working with Oracle in Linux and Unix. For those years, I made use of parametrized shell scripting and SQLPLUS to make short work analytical queries for the purposes of resolving domain/business problems quickly without the need to write out SQL. I had become accustomed to scripting in a Linux environment and automating my tasks. Moving to a Windows environment effectively clipped my wings as I was no longer able to use scripts and work with immediacy. Or so I thought. Within a week of working with SQL server, I made a fantastic transition into using Linux shell scripts in the windows environment using Linux emulator and SQLCMD. One of the positive effects is that I can make use of native Windows applications through the shell script. One of these little inventions is detailed below. This method can also be used with SQLPLUS in Windows.

Enter SourceDump
The sourcedump function is one that takes in a module name and dumps out the entire obejct into a .sql file. Once dumped, it is opened with Notepad++.
1. The first part required is a script that dumps the module out. This is a SQL database so I make use of sys.all_sql_modules instead of Oracle’s dba_source.

set nocount on
use $(Database)
go

select top 1 
       definition
  from sys.all_sql_modules (nolock)
 where object_name(object_id) like '%$(search_phrase)%'
   and object_id > 0

I place this query into a file called
c:\mine\sql\r_sql_source_dump.sql

2. The next task is to have a folder to store the files output from the script.
c:\mine\sourcedump

Once these 2 requirements are met, I move onto the shell configuration and functions.
 
 
3. In the Linux emulator, open the .profile file with vim editor
[rayb.] ➤ vim $HOME/.profile

2. Adjust the $PATH variable to increase its scope to key SQL directories (SQLCMD + DLL's) and include a text editor (Notepad++ is what I make use of)
export PATH=$PATH:"/drives/c/Program Files/Notepad++":"/drives/c/Program Files (x86)/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/DTS/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/ManagementStudio/":"/drives/c/Program Files/TortoiseSVN/bin":"/drives/c/Users/rayb/AppData/Local/Microsoft/WindowsApps"

3. Add the following shell function to the .profile file
sourcedump()
{
  sqlcmd -y 0 -w 65000 -S "<SERVER_IP>,<INSTANCE_PORT>" -v Database="$1" -U sa -P <PASSWORD> -v search_phrase="$2" -o "c:\\mine\\sourcedump\\$2.sql" -i c:\\mine\\sql\\r_sql_source_dump.sql
  notepad++ "c:\\mine\\sourcedump\\$2.sql"
}

4. Resource the .profile file
[2017-05-22 09:14.04]  ~
[rayb.       ] ➤ source $HOME/.profile

[2017-05-22 09:14.09]  ~
[rayb.        ] ➤

5. Output a module

SQL Server Top Running Queries by CPU seconds per minute

I have arrived in a new environment. I handed the real application cluster over in a stable condition and have since moved into a new Microsoft environment. In the past week, I've been writing a number of SQL scripts for SQLCMD. The scripts will speed up the process of learning and understanding the environment.

The script below was refurbished for the current environment - it is a TOPCPU script I wrote for another SQL environment around 4 years ago. It follows a similar form to the one I use for Oracle environments. Unfortunately, the code below is indented badly on the page - I will come back to cleaning it up.  The substring is for controlled output through SQLCMD. The script divides the duration of the users session by the amount of CPU seconds consumed and then orders the results by this new figure. It is not 100% perfect but it often sheds light on whats going on inside the system and any session that is out of control will usually spike to the top of the report. It has served me well over the years. It can also be adjusted to report on IO, though through my anecdotal experience, the TOPCPU often reports bad sessions to the top more often than TOPIO.

:setvar NOCOUNT ON

use $(database)
go

print ''
print '___________________________________________________________LOAD UP'

print ''
print '=============================================='
print 'TOP CPU CONSUMING SESSIONS ON : $(database)'
print '=============================================='
print ''
go

     select top 20
            substring(convert(varchar,sp.spid),0,4) sid,
            substring(sp.hostname,0,10) hostname,
            substring(object_name(st.objectid,sp.dbid),0,20) obj_name,            substring(convert(varchar(45),replace(replace(replace(st.text,char(13),''),char(10),''),char(9),'')),0,45) text,
            substring(sp.loginame,0,10) loginame,
            substring(db_name(sp.dbid),0,10) did,
            CASE WHEN sp.blocked > 0 THEN 'YES' ELSE 'NO' END blx,
            substring(sp.lastwaittype,0,15) lastwaittype,
            substring(sp.status,0,10) status,
            substring(sp.program_name,0,15) program_name,            substring(convert(varchar(15),replace(replace(sp.cmd,char(13),''),char(10),'')),0,15) cmd,
            substring(sp.nt_domain,0,10) nt_domain,
            substring(sp.nt_username,0,8) nt_username,
            convert(varchar(6),datediff(minute,sp.login_time,current_timestamp )) minutes,
            sp.login_time,
            convert(varchar(6),
                    sp.cpu/case 
                             when datediff(minute,sp.login_time,current_timestamp ) <= 0 then 1 
                             else datediff(minute,sp.login_time,current_timestamp ) end) cpu_pm
       from sys.sysprocesses sp (nolock)
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st 
 order by sp.cpu/case 
                   when datediff(minute,sp.login_time,current_timestamp ) <= 0 then 1 
                   else datediff(minute,sp.login_time,current_timestamp ) end  desc
go

print ''
print '_________________________________________________________COMPLETED'
print ''

Results below - obfuscated owing to the sensitive nature of the business. The script was setup on a screen resolution at 1920x1080 - the output may be jumbled on something smaller.