SQL Server – breaking bad habits, improving knowledge and awareness

The hardest part of being a DBA is to keep up with all the things that appear in connection to our job.

  • SQL Server new features
  • SQL Server best practices
  • SQL Server new DMVs, DMFs
  • SQL Server procedures specific to each company
  • …..

Although this information is available and reachable by checking Microsoft web sites, SQL Server specific blogs, your corporate e-mail or corporate intranet, most of the time due to the daily workload or other distractions we miss it. The answer to this will be a reminder or even better a notification. I found the notification a better option because I always like the way in which Microsoft Outlook notifies you when a new e-mail arrives.  While trying to duplicate that behavior I found out that is very hard without a good understanding of .NET programming. In order to still have what I wanted I had to accept that

  • the notification will still consume resources, it will still appear although you cannot see it – the idea behind was to check if you have more than one monitor and if yes to check if any application is running in full screen mode or covers a specific portion of the last monitor. If there will be no application that have windows that covers the lower left corner of the screen then the notification will appear otherwise the notification will not appear since you cannot see it.

Because these days especially in the office we have at least 2 displays the idea of having a notification on the third one that runs every 10 seconds and stays there for another 10 seconds reminding me about a new DMV or reminding me about something else is beneficial. Most of the time will be ignored but from time to time while contemplating the screen or waiting for a task to complete we can peek to that notification and in time we will know the new feature or what is new or other things that you want your notification to display.

Below is an example of how it looks like.

In order to close the notifications we need to double click it.

The script can be downloaded from here

SQL Server SPID – tracking how spid affects sql server

SQL Server Management Studio offers a lot of tools to track what a spid is doing and how it affects the sql server and other spids. Some of the tools can offer information at a very granular level while others to an wider level. Because sometimes we just want something between we need to use what Microsoft offers and use it appropriately. To have an overview of a specifc spid we can use:

  • sp_who2
  • sp_lock
  • built in dmv
  • Activity Monitor
  • extended events

Or we can use custom script that will aggregate information using Microsoft dmvs and SPs and present them. The biggest advantage of using custom scripts is that we can choose

  • to display results to text
  • to display columns as rows
  • to create a include client statistics look like

Microsoft is already doing this when we choose Query – Include Client Statistics which is why I decided to duplicate that and show status,blocking_session_id,wait_type,wait_duration_ms,cpu_time,memory_usage,total_scheduled_time,reads,writes,logical_reads,arithabort,transaction_isolation_level,lock_timeout,deadlock_priority
,row_count,prev_error,group_id.

The output looks like below and I want to mention before anything else that the script used is providing a broad overview and was created on spot only to test how the output look like. Because of this

  • The output should be read and understood from this perspective.
  • The script and the way was written should be seen from this perspective also.

The values that I choose to show are not the most important and they were choose only to show that by choosing to display columns like status we decrease the amount of info that can fit but we can code some of the columns using numeric values, 1 for running, 2 for sleeping and so on. The biggest advantage of this type of display is that we can see how specific spid behave up and down by having only one look throughout of execution time.

Depending on the things that you want to track you can create your own script using the script below as an example. The only thing that you need to check is Results to Text in SSMS.

You can download this script from here

Check and Register SPN for our SQL Server Instances

Goal

To check if our SQL Server instance registered its SPN and to register if not.

My way of achieving it

Using powershell.

Sometimes when troubleshooting connection issues we end up realizing that it was caused due to Kerberos and the solution is to check for SPN and to create it in case is not there. Because the creation requires the knowing of the port under which SQL Server instance is listening and also the service account under which the service is running I found useful the usage of a script that will generate and check the existence and the creation of this SPN. The script can be executed in powershell console and I prefer the one that you can open while you are using SSMS and from contextual menu you choose Start Powershell.

The output of the script looks like below

The script can be downloaded from here

Update datatype of primary key in SQL Server table

Goal

To generate the tsql commands that we can use to alter the primary key datatype.

My way of achieving it

Using powershell to check some of the relations between the tables. As with all other things that I am posting here, this, is far from being a final script. It is just something that you can use in order to check the level of work required to change the data type of a primary key. Sometimes in our job we are asked to perform this type of activity and although it seems like a trivial task there are hidden things that make it very difficult. I tried to show as many things that I could for which the change of datatype might fail but I am sure that depending on the database structure the script will still fail even after we will perform the steps described in the output of the script.  The output of the script must be followed and changed accordingly to your requirements. In the script I changed the data type of InqNo column from int to numeric(18,0).

The script requires the instance name, database name and table name

$instancename = “UCPHMA”
$databasename = “Inquiry2012”
$tablename = “Inquiries”

The output of the script can be seen below and as you can see asks for dropping and recreating some indexes among other things.

IMP – Those actions needs to be very good planned when it comes to the production servers and big tables due to all the problems that dropping an index creates and all the problems that creating an index creates. But in case the tables are small then the impact will not be big. As with every script, first needs to be tested in a controlled environment and used only after the operation was successful there.

Very important is to check the script output before executing it.

The script can be downloaded from here

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

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