GO, Go, go

GO

that’s how we tell SQL Server to  execute query or batch of tsql statements.

However GO is not TSQL statement. it’s just command recognized by SQL server utilities like SSMS, SQLCMD, OSQL that signals it is end of batch and it is time to execute the code.

If you want to execute your batch few times for example when you populate table you can  use GO and integer to execute batch that many times.

GO 5

 

go5

As you can see in picture above it executes everything in the batch, including variable declaration. That’s why we got NULL as result 5 times.

You shouldn’t include GO when executing commands using ODBC or OLE DB drivers because you will get an error during code execution.

 

 

 

MS documentation says to not do that as in result you’re going see  error:

Incorrect syntax near 'GO'

Reason for that is that SQL Server parser doesn’t recognize GO as this is not part of the TSQL syntax.

 

Problems with DAC

Recently I wasn’t able to connect to one of my servers due to high CPU usage on the machine. So I tried to connect using DAC (Dedicated Admin Connection) and here is lesson I knew but forgotten (hence this post): Do not open DAC connection in object explorer or you’ll see (in SQL Server 2008 R2):

 
  ===================================

Cannot connect to ADMIN: mySqlServer

===================================

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

------------------------------
 Program Location:

at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.CheckDacAndSmo(SqlConnection sqlConnection)
 at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
 at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
 

Which is not helpful when you know very well that you had it working

Reason for that is that Object Explorer is opening more than one connection and therefore DAC won’t work as it is meant to be lightweight single connection tool with minimal resources use connection for troubleshooting issues when you can’t connect other way.

SQL Server 2016 is much more friendly and  will give you more information:

===================================

Cannot connect to admin: mySqlServer2016.

===================================

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.CheckDacAndSmo(SqlConnection sqlConnection)
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

So solution for this issue is really simple and I am going to remember it now. Just connect using File-> New ->Database Engine Query and connect with:

ADMIN: mySQLServer

Another problem that may occur is that connection is already taken. This may happen if you (of course not) or your colleague (of course) connected to DAC and forgot to close the session afterwards.

For such situation I am have scheduled job that runs every 12hrs and kills any session using DAC endpoint

Code snippet:

If exists (
select session_id from sys.dm_exec_sessions
where endpoint_id = (select endpoint_id from sys.endpoints
where name = 'Dedicated Admin Connection'))

begin

declare @Session2 int

select @Session2 = session_id from sys.dm_exec_sessions
where endpoint_id = (select endpoint_id from sys.endpoints
where name = 'Dedicated Admin Connection')
declare @sqlkill nvarchar (24)

set @sqlkill = N'kill ' + CAST(@Session2 as nvarchar)

execute sp_executesql @sqlkill

end

 

 

 

 

Have no fear Git is here…

I am starting to work with version control and put my scripts under it. I hope it will help with my productivity and improve quality of scripts I am using.

As I am completely new to this GIT thing I used some help from http://classic.scottr.org/presentations/git-in-5-minutes/ to get it started!

It couldn’t be explained easier how to start working with Git! Thank you Scott !

 

 

ALLOW_SNAPSHOT_ISOLATION hanging in SQL Server 2016

In test environment I had SQL Server 2016 with RTM build (13.0.1601.5). Dev team asked to change option for one of the databases and set  ALLOW_SNAPSHOT_ISOLATION to ON as they wanted to test how much impact this may cause in certain scenario.

Usually change of this database setting takes ca. blink of eye but in this case it didn’t complete in seconds, minutes then hours…

Only wait type I saw was “ENABLE_VERSIONING” which “Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.

“as explained by https://msdn.microsoft.com/en-us/library/ms179984.aspx

It made sense first all update transactions must complete and newly started transactions will be able to use versioning. Trouble was there were no open  transactions in this database. Lucky for me  it was TEST/DEV machine so to make sure there were no weird transactions I have restarted first SQL Server service. It didn’t help  to solve to problem so I have tried to change this option on database I have created only for this purpose. Guess what? It also didn’t succeed…

Actual solution that resolved the problem was installation of CU1 for SQL Server 2016. After CU1 has been applied setting ALLOW_SNAPSHOT_ISOLATION database option took  less then blink of an eye.

SQL Server 2016 for FREE (almost)

Developer edition of SQL Server 2016 (and 2014) is now available for free download from here. BUT as we all good know there ain’t free lunch so instead of paying 50 € (or so) for Developer Edition we’ll pay for it with letting Microsoft know how we are using SQL Server features . I saw this in CTP3 installation but I’ve been expecting that this feature will be removed in RTM. It seemed ok that MSFT wants to know our experience with the product and capture potential problems to fix them before releasing final version.

[SQL Server 2016] Hidden telemetry

I did install SQL Server 2016 RC3 recently and after answering all questions in the wizard and ticking all of the boxes I did check ConfigurationFile.ini to see what parameters installer uses.

Few parameters that caught my eye were related to telemetry:

; TelemetryUserNameConfigDescription

SQLTELSVCACCT=”NT Service\SQLTELEMETRY$RC2016″

; TelemetryStartupConfigDescription

SQLTELSVCSTARTUPTYPE=”Automatic”

; TelemetryStartupConfigDescription

ISTELSVCSTARTUPTYPE=”Automatic”

; TelemetryUserNameConfigDescription

ISTELSVCACCT=”NT Service\SSISTELEMETRY130″

SQL Server 2014 Service Pack 2

Service Pack 2 for SQL Server 2014 will be released closed to date of release of SQL Server 2016. Reason why this is interesting for DBA’s is the fact that just right next to several  fixes to database engine, some of the new features introduced in SQL Server 2016 will be also available in this build.

  • Information displayed in SYS.DM_DB_INDEX_USAGE_STATS will be reset only when database would go offline or server is restarted.
  • Other administrator friendly feature would be possibility to see progress of database recovery process with new xEvents with new actions
  • New spills warnings during sort operations in tempdb
  • Memory grant Showplan warning which shows in one of the 3 conditions:
  1. Under used – when the max used size is to small compared to the grant size. It is causing blocking and less efficient usage
  2. Over used – when the used size exceed the grant size. May cause out of memory on the server
  3. Excessive growth – when the dynamic grant increases too much. this may cause server instability and unpredicted query performance