Ayman Elnory

Author: Ayman Elnory

I am Ayman Elnory from Egypt working as Microsoft Senior SQL Server Database Administrator for more than 3 years. I am Microsoft® Certified Solutions Expert: Data Management Analytics Plus Microsoft® Certified Solutions Expert Data Platform (MCSE) for more information about my certificate check it here http://www.mycertprofile.com/Profile/4455325105 I have in-depth knowledge and experience about all of the new SQL Server technology in SQL Server 2008, 2008R2, 2012, 2014 and 2016 also. As a database administrator, I design, install, maintain and upgrade all databases (production and non-production environments). Provide technical expertise in the use of database technology and is accountable for the overall performance of the enterprise’s database environment. Work closely with analysts and developers to define and resolve information flow and content issues, helping to transform business requirements into environment specific databases. If you have questions or inquiries, feel free to contact me directly on Email: Eng.aymaneltop@gmail.com Phone: +2 0111 8169 111 Linkedin:https://www.linkedin.com/in/ayman-elnory-5172a176/

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.

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

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.

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.