Troubleshooting slow running query

Question

Post reply

Troubleshooting slow running query

SQLisAwe5oMe

SSChampion

Points: 14047
More actions
March 9, 2023 at 5:46 pm

#4158932

I have a process that uses 2 tables in PROD and it has been running slow in PROD.
The tables are identical in UAT and the process runs much faster in UAT.
What is the best way to tackle this....I was thinking of using Extended Events to capture the details.
Is this the best approach? Plz advise

Regards,
SQLisAwe5oMe.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 1

Most likely causes:

Bad statistics in production

Parameter sniffing

Production hardware is different than staging or production hardware is oversubscribed. If UAT is in a dev cluster that is lightly used, it could be fast in spite of the hosts being oversubscribed where in production everything is being used and the virtual host that has 10 Virtual cores issued for every actual CPU on the host is struggling leading to time sharing among the guests.

use first responder toolkit sp_BlitzCache to find your slow query then run the command it gives you in one of the right most columns to remove the plan from cache. If the query is fast after that, it is parameter sniffing.

If it is still slow, do a statistics update. If it is still slow, question the hardware provided to the SQL server.

If none of those are it, there are a bunch of other branches of events that could be causing it.

Y.B. SSChampion Points: 11642 More actions · Answer 2

The question is a little vague so I'm afraid I can only give a broad answer in return. DEV/Staging environments don't typically have the same amount of data so check for issues with your query. It's not uncommon for inefficient code to work fine in dev only to be problematic in production. So without knowing any more specifics check that you have appropriate indexes to accommodate the query. Also, check the execution plan and look for any issues. That would be at the bare minimum the first things I would look at. The truth is there could be so many reasons why this is happening. Start with the basics and work your way up.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

Jonathan AC Roberts

SSCoach

Points: 18263

March 9, 2023 at 6:49 pm

#4158979

Look at the execution plan on both databases.

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 4

Thanks everyone for the responds....I will start with basics.

Do you have the link for "first responder toolkit sp_BlitzCache"

Regards,
SQLisAwe5oMe.

Y.B. SSChampion Points: 11642 More actions · Answer 5

SQLisAwe5oMe wrote:

Thanks everyone for the responds....I will start with basics.
Do you have the link for "first responder toolkit sp_BlitzCache"

That is part of a package of free tools provided by the most excellent Brent Ozar. https://www.brentozar.com/blitz/

Those tools are great to have in your arsenal, but they might actually overwhelm you at first if you are not quite sure what you are looking for. I know examining an execution plan can be overwhelming at first but it truly is a right of passage. You don't need to be an expert to be able to identify obvious issues such as incorrect cardinality estimates, index scans vs seeks, nested vs merge joins, etc... Also recognize when something is just a red herring. Index scans aren't necessarily an issue (and likely more efficient) on small tables for example.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

SQLisAwe5oMe wrote:

I have a process that uses 2 tables in PROD and it has been running slow in PROD.
The tables are identical in UAT and the process runs much faster in UAT.
What is the best way to tackle this....I was thinking of using Extended Events to capture the details.
Is this the best approach? Plz advise

The very first thing to check is the rowcount differences in the tables between that UAT and Prod databases. That will probably explain the differences.

The next step, if the data is less, is to forget about UAT and figure out what's going on with the "process" in Prod. While I agree that Brent's code is fabulous for a lot of things, it won't replace the knowledge required to look at execution plans to find the "high spots" to begin working on. You also need to know what to look for in the code like non-SARGable predicates and more.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 7

Thanks....the 2 tables in question are pretty small and have same amount of rows in both UAT & PROD

I've updated the STATISTICS on both tables and waiting for user to give me feedback.

Regards,
SQLisAwe5oMe.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

SQLisAwe5oMe wrote:

Thanks....the 2 tables in question are pretty small and have same amount of rows in both UAT & PROD
I've updated the STATISTICS on both tables and waiting for user to give me feedback.

Have you checked to see if they have the same indexes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 9

Jeff Moden wrote:

SQLisAwe5oMe wrote:
Thanks....the 2 tables in question are pretty small and have same amount of rows in both UAT & PROD
I've updated the STATISTICS on both tables and waiting for user to give me feedback.
Have you checked to see if they have the same indexes?

Oh my gosh this!!!

And the same structures, etc. If we're comparing performance, it has to be like-to-like. Same versions of SQL Server. Same server and database settings. Same connection string settings. All of it.

However, the fastest way to gain a quick understanding would be to get the execution plans for each query. Pull them straight out of the cache on the servers (or Query Store if it's enabled) and you'll see pretty much exactly why you're getting different performance and probably guideposts on how to improve it. I wouldn't recommend, in this instance, using Extended Events because capturing plans is extremely expensive (it is in Trace too).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning