SQLAuthority News – Two Whitepapers on Performance Tuning and Enhancements

The best part of being in the technology domain is that I get a lot of opportunity to talk and write at different places. I travel quite a bit in lengths to deliver some of my learnings with audiences around the world. These learnings and writings as in this blog have influenced and encouraged a number of others to also jump into the blogging or writing habit. I cherish and love doing these outside of this site too. Recently, I wrote a couple of whitepapers and thought it was worth a call here over this blog too. What are these whitepapers all about?

Beginning Performance Tuning

If you want to learn a new competency, where would you start? One cannot wake up on a warm sunny day and start driving on their own. There is a process, steps and most importantly a learning part from an expert to get them started. And over a period of time this practice will make them expert too. The analogy hold good even here. There is no silver bullet to performance tuning and there is no “it depends” to performance tuning.

The facts and the process to learn is what gets discussed in this whitepaper. The best person to start performance testing are the developers and DBAs of the application itself. They exactly know the architecture, workload, dependencies and how access happens in the system. In this whitepaper I talk about this fundamental process first and then delve into some of the simplest techniques I have used for ages to do performance testing.

You can read the Whitepaper: Beginning Performance Tuning with SQL Server 2014 here.

Performance Enhancements with SQL Server 2014

Second in series that I wrote was around Performance Enhancements with SQL Server 2014. The need to upgrade to the latest stack always has its own challenges and administrators are constantly looking at the need to understand the new features so that it can help them at work. This constant struggle and need to learn, implement and troubleshoot the latest and greatest is a challenge in itself.

In this whitepaper, I take my random 4 picks of performance enhancements with SQL Server 2014 which I personally felt are worth a mention. Some of them include ColumnStore Index, Managed Lock Priority, Buffer Pool Extensions and more. I take an introduction to each of these enhancements and what these features bring to the table for an administrator / developer. In reality, each of these topics warrant a whole whitepaper but I am sure the future blogs will contain some of these for sure. Stay tuned on that.

You can read the Whitepaper: Performance Enhancements made with SQL Server 2014 here.

I am sure you will enjoy these whitepapers and the best part is they are absolutely FREE. So do download them and let me know your thoughts. Would love to incorporate some of them in my future writing on this blog or papers I write.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQLAuthority News – SQL Server 2012 Service Pack 2 is Available

Microsoft has announced SQL Server 2012 service pack 2 is available earlier this week. Service packages are cumulative updates and upgrade all editions of SQL Server 2012. This service pack contains up to and including SQL Server 2012 Service Pack1 Cumulative Update 9 (CU9). Here is the list of the bugs which are fixed in SQL Server 2012 SP2.

If you are facing any of the following issues and have not installed any of the cumulative updates, you should indeed update to SQL Server 2012 SP2. On my personal development server, I have upgraded this SP2 and after a week’s testing, I am planning to roll it out to my production server.

I am very excited about the bug fix of Poor Performance when using table variables, as I had just talked about during my last presentation in India.

  • Can’t connect to the server when you run a cube creation MDX query in SSAS 2012
  • Image is not displayed correctly in the webpage when you use DrillThrough action on an SSRS 2012 report
  • Text truncated when you use HPB renderer to view a report in SSRS 2012
  • SQL Server crashes when the log file of tempdb database is full in SQL Server 2012
  • SSRS 2012 fails with IsolatedStorageException when subscriptions exported to Excel worksheets are larger than 10 MB
  • SSRS 2012 cannot export a report that is larger than 10 MB to Excel format
  • Rollback causes database to go to suspect mode in SQL Server 2012
  • Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012
  • New DMF sys.dm_fts_index_keywords_position_by_document in SQL Server 2012 SP2
  • New log files are not created when you restart the Reporting Services Windows Service in SSRS 2012 on January 1
  • No error message when log backup failed in SQL Server 2012
  • Visual Studio 2012 crashes because of access violation when the data source binding is completed in Report Viewer 2012 UI in local mode
  • File Share subscription doesn’t populate the description column in SSRS 2012
  • The “Name” drop-down list is empty when an SSRS 2012 report is specified
  • Event ID 22 or error 0xC1000016 will be logged when you use Discover command in SSAS 2012
  • Performance issues when you use SSISDB as your deployment store in SQL Server 2012
  • SSAS 2012 logging feature improvement in SQL Server 2012 Service Pack 2
  • Poor performance when you use table variables in SQL Server 2012
  • Access violation occurs when you run CHECKTABLE or CHECKDB against a table that has persisted computed columns in SQL Server 2008 R2 or SQL Server 2012

You can download SQL SQL Server 2012 from this download link.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Migration Assistant Upgraded to Support SQL Server 2014

We all start somewhere when it is about database. There are different reasons, why we go for one database over another database. Usually the reason is cost and convenience. After a period of time when business is successful and traffic is growing, the same two reasons of cost and convenience start to become secondary goals. I have seen quite a lot of companies starting with free databases and after a while switching to another database as they want stability and service from the product company.

Microsoft has an excellent product which lets you migrate your database from the alternate database to SQL Server. It is called SQL Server Migration Assistant (SSMA) and earlier this week, it has been upgraded to support SQL Server 2014. Now you can migrate from your database to to all editions of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014. SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft.

Here is where you can download SSMA v5.3 for various databases.

Microsoft SQL Server Migration Assistant v5.3 for Access
Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool to automate migration from Microsoft Access database(s) to SQL Server

Microsoft SQL Server Migration Assistant v5.3 for Oracle
Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a tool to automate migration from Oracle database to SQL Server.

Microsoft SQL Server Migration Assistant v5.3 for Sybase
Microsoft SQL Server Migration Assistant (SSMA) for Sybase is a tool to automate migration from Sybase ASE database to SQL Server.

Microsoft SQL Server Migration Assistant v5.3 for MySQL
Microsoft SQL Server Migration Assistant (SSMA) for MySQL is a tool to automate migration from MySQL database to SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – Download Microsoft SQL Server 2014 Feature Pack and Microsoft SQL Server Developer’s Edition

Yesterday I attended the SQL Server Community Launch in Bangalore and presented on Performing an effective Presentation. It was a fun presentation and people very well received it. No matter on what subject, I present, I always end up talking about SQL. Here are two of the questions I had received during the event.

Q1) I want to install SQL Server on my development server, where can we get it for free or at an economical price (I do not have MSDN)?

A1) If you are not going to use your server in a production environment, you can just get SQL Server Developer’s Edition and you can read more about it over here.

Here is another favorite question which I keep on receiving it during the event.

Q2) I already have SQL Server installed on my machine, what are different feature pack should I install and where can I get them from.

A2) Just download and install Microsoft SQL Server 2014 Service Pack. Here is the link for downloading it.

The Microsoft SQL Server 2014 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft SQL Server. It includes tool and components for Microsoft SQL Server 2014 and add-on providers for Microsoft SQL Server 2014. Here is the list of component this product contains:

  • Microsoft SQL Server Backup to Windows Azure Tool
  • Microsoft SQL Server Cloud Adapter
  • Microsoft Kerberos Configuration Manager for Microsoft SQL Server
  • Microsoft SQL Server 2014 Semantic Language Statistics
  • Microsoft SQL Server Data-Tier Application Framework
  • Microsoft SQL Server 2014 Transact-SQL Language Service
  • Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Shared Management Objects
  • Microsoft Command Line Utilities 11 for Microsoft SQL Server
  • Microsoft ODBC Driver 11 for Microsoft SQL Server – Windows
  • Microsoft JDBC Driver 4.0 for Microsoft SQL Server
  • Microsoft Drivers 3.0 for PHP for Microsoft SQL Server
  • Microsoft SQL Server 2014 Transact-SQL ScriptDom
  • Microsoft SQL Server 2014 Transact-SQL Compiler Service
  • Microsoft System CLR Types for Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Remote Blob Store
  • SQL RBS codeplex samples page
  • SQL Server Remote Blob Store blogs
  • Microsoft SQL Server Service Broker External Activator for Microsoft SQL Server 2014
  • Microsoft OData Source for Microsoft SQL Server 2014
  • Microsoft Balanced Data Distributor for Microsoft SQL Server 2014
  • Microsoft Change Data Capture Designer and Service for Oracle by Attunity for Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Master Data Service Add-in for Microsoft Excel
  • Microsoft SQL Server StreamInsight
  • Microsoft Connector for SAP BW for Microsoft SQL Server 2014
  • Microsoft SQL Server Migration Assistant
  • Microsoft SQL Server 2014 Upgrade Advisor
  • Microsoft OLEDB Provider for DB2 v5.0 for Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 PowerPivot for Microsoft SharePoint 2013
  • Microsoft SQL Server 2014 ADOMD.NET
  • Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Analysis Management Objects
  • Microsoft SQL Server Report Builder for Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Reporting Services Add-in for Microsoft SharePoint

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – Microsoft Whitepaper – SQL Server 2014 and Windows Azure Blob Storage Service: Better Together

Microsoft has released SQL Server 2014 earlier this month and now have released very long but interesting white paper on Windows Azure Blog Storage Services. SQL Server Data Files in Windows Azure feature is available in all editions of SQL Server 2014. This feature is enabled by default and at free of cost. This feature makes SQL Server 2014 hybrid cloud database as it provides native support for database files stored as Windows Azure Blobs. If you notice this is a big leap forward where data stored locally as well as on a cloud are accessed by SQL Server 2014 at the same time. You can also host SQL Server Instance in Azure cloud and it can also use the Windows Azure Blobs just like on-premises SQL Server.

Let me give you another example, if you have a situation that you have stored your database files in the cloud as well as backup on Windows Azure Cloud. Now if you want to restore your backup you do not have to bring that back to on-premises before restoring it. You can just restore your data directly from Windows Azure Storage to your SQL Server 2014 instance in a Virtual Machine. It will remove the necessity to do lots of data movement between cloud and on-premises servers.

This white paper discusses exactly the same feature very much in detailed. This white paper has over 116 pages and the size of the word file is 2.5 MB. However, after a long time I have found a Whitepaper which explains concepts from the beginning and walks users step by step with the examples. The appendix file to this Whitepaper contains all the necessary code as well as very interesting reference material. I think I am going to spend my entire weekend reading and learning from this Whitepaper.

Image included in this blog post is courtesy to the same white paper and it explains the story of entire white paper in just one image. Brilliant.

Download the white paper SQL Server 2014 and Windows Azure Blob Storage Service: Better Together.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL Server 2014 Developer Training Kit and Sample Databases

SQL Server 2014 RTM is around the corner (2014). There is plenty of the interest in the SQL Server 2014 in recent time. One of the most frequently asked questions is about learning resources of SQL Server 2014. Everybody wants to learn SQL Server 2014 but there is not enough learning material available currently in the outside world.

Microsoft has released two important resources around it.

SQL Server 2014 Developer Training Kit

The SQL Server 2014 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2014 databases, BI Solutions, and gain insights into the new cloud capabilities to simplify cloud adoption for your SQL databases and help you unlock new hybrid scenarios.

SQL Server 2014 Developer Training Kit Sample Databases

This download contains several sample databases for the SQL Server 2014 Developer Training Kit which showcase many of the new and improved features of SQL Server 2014, including Clustered Columnstore, Online Operations, and Resource Governor IO.

Here are few of the key features in SQL Server 2014

  •  Resource Governor IO
  • Online Operations
  • Updatable Columnstore Index
  • In Memory OLTP Engine
  • Enhencements in SSDT and BI tools
  • Power BI Integration

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Writing SQL Queries Made Easy with dbForget SQL Complete

Down with shackles of convention! dbForge SQL Complete 4.5 came in this world.

What does SQL mean for each of us? For some – the only language for working with relational databases. Yet, for others – it is a way of life…

However, when working with SQL queries, both categories of people face the same difficulties.

Eventually, writing queries becomes routine, and all because of imperfection of the built-in Intellisense in SSMS.

The new dbForge SQL Complete 4.5 is called to release everyone from the shackles of convention in SSMS Intellisense and provide more opportunities when working with T-SQL code.

For this, SQL Complete has several key features in its arsenal:

1. Code auto-completion

Because of contextual object tooltips and syntactic elements of the T-SQL language, the productivity of work with SQL Complete is not just rising – it opens on to a qualitatively new level, providing greater opportunities.

Not just a trite hint of tables and columns is ensured. The ability to generate foreign key relationships, aliases, and many other things…

Object hints are provided in such a big number of structures, that an incomplete list of them can be learned only on a separate page.

2. SQL Formatter

Features of the SQL Formatter embedded in dbForge SQL Complete will allow to forget about manual query editing forever.

Using the SQL Formatter, your code will always be perfect due to a huge number of settings, that allow to customize formatting of all SQL constructs according to your personal preferences.

Moreover, there is a possibility to format not only the current document or selected code fragment, but groups of files on the disk as well.

3. Snippets

Code reuse through snippets is a perfect opportunity to accelerate the process of writing code.

As Devart developers said, their analysis of more than 5.000 topical posts from authoritative resources such as StackOverflow had shown that the list of basic snippets was significantly extended to prevent users from typing frequently used T-SQL statements.

Besides, owing to the Snippet Manager, users can easily extend the set of existing snippets.

When typing code, a required snippet can be selected by capital letters:

and by match with the snippet’s name:

4. Auto-refresh after update schema changes

On active schema changing, many encounter a situation when new or modified objects are not suggested in SSMS Intellisense. For this, you have to manually refresh the object list.

But all that is in the past, since in the version 4.5, there is a capability of smart refresh of hints for modified objects only at specified time intervals.

Within the dbForge SQL Complete 4.5 release, I would like to bring to your attention that memory consumption is reduced and objects describing is now much faster.

All these improvements are aimed to ensure that our users to be able to enjoy work and perform it without delays.

To support words with deeds, I recommend users to re-discover the wonderful world of SQL with dbForge SQL Complete 4.5.

Reference: Pinal Dave (http://blog.sqlauthority.com)