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.

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
INSERT INTO backuptest (Test_Desc)
SELECT command + ' ' + CAST(percent_complete AS varchar) + ' ' + CONVERT(varchar, GETDATE() ,121) FROM sys.dm_exec_requests
WHERE context_info=@msg3;
GO

--part 3
USE AdventureworksDW2016CTP3
GO
SELECT * FROM backuptest

--part 4
USE master;

 

Copy and paste the below code snippet in the 2nd Window.

 

--Window 2

--part 1
USE master;
GO
DECLARE @msg3 binary(128);
SET @msg3=CAST('Kick off the full backup of AdventureworksDW2016CTP3' AS binary(128));
SET CONTEXT_INFO @msg3;

PRINT 'Backup Database Started at : ' + CONVERT(varchar, GETDATE() ,121);
BACKUP DATABASE AdventureworksDW2016CTP3 TO DISK='C:\temp\AdventureWorksDW2016CTP3_full.bak'
WITH INIT;
PRINT 'Backup Database finished at : ' + CONVERT(varchar, GETDATE() ,121);
GO

--part 2
RESTORE DATABASE AdventureworksDW2016CTP3 FROM DISK='C:\temp\AdventureWorksDW2016CTP3_full.bak'

 

Execute the Part 1 and part 2 queries in Window 1.
Immediately move to the window 2 and execute the part 1. Watch the execution of the second Window, which is BACKUP DATABASE statement and once it's  completed, you need to stop the execution of Query Window 1.

You need to copy and paste the result of the backup operation which is similar to the following.

Backup Database Started at : 2017-02-27 22:21:58.270
Processed 186680 pages for database 'AdventureworksDW2016CTP3', file 'AdventureWorksDW2014_Data' on file 1.
Processed 39 pages for database 'AdventureworksDW2016CTP3', file 'AdventureWorksDW2014_Log' on file 1.
BACKUP DATABASE successfully processed 186719 pages in 14.817 seconds (98.450 MB/sec).
Backup Database finished at : 2017-02-27 22:22:13.200

It has the information of what time the backup started and what time it finished.

Now, you can check the data in backuptest table by executing the part 3 of the query in Window 1. See below figure for sample data set;

 

 

You can notice there are many rows inserted along with the percent completed of the backup statement which executed in parallel to the BACKUP DATABASE statement in Query Window 2. For later reference, copy and paste the result set to an Excel sheet. After carefully observing the data set, you need to change the database to master in Query Window 1 (part 4)

 

At this point, you've a full backup file in the local directory and we know that while the backup operation in progress we inserted the data to a table in Query Window 1.

 

Now switch back to the Query Window 2 and restore the database using the part 2. Upon completion of the restore, observe the data in backuptest table using part 3 of the Query in Window 1.

 

You can notice, most of the data changes happened during the backup process is still there. See the below figure;

 

The last entry recorded in the table is “BACKUP DATABASE 99.9967 2017-02-27 22:22:13.060”. At that point, database backup statement is completed 99% and the time is 22:22:13:060. We also have the backup statement completed time which is, “2017-02-27 22:22:13.200”. So actually it has missed 140 milliseconds worth of data.

 

You can do this demo yourself and get more understanding.

Cheers!

TempDB Configuration

Recently we had some discussion around the best practices for tempdb configuration. The entire discussion was based on SQL Server 2012 EE. This blogpost highlights some salient points of tempdb configuration and include links to additional resources as well. 

• How many files (data / log) we need to create? Is it based on logical cores or physical CPUs?

Any SQL Server database requires two files (1 data file and 1 log file) at minimum. SQL Server will allow you to add multiple log files but there is no benefit by doing so as SQL Server writes to log file in serial fashion. As a result, we can safely state, you do not need multiple log files for tempdb. 

What about the data files? SQL Server allows to add multiple data files too and there are benefits of doing this. Since our discussion is around tempdb, we can add multiple data files to address the very common phenomena known as tempdb contention. The whole objective of adding multiple data files is to improve the concurrency of tempdb activities in other words to improve the throughput of tempdb. Beginning from SQL Server 2005, tempdb tends to become the bottleneck as SQL Server continues to add new features which utilizes the tempdb heavily. As a result contention around tempdb becomes increased. However the history of tempdb contention goes back to SQL Server 7.0 because TF-1118 is also applies to SQL Server 7.0. The TF-1118 uses to reduce the tempdb contention of allocation pages (PFS, GAM and SGAM).

Concurrency enhancements for the tempdb database   

When deciding the no.of data files for tempdb, a general rule of thumb is to have no.of data files equal to the no.of logical cores up to 8 logical cores, meaning if the machine has 16 logical cores, you can add up to 8 data files. I've seen servers where you've 16 cores and tempdb has 16 data files too. This configuration is not appropriate according to the general rule of thumb. 

Modern CPUs have large number of cores. For example, Intel Xeon E7 processor family has a processor which has 24 cores. If you consider 8 CPU sockets server then you will get 192 (24*8) logical cores and when hyperthreading is enabled it will become 384 (192*2) logical cores. So in such a server, are we going to configure 384 data files for tempdb? That is an extremely bad idea because when there are such no.of data files the SQL Server has an additional overhead of threads management. 

Back to no.of files discussion, you can start with 8 data files (if no.of cores are 8) and then increase the no.of data files by multiple of 4 (up to the no.of logical processors of the machine). You need to do that, only if you still see contention around tempdb. 

Recommendations to reduce allocation contention in SQL Server tempdb database

• How to decide the initial size of the tempdb?

There is no universal formula to do this and therefore it is non-trivial. You need to identify the features you're planning to use which potentially utilize the tempdb space. E.g: consider temp table and table variable usage, sorting, ordering, version store, etc. Then use your best judgement to decide the initial size and then monitor the tempdb usage by executing a workload in a test environment and decide whether you need to resize it or not. 

• Do we need to enable autogrowth or not? If we do, then, is it for all the files or just one file?

Autogrowth setting should be used for emergency situations (for both data and log files) and should not be the process to grow the data file or log file. You always need to manually size the data files and log file and set the autogrowth so that in an emergency situation if tempdb needs additional space the SQL Server will grow the files without impacting the current server activities. If you've multiple data files situation then enable the autogrowth for all the files. 

• The size of autogrowth?

Microsoft RAP tool has a rule which detects non standard database autogrowth sizes. The rule is as below;

"Database files have been identified that have the next Auto Growth increment of 1GB or above, and instant file Initialization is not enabled"

When the tool detects this, it classified as severity high risk. Basically it does not recommend to have autogrowth sizes 1GB or above. So that I would keep autogrowth size as 1023 MB (yes, it is not 1024 GB). This is especially important for the log file because depending on the autogrowth chunk size, the no.of VLFs vary. No.of VLF logic is as below (this applies to SQL Server 2016 as well);

chunks less than 64MB and up to 64MB  = 4 VLFs

chunks larger than 64MB and up to 1GB = 8 VLFs

chunks larger than 1GB = 16 VLFs

Note: The size of the t-log does not affect the no.of VLFs but the chunk size. 

• Do we need to keep all data files and log file in the same drive?

It is better to keep them in separate drives if you've the luxury to have multiple drives (to increase the IO bandwidth). The limitation comes in clustered environment where you can have limited drive letters. (Please note that you can also place the tempdb in SSD).

If you've 4 nodes cluster with 3 SQL Server instances. We have already utilized 20 drive letters (including the C drive for OS). These drive letters are for system data like master, msdb, etc, data files, log files, local MSDTC, tempdb, backup and Quorum. 

• Changes in SQL Server 2016

The TF (trace flag) 1117 is used to grow all the data files when a file in the filegroup reaches the autogrowth threshold. Beginning from SQL Server 2016 this behavior is controlled by the two options added to ALTER DATABASE statement. The two options are, 1. AUTOGROW_SINGLE_FILE, 2. AUTOGROW_ALL_FILES. However this is applies to user databases not the tempdb. 

Starting from SQL Server 2016, the behaviour of TF-1117 or TF-1118 will be automatically enabled by default for tempdb. So it makes DBAs life much more easier. 

ALTER DATABASE

Trace Flags

Just to make it clear, see the below table; 

source: SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases

Summary

There are lot to talk and learn about tempdb. Tempdb is the only database per instance which keeps all the temporary work of that instance. So it may become a bottleneck and the entire system will become slow. So it is always need to pay a close attention to tempdb behaviour and its activities then take remedial actions to minimize them. Best practices are there for you get a good start but what is important is to identify the optimal configuration and parameters for your system rather than following best practices blindly. 

Cheers!

Microsoft releases CU #1 for SQL Server 2016

Microsoft released their latest flagship database product, Microsoft SQL Server 2016 on June 1st 2016. After 2 months, now Microsoft announced the release of CU #1 which contains fixes for 146 issues across different categories of the product.

The CU1 has 146 hotfixes in 10 different categories as stated above. The highest no.of hotfixes are for the SQL Service.

You can download the CU1 by using the below link.

Cumulative Update 1 for SQL Server 2016

Cheers.

Denver SSUG Presentation About Latch Behavior

Yesterday I did a presentation about SQL Server Latch behavior at SQL Server User Group at Denver.

Hope everyone has learnt something and got some exposure to SQL Server latches which is internal to SQL Server and some time create issues. So that knowing about what they are and how they behaves is important in my opinion.

Below link contains the ppt and the demo scripts I used. The demo scripts are compatible with SQL Server 2014 or later.

Presentation and Demo scripts

Cheers!

How do I get access to SQL Server instance when no other access is possible

Recently I had a situation where no one knows any level of credentials to SQL a Server instance. The instance I tried was a SQL Server 2008. By default SQL Server 2008 does not provide admin access to built in Windows local admin group.

I was able to get access to it by following below steps;

1. Stop SQL Server service using SQL Server configuration Manager.
2. Right click on SQL Server service and get properties.
3. In Log On tab, use This account option to provide a windows domain account credentials which you already know. I my case I provided my credentials for the specific domain.
4. Use Advanced tab to add “-m;” startup parameter to the beginning of the parameter list. The –m startup parameter is use to start SQL Server service in single user mode.
5. Click on Apply and then Ok.
6. Start SQL Server service.
7. Open CMD shell in administrator mode and type the following sqlcmd statement; This will test the access to the server and if it successful, it returns the SQL Server name. This is just a verification.
1. sqlcmd -E -S <server name> -q "select @@servername"
8. Use the below sqlcmd statement to create a new user called “recovery”.
1. CREATE LOGIN recovery WITH PASSWORD = '1qaz2wsx@'
9. Use the below sqlcmd statement to grant admin privilege to the user we just created.
1. SP_ADDSRVROLEMEMBER 'recovery',SYSADMIN
10. Stop the SQL Server service using Configuration Manager.
11. Get SQL Server service properties and remove the startup parameter “-m” and click Ok.
12. Start the SQL Server service.
13. Open SSMS and try to connect to the server using the user name and password we created in above steps.

Hope this helps. Cheers!

Reference:

http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx

ALTER PARTITION FUNCTION Causes Blocking?

Recently I’ve experienced a situation where one of the partition maintenance jobs created a blocking. At this point the database had high t-log usage and backup log was also executing. It was clear some kind of large transaction has occurred during a partition maintenance operation. All inserts into the table which was being partitioning, got blocked too. 

MSDN states:

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

ALTER PARTITION FUNCTION (Transact-SQL)

So it is important to select a proper partition key so that when achieving using sliding window concept, it works without impacting users.

Cheers.

How To Find Object Name For “wait_resource”

When querying sys.dm_exec_requests dmv, you can see the waiting requests and the resource those requests are waiting for. Sample of such values are shown in Figure 1 below.

Figure 1 – List of wait resources

How do you interpret these values? It has the following format in this case: (It is not always the same format. Depending on the values you see in wait_resource the interpretation would be different)

[database id] : [file id] : [page id]

However still the information is not sufficient because knowing the page id does not give much details related to the issue your troubleshooting. So we need to figure it out the table related to the above page. For that you need to use some undocumented DBCC commands. (Please be careful when running them in production system)

DBCC TRACEON (3604)
GO
DBCC PAGE (5, 20, 56792898)
GO
DBCC TRACEOFF (3604)

You get an output similar to below;

Figure 2 – Output of DBCC PAGE command

Refer the objectId value. That is the object Id of the page belongs to. After that it is simple to find the object name using OBEJCT_NAME() T-SQL function.

Cheers.

Identifying the root blocker and the blocking chain

As a DBA, identifying blockings and waitings is one of the primary tasks. I searched in the Google but did not find a useful script. So I came up with one and thought to share it with others.

;WITH requests (session_id, start_time, status, blocking_session_id, 
      database_name, 
      command, sql_text) 
     AS (SELECT session_id, 
                start_time, 
                status, 
                blocking_session_id, 
                Db_name(database_id), 
                command, 
                sql_text = Cast(text AS VARCHAR(max)) 
         FROM   sys.dm_exec_requests WITH (nolock) 
                CROSS apply sys.Dm_exec_sql_text (sql_handle) 
         WHERE  status <> 'Background'), 
     blocking (session_id, start_time, status, blocking_session_id, command, 
     sql_text, rownum, levelrow) 
     AS (SELECT r1.session_id, 
                r1.start_time, 
                r1.status, 
                r1.blocking_session_id, 
                r1.command, 
                r1.sql_text, 
                Row_number() 
                  OVER ( 
                    ORDER BY r1.session_id), 
                0 AS LevelRow 
         FROM   requests r1 
                INNER JOIN requests r2 
                        ON r1.session_id = r2.blocking_session_id 
         WHERE  r1.blocking_session_id = 0 
         UNION ALL 
         SELECT r3.session_id, 
                r3.start_time, 
                r3.status, 
                r3.blocking_session_id, 
                r3.command, 
                r3.sql_text, 
                b.rownum, 
                b.levelrow + 1 
         FROM   requests r3 
                INNER JOIN blocking b 
                        ON r3.blocking_session_id = b.session_id 
         WHERE  r3.blocking_session_id > 0) 
SELECT * 
FROM   blocking 
ORDER  BY rownum, 
          levelrow 

Alternatively you can write the output of this to a table as a schedule job and the results can be analyzed later. This is really useful to identify any blocking patterns of the database server and fix them if they persist.

Stored procedure taking long time to execute

This can happen due to many factors like lack of indexes, out of dates statistics, if it returns a large number of records, etc.

I observed a similar behavior recently in a production environment. The stored procedure had the code similar to the following;

    SELECT Col3   
    FROM dbo.Tab1  
    WHERE  Col1 = @Col1 
    AND Col2 = @Col2  
    AND RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(CAST(Comments AS VARCHAR(2000)),'<br />',''),CHAR(10),''),CHAR(13),''),'&nbsp;',''))) <> ''  

Let me give you some background information about this table.

• Col2 and Col3 are UNIQUEIDENTIFIER
  
• Comments column is ntext and does not have indexes
  
• All three columns Col1, Col2 and Col3 has indexes
  
• Col2 and the PK is the clustered index of the table
  
• Table has 825,452,256 records (825 million)

Identifying slow running queries

This post is for SQL Server Database Administrators who have find difficulty in some situations when identifying slow running queries. The DBAs who work in production environment specially might have faced situations where users or other production monitoring teams complaining that suddenly they see a slowness in their systems. The “slowness in the system” a very generic term. This could be due to various reasons. Such as;

• Network issue
• Application server issue
• Database issue

As a DBA, it is a responsibility to make sure everything is clear in database perspective. Before start troubleshooting the broader phenomena, it is necessary to ask few questions to shed some light into the dark area. Below questions might be useful;

SET XACT_ABORT Option and Transaction Behavior

One of my colleagues asked what if a transaction has only BEGIN TRAN and COMMIT TRAN without a ROLLBACK TRAN. I did not have a proper answer on top of my head however I could remember the behavior is depending on the SET OPTION known as XACT_ABORT.
I initially tried the below query to test the above mentioned situation. Note that I did not change the default setting of XACT_ABORT option. The default is set to OFF.

How to get database sizes

There are several ways to get database sizes in a server. Following three system tables has the information to get database sizes.

sys.sysfiles

sys.database_files

sys.dm_db_file_space_usage

You also can use following system stored procedure.

exec sp_spaceused

However the easiest way is to use SP_HELPDB system stored procedure. Below script used that SP to get the database sizes in a server.

create table #spdbdesc

(
    dbname sysname,
    dbsize        nvarchar(13) null,
    owner sysname null,
    dbid    smallint primary key,
    created nvarchar(11),
    dbdesc    nvarchar(600)    null,
    cmptlevel    tinyint
)


INSERT INTO #spdbdesc
exec SP_HELPDB

SELECT *
FROM #spdbdesc

Currently executing queries

It is important to know what is currently executing in SQL Server at any given time. You may need this for troubleshooting purposes. The below query is useful in such occasions;

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
s.login_name,
s.host_name,
req.wait_type,
req.wait_resource,
SUBSTRING(sqltext.text, (req.statement_start_offset/2)+1,
        ((CASE req.statement_end_offset
          WHEN -1 THEN DATALENGTH(sqltext.text)
         ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) AS statement_text,
req.plan_handle,
sqltext.text
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions s
    ON req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY req.cpu_time DESC

Reasons that can not reclaim transaction log space

The best practice is to setup log backups periodically (if the database is not in SIMPLE recovery) in order to keep the database log size minimum. However there are some occasions even the log backup is setup, the log size keeps growing. To find out the reason you can refer, log_reuse_wait in sys.databases.

As per MSDN, log_reuse_wait is as follows; Also look at the log_reuse_wait_desc column for more detail.

Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = Other (transient)

Cheers for reading this blog post.

Reading a deadlock

Deadlock is a common phenomena in databases where users can work concurrently. Deadlock happens because of pessimistic concurrency control mechanism which is essentially use of locks. You can't prevent deadlocks but of course you need to minimize them occurring. There are several ways of capturing deadlocks in SQL Server. They are;

1. Using SQL Server Profiler

2. Using SQL Server Error Log

The objective of the blog post is to read and understand the deadlock graph which is captured in SQL Server Error log.