SQL Job performance issue

Problem Statement:  SQL Server job taking longer execution time. Import and load job was scheduled to run every hour and it’s taking longer time. On usual US time it executes well and gets completed in approx.. 2 hours while during US night it’s taking 4+ hours to finish.

Solution: The first point to focus is why job is taking longer time to run during night schedule.  The very next thing comes to mind is how the server load is during night hours. During job run we kept server under observation and executed few perfmon counters the server activities were normal. There was no memory issue or CPU contention present on the server. So we thought of checking what are the other activities happening on the server we found that there were many index defrag and backup job are running on the server, we guess this might be the culprit. Might be these jobs are consuming resources, might be causing locks and blocks. We checked that these jobs are finishing in secs and not doing any server contention so this option was ruled out as well. Then I monitored the inactive SQL session by sp_who2.  I saw SUSPENDED spid , that spid belongs to the concerned load job and this trend continued. SPID of that job was frequently getting SUSPENDED. So, what we need to understand here is - Why SQL server suspends any process? Simply put, SQL suspends the process because it has to wait on a resource of certain kind. Now we digged into for what reason these SPIDS are waiting? There is DMV sys.dm_exec_requests which tells the details about the SPID Executed following command to know the details :

select *  from sys.dm_exec_requests where session_id = (suspended spid )

Give spid as session id in the above query and here we got the clue the wait_type was ASYNC_NETWORK_IO. What do I understand from this - ASYNC_NETWORK_IO is commonly a result of a client application that is either slow to receive or process the resultset from SQL Server. So we analysed the output of the job and we amazed to see that query was handling around 2M rows in a shot. Hurray we nailed it right .We contacted application team with these details and advised them to decrease the number of rows pulled by application on each cycle of run and they agreed to change the logic to handle fewer loads in each run.

Is SQL Server express edition supported by Microsoft?

As DBA you would never be happy seeing express edition in your production environment. But if someone ask you whether Microsoft officially says that express edition is 'NOT' supported? The answer is 'No' , it is supported. You can use express edition in production , it depends on requirement.
Any edition other than developer edition can be used for production environment.

MSDN says :

SQL Server 2012 Developer edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server. SQL Server Developer is an ideal choice for people who build and test applications.

You can use express edition for production but it would not be scalable and there many features which you will not get in express edition. The good thing is that you can upgrade express edition seamlessly to the higher editions as per business requirements.

Following link contains SQL Server support lifecycle published by Microsoft :

http://support.microsoft.com/lifecycle/default.aspx?LN=en-us&c2=1044&x=6&y=18

Powershell Script to check if any server is pending for reboot

After the security patch, we need to validate if any of our critical server is pending for reboot and take necessary actions.PowerShell script and use it to identify servers that are pending for reboot.This will avoid the scenarios of unexpected server reboot.

 

# Filename:  CheckForPendingReboot.ps1
# Description: Imports a list of computers from a text file and then checks each of the
#    computers for the RebootPending registry key. If the key is present,
#    the script restarts the computer upon confirmation from the user.
# Assumes the existence of a file called 'Servers.txt' in the same directory, populated with
# the NETBIOS names of a list of computers, each on a new line.

 

 $computernames = gc Servers.txt

 foreach ($computername in $computernames)
 {
 $baseKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine",$computername)
 $key = $baseKey.OpenSubKey("Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\")
 $subkeys = $key.GetSubKeyNames()
 $key.Close()
 $baseKey.Close()

 If ($subkeys | Where {$_ -eq "RebootPending"})
 {
  Write-Host "There is a pending reboot for" $computername
  Restart-Computer -ComputerName $computername -confirm
 }
 Else
 {
  Write-Host "No reboot is pending for" $computername
 }
}

 

Steps to open powershell Ise

Following are the ways to open powershell ISE:

Method 1 : If you see powershell icon on your task bar. you can right click and choose 'windows powershell ISE'.

 

Method 2 : In the Run box, type, powershell_ise.exe.

Method 3 : In the Windows PowerShell console type powershell_ise.exe.

Powershell script to filter error from event viewer system log

This is first post of 2013 :)  so wanted to post something which I learnt recently.

A powershell script for filtering errors in the event viewer log. This script you need to execute in powershell ISE.

$startTime=(Get-Date).AddDays(-1)
$endtime=Get-Date
$b = get-eventlog -log system -after $starttime -before $endtime -EntryType Error
$b = $b | sort eventid
$b | Export-CSV -delimiter " "  e:\outtab.txt This script will filter out last 24hours system log and pull all the errors from the log and dump it to txt file. If you want to tweek this script and if you like to export the data in csv file you can use

$b | Export-Csv e:\out1.csv

Output would look like :

 
 
 
if

SQL Queries

Basic Restore commands all in one

 

 

Restore database adventureworks from disk = 'D:\AdventureWorks.bak'



Restore with different file names and/or file locations

RESTORE DATABASE Adventureworks

FROM DISK = 'D:\AdventureWorks.bak'

WITH MOVE 'Adventureworks_data' TO 'C:\Adventureworks_data.mdf',

MOVE 'Adventureworks_log' TO 'C:\Adventureworks_log.ldf'


Restore with different database name:

RESTORE DATABASE Adventureworks1

FROM DISK = 'D:\AdventureWorks.bak'

WITH REPLACE