• Performance

    Not in where clause SQL query running slow taking more time

    Let me give an overview of one of multi join Ad hoc query with couple of where condition, which I got received to improve the run time speed. I got a statement from a developer that one of code is running long time, more than 15+ hours, which usually runs in less than a minute. I have asked them how a less than minute code will run more than 15 hours, they said no changes made, I am sure they are lying and it is a Ad-hoc, I cannot trace the DDL changes. I started doing basic check and it is an eight table join with group by and order…

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

  • Performance

    How to use server side trace to capture running query T SQL

    Capturing queries by using server side trace In general, DBA will have a report most of time the server and application web is running slow. You have to find yourself what query is running slow and benchmark and tune it. Profiler trace is good to capture the running code. But it is a GUI and we have to start, capture and stop it. This will also make some performance slowness to the server and you it is hard to read and store data. The server side trace is another option and it is a lightweight and T-SQL code, we can create a stored procedure and schedule it on SQL server…

  • 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 (to check performance issue, benchmark for migration etc) and it is all about data and metric nowadays. How to collect them and show as a presentation. Two parts are there: 1. You can setup Perfmon, T-SQL and use excel 2. Setup perfmon, T-SQL and configure SSRS. Tools needed: SSRS, Perfmon.msc, Relog.Exe, database engine, PowerShell & CMD. Steps: Create two notepad and save it any drive (performance.lst, server.lst) and one CMD file createlogs.cmd – The performance notepad will have counter details and server notepad will have serve name and createlogs…