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

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)

• Backup index in SQL Server with covering Special Cases

any DBA he should care about the performance of his SQL SERVER CLUSTER so he should take care from database index and do maintenance for it Weekly monthly according to his environment , sometimes we decided to deleted UNUSED index to fast the OLTP transaction so before you are going to take this action you should have the rollback this one of the success key ” ANY ROLL-ON SHOULD HAVE ROLLBACK ” and by this DMV you can take backup for multiple cases :

• backup all index on all tables Exist on Database.
• backup all index in one table.
• backup Special indexes in one table or Multiple table.
• backup Special indexes in one table or Multiple table with all index in other tables.
• backup index with option replace table name { we can use it if we need to create index on table XX and this index Exist in table X )

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

• Create Column Store index on all Supported tables in SQL SERVER 2014

this the time of the upgrade to SQL SERVE 2014 before any thing you should check this link because it is big repository for all our Community posts in SQL SERVER 2014 (GO HERE) WHO can use this DMV ? the guy who is going to upgrade to SQL SERVER 2014 , and he need to change all his tables to Clustered Column stored index if it supported or he can use it for another propose before you should go for SQL Server 2014 you should now the PROS and CONS of this new version and test it to know is it compatible with his requirements or now so he should create test environment and do what he need at this time we can use this DMV  in the Conversion process from Clustered or Nonclustered index to CCI (Clustered COlumnStore index)

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

• Filling all your tables with test data

very useful and helpful stored procedure really it help me more in my daily work and any DBA or DB analysis work in database performance tasks one of this tasks is any SP or view or Function should be assessed before going to production to be absolutely sure the SP working at least 80 % fine under 200 Concurrent user (Stress test) to do this operation your tables should have enough data like the production at this time you can use this DMV to fill your tables by what any numbers of records (TEST DATA).

DMV covered all the cases :

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)

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

Follow the author:

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