Create Clustered ColumnStore index for All tables

 

 

 

 

 

 

 

Problem :

we are working in big project SQL Server 2014 upgrade and SQL Server 2014 Performance this project our role to test the new feature in SQL Server 2014 from the performance wise so we decide  to deploy the new Enhancement in SQL Server 2014 (Clustered ColumnStore index ) CCI so i take backup from one database in my work environment and restored it again with prefix _2014 .

this new type of index to create it on the table this table should be no index on it so i drooped all the index from all the tables but the Problem now How can i create the Clustered columnstore index on all tables( tables supported CCI) by one Click.

Solution :

i do one script to create Clustered columnstore index on all supported tables and here the description of the Script

1- to create CCI on any table we have some limitations one of this limitations the data type of the Columns CCI not supported Some data type.

2- I inserted the data type not supported by CCI in temp Tables

</pre>
CREATE TABLE #CCI_DataType_Limitation ( DataType NVARCHAR(MAX) )
 INSERT INTO #CCI_DataType_Limitation
 VALUES ( 'text' ),
 ( 'timestamp' ),
 ( 'hierarchyid' ),
 ( 'Sql_variant' ),
 ( 'xml' ),
 ( 'geography' ),
 ( 'geometry' )
Select * from #CCI_DataType_Limitation
<pre>
Spread the word

New Permission in SQL Server 2014

New Permission in SQL Server 2014

 

 

 

 

 

 

 

Hi Dears as we know SQL Server 2014 come with more new features and more improvements in the Performance today we will take about the new Feature in SQL Server 2014 in particular in SQL Server security SQL Server 2014 come with three new permission really amazing permission it will help any DBA in his work to can grant simple permission in All Databases in the server in the minimum time this operation it will not take from him one second lets go for the scenario and for the DEMO.

in SQL 2012 and we can see 31 permission whereas SQL 2014 has 34 permissions. These permissions are listed under Server properties >  permissions tab.

New Permission in SQL Server 2014:

  1. CONNECT ANY DATABASE Permission.
  2. SELECT ALL USER Securables Permission.
  3. IMPERSONATE ANY LOGIN Permission.

New permisions

 

Spread the word

Breakthrough in SQL Server 2012 Security fixed by SQLServer 2014

Breakthrough in SQL Server 2012 Security fixed by SQLServer 2014

 

 

 

 

 

 

 

Hi dear we have big bug in SQL Server 2012 security this from my personal view it’s Breakthrough in SQL Server 2012 Security in SQL Server 2012 can the user IMPERSONATE another user and take his privilege and do what he need What !! what i am saying is fact in SQL Server 2012 but it’s fixed in SQL Sever 2014 let’s go for demo:

Open your SQL Server 2014 and open 2 sessions

First Session : Create sysadmin user and read user

1- Sysadmin user


USE [master]
GO
CREATE LOGIN [adminuser] WITH PASSWORD=N'admin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [adminuser]
GO

2- Read user


USE [master]
GO
CREATE LOGIN [Readeruser] WITH PASSWORD=N'P@$$w0rd'
GO

Spread the word

How to Grant Show Plan Privilege

 

 

 

 

 

 

Showplan Privilege it’s granted for any one need to see the execution plane for SQL Server query to check the performance of the index or doing index analysis.

Grant Showplan for one user in one database  :


GRANT Showplan TO [DominName\username]

Grant Showplan for one user in All databases in one SQL instance:


<strong> EXEC sp_MSforeachdb N’IF EXISTS
(
SELECT 1 FROM sys.databases WHERE name = ”?”
AND Is_read_only <> 1
)
BEGIN
print ”Use [?]; GRANT Showplan TO [DominName\username]”
END’;</strong>

After the execution take the Print scripts and run it in another session.

to check the Source go here To See all my posts you can go here  View all my tips

Follow up us on

Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel, 

Spread the word

How to check the last restart for the Server

How to check the last restart for the Server

 

 

 

 

 

 

Hi Dears i received request from the developers by the Staging DB was down yesterday evening, between 6:00 PM to 9:00 PM, can you please advice at this time to do Scientific investigation and troubleshooting so at this time you should check 2 point :

  1. Last SQL Server instance restart.
  2. Last DB server (windows services ) restart.

Last SQL Server Restart :

we can check it easily and by the Easiest ways (SQL Query)

SELECT sqlserver_start_time FROM sys.dm_os_sys_info
SELECT ‘Statistics since: ‘ + CAST(sqlserver_start_time AS VARCHAR) FROM sys.dm_os_sys_info
Server Restart
Spread the word

SQL Gulf#1 Memories and Excerpts (1/3)

SQL Gulf#1 Memories and Excerpts (1/3)

 

 

 

 

 

 

 

 

2 weeks passed since SQL Gulf #1 so far and we are still in conjunction with its cheerful memories , they are really memory stuck and we can never ever each single beautiful moments either with speakers or audience , indeed they were the energy of SQL Gulf #1 and could resurrect the event from the first moment till the last moment of the event therefore I am going to share with you sequential portfolios of SQL Gulf#1 memories along with the upcoming weeks to keep them memorized , also you can download now all presentations and demos of all speakers as below :

 Hugo Sessions

Satya Sessions

kamal Session

Omar Seession

Shehap Sessions

Spread the word

A Wonderful Weekend in Riyadh, for SQLGulf #1, at Alfaisal University

A Wonderful Weekend in Riyadh, for SQLGulf #1, at Alfaisal University

 

 

 

 

 

 

 

 

 

 

 

 


Thanks to the perseverance of SQL MVP Shehap El-Nagar’s invite last year, this brave man from the Saudi Ministry of Higher Education, managed to organise a great inaugural event.  After the longest visa request process in my life, and twice, since originally his goal was to host this event
in December 2013, it finally happened: – the 1st SQLGulf event held in Riyadh, K.S.A. this past Saturday at the beautiful new campus of AlFaisal University, Riyadh.
Spread the word

SQL Gulf #1 event at Riyadh on 30/8/2014 http://www.asysg.com.sa/sql/

SQL Gulf #1 event at Riyadh on 30/8/2014 http://www.asysg.com.sa/sql/

 

 

 

 

 

 

 

 

 

 

After long waiting time and eager passion of our audience and followers to see a real series of SQL Server events in the middle east to resurrect SQL Server love in the Gulf region we are much glad to unveiled about this new brand name of events “SQL Gulf “ which is indeed the power house of SQL Server Administration , development and BI for every .net developers , Database administrator and Analysts to fancy their knowledge capitals about SQL Server  through a variety of 10 interesting sessions leaded by Top notch experts from different countries worldwide Canada , UK, Jordan , India and Saudi Arabia..etc , SQL Gulf is coming first at Riyadh on Saturday 30/8/2014 at Al –Faisal University to be the first SQL Server 2014 in the middle east that will unveiled about SQL Server 2014 myths and legends and performance dreams of 10x-30x, your esteemed presence is really a pride for us so don’t wait more and go directly now to register at http://www.asysg.com.sa/sql/ 

http://sqlserver-performance-tuning.net/wp-includes/js/tinymce/plugins/wordpress/img/trans.gif

Spread the word

Auto Identity Column Value Jump Issue

Auto Identity Column Value Jump Issue

 

 

 

 

 

 

 

 

 

Issue Introduction 

SQL Server 2012 Version coming with bug SQL Server 2012 once it restarted the identity column jumped by 1000 or 10000 record based on the Column data type.if it is integer (int) data type, then jump value is 1000 and if big integer (bigint), then jump value is 10000 and this Special case only in SQL Server 2012 not in the last versions (2005,2008,2008R2) or in the new version (SQL Server 2014), and from point view of Microsoft this Feature rather than a bug and in many scenarios it would be helpful.but in our daily work not accept this Feature because this bug from my point view.

Spread the word

Configuring Copy Database Wizard

Configuring Copy Database Wizard

 

               

                                      

 

 

 

 

 

 

As we all are well known about the different techniques used to copy or move the databases from one location to another.

Let us now focus on a Copy Database Wizard type.

Prerequisites and Limitations for CDW:

  1. SQL Server Agent should be running on Destination instance (i.e., secondary server).
  2. Only User databases are allowed.
  3. SQL server edition should not be Express Edition.
  4. Databases in CDW should be in online state.

Using CDW we can Copy, Move, Upgrade Databases including different parameters like

  1. Stored procedures from Master Database
  2. SQL Server agent Jobs
  3. User Defined Error Messages
  4. Endpoints
  5. SSIS Packages
  6. Logins

Please let me illustrate the steps in order to Copy database from one instance to another.

Spread the word

Backup index in SQL Server with covering Special Cases

Backup index in SQL Server with covering Special Cases

 

 

 

 

 

 

 

 

Hello Dears Backup index in SQL Server with covering Special Cases , this the subject of my post of Today HOW ..? i think any one will read the subject he will say it is very easy definitely right it’s easy but here in my post i will cover Special Cases it’s easy but it’s take Time so let’s go to explain deeply the Problem and How we can solve it.

Problem :

Any DBA or DB Analyst do clean up for unused index from Production but HE/SHE should take backup from this index because may be this index after deleted it will affect potentially Negatively on the Database Performance so at this time you will need to return back one index or all index based on your case. so when you go to backup this index before delete it will take time for Manuel Solution and if you have 2 index in one table and other index in multiple tables it will be hard and it will eat your time.

Solution :

at this time we should have solution to cover all our cases in this point and it should be Automatically to can do it faster  with a good result so i searched more and more but didn’t found one Script cover all what i need only i found Script to backup all index Exist on all tables in Database. i take this Script and do Special Customization on it to Cover the below Special Cases and in the next update i will update the SP to can backup Multiple index Exist in Multiple databases.

Spread the word

Performance Improvements in SQL 2014(3/4)

Performance Improvements in SQL 2014(3/4)

Hi dears, so for we have seen about the incremental update statistics and index enhancement on our previous blog post.

Now we will see about, how SQL server 2014 can cache temp tables.  Before we move further into the subject, will see about temp tables in crisp. These are the tables created at the run time and  these tables are created within the “tempdb” database.  To identify a table as a Temporary Table within the SQL statement, prefix the table name with the “#” character. And also we should know one more fact the caching is not per procedure , the caching is per objects.

SQL Server 2014 has given us some room, by which if we make our temp table in a proper way the temp table can be cached. We will see how this is possible.

Click here to continue 

Spread the word

New SQL Server 2014 permissions chart is available

New SQL Server 2014 permissions chart is available

Hello My Followers as usual our Community share the new Subject and the new news so Today i need to say for ALL DBA don’t worry from the Security part you are working in Microsoft Product.

Microsoft created new chart for all New Permission in SQL Server 2014  It includes the 3 permissions that are new in SQL Server 2014. also you will find Poster for SQL Server 2012 Security Chart and Azure SQL Database Chart

Download the poster from here. Everyone should have this in their workplace.

Spread the word

Durable vs. Non-durable tables: In-Memory OLTP

Durable vs. Non-durable tables: In-Memory OLTP

Hello followers Today i will explain very important point Durable vs. Non-durable tables SQL Server 2014 when come for us with In-Memory  Feature come with 2 Kind of tables Durable and Non-durable tables The default one is Durable Table. One cool feature besides the durable in-memory tables, is the possibility to create non-durable in-memory tables. As you realize, you can get a lot of performance improvement with the in-memory solution, but what is the benefit with non-durable tables as the data will be lost in case of failure? Well, one option is to use non-durable in-memory tables as staging tables in your ETL solution as you don’t care about the staging data in case of a system crash. do you have Temporary data ? do you have data you are OK to lose the data due to SQL Server restart at this time you can think in Non-durable tables, 

Memory-Optimized-Table come with 2 option (DURABILITY = SCHEMA_AND_DATA) , (DURABILITY = SCHEMA_ONLY)

Durable Table          : DURABILITY = SCHEMA_AND_DATA

Non-durable tables :DURABILITY = SCHEMA_ONLY

Why would I want to use a non-durable in memory table?

These non-durable tables, are special case tables after the database engine is rebooted he table will be empty but this tables is great for filling  very very fast so this tables is very fantastic for  staging tables in data warehouses, logging tables,For production tables within an OLTP system these non-durable tables are a really bad idea. If you were to use non-durable tables in a production OLTP database, under most situations if the data was lost this would end up being a really bad idea because data would be lost when the SQL Server instance is rebooted.

Spread the word

SQL Server Restart Notification

SQL Server Restart Notification

 

 

 

 

 

 

Hi dears today we will explain How we can do Automatic notification for SQL Server Restart After the DB Server Restart Successfully and All Databases Returned and not in Recovery mode this we can say it as log or as notification to know what happened in your Server but for DB Server notification when Restart Start this another thing i am  not Cover it in this post i will Cover it in another Post

Today we don’t need for any tolls only SQL Server can do it by Small Configuration let’s Go for Demo to see How We can get SQL Server Restart Notification after Restart Success

 

  • Database Server Configuration 

EXEC sys.sp_configure N'Show Advanced Options', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'scan for startup procs', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Spread the word

SQL Server 2012 Service Pack 2 (SP2) RTM has released.

SQL Server 2012 Service Pack 2 (SP2) RTM has released.

 

 

 

 

 

 

Hi Dears today i come with good news for all SQL Server DBA/DB Analyst and DB Developer Microsoft SQL Server team is pleased to announce the release of SQL Server 2012 Service Pack 2 (SP2).and the good thing here is this upgrade is free and doesn’t require an additional service contract.The Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. This includes Slipstream installation packages that can be utilized with existing licenses to install (or upgrade to) new instances with SP2 pre-installed.

Microsoft® SQL Server® 2012 Service Pack 2 (SP2)

Microsoft® SQL Server® 2012 SP2 Feature Pack

SQL Server 2012 SP2 will release through other distribution channels (including Microsoft Update) by end of July 2014.The first Cumulative Update of SQL Server 2012 Service Pack 2 is scheduled to release end of July 2014. It will contain hotfixes that have been delivered in SQL Server 2012 Service Pack 1 Cumulative Update 10.

Bugs that are fixed in SQL Server 2012 Service Pack 2 :

Spread the word

What we Should know before going to upgrade to SQL Server 2014(4/4)

What we Should know before going to upgrade to SQL Server 2014(4/4)

 

 

 

 

 

 

Hello my Followers in the last previous post  i covered some points not supported in SQL Server 2014 in the new Technology and Feature Memory-Optimized-Table

  • Memory Optimized Table Dosen’t Support Table Without Non Clustered index or Primary Key
  • IDENTITY Feature not Supported in Memory-Optimized-Table
  • ANSI_PADDING OFF Not Supported in Memory-Optimized-Table
  • Memory-optimized tables Not Suuported as target of MERGE

and today we will Cover new points and limitation in SQL Server 2014 Memory Optimized Table

SQL Server 2014

Spread the word

Speak at SQL Saturday Cairo #321 at Microsoft smart village Egypt

Speak at SQL Saturday Cairo #321 at Microsoft smart village Egypt

 

 

 

 

 

 

 

I am travelling on next Monday to speak at SQL Saturday Cairo #321 at Microsoft smart village Egypt, I am going to talk 2 sessions about SQL Server 2014 performance dreams and T-SQL performance guiltiness for better DB stress powers , here below is more details about my sessions

http://sqlsaturday.com/321/schedule.aspx

Spread the word

Memory allocated for Memory-Optimized-Table in SQL Server 2014

Memory allocated for Memory-Optimized-Table in SQL Server 2014

 

 

 

 

 

 

 

Hi dears today i coming with small DMV but it’s very important for us to know the actually memory allocated for each memory optimized table in our SQL Server 2014 Database let’s go :

  • find allocated memory for one Memory-Optimized-Table 

select * from sys.dm_db_xtp_table_memory_stats
where object_id = object_id('dbo.students')

 

Spread the word

Walk inside DMV stack (11/100)

Walk inside DMV stack (11/100)

 

 

 

 

 

 

 

 

By the last blog , I explained DMV stacks that are used often to diagnose disk space issues and by this time I am turning now into a new portfolio about SQL Server Agent service administration for jobs and alerts administration as follows:

 

1-      Assign a mail profile for SQL Server agent service :

You have to create a mail profile and much preferable to make it public to be used by any other services or functions not SQL Server agent service ONLY , you can read more about it at http://msdn.microsoft.com/en-us/library/ms187605.aspx , then you have to assign it to SQL Server agent service as follows:

 


USE [msdb]

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N'mailprofile'

GO

EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2014-05-22T12:13:19'

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=999999,

@jobhistory_max_rows_per_job=10000,

@databasemail_profile=N'mailprofile'

 

Spread the word

Subscriptions and Delivery (Reporting Services)(1/2)

Subscriptions and Delivery (Reporting Services)(1/2)

 

 

 

 

 

 

 

HI dears today we will talk in very fantastic subject amazing option in SQL Server reporting Services Subscriptions and Delivery if we have reports and need to send by email for one Subscriptions or multiple Subscriptions we can do it from Subscriptions option in RRS before Jumping to the Configuration i need to explain How you can find this options and what is the Subscriptions Requirement .

images

 

 

Spread the word

What we Should know before going to upgrade to SQL Server 2014(3/4)

What we Should know before going to upgrade to SQL Server 2014(3/4)

 

 

 

 

 

 

Hello my Followers in the last previous post  i covered some points not supported in SQL Server 2014 in the new Technology and Feature Memory-Optimized-Table :

  1. We can Create Index on NULL Column on Memory-optimized-table ..?
  2. We can Create Memory-optimized-table Without Primary Key Column ..?
  3. Clustered index Supported or not Supported in Memory-optimized-table ..?
  4. What about Computed Column / PERSISTED Supported or No in Memory-optimized-table ..?
  5. How we can Update the Primary key Column in Memory-optimized-table ..?

and today we will Cover new point and limitation in SQL Server 2014 Memory Optimized Table :

SQL Server 2014

 

 

 

 

 

 

 

Spread the word

In love of SQL Server 2014

In love of SQL Server 2014

 

 

 

 

 

 

Because our community http://sqlserver-performance-tuning.net/ is believing in SQL Server 2014 performance dreams based so that we are all trying nowadays to unveiled all of its new performance features and make spotlight on the most important ones for both DB developer and administrator through the below logs , I am urging everybody who even didn’t breath air for SQL Serer to read them and of course he will enjoy extremely working with SQL Server 2014 :

Spread the word

Performance Improvements in SQL 2014(2/4)

Performance Improvements in SQL 2014(2/4)

Hi dears, on my previous blog we saw about a enhancement regarding a update statistics on SQL 2014. Will see yet another enhancement on SQL Server 2014 which is non clustered indexes on temporary table, and which can also be called inline indexes.

“Non clustered indexes on table variable”

First we will see ‘Which is not possible in previous edition, which is now possible in 2014′ secondly we will see ‘Which is possible in previous edition, which can be coded very easily’  

NewFeatures-logo

Spread the word

Walk inside DMV Stacks (10/100)

Walk inside DMV Stacks (10/100)

Today we will talk about Backup /Restore Status DMV :

how to detect Backup /Restore status, last backup operation for your database.

Hi dear, being a DBA we should practice to tell ‘it can be’. We all know the backup are back boons, and part of our job. Being working in a environment, where the database size up to 1TB. Just scheduling the back up and sit back, won’t be fair. We suppose to answer many question regarding our backups, as I told in the beginning we should answer ‘Can be’ most of the time.

Can you tell me, when will the backup finish ?

How many percentage completed so for ?

What was the elapsed time so for ?

Can you give me the answer for all this, Yes of course. It can be answered.

Spread the word