The SQL Server Basic Installer: Just Install it in three clicks

Microsoft introduced the new Basic Installer experience for SQL Server 2016 Express, SQL Server 2016 Developer, and SQL Server 2016 Evaluation Editions. Just three clicks you can get a default installation of these editions. You may check full details here

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Advertisements
Posted in Uncategorized | Leave a comment

SQL 2014 Service Pack 2 is now Available !!!

Microsoft released SQL Server 2014 SP2 is available for download. These are the improvements introduced in SQL Server 2014 SP2

For the detailed list of  improvements you may check here

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Posted in Database Administration | Leave a comment

How to move Tables/objects between Filegroups

In this post, I will be explaining how to move tables/objects between different FileGroups.

  • Create a sample database
USE master
GO
CREATE DATABASE MoveFG
GO
  • Create Two file groups. In this example i have created two  filegroups MoveFG_DATA_1 and MoveFG_DATA_2
ALTER DATABASE MoveFG ADD FILEGROUP MoveFG_DATA_1
GO
ALTER DATABASE MoveFG ADD FILEGROUP MoveFG_DATA_2
GO
  • Create data files in different filegroups. In this example, i have created four data files, two for each filegroup
ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG11,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_11.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_1
GO

ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG20,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_20.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_2
GO

ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG21,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_21.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_2
GO
  • Now, create a table on filegroup MoveFG_DATA_1 and later move the filegroup to another group name MoveFG_DATA_2
USE MoveFG
GO

CREATE TABLE TAB1
(
TAB1_ID INT IDENTITY(1,1),
TAB1_NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
) ON MoveFG_DATA_1 -- Filegroup we created.
GO
  • Insert few records into the table TAB1
SET NOCOUNT OFF
INSERT INTO TAB1(TAB1_NAME)
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO 10000
  • Verify which file group the data resides. You can use sp_help to get the details as shown in below.

1.png

  • As you can see above screenshot the table is on First file group MoveFG_DATA_1. Let us move the table TAB1 to another filegroup name MoveFG_DATA_2. To accomplish this we need to alter table with drop constraint with move option to move to another file group as shown below
ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO MoveFG_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO
  • Now, Check which file group the table TAB1 resides

2

  • There you go, The table has been moved to secondary file group MoveFG_DATA2. You can also use below query to get the details of tables/objects on different filegroups. I have taken this from Pinal’s Blog
SELECT obj.[name], obj.[type], i.[name], i.[index_id], fg.[name] FROM sys.indexes i
INNER JOIN sys.filegroups fg
ON i.data_space_id = fg.data_space_id
INNER JOIN sys.all_objects obj
ON i.[object_id] = obj.[object_id] WHERE i.data_space_id = fg.data_space_id
AND obj.type = 'U' -- User Created Tables
GO

3.PNG

  • Be careful running this code on a production system, You may want to consider running this code during a maintenance window so the users are not impacted.

Hope you like the post. Stay tuned for next tip.

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Posted in Database Administration | 1 Comment

New SQL Server 2016 Live Query Statistics

SQL Server 2016 introduce a new feature Live query statistics which allows us to view the  real-time insights into query without needing to wait for the query to complete.

The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc.

Let’s see how it works.

I have opened the SSMS and write a sample query from Adventureworks databasse.

select a.* from HumanResources.Employee a cross apply HumanResources.Employee b

1.png

To view the live query execution plan, on the tools menu click the Live Query Statistics icon. Execute the query. The live query plan displays the execution plan as below

2.png

You can also highlight the query and right click and select “Include Live Query Statistics”

3.png

Another way of selecting the live query plan is to use activity monitor.

4.png

When you specify Include Live Query Statistics information it automatically enables statistics profile infrastructure for the current query session.

There are two other ways to enable the statistics infrastructure which can be used to view the live query statistics from other sessions (such as from Activity Monitor).

  • Execute SET STATISTICS XML ON; or SET STATISTICS PROFILE ON; in the target session.
  • Enable the query_post_execution_showplan extended event. This is a server wide setting that enable live query statistics on all sessions.

 

Note: This feature is available beginning with SQL Server 2016 Management Studio, however it can work with SQL Server 2014.

That’s all about Live Query Statistics

Cheers

Hope you like the post!

Ramasankar Molleti

MSDN:LinkedIn:Twitter

 

Posted in Database Administration, SQL Server 2016 | Leave a comment

New SQL Server 2016 SCOPED CONFIGURATION

Microsoft SQL Server 2016 introduced new way of configuring some of the instance level setting to database level which gives you the ability to easily make several database level configuration changes such as

  • Setting MAXDOP for an individual database
  • Set the query optimizer cardinality estimation model independent of the database to compatibility level.
  • Enable or disable parameter sniffing for an individual database
  • Enable or disable query optimization hotfixes for an individual database
  • Clear the plan cache for an individual database without using DBCC Command

Let’s us explore each option:

MAXDOP: 

Set the MAXDOP parameter to an arbitrary value (0,1,2, …) to control the maximum degree of parallelism for the queries in the database. It is recommended to switch to db-scoped configuration to set the MAXDOP instead of using sp_configure at the server level, especially for Azure SQL DB where sp_configure is not available. You can set the different MAXDOP settings for primary and secondary. For Example, you can set the MAXDOP value to 1 on a primary and on the secondary where used for reporting can be set to 4 as below.

-- Set MAXDOP for Primary database
 ALTER DATABASE SCOPED CONFIGURATION 
 SET MAXDOP = 1;
 GO

 -- Set MAXDOP for Secondary database(s)
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET MAXDOP = 4;
 GO

 

Legacy Cardinality Estimation:

Set the option “LEGACY_CARDINALITY_ESTIMATION” Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481. To set this at the instance level, see Trace Flags (Transact-SQL). To accomplish this at the query level, add the QUERYTRACEON query hint.

You can use the below sql statements to enable the legacy cardinality estimation for primary and secondary based on your requirement.

-- Enable legacy Cardinality Estimation for Primary database
 ALTER DATABASE SCOPED CONFIGURATION 
 SET LEGACY_CARDINALITY_ESTIMATION = ON;
 GO
 -- Enable legacy Cardinality Estimation for Secondary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET LEGACY_CARDINALITY_ESTIMATION = ON;
 GO

 -- Set legacy Cardinality Estimation for the Secondary database(s) 
--to the same value as the Primary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
 GO

 

Enable/Disable Parameter Sniffing:

Enables or disables parameter sniffing. This is equivalent to Trace Flag 4136. o set this at the instance level, see Trace Flags (Transact-SQL). To set this at the query level, see the OPTIMIZE FOR UNKNOWN query hint.

You can use the below sql statements to disable parameter siniffing.

 -- Disable parameter sniffing for Primary database
 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
 GO

 -- Disable parameter sniffing for Secondary database
 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF;
 GO

 -- Set parameter sniffing for the Secondary database(s) to 
 ---the same value as the Primary database

 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY 
 SET PARAMETER_SNIFFING = PRIMARY;
 GO

 

Query Optimizer Hotfixes:

Enable or disable “QUERY_OPTIMIZER_HOTFIXES” at the database level, to take advantage of the latest query optimizer hotfixes, regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199

Sample T-SQL to enable query optimizer hotfixes

-- Enable query optimizer fixes for Primary database
ALTER DATABASE SCOPED CONFIGURATION
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

-- Enable query optimizer fixes for Secondary database
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY
SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO

 

Clear Procedure Cache:

“Clear Procedure Cache” option allows to clear procedure cache at the database level without impacting other databases.

 Sample T-SQL Script to clear the procedure cache
 -- Clear plan cache for current database (only possible for Primary database)
 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
That’s all about new sql server 2016 scoped configuration option.

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

Posted in Database Administration, SQL Server 2016 | Leave a comment

“If you could give a DBA just one piece of advice, what would it be?”

“If you could give a DBA just one piece of advice, what would it be?”

For those of you hunger to learn and explore your skills you may read the book DBA Jump start 

This book contains advice from most renowned successful Database Professionals.

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

Posted in Database Administration | Leave a comment

Export and Import the database objects using BACPAC file

In this post i will be explaining about how to export the database objects to backpac and import from bacpac file.

  1. Connect to SQL Server , Right click on the database and Go to Tasks –> Export Data Tier Application

1

 

2. Introduction screen will be appeared as shown below, click next to continue

2.PNG

3. Export setting screen , please select where you want to store the bacpac file. It can be either on windows azure machine or on local machine. I have selected local in this example.

3

4. Please provide the location of the file to save the bacpac file.

4.PNG

5. Same export settings screen, click on advance tab to select the objects you would like to export. Once you have selected, click next to proceed

5.PNG

6. Progress screen will appear, it will show the progress of the export as shown in below figure

7

7. Results screen will appear after completion of export as shown in below figure.

8.PNG

8. You have successfully exported the database objects as bacpac file. Let us create new database by importing the bacpac file in another server. Right click on databases and select import Data-tier Application as shown in below screen

`

9.png

9.  Introduction screen will appear as show in below figure. Click next to continue

10.PNG

10. Import settings screen will appear and specify the bacpac file which was created before. If you have stored the bacpac file in windows azure blob storage then you can select second option to import the bacpac file. In this example, i have saved the bacpac file on the local machine.  Click next to continue.

11.PNG

11. Database settings screen will appear as below, specify the name of the database , data and log file path. In this case i have specified database name as “Hello_Test” and the paths are on the same drive. Best practice is to keep the data and log files separated. Click next to continue.

12.PNG

12. You would see summary screen as below after the above step. Click next to continue.

13.PNG

13. Progress screen will appear and will display the progress of the database import as show in the below figure. Click next to continue.

14.PNG

14. Results screen will show you the results of import progress. As you can see in the below screeshot, the import operation completed. Let us see the new database “Hello_Test” and the objects.

15.PNG

15. There you go. New database “Hello_Test” has been created.

 

16

So far you have learned how to export database objects as bacpac and import the bacpac to create a database. You may think, the same can be achieved by doing backup and restore. Yes, we can achieve using backup and restore. But,

  1. You do not have an option to chose few database objects/Schemas to import rather than complete database, unless you maintain separate file groups for different objects.
  2. Import a BACPAC file to create a new Azure SQL database.
  3. Restore SQL Azure database to MS SQL Server.

 

Cheers

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

Posted in Database Administration | Leave a comment