Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Find potential locks caused by joined queries Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 2:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:58 AM
Points: 13, Visits: 46
The plan was running on our staging server, which has most of the data.

My web server monitoring was indicating that this query was taking a long time during the heavy load and after reading that left outer joins lock the entire table on the left, I assumed this was the issue. Now knowing that the locking statement is false, I doubt this is the query I'm looking for.

I read about the left outer join locking in a forum, which I suspected could be incorrect. This is why I started this thread to learn how to check on the locking.

I have another query that is being flagged as slow and after analysis has 7 suggested indexes with a 26% improvement. I attached the .sqlplan for that one here. I would appreciate it if you could take a look.

Thanks,

Donnie


  Post Attachments 
rpt2.zip (2 views, 7.35 KB)
Post #1480127
Posted Thursday, August 1, 2013 2:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
thre you go Donnie;
actual rows 3, but the estimated rows is almost a quarter million.


run this for the three tables involved in this query:
UPDATE STATISTICS [dbo].[VW_DirectReports] WITH FULLSCAN ;
UPDATE STATISTICS [dbo].[VW_Departments] WITH FULLSCAN ;
UPDATE STATISTICS [dbo].[VW_Employee_Check_In_Status] WITH FULLSCAN ;

and consider adding an update statistics script to run more often that whatever is doing it now.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son


  Post Attachments 
stats.png (62 views, 30.67 KB)
Post #1480130
Posted Thursday, August 1, 2013 2:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:58 AM
Points: 13, Visits: 46
Awesome! Thanks Lowell. The 3 tables you mentioned are actually views. I'm guessing I should actually update the statistics on the tables included in the views. Is this correct?
Post #1480133
Posted Thursday, August 1, 2013 2:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
yes that is correct; find the underlying tables of those views, and update stats on them;

you could also use something like this to blindly update all stats for all tables as a shotgun approach:

DECLARE @Exec VARCHAR(MAX)
SELECT @Exec = ''
SELECT @Exec = 'UPDATE STATISTICS ' + QUOTENAME(schema_name(schema_id))+ '.' + quotename(name) + ' WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec
FROM sys.tables
ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

EXEC(@Exec)




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480134
Posted Thursday, August 1, 2013 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:58 AM
Points: 13, Visits: 46
Thanks again.

Is the goal to get the actual number of rows and estimated number of rows to be equal?

Donnie
Post #1480135
Posted Thursday, August 1, 2013 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
Donnie Carvajal (8/1/2013)
Thanks again.

Is the goal to get the actual number of rows and estimated number of rows to be equal?

Donnie


within the same order of magnitude or so, doesn't have to be exact.

the stats are used for deciding the best execution plan, and the optimizer uses them to produce the best plan it can in the microseconds it's allotted to do that in.

inserts/updates/deletes modify the real data, but not the statistics about the data.
Auto update of statistics requires 20% of the rows of the table + 500 more rows before SQL does it for you;
but on a big table a much smaller percentage of rows modified can adversely affect query plans;

20% of a MillionBillion Row table might not occur for a long time.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480138
Posted Thursday, August 1, 2013 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 13,138, Visits: 11,978
Donnie Carvajal (8/1/2013)
Thanks again.

Is the goal to get the actual number of rows and estimated number of rows to be equal?

Donnie


An exact match is not necessary. In fact, it is probably difficult to get them exact across the board. The target is to get them close so that the optimizer can compile an efficient plan. The estimates for rows is done by using statistics and when they are out of synch the optimizer will not create a plan that is optimal for the actual data.

--edit--
fixed some grammar issues...probably created new ones in the process.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480139
Posted Thursday, August 1, 2013 2:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 13,138, Visits: 11,978
Looks like Lowell beat me to a response again. At least his more detailed and earlier than mine.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480141
Posted Thursday, August 1, 2013 2:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:58 AM
Points: 13, Visits: 46
I've updated the statistics on all of the underlying tables and the estimated rows are at 207077. Any other suggestions? I've attached the new .sqlplan.

BTW, all of these views are made up of joins on other views. Is this bad practice?

Thanks again,

Donnie


  Post Attachments 
rpt3.zip (0 views, 7.30 KB)
Post #1480147
Posted Thursday, August 1, 2013 2:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 13,138, Visits: 11,978
Donnie Carvajal (8/1/2013)
I've updated the statistics on all of the underlying tables and the estimated rows are at 207077. Any other suggestions? I've attached the new .sqlplan.

BTW, all of these views are made up of joins on other views. Is this bad practice?

Thanks again,

Donnie


Generally it is not a great idea to nest views on top of views. You can end up with really bad performance issues. I would recommend using Lowell's example of updating stats on all the tables in your db.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480150
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse