Presenting @SQLPASS- DBA FUNDAMENTALS

   August 6, 2013   11:00 am CST  (Convert Time)

  Tara Shankar Jana

   SQL Server 2012:  Whats New – Breakthrough Insight

Meeting Details  

 

SQL 2012 Engine has seen a paradigm shift in the number of features offered compared to previous SQL versions, and it is considered one of the best Enterprise Ready SQL versions Microsoft has ever produced.  Therefore, it is extremely important to learn about these new features, in order to build the most effective business logic, robust architecture and optimal performance solutions to support Enterprises. In this demo heavy presentation we will cover the following topics: Columnstore Indexes; Indirect Checkpoint; TSQL Extensions and Enhancements; Contained Databases; and Online indexing changes and Extended Events.   Finally, we will also see a quick demo of Hekaton (if time permits), which is an in-memory OLTP feature added in SQL 2014.

 

PPT will be Uploaded after the Final Presentation

SQL 2012: What's New- Breakthrough Insight
Recently, I presented at the dbafundamentals- SQLPASS Virtual Chapter. I have uploaded the complete PPT and the Demo Scripts which I used for the session. Download it from here and enjoy working with them!! Happy SQL Hours!

Do let me know if you all have any questions related to the features discussed. Leave a comment on my site.
Presentation_SQLPASS.zip
Compressed Archive in ZIP Format 4.6 MB
SQL 2014- Features Drilldown
Recently, I presented at the dbafundamentals- SQLPASS Virtual Chapter. I have uploaded the complete PPT and the Demo Scripts which I used for the session. Download it from here and enjoy working with them!! Happy SQL Hours!

Do let me know if you all have any questions related to the features discussed. Leave a comment on my site.
Final_scripts.zip
Compressed Archive in ZIP Format 17.2 KB

There were questions which were asked during the session. Please find the answer to those above:
(In case I have missed some of them, put a note across or a comment and i will get back to you as soon as possible)

 

Question 1: What is going to be deprecated (obsolete) for 2012 ? the speaker mention it very quickly
Answer: Apologies! if I was quick to mention the deprecated features (they were not part of the agenda hence). There are quite a few features which are deprecated and discontinued in SQL 2012. Profiler Trace MAY get deprecated as well in future and the current tool which replaces profiler trace is Extended events. As a DBA you all must get familiar with Extended events now, the difference is XEvents is now a built-in functionality within SQL Server Engine.

 

First lets understand the differences:

  • Discontinued Database Engine Features: Discontinued Database Engine Features are no longer available in SQL Server 2012.
  • Breaking Changes to Database Engine Features: Break Changes to Database Engine Features might break applications, scripts or functionality that is based on an earlier version of SQL Server.
  • Behavior Changes to Database Engine Features : Behavior changes affect how the Database Engine Features work or interact with SQL Server 2012 as compared to earlier versions of SQL Server.  Behavior changes are non-breaking changes that might not cause your upgrade to fail but might affect your applications after the upgrade.
  • Deprecated Database Engine Features : Deprecated SQL Server 2012 database engine features will not be supported in the next release of SQL Server.  These features are scheduled to be removed.  These features still operate the same as earlier versions of SQL Server but will be removed in the next version. Deprecated features should not be used in new applications and existing applications should be modified to no longer use the deprecated features to avoid issues in future upgrades.

 

Breaking Changes to Database Engine Features LIST:

  • SQL Server Management Objects (SMO) from pre-2012 might not build against Microsoft SQL Server 2012
          -- Remove references to SMO .dlls in older versions of SQL Server
  • T-SQL
          -- ALTER TABLE Using SERVERNAME references in the ALTER Statement will fail
          -- Selecting from columns or table named NEXT
                    -- Include the ANSI standard AS keyword
  • Dynamic Management View column name changes
  • SQL CLR Data types (geometry, geography, hierarchyid)
          -- Custom applications that reference Microsoft.SqlServer.Types.dll assembly may fail
  • Distributed Query Calls to a System Procedure where the engine cannot discover metadata
          -- Example, SELECT * FROM OPENQUERY(..., 'EXEC xp_loginfo').
          ….And More

For a Complete list, check the following:
Breaking Changes to Database Engine Features in SQL Server 2012
http://technet.microsoft.com/en-us/library/ms143179.aspx 

 

Behavior Changes to Database Engine Features LIST:

  • Scripting a SQL Server Agent Task
      -- If you create a new job by copying the script from an existing job, the new job might inadvertently affect the existing job.
          -- Manually delete the parameter @schedule_uid to create a new independent schedule without affecting existing jobs.
  • Statistic Computation during Partitioned Index Operations
      -- Not all rows are scanned when a partitioned index is created or rebuilt
          -- USE FULLSCAN clause
  • sqlcmd.exe Behavior Change in XML Mode
      -- XML Result differences, data types, and precision changes
      -- And More..

 

Deprecated SQL Server 2012 Engine Features LIST: (Removed in Next version)

  •   RESTORE Database or Log WITH Password
  •   90 Compatibility Mode
  •   Set ROWCOUNT for Insert, Update and Delete
      -- Use TOP Keyword
  •   Remote Server Stored Procedures
      -- sp_addremotelogin, sp_addserver, sp_dropremotelogin, sp_helpremotelogin,   sp_remoteoption
      -- Use Linked Servers instead
  •  Ability to return result sets from triggers
  •  Sqlmaint Utility
      -- Use Maintenance Plan Feature
  •   Default Trace
  •   Database Mirroring
  •   SQL Server Profiler for Trace Capture and Trace Replay
      -- Utilize Extended Events and Distributed Replay
  •   100 Compatibility
  •   Data types text, ntext, image
      -- Use varchar(max), nvarchar(max) and varbinary(max)
  •   ActiveX subsystem
  •   Sp_attach_db and sp_attach_single_file_db
      -- Use CREATE DATABASE FOR ATTACH OR ATTACH_REBUILD_LOG
  •   TORN_PAGE_DETECTION
  •   DBCC DBREINDEX, INDEXDEFRAG, SHOWCONTIG

 

Discontinued SQL Server 2012 Engine Features LIST:
These are features that functioned in earlier versions of SQL Server but do not function any longer in SQL Server 2012.  Often, these features are options that are still present but do not operate any longer.   Although these features will not block an in-place upgrade, they may affect the behavior of your applications.  Therefore, you should remove any references to them.

  •   Database Compatibility Level (80) no longer supported
  •   DTS Technologies and Packages
  •   Address Windowing Extensions (AWE)
  •   Use of *= and =* in T-SQL statements
  •   SQL Server on Itanium
  •   DATABASEPROPERTY
  •   FASTFIRSTROW Hint
  •   SQLMail
  •   BACKUP DATABASE WITH PASSWORD/MEDIAPASSWORD
  •   RESTORE DATABASE WITH DBO_ONLY
      ….And More

For the full list of discontinued features see:
Discontinued Database Engine Features in SQL Server 2012
http://technet.microsoft.com/en-us/library/ms144262.aspx

 

Question 2: Apologies for attending the session late,saw the audience question that sql profiler would be depreciated, does it mean it would be replaced by more better tool?

Answer: Default Trace has been deprecated and slowly we will see Profiler deprecating as well from future versions. The replacement for Profiler is Extended events which now has a GUI based interface as part of the SQL Server Management Studio. You can get through to XEvents using the following path- SSMS-> Instance Connection-> Management-> Extended Events. For more information please find details here:

http://msdn.microsoft.com/en-us/library/bb630354%28v=sql.105%29.aspx

 

Question 3: Can contained databases be backed up and restored?

Answer: DEFINITELY Yes! as mentioned the idea is to contain every single entity in a database which will reduce external dependencies in order to migrate databases more efficiently and without any hickups.

 

Question 4: Can a SEQUENCE include characters as well?

Answer: No

 

Question 5: How to reset the value back to 1 in a SEQUENCE?

Answer: To restart the Sequence, you can alter the object to start with the required value. In your case you can use 1.

 

ALTER SEQUENCE seq

RESTART WITH 1

 

Question 6: This solution explorer - how can you launch in ssms 2008r2

Answer: Open SSMS (SQL Server management studio)-> Go to the Menu Option VIEW-> Select Solution Explorer.

 

Question 7: Can I attach my sql scripts to the solution explorer?

Answer: Yes you can. Better of creating a Project. As soon as you open up a new query window in SSMS, the solution explorer opens by default and the name is solution1. You can right click on the name of the solution-> select ADD new project-> Once that is done, just keep adding the new .sql scripts to the project. :)

 

Question 8: Are indirect checkpoints are an Enterprise only feature, or avail in Standard?

Answer: Available in all versions of SQL

 

Question 9: How to find Deprecated Features of SQL Server 2012?

Answer: You can use the following Discovery tools to find Deprecated Features

 

  • SQL Server Profiler
  • SQL Server Extended Events
  • Performance Monitor (Perfmon)- Perfmon can only track Deprecated Features others can find Discontinued features as well
  • The sys.dm_os_performance_counters Dynamic Management View
  • Upgrade Advisor

 

For Profiler and Extended Events-> Select the Deprecation Event Class

For Perfmon-> "Go to MSSQL$INSTANCENAME: Deprecated Features" Counter and you will be able to see the complete list (this is available in Windows 2008/R2 and Windows 2012/ R2)

 

Thanks a lot for attending the session. Hope you all enjoyed it!

Presenting @SQLPASS- DBA FUNDAMENTALS

   February 4, 2014   11:00 am CST  (Convert Time)

  Tara Shankar Jana

   SQL Server 2014:  Features Drilldown

Meeting Details  

 

 

 

 

Title: SQL Server 2014- Features Drilldown

 

Abstract (Agenda)

  • The Evolution of Microsoft data platform
  • SQL 2014 and Data Platform

    Mission Critical Performance
    Built-In In-Memory
    Microsoft In-Memory technologies
    In-Memory for OLTP
    In-Memory OLTP Architecture
    Columnstore: Clustered and Updatable
    Comparing Space Savings
    Secure
    Scalable
    SSD Buffer Pool Extension and Scale Up
    Online Operations enhancements

  • Faster Insights from any data
  • Microsoft Power BI for Office 365
    Platform for Hybrid cloud
    Hybrid Cloud Solutions
    Easy On-Ramp to Cloud
    Complete and Consistent Data Platform

 

PPT is uploaded for this presentation


SQL 2014- Features Drilldown- Demo Scripts
Please find the demo scripts attached for your reference and use. PPT is not uploaded, but can be shared on request. Please let me know if you have any questions further!
Final_scripts.zip
Compressed Archive in ZIP Format 17.2 KB

There were questions which were asked during the session. Please find the answer to those above:
(In case I have missed some of them, put a note across or a comment and i will get back to you as soon as possible)

 


Question 1: SQL 2014 In-memory built-in as testing with Bwin , indexes exist only in memory and no hash or B-tree, columnstore index seems good but can't we think of SQL server tables without any indexes and works more efficiently than In-memory or else.
Answer: Great thought I must say, I like it. Though the way DBMS was created B-Tree was an integral part of it and so became the indexes. We traverse the path using the nodes of B-Tree and only an index at leaf and node level (clustered/ non-clustered) can help in faster access of data. I am keeping this answer short as we can go on and on in and around this matter. Let me know if you wish to have discussions around it offline. Will be more than happy to have one.

 


Question 2: Any changes to application or SP when db is deployed to Azure cloud? Can I  scale out SQL Server 2014  traditional  on premises db?

Answer: No changes at all as we use the database fabric layer between the client and server to help the transition, hence the deployment is extremely smooth. Yes, you can scale out traditional on premises db to cloud but limitations apply, like datatypes support (we use different scales for datetime or BIT DTypes, also there is a necessity to have clutered indexes on each table getting migrated to cloud etc. There are quite a few features which doesn’t work in Windows SQL Azure database, for more information please see: http://msdn.microsoft.com/en-us/library/windowsazure/ff394115.aspx

 

Question 3: Can we alter a filegroup to be memory Optimized ?

Answer: No, you need to create a new FG for hosting memory optimized tables. 


Question 4: Can you also migrate back from in-memory tables back to 'real' disk tables?

Answer: There's is no way to convert a normal on disk table into an in-memory OLTP table, yes you can analyze the work and effort required to migrate data from disk based tables to in-memory tables. The table must be re-created to be pushed to in-memory OLTP every time. There's also no way to convert an in-memory OLTP table back to a normal on disk table. The only way to switch back and forth is to export the data (assuming you need to keep the data), drop the object and recreate it, then import the data back into the table if needed.


Question 5: Hello, Please more detail about Memory optimize data configuration filegroup, where it´s recommended to set it up, internally or on the shared san disk, does it store physical data? if lost, do I lost data ?

Answer: Very good question: For the data loss bit, hope you remember! we talked about durable and non-durable tables. Select Schema_and_data if data is an important aspect, which makes the table durable to data loss, if only schema is selected in durability, then you would see data loss. Now for the other question, some background first. A memory optimized filegroup contains one or more containers and each container has one or more files. There are three types of files:

 

CREATE TABLE dbo.UserSession(


   SessionId int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000),


   UserId int NOT NULL,


   CreatedDate datetime2 NOT NULL,


   ShoppingCartId int,


   INDEX ix_UserId NONCLUSTERED HASH (UserId) WITH (BUCKET_COUNT=400000)


 )


 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)


 GO


 

Root File

A root file contains metadata including the data and delta file pairs for a complete checkpoint taken for memory-optimized tables.


Data File

A data file contains data rows that were inserted into memory-optimized tables. The rows are appended to the data file in the transaction log order making data access sequential. This increases IO throughput compared to random IO. Data files have a maximum size of 128MB. Beyond this point a new data file is created. Also, a new data file is created when a manual checkpoint is performed.


The rows are stored with a header that contains the row timestamp to implement versioning and N index pointers for row chaining, where N is the number of indexes defined on the table. See the image below.


The indexes are not stored in the data files; instead they are recreated at startup.

 

Delta File

A delta file contains data rows that were deleted. For each deleted row, it inserts minimal information, the ID of deleted rows and the insert / delete transaction ID. The Delta files are stored in transactional log order, so the file can be accessed sequentially.


A note of interest is the way Hekaton handles updates. As I mentioned before, in order to avoid latches and locks, Hekaton handles multiple versions of the data with a timestamp. So, to achieve this, updates are managed as a DELETE followed by an INSERT. This implies that the engine does not need to seek the data file to update the information. Instead, it appends the updated data at the end of the data file.


Storage considerations for Memory Optimized Tables

As data and checkpoint files use streaming IO for data persistence, the limiting factor for this files is the sequential IO speed instead of latency or random access speed. To optimize data storage for database startup times, you can add multiple containers to the memory-optimized data filegroup, each on a different disk. This way SQL Server will read all of the containers in parallel. When rows are deleted, data files still contain the inactive rows. In order to consolidate data and delta file pairs, Hekaton runs a merge process in the background. All this processing will degrade file system performance due to fragmentation. Memory-Optimized tables write to the SQL Server transaction log just like disk based tables. The only difference is that there are no log records for physical structure modifications, indexing or UNDO information. So, if your system has high transaction rates, you may consider using a SSD disk or a PCI-E card for the transaction log.


 

Thanks a lot for attending the session. Hope you all enjoyed it!

Comments: 1
  • #1

    Packers and Movers Bangalore (Friday, 14 August 2015 04:48)

    The Best Services For Everyone packers and movers information Visit site:-
    Packers and movers in bangalore@ http://professionalmovers.in
    Packers and Movers in Gurgaon@ http://professionalmovers.in/packers-and-movers-in-gurgaon/
    Packers and Movers Bangalore@ http://professionalmovers.in/packers-and-movers-in-bangalore/
    Household Shifting in India Made Easy and Simple by information Visit site:-
    Packers and Movers in Noida@ http://professionalmovers.in/packers-and-movers-in-nodia/
    Packers and Movers in delhi@ http://professionalmovers.in/packers-and-movers-in-delhi/