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

Monitoring SQL Server Performance using Query Store

Query Store is a new functionality introduced since SQL Server 2016, I really love this.

What is Query Store: SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes.

Why I have to use Query Store: Quey store automatically capture a history of queries, plan, and runtime statistics, and retain these for your review. well if you want to choose the hard path to solve performance issues then don't use query store.

How to use Query Store: Well I like this question, I will try to explain whatever I understood.

1. Go to SQL Server Mangement Studio
2. Object Explorer, right-click on a database, and select properties
3. From Database Properties window select Query Store Page
4.  From Operation Mode( Requested) Select read write or read only

    *** you cannot enable Query Store for master and tempdb
After enabling Query store, go to required Database --> Query Store

Query Store will log information about each query including:
1. Number of executions
2. execution time
3. Memory consumption
4. Logical Reads
5. Logical Writes
6. Physical Reads
7. Number of execution plan changes

To reduce the load on the server, this information is aggregated into a fixed window. If you need more precise data, you should look to Extended Events.

Now open regressed queries view. you will see a similer window like below.

This tool will allow you to see regressions based on any of the recorded metrics. If you see a regression, you have the option to force SQL Server to use an older execution plan.