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

Goal

To obtain information about the job names that ran during that interval

My way of achieving it

By querying the history table and by displaying the information in the native format followed by the display using the rows as columns. The last way of displaying the information might have in some cases more meaning that the information that is displayed by default when we check the job history.

The output looks similar to this

JobsInfoPivot

The script can be downloaded from here

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

Goal

To obtain the nested list of TSQL statements (SELECT) from the most inner one to the outer one

My way of achieving it

Using the approach explained here for parsing parenthesis expressions.  As you will about to see I am using his unchanged code in my script together with my own additions to extract the select statements and to check for open parenthesis which are not closed. The existence of any open close parenthesis in the comments will break the cast to XML of the code that we want to troubleshoot.  That is why along the code that parses parenthesis expressions I added some logic to remove the comments. After some tests I realized that I need an workaround or better said a real solution for comments removal from the code and the solution came under the form of ScriptDom namespace which provides Microsoft SQL Server-specific Parser and Script Generator.  I left in the tsql script the logic for removing comments but if after execution of the script you still receive errors like

Msg 9400, Level 16, State 1, Line 387
XML parsing: line 227, character 8, unexpected end of input

Then please use the powershell script to clean the code  from comments and once you have it paste it to the tsql script for a successful cast to XML.

The output of the script , below is an example, shows in messages the list of statements from the most inner one to the outer one in an easy format that can be taken and executed step by step and we can see for each the execution plans and statistics and how long it takes and we can modify them to aim for some improvement. Of course that this might look like trivial for an experienced dba but I believe is a great way to troubleshoot in the beginning after none of the steps explained here solved the issue. This approach has its benefits as  other approaches have theirs and there are times when we have to use one or another.

innertooutter

The scripts can be downloaded from:

    • tsql script – has limited logic for comments removal
  • ps script to remove comments – in case we still receive errors when executing the tsql script
    • the powershell script above requires 2 dll files in order to work, Microsoft.Data.Schema.ScriptDom.dll, Microsoft.Data.Schema.ScriptDom.Sql.dll

SQL Server database restore – preparations and tests that we must perform before starting the restore

Goal

To obtain information about the target database files , to check the backup file that we have to use, to estimate the space required and to have a list with steps done and a list with steps to do

My way of achieving it

Using linked server and xp_cmdshell. The restore operation of a SQL Server database is something that requires most of the time careful attention especially when it comes to big databases when the restore operations is time consuming and we would like to have all in place before the restore. Not only that requires our full attention, the restore requires also preparation which is in many cases time consuming and prone to errors and mistakes or something that usually I forget to check. Because of this I decided to create a script that will check some of the things required for a potentially successful restore. The script performs the following verifications:
– checks if the bak file is accessible from the target instance
– checks the drive information of the target instance for space available for the restore
– checks if target instance is older than source instance since backups from newer versions of SQL cannot be restored on old versions
– checks the full bak file for information about the size of the files that will be restored
– check if target database exists on target instance
– check if we have space for individual database files restores and if not provides some alternative locations for relocation
– generate the restore command, is very basic one that needs to be changed in case target database doesn’t exist on target instance

Before using the script we must modify the @sourceinstance, @targetinstance, @sourcedb and @targetdb with the right information.

Th output of the script is below

restoreprerequistes

The script can be downloaded from here

SQL Server Instance – generate tsql to meet tempdb best practices and display the current configuration

Goal

Generate tsql code required to meet tempdb best practices configuration and display the current configuration

My way of achieving it

Using SSMS, tsql, xp_cmdshell

The backbone for this script was a script made a while ago by VijayRod, and reviewed by AkshayM that was checking tempdb configuration by comparing the current configuration to best practices recommended by Microsoft at that time. Because sometime we need if configuration is not compliant to modify the tempdb settings I decided to use their script and add the code that will generate the tsql commands used to configure tempdb database to meet the requirements. This version of the script has some limitations and assumes the following
– when it comes to tempdb databases that have files created in multiple locations ( different drives ), by default the script will take in consideration only the biggest drive when it comes to create the files.
– it assumes that the free space left on the drive where the files will be created will be only 1 GB from the drive capacity. If you want to leave more space then replace the 1024 in the script in this line – SELECT @availablespace=(MAX(diskspacefreeinmb)-1024)/@suggestedDataFilecount FROM @dbfilesdrivemountpoint WHERE filetype = ‘DATA’ –
– it assumes that the maximum size will be the initial size + 10MB. If you want to have a maximum size bigger then replace 10 in the script in this line – SELECT @maxsize = CONVERT(nvarchar,(@availablespace+10))+’MB’ — assumes a maximum size of initial size + another 10 MB

The output of the script looks like below depending on the current tempdb database configuration

tempdbbpgencomm1

The script can be downloaded from here

Display group membership (including nested groups) of a domain account using SQL Server

Goal

To obtain a list of groups that a domain account is member of.

My way of achieving it

Using SSMS and Ad Hoc Distributed Queries. In order to use AD Hoc Distributed queries temporary you must enable it using sp_configure.

Sometimes when we need to troubleshoot why a domain account doesn’t have the requested permissions we have to check his group membership. Most of the time the access to SQL server is given by making an user member of a specific active directory domain group and have that group added as a login or by adding that domain account as a login. Although most of the time this action will solve the request there might be cases when the users say that they don’t have the same rights as other users. This post address this situation by discovering the group membership of those domain accounts. Although we can use net user command with domain switch in a command prompt window

– net user domain_account /domain

the problem is that this will not return any nested groups.

When comparing 2 domain accounts for differences that they might have in SQL Server in regards to permissions we want to be sure that those accounts have the same group membership.

The output of the script is below and can be downloaded from here

groupmembership

 

SQL Server troubleshooting – first steps in troubleshooting performance of someTSQL statements

Goal

Road map of steps that I can use to troubleshoot the performance of a TSQL statement or a SP and keep track of the things discovered on the way for future reference

My way of achieving it

Using scripts that I gathered or taken from what other people shared over time over the internet and by scripts that I created.

first steps troubleshootingV1

1. The script can be found in this book, SQL Server DMVs in Action by Ian W. Stirk right under — Listing 3.6 Identifying indexes used by a given routine
2. The script can be downloaded from here
3. The script can be downloaded from here
4. The script can be downloaded from here
5. The script can be downloaded from here
6. Check the execution plan of the routine in XML format and inside search after PARAMETERCOMPILEDVALUE. Check if the values from there are identical with the values that the application is using or the job.

In most of the cases the index optimization or updating the statistics will help us and maybe will solve the issue but in other cases as the post stated the above steps are just the beginning of troubleshooting.

SQL Server Instance Information – physical location, volume name, disk name and number, disk information

Goal

Obtaining information about the SQL Server physical location where the databases are and displaying for those locations the volume name, disk name and disk information

My way of achieving it

Using SSMS, xp_cmdshell and DISKPART.

In order to not use detail keyword in the script when constructing the xp_cmdshell and diskpart command I decided to us the varbinary representation of it in the script.
SELECT CONVERT(VARBINARY(MAX), ‘detail’) AS [VARBINARY], ‘varbinary representation of DETAIL keyword’ AS Info
SELECT CONVERT(VARCHAR(MAX), 0x64657461696C)AS [VARCHAR], ‘varchar representation of 0x64657461696C’ AS Info

VARBINARY – 0x64657461696C – varbinary representation of DETAIL keyword
VARCHAR – detail – varchar representation of 0x64657461696C

Before any comments about the script I want to say how important is to verify the script before execution and to run it first only on a test server or virtual machine and to run it on other systems only after you create an encrypted version of it.

Sometimes when

– we have to increase a disk where SQL Server databases are, we need to provide information about that disk to other team in order for them to increase the disk

– we have to find more information about a disk from SAN team in case we have some performance issues and those issues are tracked back to I/O latency

– we want to just display the information of disk, volumes, type, size, free in only one window from inside SSMS

The idea of having something like this came to live after I was searching how to map disks and volumes in powershell and I found the script posted by Adam Conkle ( Thanks for the script ) , here, https://gallery.technet.microsoft.com/DiskPartexe-Powershell-0f7a1bab. I realized that I can use the same approach that he uses to do the same but from inside SQL Server.

The script that I am using to obtain the output below can be downloaded from the link provided at the end of this post.

mapvtodandsqll

The problem is that diskpart requires administrators privileges which means that the SQL Service account must be a member of local administrators group on the server in order for the script to function or the implementation of a proxy account that has administrative privileges at OS level. More than that is advisable to have the script created as a SP and encrypt it in order to be sure that no one will replace those keywords, detail disk or detail volume with other ones like delete which can compromise or in other words to destroy the server. I have tried to replace the keywords to its varbinary representation because I considered the script much safer than having the script using the DETAIL keyword.

The script can be downloaded from here

SQL Server cluster – checking if SQL Server clustered instance is running on preferred node

Goal

Obtaining information about the SQL Server clustered instances and if they are running on the preferred node

My way of achieving it

Using SSMS, xp_cmdshell and WMIC. When we have to check and see if our clustered instances are running on the preferred node we can use different approaches and each approach has its own benefits in comparison with the other approaches. In my case because I am used to work more with SQL Server, the usage of SSMS, xp_cmdshell and WMIC is more appealing than everything else and I chose it to find that information.

The script is using:

– 1 WMIC query that uses MSCluster_ResourceGroupToResource – used to find all the groups that are related to SQL Server clustered instances

– 1 WMIC query that uses MSCluster_ResourceGroupToPreferredNode – used to find the preferred node for all the groups

The output returned by the first query is used to generate a result set of group names and the name of the SQL Server clustered instance that is found in each group.

GroupName  InstanceName
SQL11 SQL11\I11

The output above is used to actually generate the script that we will have to ran in SSMS after enabling the SQLCMD mode. Below is an example of the generated script.

:CONNECT APPDKBA1162

DECLARE @preferrednodes TABLE (line nvarchar(500))
DECLARE @InstanceInformation TABLE (InstanceName nvarchar(100), RunsON nvarchar(50), IsPreferredNode nvarchar(3), Comments nvarchar(max))
DECLARE @groupsandinstance TABLE (GroupName nvarchar(500), InstanceName nvarchar(100))
DECLARE @GroupName nvarchar(50)

BEGIN TRY

INSERT INTO @groupsandinstance VALUES (‘SQL Server (MSSQLSERVER)’,’APPDKBA1162′)
INSERT INTO @groupsandinstance VALUES (‘SQL11′,’SQL11\I11’)

SELECT @GroupName = GroupName FROM @groupsandinstance WHERE InstanceName = @@SERVERNAME
INSERT INTO @preferrednodes EXEC xp_cmdshell ‘wmic /namespace:\\root\mscluster path MSCluster_ResourceGroupToPreferredNode’
UPDATE @preferrednodes SET line = REPLACE(line,CHAR(13),”)

IF (SELECT COUNT(line) FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’) > 1
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT TOP 1 CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”
ELSE
INSERT INTO @InstanceInformation
SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)),
(SELECT CASE WHEN CHARINDEX(CONVERT(nvarchar(100),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)), line) > 0 THEN ‘YES’ else ‘NO’ END FROM @preferrednodes WHERE line like ‘%.Name%’ AND line like ‘%’+@GroupName+’%’ ),”

SELECT * FROM @InstanceInformation

END TRY
BEGIN CATCH
INSERT INTO @InstanceInformation SELECT CONVERT(nvarchar(50),@@SERVERNAME) AS InstanceName, CONVERT(nvarchar(50),SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)) AS RunsON,” AS IsPreferredNode,(select ERROR_MESSAGE()) AS Comments
SELECT * FROM @InstanceInformation
END CATCH

GO

:CONNECT SQL11\I11

……….

Below you can see the output of the script

iropn_1

The script can be downloaded from here

SQL Server troubleshooting – what happened with my instance

Goal

Obtaining information about what happened during my last restart of SQL Server instance and if this was a normal restart or an unexpected restart

My way of achieving it

Using SSMS, xp_cmdshell and Wevtutil  we can achieve the goal and we can overcome some of the challenges we face in case we are after the same information using some of the other approaches. I have chosen to not use  SQL Server Error logs because of the

– cycling of the logs during each SQL server instance restart or by using the sp_cycle_errorlog in our environments

– information inside the logs doesn’t contain information outside of SQL Server

I want to mention that the execution time of the script is influenced by the information captured and kept in the windows application logs and the reason for which I decided to display by default 1 sql server restart times.

In case of a clustered instance we have to provide the credentials that will be used to ran the Wevutil remotely and another mention as important as first one is that the script is working only for 2 nodes cluster and in case we would like to run it on a clustered instance that has more than 2 nodes then we need to tweak the script a little bit.

The output of the script is shown below

irmi

We can use the information to check what happened during last restart and see what types of errors , critical, warnings appeared in the last 60 minutes before the last restart in the system and application log.

The script can be downloaded from here

SQL Server troubleshooting – what happened with my database

Goal

Obtaining in the same SSMS window, information about why a database might have been unavailable or having its status changed.

My way of achieving it

Almost all of the monitoring deployed solutions these days raise tickets every time a database becomes unavailable and from the DBA operational point of view this means that we must connect and check what happened with the database and take the required actions. Of course that depending of the monitoring implementation some tickets will be closed automatically if during the second check the database becomes online but this doesn’t mean that we don’t need to try to see why the database status changed. Although we can do our investigation using GUI tools that SQL Server provides this approach has some limitations that the script used here tries to overcome. Below are some of the limitations:

– the account we use to connect to the server where SQL Server instance is running might not have rights to access the location where default trace files are stored which will make almost impossible the usage of SQL Server Profiler

– filtering or searching of SQL Server Error log files using SQL Server Log File Viewer was not designed to search or filter after multiple strings in the same time which makes the filtering or searching of the logs after string a and after string b impossible.

Because of this and other limitations I turned my attention and I tried to find other ways of searching and filtering SQL Server Error Log files and default trace and display the required information in only one window.

The output of the script in some cases will provide us enough information to see what happened while in other cases might give us only the name of the logins that were performing activities during that time.

dbstatus

The main benefit of this approach is that we can have in one window the information pertaining to that database from SQL Server Error log files and the default trace.

The script can be downloaded from here