• Performance

    How to find slow running T SQL query

    Top 10 ways to find slow running T SQL query Followings are ways to identity and capture the slow running queries in SQL server database.   1.Use common activity monitor and task manager to get some overview Right click the instance – Activity monitor 2.Use DMVs Use Glenn Berry’s DMV pack https://www.sqlskills.com/blogs/glenn/category/dmv-queries/ 3.Check wait stats Use Paul Randal’s DMV  https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ 4.Use Whoisactive Use Adam Machanic’s script https://www.sqlserverblogforum.com/dba/whoisactive-capturing-a-sql-server-queries-performance-tuning/ 5.Use SP_BiltZ Use Brent Ozar’s script https://www.brentozar.com/blitz/ 6.Use server side trace https://www.sqlserverblogforum.com/dba/how-to-use-server-side-trace-to-capture-running-query-t-sql/ 7.Use extended events https://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/ 8.Use actual execution plan, if you have query In SSMS, check mark the actual execution plan and run the query to tune further. 9.Use query store, if…

  • DBA,  Performance

    Parameter sniffing problem PSP SQL server database capture and analysis options

    Being a DBA we could see many of PSP parameter sniffing problem is SQL server database. I have seen many of it, following is the way to capture and analysis. Issue 1 – We had issue for one of the important claim data loads every night, which suddenly running long time to load the data. Issue 2 – The website is going unresponsive state daily once or twice By default SQL server will sniffing the parameter, meaning the SQL engine will store the plan in cache for any parameters the same plan will be used in the future. It is good most of the time, since parameter Sniffing is useful…

  • DBA,  Performance

    Perfmon Data collection – Integrate SSRS with Perfmon – Dashboard performance of database graph using SSRS Collect perfMon and automate it load to SQL database and generate reports

    DBA wants to collect metrics and analysis them and present them to management to get something done by using the data and it is all about data and metric nowadays. How to collect them and show as a presentation. Tools needed: SSRS, Perfmon.msc, Relog.Exe, database engine, power shell & CMD. Configure SSRS Installing SSRS If you have not installed SSRS, install the service first and configure it. Go to the software folder and double click the setup – click installation in the left side –New SQL server standalone or add features on existing installation – Next and next – select “Add features to an existing instance of SQL server Next…

  • DBA,  Performance

    Use of SQL server statistics and how to make a query to use them

    Statistics are very important in the query optimization. This post will give you some basic idea of stats and how they are created and used. This post is to link with my performance tuning parts. What is statistics: Statistics store information about the distribution of data in the column/columns for a table. It has Header, Densities and Histogram. The Histogram stores only 200 steps, and for very large tables VLT, it is hard to hold much data only can hold up-to 200 steps, you have to create manually for the VLT. It is a lightweight and use very low storage. The only way to know the underlying tables from the…

  • DBA,  Performance

    Performance Tuning Steps Query by looking an execution plan and effectiveness of the index Part 2

    How to read execution plan to improve poorly performing queries Execution plan is optimizer’s calculation of how the query will be executed by creating the best low-cost query plan (if it is already in the cache, it will reuse).  SQL server is cost based optimizer.  Meaning, the optimizer decides based on CPU, I/O and statistics etc and creates a plan and returns the results. Up to date statistics is very important one to create a more accurate plan. We can view this in GUI and text both will be helpful, when the scenario come in.   Types:  Estimated and Actual execution plan – both will be helpful. Estimated plan: It…