Skip to main content

Posts

Why use SELECT * With DMVs?

Dynamic Management Views and Functions are used to retrieve information about SQL Server internals. The Dynamic Management Objects first, introduced in SQL Server 2005. The subsequent releases added many new DMVs while some got changed.  Sometime new columns can be added and column names can be changed in new versions. So it is recommended to use SELECT * whenever you want to get internal information using DMVs. This is especially important when developing production monitoring code. So that the possibility of breaking your code is less with new SQL Server versions.

Recent posts

SQLSaturday584 - Colorado Springs

Another great SQLSaturday conference ended on 25th March, 2017 at Colorado Springs. I presented SQL Server Statistics and New CE model. You can visit the below link to download the presentations and demos which you’re interested. There are many valuable topics.Schedule PageThanks everyone who participated for the event!

SQL Server errors are too generic

Below is an actual error captured in SQL Server Error log during an incident. 
SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
This error is not quite accurate because it says SQL Server must shut down in order to recover a database which is database id 1. In SQL Server, database id 1 means "master" database. The next statement says the database is either a user database or system database. In this particular case, it can not be a user database. 
The error message is quite generic. It appears that SQL Server does not change the error messages depending on the database id in this case, maybe it could be due to a performance impact. 

Understanding SQL Server backups

One of my friends and colleagues (Jayaruwan alias JB) recently asked me a question regarding SQL Server backup. The question was, during the backup operation, what will happen to the data changes? Are those data recorded in the backup? If so till what point? I knew for a fact that the data changes during the backup operation will get written to the backup. To demo this, I did the following test. You need to open two query windows in SSMS. In this demo, I used SQL Server 2016 and bit large sample database named, AdventureWorksDW2016CTP3. To run the demo yourself, you can follow the below steps.
Open two query windows in SSMS and copy and paste the below code in each query window. --Window 1
--part 1
USE AdventureworksDW2016CTP3;
GO
DROP TABLE IF EXISTS backuptest
GO
CREATE TABLE backuptest
(
    Id int identity(1,1) primary key,
    Test_Desc varchar(1000)
)
GO--part 2
DECLARE @msg3 binary(128);
SET @msg3=CAST('Kick off the full backup of AdventureworksDW2016CTP3' AS binary(128));WHILE 1=1

SQLSaturday582 - Melbourne

It was an honor to speak at SQLSaturday at Melbourne on 11th Feb, 2017. My session was about SQL Server Statistics and New Cardinality Estimator model. However, I could not go through some of the demos due to time restriction. I received much positive feedback so I'm finally happy about the session overall. 
You can find the presentation artifacts here.
Thanks, everyone who attended my session.

Dedicated Administrator Connection (DAC)

The dedicated administrator connection (DAC) allows an administrator to connect to an SQL Server instance to run diagnostic functions or T-SQL statements even if the server is not responding to normal connections. This feature was introduced in SQL Server 2012 and by default this option is disabled. 

You can enable DAC using below two methods;
​​Using SSMSOpen ​​SSMS​.​
In object explorer, connect to the SQL Server instance that you want to enable ​​DAC​.​
Right click on Server instance and select ​​Facets​.​
In ​​View Facets window select ​​Server Configuration using the drop down box. See below figure​:​

Using T-SQLsp_configure 'show advanced options',1;GORECONFIGURE;GOsp_configure 'remote admin connections', 1; GO RECONFIGURE; GO​

How to connect via DAC​​Once you enabled the DAC for an SQL Server instance, it is pretty easy to connect to the Server via DAC. You just need to mention ADMIN: in front of the server name that your connecting. See below figure:


Note that you can …

Persistent Log Buffer with SQL Server 2016 SP1

You can get significant performance gain by just moving the existing applications to SQL Server 2016. There are several case studies to prove this story. You can even get more performance gain (2X - 4X) with combination of SQL Server 2016 SP1 and Windows Server 2016 (not yet released). This performance gain achieved through Persistent (non-volatile) Log Buffer technique. This performance enhancement has been released to SQL Server with SP1. SQL Server utilizes Windows Server 2016 direct memory access (DAX). With this new feature SQL Server can write transaction log data in memory access speed over disk IO speed earlier. With the new type of memory which is persistent, the moments SQL Server writes to log cache (memory area for t-log), the data is persistent. 
For more details:Transaction Commit latency acceleration using Storage Class Memory