SQL Server troubleshooting – first steps in troubleshooting performance of some TSQL statements (part 4)

Goal

To split the code of a SP or ADHOC query into pieces in order to find out the number and names of temporary tables used, variable tables, dynamic code, calls of the stored procedures, dynamic tsql, usage of the IN, parameters used in the stored procedures, if code is using index hints, cursors, outer joins, set commands.

My way of achieving it

My approach is to split the code, one word on every line and looping to the lines and searching for specific sql keywords. Is not 100 percent accurate but it will give me a 50% or more accurate view of how the code was written and how we can improve it or how we can spot the bottlenecks. Each table has a some comments columns that can be used to add the things that we must be aware when using SP or dynamic code or DDL statements (create) with DML statemetns (insert) in a SP or when using table variables or IN operator and so on.

Below is an example of the output

split_the_code_perf_troubleshooting

First result set is the query displayed as one word per row. The second result set is the actual SP or adhoc query that we troubleshoot because here we will check the code that is point to us by the other result sets where we have locationincode. For example the fifth result set shows us that our stored procedure is using parameters and that parameter is used on line 37,  select @dbname = parsename(@objname, 3). And this can help us to find out if a conversion will take place or not which is helpful in the overall troubleshooting process. Also we can see some of the tables that are used and if by experience or because we are familiar with the server and database e know that the table has millions of rows then we can start troubleshooting from there.

The script can be downloaded from here

Mapping databases to their backup files that are still on disk

Goal

To obtain a list of backups for the databases that are on our instance whcih are still on disk and to count them.

My way of achieving it

Using tsql and xp_cmdshell. From time to time we end up having free space issues even after the backup cleanup job that we have in place executed successfully. This situation might happen because of how our backup cleanup logic works. Most of the backup cleanup logic is using dates to delete files and not numbers of the backups or a combination of these two. Because of this I created the below script in order to know how many backup files are still on disk for each of my databases. If we know that we should keep only 2 full backups and we have 3 on disk then we can delete one. The same applies for differential backups. The script works well for the environment for which I created it but with some small modifications I believe it will suit other environments need.  The script is expecting the backup file name in this form

I:\sqldata\backup\diff\SQLServer01\03\mysite_regional_02_p_20160903_042243_ab_diff.bak

  • the path contains the database name
  • the path has a timestamp in the form above
  • the path contains the type of the backup

By default the script will output

  • the database name and how many backups on disk exists for that database
  • the database names and their path and their backup date for all the databases that have more than 2 backups on disk.
  • the delete commands for a backup file that was taken for a database that is not anymore on the instance
  • some information in case the backup path contains also a folder named ‘restore’

The script provides only information and decision to delete something is in the hands of the person that interprets the results and knows how many full backups we should have, how many differentials and so on.

Below you can see the output of the script fir diff.bak and a local path

mappingdbtobakfiles

 

The script can be downloaded from here

 

SQL Server Administration through SQL Server Management Studio

Goal

To create an interface that contains the up to date information and know how when it comes to manage SQL Server databases using SSMS and to prepare for further certification or just to refresh the knowledge or improve them with something new.

My way of achieving it

By duplicating the SQL Server Management Studio interface using static information and using Tool Tips. Before anything else I want to mention that:

  • I am not a programmer
  • The information is taken from other DBAs that were kind to share it online

When I started to learn SQL Server it was obvious ( and I am speaking only about the database engine) that it will be hard to always remember anything and this became more clear throughout my jobs since every company has its own vision and they are doing things in their own way. On top of these, most of the time, depending on the position and the job description we are loosing the knowledge and the know how that we learned during a SQL Server certification or during a job where we had to do only performance troubleshooting or during a job which required only database maintenance and user creation and so on. Because of this I decided to start making this simple application in C sharp in order to use it as a repository of knowledge and best practices when it comes to SQL Server.

The main purpose of this application is to

  • help someone that just started to learn SQL Server by having some of the information at one click
  • for more experienced DBA it might be just a repository containing the experience he accumulated throughout his jobs. Every ToolTip can have a legend section  where we can put  how we solved a similar issue or what was the root cause.
  • for others might be just a place containing the answers that we have to provide when speaking, solving SQL Server problems.

Below is a video that I made showing some of the things. Ignore how the application looks it is just to show how good would have been if Microsoft would have provided an argument when starting SSMS that will allow some ToolTips and some best practices.

Below you can watch some of the things that I manged to create and do till now.