XEvent for PageSplit Tracking

Tracking PageSplits using Extended Events

In my experience working with significantly large sized databases, PageSplits can be the real culprits for performance problems if Fill Factor is not correctly determined. Having an index on a table (Clustered or Non-Clustered) is pretty useful in pulling the data out from tables but can cause serious problems (beyond the no. of Inserts or updates to happen for every index) if the Fill Factor is not properly managed. So, what is the problem that a wrong fill factor can cause and what way it can impact the performance? Well, when we have an index on a table, consider the Clustered index for a moment, the data should be maintained in a sorted order which means that the logical scan should read the data in the sorted order defined in the index. Imagine a column having a clustered index in a table is having values 1, 2, 4, 5,6,7 (3 is missing). Assume that the page in which these values exists has no more free space to accommodate any new value. At this time, if the application inserts the value 3, as per the indexed (sorted) order, the value 3 should be inserted between 2 & 4. Due to the lack of free space in the page, SQL Server performs an operation called PageSplit which is nothing but allocate a new page to the table and move the some portion of the data to it and insert 3 next to 2. To manage all this, SQL Server has to do extra work for the insert which can increase the wait (both latch and lock) time there by causes blocking etc.

 

Various Perfmon counters are available to track these PageSplit operations but doesn't give a detailed info. Using the below Extended events session script, you can extract more bit information about the PageSplit operations.

 

CREATE EVENT SESSION [VPageSplit] ON SERVER

ADD EVENT sqlserver.page_split(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text)

WHERE ([sqlserver].[database_id]>(4)))

ADD TARGET package0.event_file(SET filename=N'C:\1\PageSplit.xel',max_file_size=(5120),max_rollover_files=(3))

WITH (STARTUP_STATE=OFF)

System_Health XE Logging

Changing the default output log location for the System_Health eXtended Event in SQL Server 2012

Starting from SQL Server 2012, a new feature is introduced to track and record the SQL Server Health condition which is nothing but Server_Health extended event. Extended Events is a new feature introduced with SQL Server 2008 which provides the operational information of SQL Server and light weight tool to track the events that are happening in the SQL Server. 

SQL Server 2012 Management studio provide a nice way of creating or operating the extended events through the GUI as shown below.

Extended events in SQL Server 2012 Management Studio

As you can see there, 3 XEvents are created and AlwaysON_health and System_health are the default. The system_health XEvent keeps track of the system health in the output files in the location of the SQL Server errorlog by default. Some times, we may need to change the location of the output that this Xevent generates to a different location. This may be the case if you want to maintain the history for a longer period. As this is the default XEvent gets created by SQL Server, we have no initial setting to change it. 

So, here is the script to change the log file path to any desired one. 

ALTER EVENT SESSION [system_health] ON SERVER 
DROP TARGET package0.event_file
ALTER EVENT SESSION [system_health] ON SERVER 
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\system_health.xel')
GO 

Note: change the path in the filename to any desired one you need.

This script first deletes the existing value of the log location and creates a new target with the new location. 

Undo with Database Snapshots

How to undo the data changes using Database Snapshots

In this blog post, I would like to talk about how can we revert or undo the data changes which might have happened by a user mistake. For example, if some one has deleted a useful data from the database (for which there is already a snapshot exists prior to the data deletion), we can get the data back using the database snapshots. In this post, I am not going to talk about how the database snapshots works and it's internals.

To demo this, let me create a database and create a table and populate with some data. To do this, you can use the script below.

CREATE DATABASE VSNPSHT
GO
USE VSNPSHT
GO
CREATE TABLE VSample (id int, name char(10))
GO

So, we have the table created now. Let me populate it with some data. To do this, just execute the below T-SQL Statement.

INSERT INTO VSample VALUES (1, 'V')
GO 100

Let me now create a Database Snapshot for this database.

CREATE DATABASE VSNPSHT_SNAPSHOT ON
( NAME = VSNPSHT, FILENAME = 'C:\MSSQL\DATA\VSNPSHT_DB_SNAPSHOT.ss')
AS SNAPSHOT OF VSNPSHT
GO

Here, the NAME refers to the logical file name of the data file, and the filename is used to indicate the location for the snapshot file to be created. We can view the snapshot of this database under the path Databases -> Database Snapshots in SQL Server Management studio as shown below.

To access the data from the snapshot, you need to use the database name in the query before the table or the schema name as shown below.

select * from [VSNPSHT_SNAPSHOT].[dbo].[VSample]

Alternatively, you can select the database context to the Snapshot in the QueryAnalyser to select the data from the snapshot.

Now, let me delete the data from the table in the database.

Delete from [VSNPSHT].[dbo].[VSample]

Now, to get the data back to the database using the snapshot, execute the T-SQL Statement below. This statement looks similar to a restore statement.

USE master;
RESTORE DATABASE [VSNPSHT] FROM DATABASE_SNAPSHOT = 'VSNPSHT_SNAPSHOT';
GO

We all are set. Now we should get the data back and you can verify it through a select statement on the table. But, keep in mind that this option would bring the database back to the state when the snapshot was created. So, if you are looking for any specific table's data to be recovered, this option may not work as described here. You need to do some more operations for that like export the data from the snapshot table to a temp table and then import the desired data to the live table.

Hope this helps you.. Thanks for reading.

List Table Names, no.of Columns & Rows

Query to retrieve all the table names with no.of columns and no.of rows

 

Note: This applies from SQL Server 2005 onwards.

Recently, I have started working on a performance issue where customer says the data retrieval operation from the table takes longer time. The table structure is really a shocking one for us as most of the tables are having more than 300 columns which are being used for OLTP system. At this time, we wanted to retrieve all the table names with total no.of columns and no.of rows for each table in a single report. This made me to prepare a small query with basic joins between sysobjects, sys.indexes and syscolumns system views.

Here is the query which has given me the desired output.

select a.name as ObjectName,b.Rows,count(c.object_id) AS [No.Of.Columns] from sysobjects a JOIN sysindexes b ON a.id = b.id
JOIN sys.columns c ON a.id = c.object_id
where a.type = 'U' and b.indid <=1 group by c.object_id,b.rows,a.name order by 3 DESC

Here is a sample report for the above query which I ran against the AdventureWorksDW2012 database.

Sample output of the query

 

What's new in new Task Manager ?

Task Manager changes in Windows Server 2012/Windows 8

 

Windows Server 2012/Windows 8 is providing most important information in its task manager. I would like to show a demo of some of the useful tips of it for the DBAs.

 

Here is a snap how the task manager looks like in Windows Server 2012. This even applies for Windows 8 as well.

 

Fig 1: Task Manager in Windows 8 Professional X64

 

 

From the image above, we can get to know about most interesting things about CPU.

Looking at the right top corner, we could see the processor type mentioned. I have got Intel Core i5 processor for my laptop which I can see from there.

Looking at the right most bottom corner of the image, we could see pretty useful information.

Maximum speed: is the speed of the CPU installed on the machine.

As you can see in the figure 2 & 3 below, a socket is a holder which holds the processor in the mother board. Sockets in the task manager indicates how many CPU sockets are exists on the machine.



                                            Fig 2: Socket         Fig 3: Processor installed in the socket.

Cores: value in Fig 1 represents the information about how many cores exists per processor in the socket of the machine. For example, as you can see below for a dual core processor 2 cores exists for the processor.

Fig 4: Dual core processor (Core 0,1)

Virtualization: represents the configuration option of Virtualization enabled for processor.

Logical Processors: This represents the Logical CPUs count in the machine. For Intel processors if Hyper Threading (called as HT) is enabled, the logical processors count would be doubled. Means, we will get additional performance. But care should be taken while we deal with this as this may reduce the performance of SQL Server in some specific scenarios.

L1,2,3 Cache: are the cache sizes of L1, L2, L3 caches which are meant for the CPU to use to maintain the data needed for the execution.

I would recommend you to read the "SQL Server Hardware" book authored by the famous and my favourite SQL Server MVP "Glenn Berry" for more information about the Hardware choices for SQL Server. My special thanks to "Glenn Berry" for correcting me on a mistake in the initial version of this article.
 

 

RESTART the Restore

How to begin the restore process from where it got failed ?

Note: This applies to SQL Server 2005 or later.

In every SQL Server DBA's day to day experience, backup and restore is a common thing and we get used to it as we do lot many restores. I'm sure that at some point of time we would have got into the situation where the restoration process failed in between or just before the restore gets completed and this happens because of many reasons. One of the common issue I found most of the times is with because of the communication drop between the SQL Server and the backup file.

For example, if we do a restore from a file which is located in a network path and because of what ever reasons, SQL Server lost the communication with the backup file while the restore is going on. After waited for some time, SQL Server will just terminate the restore session and we will see a error message pertaining to the error like no communication etc. If the restore is with a small sized database, we could bear to start the restart the restore. But, Imagine if we are dealing with large database restoration, it would be a for sure problem.

I had same situation in my experience that we needed to perform a restore of 600+ GB database every month between two SQL Server instances located at different geographical locations and the restore was taking more than 24hrs of time all the time as the backup file is located remotely and we were using remote share path in the restore command. If the restore fails, we need to restart the restore which has increased the outage for the application. If we have a way using which we can resume the restore from the point where it got failed, we will be very happy to touch the restore of that database again and this will be helpful to minimise the downtime for the applications.

Using the option "RESTART", the resume of the restore process if possible. Using this option is very simple like any other options including MOVE, REPLACE etc.

Let me show a demo of using it. I have the backup file in a USB drive which is connected to my system as D: drive. I initially used the script below to restore the database to my instance.

USE [master]
RESTORE DATABASE [RESTART] FROM DISK = N'D:\SQL Server\ADWDB2012.bak' WITH 
MOVE N'AdventureWorksDW2012_Data' TO N'C:\1\AdventureWorksDW2012_Data.mdf', 
MOVE N'AdventureWorksDW2012_Log' TO N'C:\1\AdventureWorksDW2012_log.ldf', STATS = 5

After the restore is started and shown as 10%, I have un-plugged the USB drive to make the restore process fail and I could see the below error after I did that.

5 percent processed.
10 percent processed.
Msg 3203, Level 16, State 1, Line 2
Read on "D:\SQL Server\ADWDB2012.bak" failed: 1110(The media in the drive may have changed.)
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally. 


So, now we have the restore process failed. Let's try using the option of RESTART and see how it goes. I have plugged-in my USB drive which has the backup file and executed the same query above adding RESTART option as you can see below.


USE [master]
RESTORE DATABASE [RESTART] FROM DISK = N'D:\SQL Server\ADWDB2012.bak' WITH 
MOVE N'AdventureWorksDW2012_Data' TO N'C:\1\AdventureWorksDW2012_Data.mdf', 
MOVE N'AdventureWorksDW2012_Log' TO N'C:\1\AdventureWorksDW2012_log.ldf', 
STATS = 5, RESTART

Now, I could see the database is restored from there and became accessible.

I would like to alert you about the checkpoint file a restore process creates and the impact if we delete that file with using the RESTART option. When you do a restore operation, SQL Server creates a checkpoint file in the default backup location with .CKP extension as you can see it here.

Because of what ever reasons if this file is not available or accessible, the RESTART option will not help and we can see the below message.
 The restart-checkpoint file
'C:\Program Files\Microsoft SQL Server\MSSQL11.V2014\MSSQL\Backup\RestoreCheckpointDB6.CKP' was not found.
The RESTORE command will continue from the beginning as if RESTART had not been specified.

Hope this will help you when you have a restore process fails and need to restart.

In-Memory OLTP (Hekaton)

In-Memory technology in SQL Server 2014

(Code name Hekaton)

 

Like the other players in the database space, Microsoft has started evolving in the In-Memory database technology a way back in 2009. After 4years they started working for it, we have now got the initial release of it released with SQL Server 2014. Microsoft has announced the release of SQL Server 2014 in the TechED2013 and you can download the CTP1 version of it today. I have downloaded my copy and started learning this as this concept can address many concerns that the previous releases can't address. I would like to take an opportunity to share my experience and knowledge about it.

 

What is In-Memory technology ?

In simple words, I can say that this technology felicitates the possibility to hold the data of a database completely with in the memory (RAM). Having the whole data available in the RAM, we can overcome the latency issues of the IO Disk subsystem.

 

How Hekaton is different from normal SQL Server database engine ?

The moment I started learning the In-Memory, I was scratching my head to understand how this is really benefit if we have more amount of RAM available in the server compared to the database size. Well, even if we have more amount of RAM available for SQL Server compared to the database size, we still need to operate with the 8KB sized data page(s) even in bufferpool after it is completely read in to the buffer. Imagine if we need to search for a row from a table which satisfies my search condition, SQL Server needs to retrieve couple of data pages from Disk to bufferpool and get the row displayed. In doing so, SQL Server would uses locks and latches to maintain the logical and physical consistency of the data. So, even if we have more amount of RAM available, we have no option to mitigate the lock and latch contention. Also, when a page is locked or latched just to operate with some portion of it, the other session gets blocked which needs to operate the different portion of the data in the same page. The Hekaton addresses these concerns mainly. With Hekaton, the database engine becomes lock and latch free.

 

Is this In-Memory a new separate release of SQL Server ?

Microsoft has introduced this technology as a feature in SQL Server 2014 similar to any other features like AlwaysON AGs etc. Which means that, we can choose tables which are to be memory optimized.

 

What is memory optimized ?

Now with SQL Server 2014, we have two types of tables available which are nothing but disk based and memory optimized. The memory optimized tables are used by Hekaton and not maintained in 8KB allocation units. The disk based tables are the conventional tables which are stored in 8KB allocation units on the disk. I will talk more about this in a separate blog.

 

Can I have both disk based and memory optimized tables for a single database?

Yes, we have the option to migrate selective tables to Hekaton of a database.

 

Which editions of SQL Server provides this feature?

This would be made available in Enterprise, Developer and Evaluation editions of SQL Server 2014.

Any other features available in Hekaton ?

Yes, the Stored Procedures can be now native which means the SPs can now be compiled and executed as C programs which requires less CPU cycles compared to the normal SP execution process by SQL Server. Under the hoods, the SP gets converted as a C program and execute the login we specify in the SP.

 

What is the benefit of Native SPs ?

The CPU execution speed is not growing aggressively like before. So, we may have CPU bottleneck when we deal with complex SP execution. Using the native SPs, the logic can be executed with less no. of CPU cycles with which we can overcome the issue of CPU bottleneck.

 

Will I see a guaranteed benefit by moving tables to Memory optimized or using Hekaton?

Well it depends. If there is no latch or lock contention exists for the tables with the regular workload, there may not be a benefit of migrating them to memory optimized.

 

What is the impact of Paging with Hekaton ?

No Paging.
 

What happens if there is no free memory available for new inserts ?

Well, SQL Server just rejects the new transactions. It is like a transactionlog file when there is no free space available in the Transactionlog, new modifications are not allowed.

 

Any maximum size limit exists for a table which is memory optimized ?

Yes as of CTP1, SQL Server 2014 supports a max of 512GB.

 

Is there any change for the max length of the row allowed with Hekaton ?

Like the normal database engine, Hekaton supports up to 8060 Bytes per ROW.

 

Does Hekaton uses the BufferPool ?

No, Hekaton uses non bufferpool area of memory portion.