How to download SQL Server 2016 / 2017 Free

How to download SQL Server 2016 / 2017 Free:

It's simple please go through below link to download free, nowadays Microsoft giving Developer edition free download, because they love developers and you get all feature whatever you get in the enterprise version, soo cool right, but only one catch you can't use developer for your production environment.

Please find the difference in below table.

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Features	SQL Server 2016 Enterprise	SQL Server 2016 Standard	SQL Server 2016 Express	SQL Server 2016 Developer
Maximum number of cores	Unlimited	24 Cores	4 Cores	Unlimited
Maximum memory utilized per instance	Operating system max	128 GB	1 GB	Operating system max
Maximum size	524 PB	524 PB	10 GB	524 PB
Production use rights	Yes	Yes	Yes	No
Basic OLTP	Yes	Yes	Yes	Yes
Manageability: Management Studio, policy-based management	Yes	Yes	Yes	Yes
Basic high availability: 2-node single database failover, non-readable secondary	Yes	Yes	NO	Yes
Enterprise data management: Master Data Services, Data Quality Services	Yes	NO	NO	Yes
Advanced OLTP: In-memory OLTP, operational analytics	Yes	NO	NO	Yes
Advanced High Availability: Always On Availability Groups, multi-database failover, readable secondaries	Yes	NO	NO	Yes
Basic security: Row-level security, data masking, basic auditing, separation of duties	Yes	Yes	NO	Yes
Advanced security: Transparent database encryption, Always Encrypted	Yes	No	NO	Yes
Advanced data integration: Fuzzy grouping and lookups, change data capture	Yes	NO	NO	Yes
Data warehousing: In-Memory Columnstore, partitioning	Yes	No	NO	Yes
PolyBase2	Yes	Yes	NO	Yes
Maximum memory utilized per instance of Analysis Services	Operating system max	Tabular: 16 GB MOLAP: 64 GB	No	NO
Maximum memory utilized per instance of Reporting Services	Operating system max	64 GB	Express with Advanced Services: 4 GB	NO
Programmability and developer tools: T-SQL, CLR, Data Types, FileTable, JSON	Yes	Yes	Yes	Yes
Basic reporting and analytics	Yes	Yes	No	Yes
Basic data integration: SQL Server Integration Services, built-in connectors	Yes	Yes	No	Yes
Basic corporate business intelligence: Basic multi-dimensional models, basic tabular model, in-memory storage mode	Yes	Yes	No	Yes
Mobile reports and KPIs	Yes	No	NO	Yes
Advanced corporate business intelligence: Advanced multi-dimensional models, advanced tabular model, DirectQuery storage mode, advanced data mining	Yes	NO	NO	Yes
Basic R integration: Connectivity to R open, limited parallelism	Yes	Yes	Yes	Yes
Advanced R integration: Full parallelism ScaleR	Yes	NO	NO	Yes
Hybrid cloud Stretch Database	Yes	Yes	Yes	Yes

If you have any questions, please comment below, I will try to reply back ASAP

Script to check Disk IO

select db_name(database_id) as DatabaseName, file_id,io_stall_read_ms,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms',
io_stall_read_ms + io_stall_write_ms as io_stalls,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by [DatabaseName] desc

How to add additional user to Azure Subscription

1. 1. Login to portal.azure.com 
2. 2. Search by subscription 
3. 3. Select right subscription name 
4. 4. Select Access Control(IAM) 
5. 
   5. Click on Add
6. 
   
7. 6. Select role you want to provide and provide Microsoft email  and click on save
8. 
   
9. 
   
   
10. 7. That all done. Now new user can access portal with provided permissions

How to Enable or Disable Index on Table using SQL

Index Status: 

select 

    sys.objects.name, 

    sys.indexes.name 

from sys.indexes 

    inner join sys.objects on sys.objects.object_id = sys.indexes.object_id 

where sys.indexes.is_disabled = 1 

order by 

    sys.objects.name, 

    Sys.indexes.name 

----------------------------------------------------------------------------------- 

Index Disable: 

use DB 

go 

ALTER INDEX Index_name ON Table_name 

DISABLE; 

-------------------------------------------------------------------------------- 

Index Enable: 

use ACTIVEQUOTE 

go 

ALTER INDEX Index_name ON Table_name 

rebuild

Find most used tables & Most used Indexes & Unused Index & Missing Index Script

--get most used tables

SELECT  

    db_name(ius.database_id) AS DatabaseName, 

    t.NAME AS TableName, 

    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed 

FROM sys.dm_db_index_usage_stats ius 

INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id 

WHERE database_id = DB_ID('ACTIVEQUOTE') 

GROUP BY database_id, t.name 

ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

--get most used indexes

SELECT  db_name(ius.database_id) AS DatabaseName, t.NAME AS TableName, i.NAME AS IndexName, i.type_desc AS IndexType, ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id WHERE database_id = DB_ID('MyDb') ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

-- Get top 25 Unused Indexes

SELECT TOP 25 

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.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' 

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 (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC 

GO

-- Get top 25 missing Index

SELECT TOP 25 

dm_mid.database_id AS DatabaseID, 

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, 

dm_migs.last_user_seek AS Last_User_Seek, 

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' 

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')  

+ CASE 

WHEN dm_mid.equality_columns IS NOT NULL 

AND dm_mid.inequality_columns IS NOT NULL THEN '_' 

ELSE '' 

END 

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') 

+ ']' 

+ ' ON ' + dm_mid.statement 

+ ' (' + ISNULL (dm_mid.equality_columns,'') 

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns  

IS NOT NULL THEN ',' ELSE 

'' END 

+ ISNULL (dm_mid.inequality_columns, '') 

+ ')' 

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement 

FROM sys.dm_db_missing_index_groups dm_mig 

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs 

ON dm_migs.group_handle = dm_mig.index_group_handle 

INNER JOIN sys.dm_db_missing_index_details dm_mid 

ON dm_mig.index_handle = dm_mid.index_handle 

WHERE dm_mid.database_ID = DB_ID() 

ORDER BY Avg_Estimated_Impact DESC 

GO