RSS

Monthly Archives: March 2015

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

Untitled

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME

 
Leave a comment

Posted by on March 10, 2015 in General topics

 

Tags: , , , , , , , , ,

How many nodes exist in a SQL Server cluster

Bild_go-cluster+(S.+34)+-+NICHT+IN+SOCIAL+MEDIA+VERWENDEN

Any DBA going for new environment he should know how many nodes Exist in SQL Server Cluster he will mange it so today i will share with small DMV can tell us this information easily without doing more effort

“” SELECT * FROM ::FN_VIRTUALSERVERNODES() “”

DMV Return :

Node Name : the name of the Windows Server (Computer Name)

Status : Sever Status (Ruining / DOWN )

Is Current owner : it will tell you which node your SQL sever up and running on it now

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME

 
1 Comment

Posted by on March 8, 2015 in General topics

 

Tags: , , , , , , , , ,

Rolling up multiple rows into a single row

Problem :

I have one table have 30,000 phone Number and i need to path it to SMS services as one row to can send massage for this numbers in one time so i need to return all this rows in one row ,

Solution :

we have two solution either you can return it as XML by using XML Path or using STUFF FUNCTION to return all this rows in one row , Stuff function can be used in more things and this case one of this usage.

STUFF FUNCTION:

The STUFF string function inserts a string into another string.  It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.  The syntax of the STUFF string function is as follows: for more information about STUFF function check this LINK

STUFF ( < character_expression >, < start >, < length >,

< character_expression > )

 DEMO PART

  • Create table name PhoneNumber
  • Insert one record.
  • Filling table by 30.000 Record using Filling Table Stored procedure GO FOR Download (SP after the execute it will print T-SQL Script Execute it to filling the table)
  • Return All record in one row as XML
  • Return all record in one row using stuff function.

Read the rest of this entry »

 
Leave a comment

Posted by on March 7, 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
— =============================================

SET NOCOUNT ON
CREATE TABLE #Table_Policy
(
ID INT PRIMARY KEY
IDENTITY(1, 1)
NOT NULL ,
Table_Name NVARCHAR(100) ,
Rows_Count INT ,
Is_Heap INT ,
Is_Clustered INT ,
num_Of_nonClustered INT
);
WITH cte
AS ( SELECT table_name = o.name ,
o.[object_id] ,
i.index_id ,
i.type ,
i.type_desc
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
),
cte2
AS ( SELECT *
FROM cte c PIVOT
( COUNT(type) FOR type_desc IN ( [HEAP], [CLUSTERED], [NONCLUSTERED] ) ) pv
)
INSERT INTO #Table_Policy
( Table_Name ,
Rows_Count ,
Is_Heap ,
Is_Clustered ,
num_Of_nonClustered
)
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
————————————————————————————–
–DMV
————————————————————————————–
—-Tables didn’t have Primary key and didn’t have any index
SELECT *
FROM #Table_Policy
WHERE num_Of_nonClustered = 0
AND Is_Heap = 1

DECLARE @name NVARCHAR(100)
DECLARE db_cursor CURSOR
FOR
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
WHILE @@FETCH_STATUS = 0
BEGIN
——-Cusror NO 2
DECLARE @name2 NVARCHAR(100)
DECLARE db_cursor2 CURSOR
FOR
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID(@name)
AND column_id = 1
OPEN db_cursor2
FETCH NEXT FROM db_cursor2 INTO @name2
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(MAX)= N’Create Clustered index [IX_’
+ @name + ‘] on [‘ + @name + ‘]
(‘ + @name2
+ ‘ ASC) with (Fillfactor=80,Data_Compression=page)
GO’
PRINT @SQL

FETCH NEXT FROM db_cursor2 INTO @name2
END

CLOSE db_cursor2
DEALLOCATE db_cursor2
—-End of Cursor 2

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
GO
DROP TABLE #Table_Policy

———————————————————————————————————————————————–

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

Untitled

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

Untitled

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

 
4 Comments

Posted by on March 2, 2015 in SQL Server Index

 

Tags: , ,

My first technical Video in SQL Server Technology

Hello Guys “a journey of a thousand miles begins with a single step.” based on that i started my journey to MVP award i am now have more than 200 Post in SQL server Technology i am founder of Community SQL Database administration from 2012 and i started today New step to Promote my work I published today my first Technical Video in SQL Server 2014 “How we can crate Clustered Columns Store index on all tables by Single Click” this the Description of the video

Download Scripts : http://sqlserver-performance-tuning.n…

 
Leave a comment

Posted by on March 1, 2015 in General topics

 

Tags: , , ,