Split strings using tsql – a different method

Goal

To split a string separated by comma or other char using tsql

My way of achieving it

Using dynamic sql

The method that I will describe here is working good enough for the situations in which we want to split a char no longer than 50k chars and it has the benefit of less typing and more intuitive approach but this is only my opinion about it.  Most of the approaches are using CLR, XML, numbers table, CTE and others and are very good explained here (http://sqlperformance.com/2012/07/t-sql-queries/split-strings). The code that I am using to split the string is below

declare @databases nvarchar(max)
set @databases=’a,id,xyz,abcd,abcde,sa,foo,bar,mort,splunge,bacon’
–SET @databases = REPLICATE(@databases, POWER(10, 3));
select @databases=’select ”’+replace(@databases,’,’,”’ union all select ”’)+””
exec(@databases)

If you need to split the string based on a different char then replace comma in the replace function with the new char, for example

select @databases=’select ”’+replace(@databases,’|’,”’ union all select ”’)+””
exec(@databases)

It seems that in the form above the dynamic sql is very dangerous (was made very clear to me here, http://sqlperformance.com/2016/03/sql-server-2016/string-split#comment-121269 )  and I changed it to

select @string ='select '''+replace(replace(@string,'''',''),',',''' union all select ''')+''''

which removes the possibility of running some bad code but it also breaks words like don’t or won’t.

Sqlcmd formatting for better vizualization (part 2)

Goal

To keep when possible the same display as SSMS GUI has for the output of a SELECT statement.

My way of achieving it

By creating a SP that will generate a dynamic tsql for the select statement or by rewriting the select statement.

I haven’t come up with the SP yet but I created the code that will generate the dynamic tsql that we can run to format the output. The select must be simple more exactly the columns provided in the select statement otherwise it will break the code.  More than that, some type of columns and chars need more attention and when those are displayed, this might break the formatting also. As an example you can see first a result of select name,create_date,compatibility_level from sys.databases followed by a result of select name,create_date,owner_sid,compatibility_level from sys.databases which prove the formatting challenges if some of the columns contains some kind of chars.

sqlcmdoutput2

Nevertheless the code can be modified to deal with these situations and format them properly.

The code can be downloaded from here

Sqlcmd formatting for better vizualization

Goal

To keep when possible the same display as SSMS GUI has for the output of a SELECT statement.

My way of achieving it

By creating a SP that will generate a dynamic tsql for the select statement or by rewriting the select statement.

While working in SQLCMD we face the problems whenever we issue a select statement or we run an internal stored procedure. The problem is that the output is not displayed or aligned in an way that is easy to read. Below are some outputs of connections using different switches in sqlcmd. As you can see in the below picture I tried the same query with no switch, with -W, -Y and -y.

sqlcmd_format_output

 

No matter of the switch used the display is either truncating the columns or is hard to read. The last display is the one that I believe is easier to read. Of course that this simple example is only the tip of the iceberg and depending on the number of the columns that we want to select the generating of the dynamic tsql might be impossible but even so it is worth to add this type of output to our internal stored procedure for a better visualization in sqlcmd.

The SP for converting the select is not yet provided in the scripts below but I provided the code used to format the output.

The scripts used to create the output above and the difference between running xp_fixeddrives and the code for generating what I call a better display can be downloaded from here .

 

 

Check what type of storage your SQL Server is attached to

Goal

To group the storage of our sql server in 4 main categories that will help us when seeking for information about the speed of the storage, how and if the storage can be increased.

My way of achieving it

Using powershell.

The easiest and more convenient way was to create an oneliner that we can run in powershell and output the desired information. I don’t know how good will work with all the other hardware configurations since the info that I used to create the oneliner was containing only the following information

Disks

  • NETAPP LUN  Multi-Path Disk Device
  • HP LOGICAL VOLUME SCSI Disk Device
  • PowerDevice by PowerPath
  • HP LOGICAL VOLUME SCSI Disk Device
  • SanDisk SD7TB3Q-256G-1006 ATA Device
  • HGST HTS545050A7E380
  • VMware Virtual disk SCSI Disk Device

Controllers

  • QLogic Fibre Channel Adapter
  • Emulex OneConnect OCe11100, iSCSI Storport Miniport Driver
  • QLogic Fibre Channel Adapter
  • EMS Device Bus
  • Microsoft Multi-Path Bus Driver
  • Smart Array P410i Controller
  • Microsoft Storage Spaces Controller

but I will update the script in time.

The onliner is this

Get-WmiObject Win32_DiskDrive | group-object model | foreach {if($_.Name -like “*virtual*”) {“1. Virtual Machine”} else {Get-WmiObject Win32_SCSIController | group-object name |foreach {switch -Wildcard ($_.Name) {“*smart array*” {“2. Smart Array”}; “*fibre*” {“3. HBA’s”}; default {“4. Buit-in Controllers”}}}}} | group-object $_ | select Name |sort-object Name

Below is a picture of running the script on 3 different servers

groupcontrollers

SQL Server comparison script

Goal

To obtain specific comparison information  for the most common DBA specific tasks.

My way of achieving it

Using powershell.

This is not a final script is merely an example that can be used to create something that can be used when we need to compare 2 logins, 2 database users, 2 instances and so on. The script can contain other type of comparison and this example contains only what I am used to compare most of the time.  Another benefit is that provides an output that can be easily exported and filtered further in excel and provided to other people in an easily accessible format .

The output of the script look similar to this

comparison_script

 

The script can be downloaded from here

Task Manager and Activity Monitor for SQL Server instances

Goal

To obtain the same information that Activity Monitor in SQL Server and Task Manager in the Windows provides for all the SQL Server Instances running on our server.

My way of achieving it

Using powershell and registry we can find out information about the sql server services that are running on our server and with that information we can construct the counter names that we want to gather. The counters might be added or removed as needed but I tried to put what Activity Monitor and Task Manager display. The main benefit is that we can have these for all the instances and taking advantage of the out-gridview cmdlet we can sort and order values as we like without worrying that the information displayed after sort will contains processes that we don’t want. For example the output created for the performance counters below

\Memory\Available MBytes
\Processor(_Total)\% Processor Time
\LogicalDisk(C:)\Current Disk Queue Length
\LogicalDisk(C:)\Disk Read Bytes/sec
\LogicalDisk(C:)\Disk Write Bytes/sec
\LogicalDisk(D:)\Current Disk Queue Length
\LogicalDisk(D:)\Disk Read Bytes/sec
\LogicalDisk(D:)\Disk Write Bytes/sec
\Process(sqlservr)\% Processor Time
\Process(sqlservr)\Private Bytes
\Process(sqlservr)\ID Process
\Process(sqlservr)\IO Read Bytes/sec
\Process(sqlservr)\IO Write Bytes/sec
\Process(sqlservr)\Elapsed Time
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:Databases(_Total)\Backup/Restore Throughput/sec
\SQLAgent:Jobs(_Total)\Active jobs

is

perfmonamtm1

 

while the output for a cluster might be the one below

perfmonamtm

Of course that for some counters depending on the SQL Server Edition we might receive these type of errors which are normal and I display them.

get-counter : The \SQLAgent:Jobs(_Total)\Active jobs performance counter path is not valid.

The script can be downloaded from here

SQL Server installation history

Goal

To obtain sql server installation history like log file name, feature name, who install it and what was installed or updated

My way of achieving it

Again using xp_cmdshell, registry and sql server installation logs. The script is taking information from

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products

and from

C:\Program Files\Microsoft SQL Server\*.log

The result of these is joined based on the registry key name and after that some info is read from the log files. The script doesn’t show the products, sql server related that were uninstalled and sometime because sql server logs might have been deleted no info is returned when trying to join the results obtained by searching those 2 locations. Whatever will be the reason, the script has commented select from the various tables and we can find at least half of the information that we want. The script performance and time execution can be improved and I want to mention that since I am checking all those log files and since I am applying some string functions to raw data the execution might take minutes to complete. Maybe later I will try to improve it but for the time being I am ok with it.

The output looks similar to this

sqlsrvrinsthistory

The script can be downloaded from here

 

Windows 2012 disk information – displaying disk information

Goal

To obtain disk information like number, size, partition style, drive label and drive letter

My way of achieving it

Using powershell. I must start by saying that this requires windows 2012. The necessity of having something like this is my desire to create a SQL installation script that eventually will install a SQL Server from one end to the other. The only parameters that the script will require will be the account used for the services and the instance name. Everything else will be discovered automatically. The script from this post is my attempt of discovering the disks available on a server and formatting them accordingly. Till now the script was tested only against simple volumes. The script contains a few functions, one that discover all the disks, another one that discover all available disks (RAW format) , another one that prepare the disk for a stand alone installation of SQL Server and the last one which is preparing the disk for a clustered SQL server installation.

Below is the output generated by the script.

disk_config

As in the case of any actions that change the data, the format functions should be used with caution. The steps for running the script in a safe manner will be to execute the script and call the functions in the following order

  • all_disks
  • av_disks
  • formatclusterdisk or formatclusterdisk with the whatif parameter
  • formatclusterdisk or formatclusterdisk without the whatif only after checking that indeed the disk is RAW.

The script can be downloaded from here

Windows cluster – checking if services or applications are running on preferred node

Goal

To obtain information about the location where cluster services or applications are running now and if they are running on the preferred node.

My way of achieving it

Using powershell. From time to time we want to know if everything is running on the preferred node on our cluster. Although the best practice will be to already have a powershell script that I can run on our server in some cases if the script is not there already or if the creation of the scripts is forbidden or just because I find more easier to copy paste the script in powershell console I decided to create something that might be considered like  an one-liner for this job.

The output of the script is below

preferrednode_oneliner

The script can be downloaded from here

SQL Server troubleshooting – tempdb utilization

Goal

To obtain information about tempdb database from utilization perspective

My way of achieving it

Using some of the DMV/DMF that come with SQL Server and overall advises (scripts taken from different sources over internet that all refers to SQL Server DMVs in Action Better Queries with Dynamic Management Views by Ian W. Stirk)  from peers colleagues from internet customized to fit my needs  and my own way of troubleshooting. For me the most important thing while troubleshooting is to have the result of the investigation side by side with what it means since in most of the cases I am not dealing with this everyday and my memory about this topic is as good as the number of occurrences I had throughout my working experience. In simple terms for tempdb database as for other user databases we can find information about usage using specific DMVs and DMFs. The utilization information (tempdb) provided by SQL Server comes under the form of

  • present utilization
    • current space occupied by all the objects that uses tempdb – user objects, internal objects, version store, mixed extent
    • current space each session is currently using in tempdb because of running tasks
    • current space each session is currently using in tempdb because of row versioning
  • past utilization
    • used space in tempdb by all the sessions since they were established

The output of the script is ordered by the space still used and all the columns along the table gives me all the information that I need in order to decide the next steps or to say whom, when,  why in regards to tempdb utilization.

tempdbtroubleshooting

The script can be downloaded from here