Mustafa EL-Masry

March 25, 2015 Mustafa EL-Masry Replication

SQL Server DMV Replication Monitoring Part 1

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]

Continue reading “SQL Server DMV Replication Monitoring Part 1” →

March 14, 2015March 15, 2015 Mustafa EL-Masry SQl server Administration

Awesome!! DMV and Stored procedure for any DBA

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

list by all heap tables.
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 )
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)

Continue reading “Awesome!! DMV and Stored procedure for any DBA” →

March 13, 2015March 15, 2015 Mustafa EL-Masry General topics

How to Avoid the performance risk of the Delete Statements

Introduction

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

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

grabbing any unique ID from Target table and insert it into Temp table.
looping on the temp table to return each time the first 1000 record as Example.
using Merge technology to delete the Data exists on target table where the ID equal the ID in source table.
Update the counter of the looping
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.

Use Truncate table, if you need to delete all
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.
Breaking a big transaction into a small transactions applies to Insert and Update as well.
Where possible use table partitioning. This makes the maintenance easy. You can drop a partition if you need to delete it.

DEMO PART :

Continue reading “How to Avoid the performance risk of the Delete Statements” →

March 11, 2015March 13, 2015 Mustafa EL-Masry General topics

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

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:

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.)
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#

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

Continue reading “Create Clustered index on all heap tables by on single click V2” →

March 10, 2015 Mustafa EL-Masry General topics

Shared disks on SQL Server Cluster

Hello my followers today my post is very simple post but more helpful and more important to know by the easiest way what is the shared disks on SQL Server cluster you manage it .

USE master
GO
SELECT *
FROM sys.dm_io_cluster_shared_drives
—-OR
SELECT *
FROM fn_servershareddrives()

this script will return the Shared drive names on your SQL Server cluster