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.
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>
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:
- CONNECT ANY DATABASE Permission.
- SELECT ALL USER Securables Permission.
- IMPERSONATE ANY LOGIN Permission.
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
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.
Follow up us on
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 :
- Last SQL Server instance restart.
- Last DB server (windows services ) restart.
Last SQL Server Restart :
we can check it easily and by the Easiest ways (SQL Query)
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 :
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.
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/
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.
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:
- SQL Server Agent should be running on Destination instance (i.e., secondary server).
- Only User databases are allowed.
- SQL server edition should not be Express Edition.
- Databases in CDW should be in online state.
Using CDW we can Copy, Move, Upgrade Databases including different parameters like
- Stored procedures from Master Database
- SQL Server agent Jobs
- User Defined Error Messages
- SSIS Packages
Please let me illustrate the steps in order to Copy database from one instance to another.
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.
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.
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.
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.
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.
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.
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
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.
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 :
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
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
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')
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'
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 .
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 :
- We can Create Index on NULL Column on Memory-optimized-table ..?
- We can Create Memory-optimized-table Without Primary Key Column ..?
- Clustered index Supported or not Supported in Memory-optimized-table ..?
- What about Computed Column / PERSISTED Supported or No in Memory-optimized-table ..?
- 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 :
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 :
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’
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.