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

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

List on Non-Clustered HASH index in SQL Server 2014

List on Non-Clustered HASH index in SQL Server 2014

Hi Dears Non Clustered HASH Index is new index in SQL Server 2014 supported for Memory-Optimized-Table this index we will speak on it by Detail in the next posts because it’s need More Posts to Cover all things in this New Index type but today we will know How we can list on Non-Clustered HASH Index in SQL Serve 2014

SELECT
 object_schema_name([object_id]) As [Schema Name]
, object_name([object_id]) As [Table Name]
, [name] As [Index Name]
, [type_desc] As [Index Description]
FROM
sys.hash_indexes
Spread the word

List All Memory-Optimized-table in SQL Server 2014

List All Memory-Optimized-table in SQL Server 2014


Hi Dears today i will post very simple DMV but it’s very nice Select How we can list all Memory-optimized-table in SQL Server 2014 any one work on SQL Server now Sys.tables in SQL Server 2014 we have 2 new Columns

(durability_desc,Is_memory_Optimized)


SELECT name,type_desc,durability_desc,Is_memory_Optimized FROM sys.tables
WHERE Is_memory_Optimized = 1

Spread the word

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

What we Should know before going to upgrade to SQL Server 2014(2/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. Memory Optimized table doesn’t support Alter.
  2. We can’t create index after we create the table but before we can do (Non-Clustered Hash / Non-Clustered )
  3. Memory optimized tables cannot be created in system databases.
  4. The index option ‘fillfactor’ , data_compression is not supported with indexes on memory optimized tables.
  5. Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables
  6. The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables.

Today we are going to Show another Important point must be the DB Developer and DB Analyst / Architecture / DBA take care about it  let’s go for our DEMO : I will work on the Same Database i sued it on the previous Post

What we will Cover today in this Post is :

  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 ..?
Spread the word

Performance Improvements in SQL 2014(1/4)

Hi dears, it is always nice to know something new. Would like to start with a wisdom i like “Learning brains will stay younger”. It is always advised by many performance tuning expertise,

“Update Statistics before you take any other steps to tune performance”.

SQL server 2014 has new feature called incremental statistics. Really it was a nice a feature, if you are having your tables spread into many partitions. Tables on single partition won’t be benefited by this feature.  Before we know about this feature, will have a glance about Update statistics, if you did’t update statistics on the database. The query optimized will struggle to give you best execution plan, which will ruin the performance of your query for sure.

Click to Continue

Spread the word

Walk inside DMV Stacks (9/100)

 

 

 

 

 

 

 

Today we will talk about one of Security DMV :

how to detect any create /update for sys admin user to your logins.

Sys admin privilege is the higher privilege in SQL server service so you should know and detect if any one added to your server and assigned as sys admin

The below DMV you could put it in job that run every 30 minutes and it will detect if any sys admin user added in the last hour to your logins.

also this SP will send to you mail notification about the user added and i make also customization for raising message that you could use it’s number through alerts.

Spread the word

Walk inside DMV Stacks (8/100)

Walk inside DMV Stacks (8/100)

 

 

 

 

 

 

 

 

By the last blog , I explained DMV stacks that are used often to determine tables sizes as well as DB growth , now I am going to zoom more on another disk issue that annoy often all database administrator which is log file inflation due to a certain transaction which might end up eventually with long DB recovery time after DB service restart or cluster failover , this is really horrible and people might look for you J if it is not solved quickly , that is why I am going now to illustrate fast ways to define log space issues and fix them ASAP in addition some tips to mitigate DB recovery time

1-      Define transaction log file free space as well as size

Last blogs , we discussed how to find the disk spaced consumed by all data and log DB files but this time you are trying to know the percentage of free space inside a log file itself so that you can decide faster which log file can be shrunk quicker , you can do it easily as follows:

Spread the word