Performance Tuning Interview Questions Sql Server

• Performance Tuning Interview Questions Sql Server Download
• Sql Server Performance Interview Questions

Monitoring and tuning databases for performance. Microsoft SQL Server and the Microsoft Windows operating system provide utilities to view the current condition of the database and track performance as conditions change. There are a variety of tools and techniques you can use to monitor Microsoft SQL Server. Monitoring SQL Server helps you.

I have published SQL Server Interview Questions and Answers on many topics. Visit attached link to get published on this website on different topics. Here, I am going to publish SQL Server Alwayson Interview Questions and Answers.SQL Server Interview Questions and Answers on AlwaysonQuestion- How Always ON Availability Group is different from Database Mirroring?Answer- Always On Availability Group is advanced version of Database Mirroring. We cannot configure database mirroring for more than one database per configuration whereas we can group multiple databases as one availability group and do the failover as one unit in AOAG.Question- How many Replicas we can create in an AOAG Configuration? Question-How many databases can be added in an AOAG configuration?Answer- The actual number of databases you add to the AOAG depends on the available worker threads on the server, as such there is no enforced limit for databases as well.Question- I have an AOAG configuration between an FCI and standalone server. Why AOAG is not able to perform automatic failover?Answer- Automatic Failover is not supported for Availability groups that are configured on Failover Cluster Instances.

Question- Can we configure AOAG without windows cluster?Answer- We can create clusterless AOAG in SQL Server 2017 but rest of all previous version you must have windows cluster to create AOAG.Question- Can we restore a database participating in Always on Availability Group?Answer- No, we cannot restore a database that is participated in AOAG. Neither we can delete or drop these databases.Question- How can we Restore a database that is participated in AOAG?Answer- To Restore a database that is part of Availability Group, first we need to remove it from availability group. Once database will be removed from availability group, you can restore it and later you can add it to the same availability group. Read attached article to learnQuestion- What will be the difference between configuring AOAG in multi-subnet network and same subnet network?Answer- When we need to configure AOAG between multi-subnet cluster, we need to have one IP from each subnet to configure Listener whereas we need only one IP for listener in same subnet.I hope you like this set of SQL Server Interview Questions & Answers on Alwayson Availability Group. Please follow our and handle to get latest updates.Read More Interview Questions:.

LIKE IS SARGable, How to write better Where Clause –II. Let’s Check?SQL Server has many functions that can be used in your WHERE clause and in the SELECT clause. Now these functions can be user defined or inbuilt. These functions normally provide functionality which would be very difficult to get without these functions.Now when these functions are used improperly in the WHERE clause these functions can cause major performance issues. Now very few SQL functions are SARGable and most of them are NOT SARGableNow what is SARgable?It is Search Argument able. It’s the ability of the query optimizer to use indexes.Let’s see an example that shows using a function in the WHERE clause can affect performance. Add actual execution plan and statistics IO ON for better understanding.Connect to AdventureWorks2012 & create a NonClustered Index on Person.Person(FirstName).

Now we are finding all the persons having name start with Letter “K”. Let’s check two queries to get desired results and their execution plan.-SET STATISTICS IO ON/. QUERY 1./SELECT FirstName FROM Person.PersonWHERE LEFT(FirstName,1) = 'K'/. QUERY 2./SELECT FirstName FROM Person.PersonWHERE FirstName LIKE 'K%'-First let’s check out the messages tab. Both the above queries are working fine and we got 1255 rows in both the case. Now let’s check the logical reads (1 Logical Read = 8KB Pages) for both the queries.

First query is reading 66 pages and the second query is reading only 7 pages. It is clearly evident that second query is performing much better than the first one.Now let’s look out the execution plan for both the queries.We can clearly check out the Query 1 is having Index Scan means reading all the records of the table from leaf level and it is taking 91% of the cost. On the other hand second query is having nonclustered seek and taking only 9% of the cost. Now if we use second query then our system can become at least 10% more scalable; which is a very good thing.SummaryWhen we are checking things for performance always try to find out these small things. These can help you queries performance greatly. We are reducing I/O by reading less data pages. So by not using functions in the WHERE clause we can provide big performance gains.

We just have to tweak where clause. That should be easy.That’s all folks; I hope you’ve enjoyed learning about Like IS SARGable, How to write better Where Clause –II and I’ll see you soon with more “Performance Tuning” articles.Thanks!Pawan Kumar KhowalMSBISKills.com. CAST IS SARgable, How to write better Where Clause. Let’s Check?SQL Server has many functions that can be used in your WHERE clause and in the SELECT clause. Now these functions can be user defined or inbuilt.

These functions normally provide functionality which would be very difficult to get without these functions.Now when these functions are used improperly in the WHERE clause these functions can cause major performance issues.Now very few SQL functions are SARgable and most of them are NOT SARgable.Now what is SARgable?It is Search Argument able. It’s the ability of the query optimizer to use indexes. Well we can tweak where clause of our queries little bit so that optimizer can use indexes.Let’s see an example that shows using a function in the WHERE clause can affect performance. Add actual execution plan and statistics IO ON for better understanding.Let’s say we have a table called CustomerInfo with around 20K records.

This Customerinfo table has 3 columns named (CustomerID, CustomerName, and ModifiedDate). This table also has 2 indexes which are given below- indexnameindexdescriptionindexkeysIxModifiedDatenonclustered located on PRIMARYModifiedDatePKCustomerclustered, unique, primary key located on PRIMARYCustomerIDNow let’s say we wanted to find out below all modified dates when ModifiedDate = ’′. So we wrote the below query-SET STATISTICS IO ONSELECT ModifiedDate FROM CustomerInfoWHERE CONVERT(VARCHAR(10),ModifiedDate,101) = '-Now the above query is perfectly fine, it’s give us data that we wanted but the problem here is that we are not using any indexes effectively here. It is reading all the pages from the leaf level of the tree. Let’s check out the execution plan.In the below execution plan we can clearly see that we are doing nonclustered scan of complete leaf pages even we have index on this columns.So this is not good. Now let’s rewrite this query in a different fashion and check its execution plan.-SET STATISTICS IO ONSELECT ModifiedDate FROM CustomerInfoWHERE CAST(ModifiedDate AS DATE) = '-Now as per the execution plan we are doing index seek which is a better approach in this case.

But here we are doing some other physical operations like Nested Loop, Compute Scalar and Constant scan. Although these operators are not taking any cost but the question we have right now is can we remove these extra physical operators.Let’s rewrite this query in some other fashion and check out its execution plan.-SET STATISTICS IO ONSELECT ModifiedDate FROM CustomerInfoWHERE ModifiedDate = ' AND ModifiedDate. Update Statistics – Synchronously and asynchronously.

Which one is better?We can Update Statistics synchronously and asynchronously.Default is SynchronousHow we can enable this option-ALTER DATABASE DatabaseName SET AUTOUPDATESTATISTICS ONALTER DATABASE DatabaseName SET AUTOUPDATESTATISTICSASYNC ON-Synchronous Case-In this case SQL Server first update the statistics ( Of course if they were outdated ) and then execute the query. So depending on the table size and the other processes running on the server update statistics may take some time and slow down you query execution. In this case your query has to wait till the time update statistics operation is not completed. In this case all the queries current and future will get the benefit of the statistics update.Asynchronous Case-In this case SQL Server first execute the query and then update the statistics ( Of course if they were outdated ). In this case your query will NOT have to wait till the time update statistics operation is not completed.

In this case statistics update are handled by a background process. The query will not get the benefit of the statistics update, however future queries will.Note 1- Enabling the async option affects will not allow yout to put a database into single-user mode. Why we should update statistics? When one should update statistics?First let’s discuss why to update statistics?If we do not update statistics then we will get bad query execution plan. Statistics are necessary for best query performance.

Statistics essentially “describe” the data what is there in the index. SQL Server Query Optimizer uses statistics to estimate the distribution of values. Statistics helps query optimizer in making better decision making.When to update statistics?The best answer for this is we should update statistics regularly.We should keep them updated using Auto Update Stats = true. If auto stats update is enabled then updates happen based on number of changes to the data.

This is sometimes not enough.SQL Server has index and column stats. We can use Update statistics to update stats on an index and on columns whenever you want using Update Statistics command.On larger tables we should run update statistics on a schedule to refresh stats regularly. Otherwise we will land up in problem as your query is running slow.Rebuilding indexes will also updates statistics. This process normally happens as a weekly maintenance task. Often when weekly index rebuild happens things works very fine on Monday and Tuesday.

Now on Wednesday we will start getting issues like this query is working slow and stuff like that. Now our query works fine on Monday and Tuesday because of updated statistics.spupdatestats – It runs Update Statistics against all User defined tables and internal tables in the current database. Well this is not recommended.We can update statistics synchronously and asynchronously. Default is synchronously.

Here when SQL Server tries to execute a query and before executing SQL Server will check the threshold value against the # of changes happened in the table and if the threshold has crossed than SQL Server first update the statistics and then the query will be executed. So depending on the table size and the other processes running on the server update statistics may take some time and slow down you query.In asynchronous case the query will be executed first and then the update statistics will happen. In this case the benefit is that our query will not have to wait for update statistics to happen. The disadvantage of this case is that our query may get executed with less qualified execution plan as the statistics are not up to date. Most of the cases people don’t ON this feature.That’s all folks, I hope you’ve enjoyed learning about why and when we should update statistics, and I’ll see you soon with more “Performance Tuning” articles.Thanks!Pawan Kumar KhowalMSBISKills.com. Avoid Using Function in WHERE Clause. Why?SQL Server has many functions that can be used in your WHERE clause and in the SELECT clause.

Now these functions can be user defined or inbuilt. These functions normally provide functionality which would be very difficult to get without these functions.

Performance Tuning Interview Questions Sql Server Download

Now when these functions are used improperly in the WHERE clause these functions can cause major performance issues. When these same functions are used in the WHERE clause this forces SQL Server query optimizer to do a table scan or index scan to get the desired results instead of doing an index seek if there is an index that can be used.The reason for this is that SQL Server has to call the function for each value of the column and match that with the matching criteria.Some examples are given below that shows using a function in the WHERE clause can affect performance. Add actual execution plan and statistics IO ON for better understanding.Let’s say we have a table called CustomerInfo with around 20K records. This Customerinfo table has 3 columns named (CustomerID, CustomerName, and ModifiedDate).

This table also has 2 indexes which are given below-indexnameindexdescriptionindexkeysIxModifiedDatenonclustered located on PRIMARYModifiedDatePKCustomerclustered, unique, primary key located on PRIMARYCustomerIDNow let’s say we wanted to find out below all modified dates when ModifiedDate = ‘1947-08-29’. So to satisfy above query we wrote below query –-SELECT ModifiedDate FROM CustomerInfo WHERE ModifiedDate = '1947-08-29'-But above query will not return anything since the Modified Date column is of type DateTime. So we have to modify our query. So we came up with a new query.-SELECT ModifiedDate FROM CustomerInfo WHERE CAST(CONVERT(VARCHAR(10),ModifiedDate,101) AS DATETIME) = '1947-08-29'-Now let’s check the execution plan of the above query. Now with the below execution plan we can clearly find out 2 bad things. One is that we are having NonClustered index scan and the other thing is that we have a warning due to column data type conversion.

Sql Server Performance Interview Questions

The culprit here is the functions we are using in the where clause.Now let’s modify the above query and check out what happens. Here we are getting Index seek.-SELECT ModifiedDate FROM CustomerInfoWHERE ModifiedDate = '1947-08-29' AND ModifiedDate.