Mustafa EL-Masry SQL Server 2016

SQL Server table partitioning – planning and implementation guidelines


Nowadays, the big environments that have large databases with a heavy amount of transaction they had one common performance issue in the OLTP transaction when the database size increased to the hundreds of gigabytes or more it will be difficult to load data.

Microsoft SQL Server provides us table partitioning to make this OLTP operation more either and manageable. Applying the partitioning on the large table it will split the table and its indexes into the smaller partition, at this time the maintenance procedure can be applied to an exact partition instead of doing it on the whole table. Additionally, the SQL Server engine will direct the filtered queries to the suitable partitions instead of the entire table. All of this will enhance the performance of OLTP transactions significantly. I will list Commons steps for database migration, including the following:

  • Concept of table partitioning
  • Why we need to implement Table Partitioning
  • Table partitioning limitation and restriction
  • Table partitioning type
  • Table partitioning dependence
  • Table partitioning implementation
  • Conclusion
  • Useful links
Continue reading “SQL Server table partitioning – planning and implementation guidelines” →
Mustafa EL-Masry #Azure, Azure Database, SQL Server 2016

SQL Server Stretch Database to Azure


One of the interesting feature announced in SQL Server 2016 and later to move your cold data to Azure and the data will be accessible from your APP with ZERO development cost no change in the APP level at all and no change on the Query level in SQL Server it is just configuration and implementation for Stretch Database from on-premises to Azure, IF you are DBA and you have tables with big size and you need to reduce it, if you have Cold data and the APP not accessing it frequently, If you need to reduce the backup and restore time at this time this feature for you.

Before starting on the benefits of this feature and How to implement it I highly recommend you to take a look into this page to learn more about Azure SQL https://lnkd.in/edn6nyY/#AzureSQL

  • Benefits from SQL Server Stretch Database
  • How to Configure the SQL Server Stretch Database to Azure
  • Validate the Database Stretch configuration and Data movement
  • How to Disable the Stretch data
  • References
  • Keep Following
  • Cloud Tech Website blog survey
SQL Server Stretch Database
Continue reading “SQL Server Stretch Database to Azure” →
Andrew Jackson Errors, SQL Server 2012, SQL Server 2014, SQL Server 2016

SQL Server Torn Page Repair: Without Worrying About Data Loss or Corruption


It can often be bugs and errors in fetching data from the server. This can leave system administrators baffled as all work comes to a standstill. Incomplete or incorrect transaction cause confusion among employees working on the different sections of a database.

One such frequent issue is the SQL Server torn page error. It can be quite a hassle for a non-technical user facing such a situation. There are ways to fix it easily without worrying about any data loss. This post covers all there is to know about this topic. Continue reading to find out more.

What is the Torn Page in SQL Server?

It is the inability of the server to fetch a particular data during a transaction. It is caused when an Input/Output header tries to access a page that was written incorrectly to the disk. It reports a message saying ‘I/O error (torn page) detected during read’. The reason for this can be primarily contributed to power failure causing partial writes. Other factors include damaged disk or other hardware to which data is being written to. If a torn page is detected by SQL Server, it will sever all connections as the requested data is inaccessible. During the restore process, detecting a torn page sends the database into SUSPECT mode.

sql server torn page repair

Continue reading “SQL Server Torn Page Repair: Without Worrying About Data Loss or Corruption” →

Ayman Elnory General topics, Performance MSSQL, Query Store, SQL Server 2012, SQL Server 2014, SQL Server 2016

Query Store for Solving Query Performance Regressions


Query performance is a very important area of SQL server. We always have badly performance queries around.

Query store is the newest tool for tracking and resolving performance problems in SQL server.

In this article, we are going to have a look at some practical uses of SQL server Query store.

What is Query Store?

The query store has been described by Microsoft as a ‘flight data recorder’ for SQL server queries. It tracks the queries run against the database, recording the details of the queries and their plans and their runtime characteristics. Query store is per database feature and runs automatically. Once turned on, nothing further needs to be done to get it to track data. It runs in the background collecting data and storing it for later analysis.

Query store is available in SQL Server 2016 and later, and Azure SQLDB v12 and later. It is available in all editions of SQL server, even in Express edition.

How is Query store different from other tracking options?

We have had query performance tracking for some time though in the form of dynamic management views. Mostly, sys.dm_exec_query_stats and sys.dm_exec_query_plan and tracing tools like SQL server profiler and extended events.

So, what makes Query Store different? Let me start answering that by describing a scenario that I encountered a couple of years ago.

A particular critical system was suddenly performing badly. It had been fine the previous week and there have been no extended events sessions or profiler traces running historically. The admin had restarted the server when the performance problem started, just to make sure it was not something related to a pending reboot.

As such, there was no historical performance data at all and solving the problem of what happened, why the query performance is different this week was extremely difficult.

1

Continue reading “Query Store for Solving Query Performance Regressions” →

Ayman Elnory Administration, Configuration, Performance MSSQL, problems, SQL Server 2014, SQL Server 2016, SQl server Administration

Improve SQL Server Performance with Compression


SQL Server data compression is now available in all editions of SQL Server, starting with 2016 SP1.

In this Article, you will not only know how data compression will save space, you’ll also find out how compression can sometimes improve performance as well.

Space Savings vs. Performance

When I first heard about compression back in 2008, my first thought is that it would have a big performance penalty. Compression would save disk space, but it would probably decrease performance as the data was compressed and decompressed. It turns out that compression can improve performance instead. Because compressed data fits in a smaller number of data pages, there are decreased I/O requirements. Since I/O is generally the bottleneck in SQL Server, this can improve performance. Compressed data also has a decreased memory requirement. When querying compressed data, a smaller number of pages will be copied to the buffer pool. The one area that is impacted is CPU. You do need to have some CPU headroom because compression will require some additional CPU resources.

a1

The good thing is that if the workload is reasonably tuned, many SQL Server instances have more CPU resources than they need. One note of caution here. Don’t look to compression as the solution to solving major performance issues. You need to look at physical resources, configuration, indexing, and query tuning. The point I’m trying to make is that decreased I/O and better memory utilization will benefit the workload in many cases.

Continue reading “Improve SQL Server Performance with Compression” →