Tag Archives: Clustered Index

Next SQL Gulf event “SQL Gulf #2 “ at Microsoft Dubai


SQL Gulf Community reveals about its upcoming SQL Gulf event “SQL Gulf #2” at Microsoft Dubai on 5/9/2015, It is the first SQL Server event ever in the Middle East to talk about SQL Server 2016 ..!
SQL Gulf #1 was at Saudi Arabia, Riyadh on 30/8/2014 , indeed it was extremely successful one that is why we are so excited to spread the word everywhere in the Gulf through many SQL Gulf events at all Gulf cities ,interestingly said that the top notch SQL Server experts and most ever popular speakers are coming from US, UK ..etc are coming to speak to you  at SQL Gulf #2 like Kevin Kline from US and Satya Shyam K Jayanty from UK ..etc  and more importantly to know it is the first SQL Server event in the Middle East ever to talk about SQL Server 2016…

Register here to reserve your seat ASAP, seats are limited , here below is the event program:

Session Abstracts:

1-FIRE! A Fullproof Checklist for Tuning and Troubleshooting

Session Abstract: Learning how to detect, diagnose and resolve performance problems in SQL Server is tough.  Often, years are spent learning how to use the tools and techniques that help you detect when a problem is occurring, diagnose the root-cause of the problem, and then resolve the problem.
In this session, attendees will see demonstrations of the tools and techniques which make difficult troubleshooting scenarios much faster and easier, including:
•             XEvents, Profiler/Traces, and PerfMon
•             Using Dynamic Management Views (DMVs)
•             Advanced Diagnostics Using Wait Stats
•             Reading SQL Server execution plan

Every DBA needs to know how to keep their SQL Server in tip-top condition, and you’ll need skills the covered in this session to do it.
Prerequisites: Intermediate database administration and development skills, especially competence with SSMS.
Goal 1: Learn the “sieve” method of troubleshooting and problem solving, and how to make SQL Server alert you when problems arise.
Goal 2: Discover how to use wait stat analysis, as well as correlate performance information from other sources inside of SQL Server including DMVs, performance counters, and Xevent/trace information.
Goal 3: Learn how to use the most important native tools within SQL Server through live demos to successfully conduct troubleshooting and performance tuning.

Summary: Microsoft ships a multitude of tools to help detect, diagnose and resolve problems inside of SQL Server. But which is best to use and when? This session teaches attendees how to tackle the troubleshooting process to achieve repeated, optimal results.

2-What do I need to know about Data Platform Upgrade best practices & techniques

Data Platform Upgrade topic has been a popular session that I’ve presented in major conferences like Microsoft Tech-Ed (North America, Europe & India), SQLPASS, SQLSaturdays and SQLbits since the year 2008.

In this session, we will overview in depth end-to-end upgrade process that covers the essential phases, steps and issues involved in upgrading SQL Server 2000, 2005, 2008 R2 & SQL Server 2012/2014 (with a good overview on 2016 too) by using best practices and available resources.
We will cover the complete upgrade cycle, including the preparation tasks, upgrade tasks, and post-upgrade tasks. Real-world examples from my Consulting experience expanding on why & how such a solution will work in critical situations.

3-Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016

“Performance Dreams started at SQL Server 2014 and come true now at SQL Server 2016”
, it is just like this because you Microsoft launched live SQL Server 2014 with unbelievable performance reads and substantial improvement that you cannot give up them , come in here to my session and you will know more about many new features and rich powers of SQL Server 2014 regarding performance particularly like  Microsoft project “Hekaton” for In-memory built in for OLTP , CCI( columnstore index) ,Resource Governor for IO consumption ,  lock priority management  and also Single partition online index rebuild technologies ,you will get much hands-on experience  for all definitions , architecture design , values and benefits ,caveats  and recommendations related to each one of them so that you can drive a conscious decision for upgrading your DBs to SQL Server 2014 but keep in mind that are some limitations for those features that should be considered largely before  production deployments …If not ,don’t be upset as it Is still not the end of way coz Microsoft had really rolled out SQL Server 2016 which could address successfully many of those limitations and indeed SQL Server 2014 performance dreams come true at SQL Server 2016, I will speak there simply to help those who didn’t breath SQL Server for the last little while

4-SQL Server Internals and Architecture

Session Abstract: Let’s face it.  You can effectively do many IT jobs related to SQL Server without knowing the internals of how SQL Server works.  Many great developers, DBAs, and designers get their day-to-day work completed on time and with reasonable quality while never really knowing what’s happening behind the scenes.  But if you want to take your skills to the next level, it’s critical to know SQL Server’s internal processes and architecture.  This session will answer questions like:

–       What are the various areas of memory inside of SQL Server?
–       How are queries handled behind the scenes?
–       What does SQL Server do with procedural code, like functions, procedures, and triggers?
–       What happens during checkpoints?  Lazywrites?
–       How are IOs handled with regards to transaction logs and database?
–       What happens when transaction logs and databases grow or shrinks?

This fast paced session will take you through many aspects of the internal operations of SQL Server and, for those topics we don’t cover, will point you to resources where you can get more information.  So strap on your silly, as we cover all these topics and more at speed with tongue planted firmly in cheek!

Prerequisites: Basic understanding of SQL Server operations and activities – such as transactions, queries, and preventative maintenance tasks like backup and recovery.

Goal 1: Learn about the major components within the SQL Server architecture, starting with the relational engine and storage engine, working down to greater and greater detail.
Goal 2: See what happens inside the SQL Server query optimizer and how the query optimizer affects transaction execution time, both read-only and read-write transactions
Goal 3: Review the major operational processes inside of SQL Server that affect memory management and IO activity.

5-Power BI deployment best practices to deliver data analytics to the business

How to deploy Power BI, how to implement configuration parameters and package BI features as a part of Office 365 roll out in your organisation.

Having said that, cloud computing is another aspect of this technology made is possible to get data within few clicks and ticks to the end-user. Let us review how to manage & connect on-premise data to cloud capabilities that can offer full advantage of data catalogue capabilities by keeping data secure as per Information Governance standards. Not just with nuts and bolts, performance is another aspect that every Admin is keeping up, let us look into few settings on how to maximize performance to optimize access to data as required.

Gain understanding and insight into number of tools that are available for your Business Intelligence needs.

There will be a showcase of events to demonstrate where to begin and how to proceed in BI world.


Posted by on August 12, 2015 in Event


Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

SQL Server 2014 Health check DMV queries Day 2

images (1)

Hi guys in the Previous post i explained How we can get the SQL Server instance version and How we can know the installed date for SQL Server instance put today is DAY 2 and i will show new DMV in our track Instance Level configuration Queries

DMV#3 Server Properties

below DMV is more helpful and useful this DMV This gives you a lot of useful information about your instance of SQL Server like (Computer name , ProcessID , SQL Server collation  , Product level…etc)

DMV#4 SQL Server Agent Job Information

below DMV it will return for us basic information about SQl Server agent and the user name configure it to review it one by one.

DMV#5 SQL Server Agent Alert Information 

by the below DMV we will return the basic information about SQL Server agent alerts

Check the Full posts and the scripts :

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Website Slideshare ,Youtube Channel.

1 Comment

Posted by on April 23, 2015 in SQL Server 2014


Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

SQL Server 2014 Service Pack 1 has released

Congratulation Microsoft and big Congratulation for us (SQL Server Releases blog)


One year ago, Microsoft launched SQL Server 2014. Today, April 15, Microsoft are pleased to announce the release of SQL Server 2014 Service Pack 1 (SP1). The Service Pack will be available for download on the Microsoft Download Center.

SQL Server 2014 SP1 contains fixes provided in SQL Server 2014 CU 1 up to and including CU 5, as well as a rollup of fixes previously shipped in SQL Server 2012 SP2. For highlights of the release, please read the Knowledge Base Article for Microsoft SQL Server 2014 SP1.

As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below. SQL Server 2014 with SP1 will be available in additional venues including the Volume Licensing Center and via Microsoft Update starting May 1, 2015.

Microsoft® SQL Server® 2014 SP1
Microsoft® SQL Server® 2014 SP1 Express
Microsoft® SQL Server® 2014 SP1 Feature Pack

To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Website Slideshare ,Youtube Channel.


Posted by on April 16, 2015 in SQL Server 2014


Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Awesome!! DMV and Stored procedure for any DBA

 Untitled Hello every body today no new post today but i will share some amazing and Awesome!!  DMV and stored i created it to help me in my daily work really it is very helpful for any Database Administrator
  • Create Clustered Index on all HEAP Tables by on Single Click.

this one of the best DMV for me because it fast the process for me and it is very helpful by this Stored procedure i sued the technique of   SQL SERVER DYNAMIC QUERY to loop on all databases exists on my SQL SERVER CLUSTER where this databases is not in system databases and it should be with status online to retrieve from it

  1. list by all heap tables.
  2. Loop in this list to retrieve the best column can be Clustered index Based on the criteria that have been developed from my side (Customized option )
  3. build the T-SQL statment of create clustered index

For more information and for download DMV check the (POST &  VIDEO)

  • Index Statistics for all Databases Exists on your Server

NOW any DBA can return all his index Statistics and save it to decide what he need to do shall i should drop some index (UNUSED INDEX) or shall i should check the index size to take A wise decision we should have the information about the index (last user seek data and percentage , user scan , index size , drop index statement if you need to delete index,..ETC)

For more information and for download DMV check the (POST)

Read the rest of this entry »


Tags: , , , , , , , , , , , , , ,

Create Clustered index on all heap tables by on single click V2

images (1)Hello my followers in my last post i created one DMV to can help us for figuring the heap tables on database level then create automatic clustered index on this heap table then after i worked on the DMV i found it worked on database level not on server level and this very hard to execute it on databases one by one (I love to create general DMV for all Server to be saved as Stored procedure under MSDB ) and i found also the DMV take the first column in the table then i create on it cluster index and this from index design and scmaa design not correct because so i updated my DMV to cover two new point very important and they will do the stored proceure more comprehensive and reliable 

update on version number 2:

  1. Select the best column from heap table to create on it the clustered index (i used case when T-SQL to return the data type columns with specific data types i can create on it clustered index.)
  2. DMV now running on server level to cover all database exists on SQL Server instance. ( Converted the query to dynamic query and i looped on each database on the server then i executed this dynamic query on it to print to me one script for the database )

How to execute the Stored procedure #Check_Heap_Tables#

  1. Create Stored Procedure Check_Heap_Tables on MSDB database.
  2. Execute Check_Heap_Tables Stored procedure .
  3. Copy the T-SQL result then execute it on anther session.
  4. Copy the T-SQL for Clustered index create and execute it on new session.

Read the rest of this entry »

Leave a comment

Posted by on March 11, 2015 in General topics


Tags: , , , ,

Create Clustered index on all heap tables by on single click

images (1)Hi Guys today i am coming to write in very interesting subject for any DBA and DB analyst , any DBA should be care from more things one of this is Database design for this database design we should take care from heap tables and this one of the steps of Database assessment to check all heap tables and fix them by creating new Clustered index.
based on that i write one script to return the tables with more information then i will filtered it to return only the tables without any index then i will loop on this amount of tables to create dynamic statement for Create cluster index for each table.


— =============================================
— Author: Mustafa EL-masry
— Create date: 01/03/2015
— Description: DMV Create Clustered index on all heap tables by on single click
— =============================================

CREATE TABLE #Table_Policy
Table_Name NVARCHAR(100) ,
Rows_Count INT ,
Is_Heap INT ,
Is_Clustered INT ,
num_Of_nonClustered INT
WITH cte
AS ( SELECT table_name = ,
o.[object_id] ,
i.index_id ,
i.type ,
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE o.type IN ( ‘U’ )
AND o.is_ms_shipped = 0
AND i.is_disabled = 0
AND i.is_hypothetical = 0
AND i.type <= 2
( COUNT(type) FOR type_desc IN ( [HEAP], [CLUSTERED], [NONCLUSTERED] ) ) pv
INSERT INTO #Table_Policy
( Table_Name ,
Rows_Count ,
Is_Heap ,
Is_Clustered ,
SELECT c2.table_name ,
[rows] = MAX(p.rows) ,
is_heap = SUM([HEAP]) ,
is_clustered = SUM([CLUSTERED]) ,
num_of_nonclustered = SUM([NONCLUSTERED])
FROM cte2 c2
INNER JOIN sys.partitions p ON c2.[object_id] = p.[object_id]
AND c2.index_id = p.index_id
GROUP BY table_name
—-Tables didn’t have Primary key and didn’t have any index
FROM #Table_Policy
WHERE num_Of_nonClustered = 0
AND Is_Heap = 1

SELECT Table_Name
FROM #Table_Policy
WHERE num_Of_nonClustered = 0
AND Is_Heap = 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
——-Cusror NO 2
FROM sys.columns
WHERE object_id = OBJECT_ID(@name)
AND column_id = 1
OPEN db_cursor2
FETCH NEXT FROM db_cursor2 INTO @name2
DECLARE @SQL NVARCHAR(MAX)= N’Create Clustered index [IX_’
+ @name + ‘] on [‘ + @name + ‘]
(‘ + @name2
+ ‘ ASC) with (Fillfactor=80,Data_Compression=page)

FETCH NEXT FROM db_cursor2 INTO @name2

CLOSE db_cursor2
DEALLOCATE db_cursor2
—-End of Cursor 2

FETCH NEXT FROM db_cursor INTO @name

CLOSE db_cursor
DEALLOCATE db_cursor
DROP TABLE #Table_Policy


Here i can found 3 tables without any clustered index or Non clustered index


If you check the massage you will find the T-SQL print for the Creating of Clustered index for the three tables.


by this way by one single click you can fix all heap tables on your databases

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME


Posted by on March 2, 2015 in SQL Server Index


Tags: , ,