Query running fast in production and slow in non-production I have asked to look one of the queries that run fast in production and slow in non-production. Question asked – how long it took to complete the results. It is about 1.50 minutes in non-production, but in production it completed less than 5 seconds. Got the code and run myself in SSMS to compare the plan, since it will finish 1.50 minutes. It quickly showed estimated number of rows are totally different. This could be either out of stats or data skew. I just checked the last stats update and updated the stats that fixed the issue. You can…
-
-
SQL query tuning front end Website slowness
SQL query tuning front end Website slowness Overview of the issue. There is a health care website which used by doctors, in a day which runs slow sometimes and runs fine most of the time. The slowness is about 1 to 3 seconds and it sometimes timeout as well, when it reaches timeout setting. I have configured server side trace, whoisactive and Perfmon counters as well to correlate with anything when needed. As a basic check, the website is fetching data from two databases one is 300 MB and other is 500 MB and no maintenance job been run in place. Have configured maintenance job, still same issue after a…
-
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…
-
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://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://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…
-
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…