Improve SQL Server performance using profiler and tuning advisor

Lets start with point number 7 How can we improve SQL Server performance by using Database engine tuning advisor SQL Server performance largely depends on how you have created the index i.e your cluster index and non cluster index etc Most of the times developers do not even think about indexes By default SQL Server creates a cluster index on the primary key and that's a only index you will find in most database by default non cluster index if you have not given thought of index or indexes I'll say non cluster indexes far away When these default settings are taken to production It doesn't workout for a long period of time or it doesn't workout as your work load increases As a work load changes over a period of time. In other words as rows increase these indexes are not able to cope up and your SQL Server performance starts degrading In other words as time passes by on a production server would like to rethink on your indexing strategy This rethinking of indexing strategy its not so simple. In other words its not just going and right clicking and recreating the index or something Its a bit of a complex processes Its a 3 step process The first thing you have to do is you have to collect the work load in other word when you deploy your indexes or when you create a indexes first time must be the work load was 10% now the work load has gone 80% First you would like to collect What is the amount of work load you have Then you would like to analyse that as per this workload are the current indexes or the default indexes are they appropriate If they are appropriate then its fine must be you have to hunt around something else to find that where the SQL performance can be improved If they are not then we would like to update the indexes as per the work load. In other words its a three step process first you collect the work load Second you analyze if the indexes are appropriate as per the work load and then you update your indexes Both of these activities i.e collection of the work load as well to analyse if the indexes are appropriate as per the work load cannot be a manual process You would like to use some kind of tool for this In case you are working on a database which has thousand of tables and lot of store procedures and it has lot of transactions its humanly not possible to go and analyse that workload and come up with accurate index plan. For the same what SQL Server has done is it has provided two tools one is a SQL profiler and the other one is tuning advisor The SQL profiler will help us to automate the collection of the work load while the tuning advisor will help us to take the work load which is been gathered by the SQL profiler and come up with appropriate indexes Lets do a small demonstration practice here and in this demonstration we will pickup a database and we will pickup couple of tables from the database First collect the work load by using SQL Server profiler and then we will run the tuning advisor on the work load to see that if the indexes are appropriate or not We will see how the performance improves after using the tuning advisor In order to demonstrate how to use a profiler as well as the tuning advisor Here is a simple sample we are going to do I have customer database here and this customer database has two tables here one is a tbl_Company and the other one is the tbl_Customer The tbl_Company has approximately 2974 records it has while the tbl_Customer has 5 or 6 records Both of these tables don't have indexes for example if I go to tbl_Customer and if I see for the indexes currently there is no index for now The same for tbl_Company currently there is no index for now if I go to the design and if I go to the indexes currently there is no indexes on both of these tables One of the tables has i.e a tbl_Company has a approximately 3000 records and tbl_Customer has 5 records On these two tables following kind of SQL queries are fired from a application From a ASP.NET application or from a C# application sometimes the tbl customer is searched by customer Id the tbl_Company is searched by ComapnyId and sometimes tbl_Company is also searched by company id as well as the company code This is like these are the frequently fired select statements on these two tables tbl_Company is having approx 3000 records while tbl_Customer just has 5 records We will use this select nature and we will generate a work load file using the SQL Server profiler We will fire this select statements at the back end we will run the profiler and we will first generate a work load file and then Once we generate the work load file we will take that work load file and then we will use the tuning advisor to see what kind of indexes it suggests us Lets run the profiler here click tools -> click on SQL Server profiler and click on connect Before I go and run this tool let me talk about what exactly is a SQL Server Profiler SQL Server profiler is a nice tool which helps us to capture what kind of SQL statements are fired on your SQL Server When I click on run it has started capturing lot of events here it has started capturing lot of activities which are happening on the data base. For example its capturing some SQL Statements which are getting fires on report Server. There are some SQL statements which are notificationing SQL statements etc In other words SQL Server Profiler is like a listener tool which sits at the back end and quietly hears you what kind of SQL statements are getting fired on your database This tool is heavily used when you want to debug your application when you want to know what kind if select query is fired and what kind of data is been sent to the select queries to your SQL queries We are running this tool but there is a one big problem here It is capturing lot of SQL statements which are probably not relevant to us. For example you can see that it is capturing SQL statements which are fired on report server. It is capturing some SQL statements which are fired on like for the notification services must be for the lock purpose etc Our main goal is we want to see how we can improve performance on the customer database We are not really interested in hearing whats happening in the report server or We do not want to capture the work load which is not relevant to our tuning currently I want to hear SQL statement which are getting fired on customer database and I want to take those SQL statements as my work load and then do the fine tuning In other words I would like to remove this noise over here which is not relevant or it would not add any value for my tuning activity In order to remove this noise put a filter here Go back again and fire the SQL profiler and this time let me put up a filter There goes my SQL Server profiler connect Because our activity is more relevant towards tuning we will not select the standard default template but we will select the tuning template This tuning template is provided by the SQL server itself If you click on the tuning template it basically listen to the SQL statements and the RPC statements First select the tuning template and second put a filter. Go to columns filter here And say that I am just interested to hear SQL statement which are fired on customer tables It will no more here to other databases like report server or any other database But it will only capture SQL statements which are relevant to the customer database Fire the SQL statements which we had written My SQL server profiler has captured the load over here If you note here he has only captured the select statements which are fired on the customer database because we have put a filter on them Save this work load and then run the tuning advisor on the work load to get suggestions So stop the profiling and save this work load file In order to save this work load file you have to create it as a trace file. You can see there is a file ovre here save as and trace file Give a nice name to this called as Optimization The next step is I have to run my tuning advisor on the work load file click on tools -> click on database tuning advisor and provide your work file over here Browse this and select this optimization.trc which I had recorded just now Because I want to run this optimization on my customer data base so I am going to select that database At the top there is a button called as start analysis hit on it There is some kind of error here let me click on the error what it is saying is that it need some extra space basically to run your tuning advisor Go to the tuning options and go to the advance options and further recommendation for the max space I am just providing 5 MB over here Re-run the analysis. Hit on start analysis again There is our analysis and recommendation How much percent you can improve if you implement this recommendations It has given out one recommendation here Your performance will improve 34% if you are going to implement this recommendation In order to see what recommendation is Click on the definition link and you can see the SQL preview of the recommendation if you create a non cluster index on the company code and the company id Your performance will improve like 34% In our sql load which we have given. We had one select query which had the combination of company id and company code By looking at that combination it has given you a recommendation of creating a non cluster index on both of these columns Now the next question is what happened to our select tbl customer It give recommendation to tbl company But for tbl customer it has not given any recommendation One point you need to remember is that the tbl customer had very less number of records Let me show you what he thinks about tbl customer Click on the progress tag here There is a small statement which is written here The tbl_Customer is very small. You can see the reason is the statement reference to only small tables. For small table there is no point giving in index suggestion because indexes uses b tree structure or your balance tree structure which is far complex than doing a table scan for small tables Currently whatever is the scenario is good Creating indexes or complicate the matter further You can see how it has given recommendation also how it has given reasons for where he has not given the recommendation I hope you liked this video In this video we looked into how to create a trace file or work load file by using the SQL Server profiler On the SQL Server profiler we basically ran the index tuning wizard or the database tuning wizard to see how it gives out recommendations and we can apply those recommendations to improve our performance and will keep continuing the SQL Server performance tips in this section as we go ahead Thanks a lot

Channel: .NET Interview Preparation videos

Views: 307,230

Rating: 4.899437 out of 5

Keywords: SQL server, .NET interview questions, programming, csharp, Learn SQL Server, sql server, sql server tuning, profiler, sql profiler, sql server tutorial for beginners, sql basics, performance Tuning advisor, performance using profiler, capture sql statements, use SQL Profiler, How to use SQL server Profiler?, questpond, sql profiling, sql tutorial step by step, write sql query, structured query language, sql server profiler tutorial, sql step by step, sql performance

Id: AaPaIVI-yyI

Length: 13min 12sec (792 seconds)

Published: Wed Feb 23 2011