• DBA,  Performance

    Performance Tuning Series – 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. 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 the additional info like, 3 times…

  • 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…

  • Performance,  SQL party

    T-SQL best practice SQL server

    SQL server T-SQL best practice This month’s TSQL Tuesday party is being hosted by Amit Banerjee (Blog | Twitter). He is working with Microsoft and see here his online activities MSDN. The topic this month is T-SQL best practice. It’s the both DBA and developers Topic/Job. Transact SQL is the best tool to improve the SQL server overall performance. You write your code efficiently then you no need to spend lots of money to buy extra resources like hardware, RAM etc… So the coders/developers have to know the best practice. He or She does a good job (writing correct codes) then, There is no work for the DBAs. There is lots of T-SQL best practice available…