• DBA,  Performance

    Performance Tuning Steps a Query by looking an execution plan and effectiveness of the index creation Part-1

    The index is very important and powerful object in the database. It is an optional and secondary object for the table. It will speed up the data return time by navigating the B-tree structure, like a book’s index. In General, index tuning is important one, putting a right index will speed up the code.  Please read this post for finding symptoms, where you have issue at server or database or query level.  Most of the time, the query will be the issue. Once, you have long running query start tuning it. In this post, I am going to share the usage and effectiveness of index from low level to high…

  • DBA,  Performance

    Reading execution plan sql server use of Plan Explorer

    Plan Explorer I recently started using this. Since it was licensed earlier, now it’s free. This is an awesome tool for query performance tuning. SQL Server Sentry Plan Explorer is the free tool given to SQL community you can download here https://www.sentryone.com/plan-explorer click download plan explorer button with right bit. It will not ask you much for download. Plan Explorer really makes DBAs job easier, this will help to read the bigger execution plans quickly and which code block  taken more time duration, even if it’s 1000 lines code. Once you have installed, open the tool click file –> new and on command text ” select * from sysobjects o…

  • DBA,  Performance

    Performance Tuning step by steps Series – SQL Main Part

      Performance tuning is a tricky part. We need to analysis all points of view. Sometimes it is an easy fix and can be solved in a minute and sometimes it take a day or more to find and fix. I received many emails for performance tuning help. It has a variety of questions, that how can we tune a query and how to find out what is going on in the server, since my application team is does not give a slow running code. The query only slowdown in some time period. My server is banging all time above 95% of resources. Nowadays, every business has a tool that…

  • DBA,  Performance

    Whoisactive capturing a SQL server queries Performance Tuning

      Whoisactive stored procedure is a powerful script to capture the SQL server queries with tons of information. It’s written by Adam Machanic. It makes DBAs job easier. It’s a great script with lots of parameter and I am going to show, which are all more important. Note: It been moved to – http://whoisactive.com/ Run the stored procedure without any parameter parameters You can run the procedure without any parameter. It’s a default mode. Exec [sp_WhoIsActive]   Lightweight parameters @get_plans = 1 – It will show the execution plans of the running query. @get_task_info = 2 – It will show the wait type of the query. Ex: Parallel CXPACKET waits. With…

  • Performance,  SQL party

    Merge join Vs Hash join Vs Nested loop join

      This month’s TSQL Tuesday party is being hosted by Stuart R Ainsworth (Blog| Twitter). I am very glad to write my first blog post as t-SQL Tuesday post on my newly designed website. SQL server has three types of internal joins. I know most of folks never heard this join type because it’s not logical join and it’s not often used in their codes. Then, when it will be used? Well the answer is “it depends”. This means it depends upon the record sets and indexes. The query optimizer will be smart and always try to pick up the most optimal physical joins. As we know SQL optimizer creates…