How to avoid Parameter Sniffing issues.

How to avoid Parameter Sniffing issues.

On my previous post we saw in detail about what is parameter sniffing and what are all the impacts it could make on a execution plan.  Regarding the solutions to solve it.

@symbol

There is No one size fits all. Analyze the options and choose the best one

 First i would like to list down the possible solutions to handle parameter sniffing and then will each one with a nice demo.

  • Use the option with(recompile)
  • Disable parameter sniffing
  • Use the option with OPTIMIZE FOR query hint.

Post Source : batchahaja.wordpress.com

Spread the word

SQL Server DMV Replication Monitoring Part 2

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

Source  post : www.MostafaElmasry.WordPress.com

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

SQL Server DMV Replication Monitoring Part 1

SQL Server DMV Replication Monitoring Part 1

 

 

 

 

 

 

 

cloud-monitoringHello 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 :

 

 

Source Post : SQL Server DMV Replication Monitoring Part 1

DMV Replication Monitoring Series  1     2

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 :

Spread the word

Execution Plan troubles caused by Parameter Sniffing.

Execution Plan troubles caused by Parameter Sniffing.

Hi Guys,

On my recent series of posts, we have seen so many details about understanding and solving issues on Execution Plans. For one and all, if you are about to improve the performance of query, you should know what are all the things going on behind the scenes. SQL Server execution plan is a wonderful area to be concentrated.

Execution Plan Series  1     2      3       4       5

One of the performance killer for your is the Parameter Sniffing, which will be discussed in detail on this post. Here below is the simple phrase to understand parameter sniffing.

@symbol

The Elephant and Mouse should be treated differently, to provide the space for them.

How you will plan if someone asked to you plan a place for Elephant and Mouse on a zoo, of course you will have two different plan. If you have the same plan then it will be troublesome. The SQL Server query optimizer will produce the same execution plan for a stored procedure from cache for different parameter values, which is called Parameter Sniffing. That is why i told the Elephant and Mouse should be treated differently.

Post Source : batchahaja.wordpress.com

Spread the word

SQL Server Administration skills jump start (21/100)

SQL Server Administration skills jump start (21/100)

 

 

 

 

 

 

 

 

By the last blog , we started the most important step of any DB administration platform which is DB backup policy configuration however speaking of ITIL concept , backup policy help ONLY to guarantee warranty (fitness for use) terms of any SLA or OLA for your DB consulting  services however probably your DBs doesn’t meet the requirements of Utility (fitness for purpose) agreements because of its performance is not scalable enough to afford inflated end users transactions so definitely you need to perform other sorts of DB administration tasks to maintain your DB health and keep intact and strong enough to confront inflated end users transactions , this can be done through the next 4 steps :

2-      Index rebuild

3-      DB log file shrinks

4-      Statistics update

5-      Integrity check

DB Administraion2

Spread the word

How to make a table backup for group of tables ?

How to make a table backup for group of tables ?

Many of us working with SQL Server used to do table backup at some situations, specifically before making any correction on the data inside the table.  Also it was very good practice before you run a DML query on a table, by which you will have very easy to make your rollback, just rename the backup table as the original table.

We use the simple below method.


Select * into [Person].[Address_bak] from [Person].[Address]

Now we will see, what we should do if need backup all the table name containing Employee from AdventureWorks2012 database. You can replace anything ‘LIKE ‘%Employee%” instead employee to take the backup of those table. Just you have to execute the below query and take result of the below and execute them on the another window.

Now we will see, what we should do if need backup all the table name containing Employee from AdventureWorks2012 database. You can replace anything ‘LIKE ‘%Employee%” instead employee to take the backup of those table. Just you have to execute the below query and take result of the below and execute them on the another window.

images
SELECT
'Select * into '+name+ '_bak from ' +name+ ' GO '+
'Print '''+name+ '_bak Done'''
FROM sys.tables WHERE name LIKE '%Employee%'

The above query will give you something like below, needs to be executed on another window. That is it, you are done.

Queryresult


 

Post Source : batchahaja.worpress.com

Spread the word

SQL Server Administration skills jump start (20/100)

SQL Server Administration skills jump start (20/100)

 

 

 

 

 

 

 

 

By the last blog , I concluded this subject of DB migration /consolidation from Server A to Server B after I have taken it along with 5 consequential blogs but still not fair enough for any migration or consolidation process coz what I was talking about along with those 5 blogs is just how to clone the basic server and DB configurations from Server A to Server B but still server B is not FULLY administrated that is why my focal task for the upcoming blogs of this series is ONLY about how to administrate your Server B by the best way , right here is the basic elements of any professional DB administration platform :

1-      DB backups (Full, differential and transaction log )

2-      Index rebuild

3-      DB log file shrinks

4-      Statistics update

5-      Integrity check

6-      Server audits

7-      Database audits

8-      SQL Server alerts (Warning and critical)

9-      PBM (Policy Based Management)

10-   UCP Monitor

11-   Custom DMV Monitors

12-   Resource Governor

This indeed represents the pocket guide for any DB Administrator while his cruise inside database campus coz I have summed up the most important DB administration tasks that meet the desired criterions  of utility  (Fitness for purpose ) and Warranty (Fitness for purpose) for any agreed SLA  (Service level agreement) with customer ) or OLA (Operational level agreement ) with other departments inside your organization) and I can possibly say that your odds of success as DBA are stacked in the favor of understanding well this pocket guide and practicing it many more times , here is to your success now as DBA

Spread the word

Secrets of SQL Server Execution plan (5/5)

Secrets of SQL Server Execution plan (5/5)

plan Hi guys, so for we have seen so many interesting things regarding the SQL Server Execution Plans. Regarding the execution plan on SQL server it was not a matter to complete with 5 post series. It should be infinity, i will write a new series so soon. Okay on my previous post we saw more about joins in details. In this post i would like to share something more interesting, there are some situations the execution plan struggle to give a better plan and we can see warning symbols over certain symbols.  The aim of this post is how to eliminate such warnings.

“How to eliminate the warnings on execution plan, how to mitigate them with a better index or a better query design.”

In general we should understand one thing in common, if saw the message once you hover over the warning and saw message that

“Operator used tempdb to spill data” it was the indication that the operator not able to do the operation within the available memory.

Solutions :

  • You may have update statistics out dated make the plan the estimate wrong volume of data. So update statistics may fix the issue.
  • Even though the statistics are updated and the volume of data is still high, use the appropriate filters to minimize the rows.
  • You can come up with a better indexes to solve the issue.
Spread the word

Awesome!! DMV and Stored procedure for any DBA

Awesome!! DMV and Stored procedure for any DBA

 

 

 

 

 

 

 

 

5_Things_You_Need_To_Know_20100924042045_320_240

 

Hello every body today no new post today but i will share some amazing and Awesome!!  DMV and stored procedures 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)

Spread the word

Avoid the performance risk of the Delete Statement

Avoid the performance risk of the Delete Statement

 

 

 

 

 

 

 

 

downloadIntroduction

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. (Original POST)

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 .

imagesSolution :

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 :

Spread the word

SQL Server Administration skills jump start (19/100)

SQL Server Administration skills jump start (19/100)

 

 

 

 

 

 

 

 

By the last blog , I explained the latest configuration on SQL Server level in generic and now I am ending up this interesting subject of DB consolidation by DTC Service configurations which are probably to be used by some transactional applications that look seriously at transactions consistency like BPM (Business Management Process) such as Ultimus and also some Microsoft products like Biztalk , using DTC is coming at the top priority of configurations for those applications regardless of its security and performance constraints coz it has non-appreciable impacts on any OLTP transactions like (insert , update , delete , merge) and performance may vary based on circumstances such as volume of data and concurrent transactions and ports restrictions of DTC service…etc thereby you have to be so careful about using it

Therefore I am gonna zoom more on DTC configurations to articulate interesting details about this regard that can be a full pocket guide to help you out for different sorts of configurations at any environment no matter how much complex its security constraints .

Spread the word

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

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 ,SOURCE POST

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.
Check the Video Create Clustered index on all heap tables by on single click
Spread the word

Shared disks on SQL Server Cluster

Shared disks on SQL Server Cluster

 

 

 

 

 

 

 

Shared disks

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

For SQL Server 2014 this DMV will deprecated so we can use the below DMV

(It  come to me from one of the Comment in the post from Mr.IJeb Reitsma Thanks for him)


Select * from  sys.dm_io_cluster_valid_path_names

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME

Follow up us on

Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel

Spread the word

How many nodes exist in a SQL Server cluster

How many nodes exist in a SQL Server cluster

 

 

 

 

 

 

 

Bild_go-cluster+(S.+34)+-+NICHT+IN+SOCIAL+MEDIA+VERWENDENAny 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

Follow up us on

Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel

Spread the word

Rolling up multiple rows into a single row

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

SQL Server Administration skills jump start (18/100)

SQL Server Administration skills jump start (18/100)

 

 

 

 

 

 

 

 

By the last blog , I explained 2 important configurations sets that should be considered largely while any DB consolidation or upgrade (side by side) projects  , mail profile and linked server ,here I am gonna talk about the last set of configurations on SQL Server instance level , step #8 and step #9 as follows:

8-      Cloning some server configurations like CLR ,CPU parallelism, Replication size , Network packet size , optimize for ad hoc workloads

9-      Cloning all Replications configurations for publishers and subscribers

Spread the word

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

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

 

 

 

 

 

 

 

 

images (1)

Stored Procedure Version Two

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.SOURCE POST


-- =============================================
-- Author: Mustafa EL-masry
-- Create date: 01/03/2015
-- Description: table policy by DMV
-- Tables have Row account >= 7,000,000
-- Tables didn't have Clustered index.
-- Tables didn't have Primary key and didn't have any index.
-- Tables have Primary key but with non-Clustered index
-- Tables with ChangeTrackingEnabled = True.
-- Databases with is_quoted_identifier_on = TRUE.

-- =============================================
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

Follow up us on

Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel

Spread the word

SQL Server Administration skills jump start (17/100)

SQL Server Administration skills jump start (17/100)

 

 

 

 

 

 

 

 

By the last blog , I explained the minimum level of configurations that should be cloned while any DB consolidation or migration project from Server A to Server B which are allowing to perform well business functionalities, now I am turning now into step #6 and step #7 as follows :

6-      Cloning all mail profiles with their mail accounts and adding notifications for SQL Server jobs and alerts

7-      Cloning all Linked server + Aliases might be used inside old DB servers

Spread the word

Keep your database in safe mode

Keep your database in safe mode

 

 

 

 

 

 

 

images (1)

Hi Seniors DBA HI junior  DBA HI DBA Consultant , There is no one without errors or defects if you agree with me Complete the post if not don’t waste your time !, How you can keep your database in safe Mode from any mistake can by happen by any accident ? we should put our database in the safe mode Under any circumstances ,so what you can do if you deleted your database by wrong ! and in the same time you lost your last backup for this

database !! really it is very bad situation and may be not happened put may be happen also, to be calm and to Keep your database in safe mode you should take preventive action one of this preventive action Forcing the Drop database Command or Rename database to rollback and you can do it easily without more effort and without more maintenance cost by using DDL trigger ,  to know more about triggers and DML , DDL and Login triggers go for this link (CREATE TRIGGER (Transact-SQL)) Trigger can be created on tables , Databases and on All Server what we will do now Create trigger on all Server SOURCE POST

Create Trigger 


IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'KeepYourDBinSafeMode')
DROP TRIGGER KeepYourDBinSafeMode
ON ALL SERVER;
GO
Create TRIGGER KeepYourDBinSafeMode
ON ALL SERVER
FOR Drop_Database ,Alter_Database
AS
PRINT 'You must disable Trigger "KeepYourDBinSafeMode" to drop or alter Database!'
ROLLBACK
GO

Check the trigger from Mangment Studio 

Keepyourdatabaseinsavemode

Spread the word

Filling all your tables with test data V2

 

 

 

 

 

 

 

introduction

Hi guys in my last post  (Filling all your tables with test data) i explained how we can fill multiple tables by test data by using My own Stored procedure , but after i creating the script and i do on it more test on our staging environment and it’s  working very well i got  one issues on the script ( SP can not cover the tables with composite primary key )  at this time I reviewed  the Stored procedure from the scratch to know where is the issue and how i can solve it at the end i asked on e of my best friends Mohamed Abdel Kariem mentor T-SQL to help me in this subject and at the end we do awesome script and here the below new enhancement on our main stored procedure . SOURCE POST

Stored procedure enhancement:

  1. SP now supported tables with composite primary key
  2. SP now supported tables with composite primary key with data type uniqueidentifier.
  3. reduced the liens in the SP by using CASE technology
  4. using QUOTENAME function instead of more concat.
All Features in Stored Procedure  :
  1. Filling any tables with any amount of data
  2. covering the identity columns
  3. covering the Computed Columns
  4. covering the tables created in any schema difference than dbo
  5. covering the uniqueidentifier primary Key
  6. covering Primary key with no Identity
  7. covering tables with composite primary key (New in V2)
  8. covering table with special column name like KEY
  9. using QUOTENAME instead of doing concat (New in V2)
  10. using Case when instead of (IF statement ) and by this way we reduced 50 % from the T-SQL (New in V2)
Spread the word

Index Statistics for all Databases Exists on your Server

Index Statistics for all Databases Exists on your Server

 

 

 

 

 

 

 

 

How to return the unused index on all databases? How to save index Statistics? in the previous post i explained How to check the (unused index , duplicated index , missed index) but in one single database yesterday I updated the script of Unused index to can run on all databases exists in the server and by this new DMV I can save the result (index Statistics) in the table because if you do restart for SQL Server or do failover for the cluster you will lose the Index Statistics but by the below steps you can save it easily and return for it in any time to check all My posts and my Friends posts in SQL Server Index GO HERE  , Check the Source Post GO HERE

download

 

 

 

 

 

 

 

 

 

 

Steps for Saving index Statistics:

  • Create Table Index_Statistics on MSDB database

USE [msdb]
GO

/****** Object: Table [dbo].[Index_Statistics] Script Date: 01/02/2015 01:58:31 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Index_Statistics](
 [ID] [INT] IDENTITY(1,1) NOT NULL,
 [Database_name] NVARCHAR(200),
 [ObjectName] [sysname] NOT NULL,
 [IndexName] [sysname] NULL,
 [IndexID] [INT] NOT NULL,
 [UserSeek] [BIGINT] NOT NULL,
 [UserScans] [BIGINT] NOT NULL,
 [last_user_seek] [DATETIME] NULL,
 [last_user_scan] [DATETIME] NULL,
 [UserLookups] [BIGINT] NOT NULL,
 [UserUpdates] [BIGINT] NOT NULL,
 [TableRows] [BIGINT] NULL,
 [drop statement] [NVARCHAR](790) NULL,
 [Execution_time] [DATETIME] NOT NULL,
PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,FILLFACTOR=80,DATA_COMPRESSION=PAGE) ON [PRIMARY]
) ON [PRIMARY]

GO

  • Create Stored procedure Unused index

USE msdb
Go

Create PROC UnusedIndex
AS
BEGIN
SET NOCOUNT ON
DECLARE @name NVARCHAR(200)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
 DECLARE @SQL NVARCHAR(MAX)=N'
Use '+@name+'
SELECT '''+@name+''' As Database_name,
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
,dm_ius.last_user_seek
,dm_ius.last_user_scan
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, ''DROP INDEX'' + QUOTENAME(i.name)
+ '' ON '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(OBJECT_NAME(dm_ius.object_id)) as '' DROP STATEMENT ''
,GETDATE() AS Execution_time
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id
INNER JOIN sys.objects o on dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (
SELECT SUM(p.rows) TableRows, p.index_id, p.object_id
 FROM sys.partitions p GROUP BY p.index_id, p.object_id
 ) p
 ON p.index_id = dm_ius.index_id AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id,''IsUserTable'') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = ''NONCLUSTERED''
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY o.name ASC , I.name ASC '
EXEC SP_ExecuteSQL @SQL
 FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
END

  • Create Job to Execute the unused index (optional)

Because if you create the job I recommended to run it manually not schedule it because our target here to save the index statistics before restart the server or failover it so after this happened I should not overwrite on my data exists in index Statistics table .

Follow the author:

View all my tips , LinkedIn Website Slideshare 

Follow up us on

Facebook Page LinkedIn Group,Twitter,Networked Blogs,Facebook Group,Youtube Channel, 

Spread the word

ONE HUNDRED Technical BLOG POST IN SQL SERVER

ONE HUNDRED Technical BLOG POST IN SQL SERVER

 

 

 

 

 

 

 

images

 

 

Thank God by this end of year 2014 i completed one Hundred blog Post Published on the biggest SQL Server Community in the middle East SQLSERVER performance Tuning and 174 BLOG Post Published on My Community SQL Database Administration   { This blog was viewed about 25,000 times in 2014.} Check Annual report https://mostafaelmasry.wordpress.com/2014/annual-report/  174  technical post in SQL Server in the most important subject in SQL Server like :

  • SQL Server 2014 technology
  • SQL Server 2012 new feature
  • Reporting Services
  • SSIS
  • Database administration
  • SQL Server Performance Tuning
  • T-SQL DMV
  • Database Configuration
  • Database Implementation
  • Disaster recovery cases

ONE HUNDRE Blog Post list :

Spread the word

MVA (Microsoft virtual Academy) courses on SQL Server 2012 , 2014

MVA (Microsoft virtual Academy) courses on SQL Server 2012 , 2014

 

 

 

 

 

 

 

 

Actually guys while I was watching  MVA  (Microsoft Virtual Academy) training courses  , I really enjoyed them and indeed they are much valuable and helpful for may interesting topics and trickier subjects at SQL Server such as BI, clouding, Big data. SQL Server 2014 performance dreams, database fundamentals and SQL Server essentials for  Oracle DBA  ….etc that is why SQL Server performance tuning community believe in them and thus we started to share them also over the community to spread the word and broadcast more those new wonderful SQL Server 2014 and clouding features , you can easily watch them and round them easily and happily in a single page as below.

Microsoft logo1

Spread the word

Create Clustered ColumnStore index for All tables(VI)

 

 

 

 

 

 

 

Problem :

we are working in big project SQL Server 2014 upgrade and SQL Server 2014 Performance this project our role to test the new feature in SQL Server 2014 from the performance wise so we decide  to deploy the new Enhancement in SQL Server 2014 (Clustered ColumnStore index ) CCI so i take backup from one database in my work environment and restored it again with prefix _2014 .

this new type of index to create it on the table this table should be no index on it so i drooped all the index from all the tables but the Problem now How can i create the Clustered columnstore index on all tables( tables supported CCI) by one Click.

Solution :

i do one script to create Clustered columnstore index on all supported tables and here the description of the Script

1- to create CCI on any table we have some limitations one of this limitations the data type of the Columns CCI not supported Some data type.

2- I inserted the data type not supported by CCI in temp Tables

</pre>
CREATE TABLE #CCI_DataType_Limitation ( DataType NVARCHAR(MAX) )
 INSERT INTO #CCI_DataType_Limitation
 VALUES ( 'text' ),
 ( 'timestamp' ),
 ( 'hierarchyid' ),
 ( 'Sql_variant' ),
 ( 'xml' ),
 ( 'geography' ),
 ( 'geometry' )
Select * from #CCI_DataType_Limitation
<pre>
Spread the word

New Permission in SQL Server 2014

New Permission in SQL Server 2014

 

 

 

 

 

 

 

Hi Dears as we know SQL Server 2014 come with more new features and more improvements in the Performance today we will take about the new Feature in SQL Server 2014 in particular in SQL Server security SQL Server 2014 come with three new permission really amazing permission it will help any DBA in his work to can grant simple permission in All Databases in the server in the minimum time this operation it will not take from him one second lets go for the scenario and for the DEMO.

in SQL 2012 and we can see 31 permission whereas SQL 2014 has 34 permissions. These permissions are listed under Server properties >  permissions tab.

New Permission in SQL Server 2014:

  1. CONNECT ANY DATABASE Permission.
  2. SELECT ALL USER Securables Permission.
  3. IMPERSONATE ANY LOGIN Permission.

New permisions

 

Spread the word