Wednesday, February 3, 2016

The transaction log for database 'dbname' is full due to 'XTP_CHECKPOINT'‏

When using In-Memory OLTP in SQL Server 2014, under certain conditions you might get the below error message:

The transaction log for database '[database_name]' is full due to 'XTP_CHECKPOINT'‏ 

This was fixed in Cumulative Update 3 for SQL Server 2014 SP1

More information about the fix can be found here.

Read more on this article...

Thursday, December 31, 2015

Selected Tweets of Year 2015!

Another year has passed...as always, full of everything! Events, articles, direct communication with you, my fellow SQL Server community members, via different online and offline channels, software and book releases and many other activities!

Among my blog and journal articles, user group/conference events, tweets and software and book releases, I feel that two of my activities were the most highlighted in 2015. The first highlight was the release of my software tool 'In-Memory OLTP Simulator', and the second was the release of my third book titled 'Developing SQL Server' which is part of 'The SQL Server and .NET eBook Series'.

In-Memory OLTP Simulator allows you to easily test SQL Server's In-Memory OLTP Engine against your own workload, with the use of custom scenarios which you can easily create using the tool. I was working for two years on this and I am really proud about it!

My book 'Developing SQL Server' contains dozens of articles I wrote on different topics that have to do with SQL Server's development. This book completed my three eBook series 'The SQL Server and .NET Blog eBook Series' but hey, don't worry, there is much more to come! :)

All the above, provided me with a great interaction with you, my fellow SQL Server community members, for one more year. Thank you for making the SQL Server community an amazing place to be!

I'm really looking forward for 2016 having already scheduled a variety of amazing community activities!

Wishing to all of you a Happy New Year 2016!

As every year's end, below you can find my selected tweets for the year 2015.

In-Memory OLTP Simulator



Blog Posts/Articles/Books and Announcements



News

  • Another successful #CDNUG event took place yesterday! Next stop: The In-Memory OLTP Experiment Technical Whitepaper! #SQLServer #MVPBuzz
  • Preparing my session for CDNUG's event on Nov 24. Title: #SQLServer 2016 Top Features! #sqlfamily #community #mvpbuzz
  • A technical whitepaper for In-Memory OLTP Simulator is in the works. Make sure you check often: https://www.inmemoltpsim.com/  #SQLServer #MVPBuzz
  • Stay up to date with all my publications on #SQLServer - Register to the monthly newsletter! - http://goo.gl/forms/jlINJR9vCb #MVPBuzz #Community
  • If you are interested in #SQLServer In-Memory Optimization then you should try "In-Memory OLTP Simulator". https://www.inmemoltpsim.com/download/  #Hekaton
  • Get the Community Edition of In-Memory OLTP Simulator today at: https://www.inmemoltpsim.com/download  #Hekaton #SQLServer #InMemoryOLTP
  • Up to 20 Million records where processed via a custom scenario. The results are amazing! More news soon! #Hekaton #SQLServer
  • A tech whitepaper on In-Memory OLTP Simulator describing the 1st large-scale In-Memory OLTP Simulation conducted is in the works. #SQLServer
  • All scenarios are designed and executed using the Ultimate Edition of In-Memory OLTP Simulator: https://www.inmemoltpsim.com/features/  #SQLServer
  • Up to now: 1M records:2.25x speedup, 5M records:7.52x speedup, 10M records:10.17x speedup.Now, who can see the trend? :) #SQLServer #MVPBuzz
  • Started running the In-Memory OLTP Experiment! It is expected to be completed by tomorrow or Wednesday the latest! #MVPBuzz #SQLServer
  • Designing the 100M scenario in In-Memory OLTP Simulator (https://www.inmemoltpsim.com/) - More info tomorrow! #MVPBuzz
  • Generating 100M records to simulate a complex update operation using the In-Memory OLTP Engine in #SQLServer #MVPBuzz
  • I have just been re-awarded as a Microsoft SQL Server MVP for the 7th Year in a Row! http://aartemiou.blogspot.com/2015/04/microsoft-sql-server-mvp-for-7th-year.html #MVPBuzz #SQLServer
  • Simulating heavy aggregations against 10M+ records using In-Memory OLTP...This is amazing! More news real soon! #MVPBuzz #SQLServer
  • Recommended 2-day free online .NET conference - dotnetConf - http://www.dotnetconf.net/ 
  • The more I use @Snagit the more I get pleasantly surprised by its intelligent engine! Keep it up guys! #MVPBuzz


Miscellaneous

  • Have just rebuilt the #SQLServer system databases on an instance in order to change their collation! Cool process but be careful! �� #MVPBuzz
  • Migration completed! Back to "relaxing" mode :) #MVPBuzz
  • Relaxing...before starting the migration of 60 DBs from #SQLServer 2008R2 to SQL Server 2014! �� #MVPBuzz
  • There is always a bug around when you write and maintain tens of thousands of lines of code! If you are lucky you will find it :) #Community
  • Great article on @lifehackorg - How To Work Faster And Smarter - http://www.lifehack.org/articles/productivity/how-work-faster-and-smarter.html?mid=20150205&ref=mail&uid=363574&feq=daily - That's a must, especially in large scale projects!
  • ISACA CY / itSMF CY Event on Cloud Security & Service Management on Jan 29, 2015. For more info & agenda: http://www.itsmf.org.cy/  #Community


Read more on this article...

Saturday, December 26, 2015

SQL Server 2016: TempDB Enhancements

TempDB system database plays a major role when it comes to performance in SQL Server. TempDB is available to all users connected to a SQL Server instance and it is used to hold local or global temporary tables, temporary stored procedures, table variables and cursors.

Another example of when TempDB is used, is when you make use of the 'SORT_IN_TEMPDB' option when you create or rebuild an index. This option tells SQL Server's Database Engine to store the intermediate sort results that are used to build the index in the TempDB system database.

For all the above, and more, properly configuring the TempDB database plays a significant role to SQL Server's performance.

Until SQL Server 2014, you would usually install the SQL Server instance and then tune the TempDB database after installation.
SQL Server 2016 now provides you with the capability of configuring the TempDB database during the installation of a SQL Server instance along with some other other enhancements. These are:
  • All files will grow at the same time (no need for Trace flags 1117 and 1118)
  • By default Setup adds as many tempDB Files as the CPU count (max=8)
  • Through Setup you can specify:
    • No. of tempdb database files, initial size, autogrowth and directory placement (you can set multiple volumes for tempDB database files)
    • Initial size of log file, autogrowth and directory placement

The above assist the DBA to properly configure the TempDB system database faster and simpler during the setup of SQL Server, thus ensuring a performant tempDB configuration.

Below you can find a screenshot taken during the installation of SQL Server 2016 (CTP 3.0) that illustrates the TempDB configuration dialog:


























References:
MSDN Library Article: tempdb Database
MSDN Library Article: SORT_IN_TEMPDB Option For Indexes

See also...
SQL Server 2016 Top Features
Read more on this article...

Wednesday, December 16, 2015

SQL Server 2016: Temporal Tables

Temporal tables in SQL Server 2016 is a new type of user table. It was introduced in ISO/ANSI SQL 2011.

Temporal tables keep a full history of data changes (based on UTC time) by using a pair of current-historical tables. These tables has the same structure.

The period of validity for every row is managed by the system (i.e. Database Engine) by using two explicitly-defined columns of the DateTime2 datatype.

Below you can find an example of a user table's definition that has system versioning enabled:

--Create table along with specifying its historical table
CREATE TABLE dbo.Students2
(
  studentID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Students PRIMARY KEY,
  studentName VARCHAR(150) NOT NULL,
  studentAddress VARCHAR(150) NOT NULL,
  regDate DATE NOT NULL,
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
  SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Students2History));
GO

The above DDL will create two tables: (i) Students2, and (ii) Students2History.

In the "dbo.Students2" table, the user needs to populate only the first 4 columns (studentID, studentName, studentAddress and regDate). Note: In this case "studentID" will be populated automatically too because it is an identity column. The rest of the columns will be maintained automatically by SQL Server's Database Engine. Table "dbo.Students2History" will be populated fully automatically by the Database Engine.

In the below screenshot we can see both tables:

Figure 1: Pair of Current-Historical Table



































Let's check both tables for any contents:

Figure 2: Table Contents.
























Now, let's insert some data in the "dbo.Students2" table:

--Insert initial data
INSERT INTO dbo.Students2
        ( studentName ,
          studentAddress ,
          regDate 
        )
VALUES  ( 'John Adams', 
          'Address 1', 
          GETDATE() 
        );
GO

Let's check again both tables for any contents:

Figure 3: Table Contents After Insert.






















As you can see, the "dbo.Students2" table was populated with data.

The "dbo.Students2History" table was not populated with any data because system versioning works only in case of an update operation against one or more rows.

That being said, let's try to update the row:

--Now let's update a record
UPDATE dbo.Students2
SET studentAddress='Address 1 Update'
WHERE studentID=1;
GO

Let's check again the table contents:

Figure 4: Table Contents After Update Operation.





















Now you can see that the table "Students2History" contains the original values for row with studentID=1 just before the update operation took place.

Let's update the same record again:

--Update the same record
UPDATE dbo.Students2
SET studentAddress='Address 1 Update 2'
WHERE studentID=1;
GO

Let's check the table contents:

Figure 5: Table Contents After Second Update.























As you can see, the table "Students2History" contains also the "new" original value for row with studentID=1 just before the new update operation took place. So in total, the historical table contains the rows for studentID=1 just before the two update operations took place. The validity of each row can be determined from the "SysStartTime" and "SysEndTime" values.

This article was a simple example of using temporal tables in SQL Server 2016. I consider temporal tables one of the top new features in SQL Server 2016 as they have to offer significant functionality.

Some benefits of using temporal tables:
  • Auditing all data changes (i.e. for data forensics)
  • Data reconstruction/recovery
  • Trends monitoring over time


Read more on this article...

Monday, December 14, 2015

SQL Server 2016: Built-In JSON Support

JSON stands for JavaScript Object Notation and it is widely used for data representation in the data services world. With JSON support, you can format query results as JSON by adding the FOR JSON clause to a SELECT statement in SQL Server. Also, you can converts JSON data to rows and columns by calling the OPENJSON rowset provider function.

Below we are going to see some examples of utilizing SQL Server's JSON support.

For the purposes of these demos, I have created a sample tabled called "Students" and I have populated it with random data. Let's take a look at the first 10 rows of this table:


























Figure 1: Sample Table "Students".

In order to extract data from a table and represent it in the JSON format you must use wither the "FOR JSON AUTO" clause or the "FOR JSON PATH" clause.

The "FOR JSON AUTO" clause allows you to have full control over the format of the JSON output. For example, you can specify the root.

Example:


Figure 2: Using the "FOR JSON AUTO" Clause.


The "FOR JSON PATH" clause automatically formats the JSON output based on the structure of the source table.

Example:














Figure 3: Using the "FOR JSON PATH" Clause.


Now, if you want to transform JSON data into a tabular form, you can use the OPENJSON rowset function.

Example:





























Figure 4: Using the "OPENJSON" Rowset Function.



References: 
MSDN Library Article: What's New in SQL Server 2016, November Update
MSDN Library Article: Format Query Results as JSON with FOR JSON (SQL Server)
MSDN Library Article: Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)

See also...
SQL Server 2016 Top Features

Read more on this article...

Thursday, December 3, 2015

SQL Server 2016 Top Features

SQL Server 2016, Azure SQL DB and related cloud technologies, are Microsoft's implemented vision for a complete data platform that offers everything that has to do with data management, transformation, knowledge extraction from structured, semi-structured and unstructured data and beyond.

At a recent community event, I talked about SQL Server 2016 and its top features and i can assure you, SQL Server 2016 comes with a rich set of exciting new features as well as many enhancements to features shipped with earlier SQL Server releases such as In-Memory OLTP! The features I consider the most significant in the upcoming release of SQL server are:

Stretch Database
Stretch Database stores historical data in the Microsoft Azure cloud. It runs in the background and provides a seamless access to both local and remote data. Among other, it can be used for archiving processes.

Built-In JSON Support
JSON stands for JavaScript Object Notation and it is widely used for data representation in the data services world. With JSON support, you can format query results as JSON by adding the FOR JSON clause to a SELECT statement in SQL Server. Also, you can converts JSON data to rows and columns by calling the OPENJSON rowset provider function.

In-Memory OLTP Enhancements
The In-Memory OLTP Engine uses non-blocking multi-version optimistic concurrency control that is having multiple version of rows of data loaded in memory thus eliminating both locks and latches. It features Memory-optimized tables (Durable & Non-Durable) and Natively-compiled stored procedures and when they are utilized in a plethora of scenarios, they can offer significant performance boost. In-Memory OLTP was first introduced in SQL Server 2014. In SQL Server 2016 a large number of improvements is provided having as a highlight the following: support for all collations, support for DML triggers in memory-optimized tables and support for more T-SQL constructs in natively-compiled SPs.

Query Store
With Query Store you can get insights on query plan choice and performance. You can also quickly find performance differences caused by changes in query plans.

Temporal Tables
Temporal Tables is a new type of user table. It was introduced in ISO/ANSI SQL 2011. The main role of temporal tables is to keeps a full history of data changes. This allows easy point in time analysis. In temporal tables, the period of validity for each row is managed by the system (i.e. Database Engine). Some benefits of Temporal Tables are: auditing of all data changes/data forensics, data reconstruction/recovery, trends monitoring over time, etc.

TempDB Enhancements
You can now configure TempDB via SQL Server 2016 installation wizard. For example you can set the number of data files and growth settings, as well set multiple volumes for TempDB database files. Additionally, now all TempDB file will grow at the same time so there is no need any more for the Trace flags 1117 and 1118.

PolyBase
With PolyBase you can access data stored in Hadoop or Azure Blob Storage with T-SQL Statements. As of that, you can query semi-structured data and join the results with relational data sets stored in SQL Server. PolyBase is optimized for Data Warehousing workloads and it is intended for analytical query scenarios.

Always Encrypted
This feature is designed to protect sensitive data. It allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. To this end, through a transparent encryption for the client applications, data is not visible to the DBA. In order for the client to be able to decrypt the data you just need to install an Always Encrypted-enabled driver installed on the client computer.

Dynamic Data Masking
Dynamic Data Masking in another data protection feature in SQL Server 2016. It limits sensitive data exposure by masking it to non-privileged users. The masking rules are applied in the query results and you designate how much of the sensitive data to reveal and to who. The Dynamic Data Masking feature provides four types of masks: Default, Email, Custom String and Random. A simple example of Dynamic Data Masking is to display only the last 4 digits of a phone number to the support personnel of an IT Department.

Row Level Security
Row Level Security controls access to rows in a table based on the characteristics of the user executing a query. An example is having salesmen see only sales they did and not all the sales in a table. The access restriction logic is located in the database tier and access restrictions are applied always and cannot be skipped.

SQL Server R Services
With SQL Server’s support for R you can call the R language runtime through T-SQL and thus uncover new insights and create predictions on top of your data.

In subsequent articles I will be presenting each one of the above features with comprehensive description and many demos!

References: 
MSDN Library Article: What's New in SQL Server 2016, November Update

See also...
In-Memory OLTP Simulator
SimpleTalk Article: Introducing SQL Server In-Memory OLTP 


Read more on this article...

Thursday, November 19, 2015

My Free eBook Series: "The #SQLServer and .NET Blog"

Earlier this month I have released the third and last book of "The SQL Server and .NET Blog eBook Series".

Through this three-book series, I share my experiences with SQL Server on the below areas:
  • Tuning
  • Administration
  • Development and Data Access
The series contains hundreds of articles I published through time on different specialized topics of SQL Server and can help you perform a variety of operations in SQL Server in an easy and efficient manner.

The three books are:

1. Developing SQL Server 
- Release Date: November 4, 2015 New Release
- Level: Intermediate-Level SQL Server development experience.
- Price: Free
- Subscribe (*recommended): Please use this link.

About the book:
This book is for database developers and architects who develop SQL Server databases and related database processes. The book features tens of articles that provide detailed information on how to develop in SQL Server. It assumes at least intermediate-level experience with SQL Server development and knowledge of basic database principles.















2. Administering SQL Server
- Release Date: December 2, 2013 
- Level: Intermediate-level experience with basic SQL Server administration skills.
- Price: Free
- Subscribe (*recommended): Please use this link.

- Download Link

About the book:
This book is for database administrators and architects who monitor and administer SQL Server instances in order to keep them operating to the highest possible level of stability and performance. The book suggests several techniques that can be used for ensuring a healthy SQL Server instance.

















3. Tuning SQL Server
- Release Date: October 1, 2013 
- Level: Intermediate-level experience with knowledge of basic database principles (i.e. indexing, locking, etc.).
- Price: Free
- Subscribe (*recommended): Please use this link.

About the book:
This book is for database administrators and architects who monitor and tune SQL Server instances in order to keep them operating to the maximum possible performance and stability. The book suggests several techniques that can be used for ensuring a performant SQL Server instance.
















See also...
- My Upcoming eBooks on SQL Server
- The SQL Server and .NET Blog eBook Series: Tuning SQL Server
- The SQL Server and .NET Blog eBook Series: Administering SQL Server
- My Third Book Titled 'Developing SQL Server' is Now Out!
- Artemiou SQL Books

Read more on this article...

Thursday, November 5, 2015

My Third Book Titled 'Developing SQL Server' is Now Out!

Yesterday, I released my third book on SQL Server, titled ‘Developing SQL Server’.  This eBook is the third and the last one of “The SQL Server and .NET Blog eBook Series” and it is dedicated to SQL Server development but don’t worry, there are more books to come! :)

This book is for database developers and architects who develop SQL Server databases and related database processes. The book features tens of articles that provide detailed information on how to develop in SQL Server. However, the book is not intended to be a step-by-step comprehensive guide. Additionally, it assumes at least intermediate-level experience with SQL Server development and knowledge of basic database principles.

The book is organized as follows. Chapter 1 discusses different programmability topics on SQL Server and ways to handle different problems you might encounter while developing database operations. Chapter 2 discusses how you can work with unstructured data in different versions of SQL Server. Chapter 3 introduces SQL Server’s In-Memory OLTP Engine and discusses how you can identify if you have a workload that could significantly benefit by using this technology. Chapter 4 presents In-Memory OLTP Simulator which is an application I developed, with which you can simulate virtually any workload against SQL Server’s In-Memory OLTP Engine and get rich analytics as well as real-time performance statistics. Chapter 5 discusses about PowerPivot; an add-in for Excel which enables the user to perform Self-Service Business Intelligence against millions of records of data within minutes. Chapter 6 discusses Data Access topics like the ADO.NET Entity Framework and WCF Data Services. Chapter 7 contains T-SQL tips for performing specific tasks. Chapter 8 features several special topics on SQL Server like where programmability objects are stored and how to retrieve information about them. Finally, chapter 9 discusses miscellaneous topics on SQL Server and on generic database-related topics.

The book is available as a free download at Artemiou SQL Books website.



Feel free to provide your feedback!


Read more on this article...

Thursday, October 29, 2015

The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'.

Question: What I'm I doing wrong and I'm getting the below error message when trying to list my database's tables?




















Answer: Are you ready for the simplest explanation ever? :)

The reason is that -most probably- (hey, you never know) you are using a SQL Server login that has conflicting permissions.

More specifically: The login you with which you are connected to the SQL Server instance, has the db_denydatareader database role assigned. This means, that whatever else database role the login has assigned to it, even db_owner, the db_denydatareader will be in conflict thus not allowing to access certain resources of the database.

How to fix that: If it is OK with your security requirements, remove the db_denydatareader role from the login. After that, you will be able to access the mapped database (if course the rest of the required permissions are in place).

Similarly, have in mind that you get the same behavior if you have write access to the database but also have the db_denydatawriter database role assigned to the login.

In general, be careful with the database roles you assign to any login and avoid giving conflicting accesses.


Read more on this article...

Tuesday, October 13, 2015

In-Memory OLTP: Comparison of Features/Limitations between SQL Server 2014 and SQL Server 2016

In earlier articles I talked about the In-Memory OLTP Engine in SQL Server 2014. Even though it is very powerful, it had some limitations (note the past tense of "have" here as I have some good news! :)

For example you couldn't use subqueries in the clauses of a SELECT statement inside a natively-compiled stored procedure, or nested stored procedure calls, etc.

Here's the good news: SQL Server 2016 (currently CTP 2.4) lifted all these limitations and provides far more support for In-Memory OLTP, thus making it much easier to use this cool technology.

The table below summarizes the features/limitations of the in In-Memory OLTP Engine in SQL Server 2014 against SQL Server 2016:


Feature / Limitation SQL Server 2014 SQL Server 2016 CTP2
Maximum memory for memory-optimized tables Recommendation (not hard limit): 256 GB Recommendation (not hard limit): 2TB
Collation support Must use a *_BIN2 collation for:
(i) Character columns used as all or part of an index key.
(ii) All comparisons/sorting between character values in natively-compiled modules.

Must use Latin code pages for char and varchar columns.
All collations are fully supported
Alter memory-optimized tables (after creation) Not Supported Supported
Alter natively-compiled stored procedures Not Supported Supported
Parallel plan for operations accessing memory-optimized tables Not Supported Supported
Transparent Data Encryption (TDE) Not Supported Supported
Use of the below language constructs in natively-compiled stored procedures:
- LEFT and RIGHT OUTER JOIN
- SELECT DISTINCT
- OR and NOT operators
- Subqueries in all clauses of a SELECT statement
- Nested stored procedure calls
- UNION and UNION ALL
- All built-in math functions
Not Supported Supported
DML triggers in memory-optimized tables Not Supported Supported
(AFTER triggers, natively-compiled)
Multiple Active Result Sets (MARS) Not Supported Supported
Large Objects (LOBs):
- varchar(max)
- nvarchar(max)
-
varbinary(max)
Not Supported Supported
Offline Checkpoint Threads 1 Multiple Threads
Natively-compiled, scalar user-defined functions Not Supported Supported
Indexes on NULLable columns Not Supported Supported


As a last note, if you want to easily test the In-Memory OLTP Engine of SQL Server, you can download the special software I developed for this purpose called "In-Memory OLTP Simulator".

Read more on this article...

Wednesday, September 9, 2015

DBStats: A Stored Procedure for Easily Retrieving Basic DB Information

In this article I am publishing a stored procedure I have recently written and which given a database name as an input parameter it returns the below basic information:
  • TotalTables
  • TotalViews
  • TotalSPs
  • TotalFunctions
  • TotalDMLTriggers
  • TotalDBSize (MB)
  • LogSize (MB)
  • List of all tables with their number of columns
The below is a screenshot of the output when I execute the stored procedure:





















The following catalogs are used:
You can download the stored procedure here.
Feel free to post any comments for adding more functionality to the stored procedure!

Read more on this article...