Showing posts with label SQL Development. Show all posts
Showing posts with label SQL Development. Show all posts

Monday, April 4, 2016

SQL Server 2016 - Query Store

Performance tuning is a constant topic of discussion, also one of the top priority items when it comes to good code development, better performing queries provide quicker response times, users/customers are satisfied. There have been constant improvements in the area of performance tuning features in SQL Server, also in the later releases of SQL Server, the execution plans were greatly improved. It is very important for developers to understand execution plans so that they improve queries that cause bottlenecks in the application. One of challenges have been to effectively store execution plans and queries so that they could be reviewed for bottlenecks and improve them. In SQL Server 2016 the concept of query store has been introduced. This Query store feature helps one to gain insight into the query plans and statistics.
In order to enable query store feature in SQL Server 2016, one can use Transact-SQL:

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

The query store feature in SQL Server 2016 can also be enabled through SQL Server Management Studio, by choosing the database properties/query store page.
Quoting from Microsoft MSDN Page: I am listing a couple of scenarios for using query store feature:
  • Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
  • Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
The MSDN link for Query Store is listed below: The page below provides a more completion explanation of query store.

Friday, November 29, 2013

SQL Server Management Studio - Project

One of the features I like in sql server management studio is when the Solution Explorer got added. This is very similar to the one in Visual Studio, one of the nice things about this is the ability to keep things organised. When working with sql server Management studio and one is on a large project/being dba is to use multiple query files. Even though one maintains the files in folders, using the solution explorer can keep things organised. The solution explorer allows one to maintain the important server connections and queries all in one place. One of the options I noticed recently in SSMS is what you see in the image below: When you click on the File Menu option, at the end very of the list one can see a Move Option:

 

The Purpose of the Move option is to take your current active query file and move it one of your active projects in solution explorer. Once you choose the project the query gets added to the project in solution explorer. One of the nice things about this option is that it allows to quickly place the .sql to the project instead of going to the solution explorer and adding it. I have highlighted the query file after getting placed in the project.