RSS

Monthly Archives: March 2015

My First Arabic Post in SQL Server 2014

Hello my friends this my first Arabic post in SQL Server technology I do it in SQL Server 2014 it is about { Durable vs. Non-durable tables: In-Memory OLTP} ISA I will select the best posts on my library and I will translate it to Arabic Language
Arabic version: http://sqlserver-performance-tuning.net/?p=5653
English version: http://sqlserver-performance-tuning.net/?p=4553
Video : https://www.youtube.com/watch?v=e2m2gp1ECMk
this is not the end please follow me because i will publish more interested Posts in SQL Server 2014 by arabic and English also i will translate My videos to be arabic version so each video i will do it by two language (English and arabic )تكنولوجيا الجداول الدائمه والغير دائمه فى محرك قواعد البيانات مايكروسوفت 2014

 
4 Comments

Posted by on March 31, 2015 in Arabic Posts

 

Tags: , , , , ,

SQL Server DMV Replication Monitoring Part 2

cloud-monitoring

 

Hello my followers  in the previous post i started my new series in (SQL Server DMV Replication Monitoring) and i explained very interested point for any DBA WORKING on replication solutions How we can know the current status of our Publication and subscription is it Active , In Active by simple DMV depend on dynamic Query technique and on SQL Server cursor TECHNOLOGY .

 

Summary of the previous post:
1- Publication Status
2- Subscription Status
3- Send Email by the Current status

 Replication in SQL Server Series  

1     2      3       4 

DMV Replication Monitoring Series  1     2

Today is  will complete this series to be this part is part two and i will add 2 new point very helpful for our daily WORK :

  • Replication information for the Complete setup.
  • Find publication article.

Read the rest of this entry »

 
1 Comment

Posted by on March 28, 2015 in Replication

 

Tags: , , ,

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 two options (DURABILITY = SCHEMA_AND_DATA) , (DURABILITY = SCHEMA_ONLY)

Durable Table          : DURABILITY = SCHEMA_AND_DATA

Non-durable tables :DURABILITY = SCHEMA_ONLY

Complete the post to know more about the Scripts used in the DEMO

Read the rest of this entry »

 
1 Comment

Posted by on March 25, 2015 in General topics

 

SQL Server DMV Replication Monitoring Part 1

IC216462

 

Hello My followers  today i will took in very important subject for any DBA How we can monitor our Replications !! for any replication i have publication and subscription , How i can now is it working or it is active or down or what is the status of it now , based on that i created one DMV build with technique of Dynamic Query and using SQL Server cursor but before you go for Monitoring (advanced level) you should know some points at the beginning :

 

 

 

 

Replication in SQL Server Part 1

  • What is SQL Replication?
  • Types of SQL Replication
  • Replication Overview

Replication in SQL Server Part 2

  • How to create Transaction Replication

Replication in SQL Server Part 3

  • How to Add New Publisher Server to the Distributor Server

Replication in SQL Server Part 4

  • Disable Publishing and Distribution Servers

Now after we explained the main highlights for the replication lets see How we can monitor the status of the publication and subscription? and How we can receive email if any one of them is InActive :

  • First i return all databases used in replication
[sql]

SELECT
 MSA.publisher_db AS 'Database Name'
FROM
 distribution.DBO.MSarticles AS MSA
OPEN db_cursor
[/sql]

Read the rest of this entry »

 
2 Comments

Posted by on March 25, 2015 in Replication

 

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: , , , , , , , , , , , , , ,

How to Avoid the performance risk of the Delete Statements

Introduction

downloadHello everybody in my last post i explained How we can create Clustered index on all heap tables by one single click for video check this link today i will write and speak in new something How we can avoid the performance risk of the Delete Statement, YES delete statement can do big trouble on my server if i have one table with huge volume of data and more transaction hitting this table this meaning this critical table you should be Sensitive  with this critical  databases or this critical objects , because if you need to delete huge data from this table Based on certain criteria AS Example you need to delete 1,000,000 of record.

Problem

At this time when you need to delete 1,000,000 with some where condition and you are deleted from one of the critical table on your server don’t take the wrong way to write one Delete statement direct it will cost more Sync_network_IO and more CXPACKET also probably it can make Schema lock or Query lock .

Solution :

images

Forget the direct delete statement no think here ! you should do the delete statement as patching:

  1. grabbing any unique ID from Target table and insert it into Temp table.
  2. looping on the temp table to return each time the first 1000 record as Example.
  3. using Merge technology to delete the Data exists on target table where the ID equal the ID in source table.
  4. Update the counter of the looping
  5. commit the transaction if it success rollback transaction  if  it fail

by this way the statement of the delete will run smoothly without any bad affect on the SQL Server cluster performance. don’t think in the time you should think in the impact no problem for Query take time without impact because it is better than fast query Executed in 3 SEC but it  fire the CPU or the IO of the server when it run.

Things to consider while working with big tables.

  1. Use Truncate table, if you need to delete all
  2. If you are deleting records more than 70% of data, I would create a temp table, copy only the records you need to this table and run truncate on the original table. This approach is much faster.
  3. Breaking a big transaction into a small transactions applies to Insert and Update as well.
  4. Where possible use table partitioning. This makes the maintenance easy. You can drop a partition if you need to delete it.

DEMO PART :

Read the rest of this entry »

 
Leave a comment

Posted by on March 13, 2015 in General topics

 

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: , , , ,