SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find potential locks caused by joined queries


Find potential locks caused by joined queries

Author
Message
Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 51
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
Attachments
rpt2.zip (6 views, 7.00 KB)
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67319 Visits: 40889
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Attachments
stats.png (69 views, 30.00 KB)
Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 51
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?
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67319 Visits: 40889
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 51
Thanks again.

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

Donnie
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67319 Visits: 40889
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58894 Visits: 17940
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 Modens 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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58894 Visits: 17940
Looks like Lowell beat me to a response again. At least his more detailed and earlier than mine. :-D

_______________________________________________________________

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 Modens 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)
Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 51
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
Attachments
rpt3.zip (5 views, 7.00 KB)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58894 Visits: 17940
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search