December 14, 2021 at 4:59 am
Hi everyone
The query I am working on seems slow to me. The table has about 600 records and the query takes about 15 seconds to run. I put on statistics and here is what I get. I don't know how to interpret this. Are the stats showing what I am seeing? I took a look at the execution plan too and I have no clue on how to use it to improve the query. The file is 40 MB which is bigger than my query 22 KB. I am seeing a lot of Nested Loops (Inner Join) with very high %. Some are like 3000% others are 9000% etc. I am not an expert but I am pretty sure these are not good things. I am not sure what information to provide so you can actually help me. Please let me know what you need and I will provide it.
(1 row affected)
Table 'Data'. Scan count 15172, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 10472, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Data'. Segment reads 7586, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
Completion time: 2021-12-13T20:34:18.5386878-08:00
December 14, 2021 at 5:05 am
the concern I have is that if it takes 15 seconds to process a 600 row table then what it is going to look like when it has to process a 5million plus row table. I am not really sure where to start on improving its performance. I thought that having CTEs would improve performance so that way certain calculations are done once and only once. Any suggestions on what I should look at when I am improving its performance?
December 14, 2021 at 10:01 am
The information we need to help, at a minimum:
Once we have these, there may be requests for additional info.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 14, 2021 at 1:33 pm
If possible, the schema of the tables would help.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 14, 2021 at 1:52 pm
I'm with everyone else. To help you, we need to see code & structures. However, a couple of points.
If you're seeing 3000% usage, I know two things. First, you're using a multi-statement table-valued user-defined function. Those are notoriously problematic when it comes to performance. I would look to eliminate it, right now. Second, you're using an old version of SSMS because the 3000% mess was fixed in later versions. Might want to update.
Also, 10472 reads suggests more than a little bit above 600 rows in the table, unless they are EXTREMELY wide rows (lots of columns). I don't think we're getting quite a complete picture here.
"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
December 14, 2021 at 5:16 pm
Hi everyone
Thanks for the replies. If I include the execution plan then does the query come with it automatically or do I have to add the query separately?
Thank you
December 14, 2021 at 5:26 pm
Hi everyone
Thanks for the replies. If I include the execution plan then does the query come with it automatically or do I have to add the query separately?
Thank you
The execution plan has the query in it. However, if you're using UDFs, that code won't be in the plan.
If possible, execute the query and get the execution plan plus runtime metrics (aka, the Actual Plan). If you can't, just the execution plan will help.
"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
December 14, 2021 at 6:02 pm
Not to mention that the code included in the execution plan is frequently truncated or a bit "warped". I recommend always including the code separately. It doesn't take long to do and can save time elsewhere.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2021 at 2:59 pm
You can also use this site: https://www.brentozar.com/pastetheplan/
If you use the above, posting the code here would also be a good idea - even if the code is included in the plan. When posting the code - please use the insert/edit code sample button.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2021 at 3:46 pm
Guess the op lost interest.
It also looks like we've had a problem with DBCC TIMEWARP on this thread. The original post was on 14 Dec 2021 at 11:59PM.
Even the Op's second post was almost a full day before that according to the date and time of 14 Dec 2021 at 12:05AM.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2021 at 12:17 pm
Guess the op lost interest.
It also looks like we've had a problem with DBCC TIMEWARP on this thread. The original post was on 14 Dec 2021 at 11:59PM.
Even the Op's second post was almost a full day before that according to the date and time of 14 Dec 2021 at 12:05AM.
Maybe the user made a trip across the International Date Line between posts. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply