February 3, 2014 at 12:37 pm
I have a fairly simple procedure with 4 if statments and code for each.
When I run the procedure in a SQL session it runs in less than 1 second.
When I execute the sp_name p1, p2, p3 statement, it hangs.
It has gone upwards of 2 minutes with no result returned because I canceled the execution.
How do I go about troubleshooting this?
Not sure where to go with this.
Tonya
February 3, 2014 at 1:27 pm
You have given us exceptionally little to go on here, so this is just an educated guess (albeit backed up by 45000 man hours invested in SQL Server) 😎
In your run that goes fast, show the ACTUAL execution plan.
In the run that hangs, show the ESTIMATED execution plan.
Now compare all of the various estimated and actual row counts and the plan shape. My guess is that you will find MASSIVE differences. Now some questions:
1) Do you have things like UDFs in your code? If so, get rid of them.
2) Are you data types all EXACTLY THE SAME as the fields in the tables that they join to or filter on (sproc parameters here too)?? This is a MUST.
3) Are your statistics up to date? Automatic stats updates are NOT sufficient for optimal query generation.
4) Do you have input parameters that can WIDELY vary, or cause the rows affected to widely vary? Think @BeginDate and @EndDate, etc.
5) Do you wrap any columns in functions (of ANY type) in the WHERE clause?
6) Run your slow query and in another SSMS window run sp_whoisactive (freebie from Adam Machanic on sqlblog.com - latest build is v11.11 I think). See if you are getting blocked or chewing CPU or tempdb or massive reads/writes, etc. That is an AWESOME script - learn to use it!!
After all of the above (perhaps even before, but the above is a great learning exercise for you) I would try OPTION (RECOMPILE) at the end of your SELECT statement. Observe the actual query plan back for different estimated/actual row counts too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2014 at 1:51 pm
The Procedure and the query are the exact same code.
I declared the parameters for the procedure and ran just the query to see where the issue was with taking so long.
I was very surprised that it ran in less than a second when running the procedure seems to hang.
I have let the procedure run for up to 2 minutes before I canceled it.
I am returning the top 5 records of a sum(value) group by issue.
In the actual execution plan it is running through only 748 actual records, but the estimated count just below it says 68174.7.
I am getting a "Missing Index" notification that says the Impact is 67.3823 - It wants to create a nonclucstered index.
I have never been responsible for creating indexes - another team member has always done it, so I am not sure what to do.
The Estimated Execution plan is much different than the actual plan.
There is no Hash Match cost in the Estimated plan.
I have downloaded the WhoIsActive script, but have not installed it yet.
The sql is as follows:
USE [myDB]
GO
/****** Object: StoredProcedure [dbo].[ps_SpeedTest] Script Date: 2/3/2014 1:36:50 PM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
----EXEC ps_SpeedTest 1, 1508, 1337, '1/1/2014'
--create PROC [dbo].[ps_SpeedTest]
--(
declare
@language INT = 1
, @ID INT = 1508
, @RespID INT = 1337
, @ddate DATETIME = '1/1/2014'
--)
--AS
--SET NOCOUNT ON
DECLARE
@qID BIGINT
select @ddate
SET @ddate = InnoUtility.dbo.fx_UTY_ReturnDate(@ddate)
select @ddate
SELECT TOP 5
0
, @RespID
, ''
, 1
, 1
, d.mQuestionName
, SUM(b.mValue) [sumValue]
, 0
FROM tblMy_Relation a
INNER JOIN tblMy_Data b ON b.mYear = YEAR(@ddate) AND b.mMonth = MONTH(@ddate) AND (a.mChildQID = b.mQuestionID OR a.mSisterQID = b.mQuestionID)
INNER JOIN vw_Return_Entity_Ops c ON b.mMachineID = c.entityID AND c.eParentEntityID = @cellID AND c.eLanguage = @language INNER JOIN InnoTrak.dbo.vw_TRK_MetricQuestionList d ON a.mChildQID = d.mQuestionID AND d.mLanguage = @language
WHERE a.mCalcParentQID = 73
GROUP BY d.mQuestionName
ORDER BY [sumValue] DESC
GO
February 3, 2014 at 2:05 pm
well the things i see so far are:
you are joining other tables to a view vw_Return_Entity_Ops
it is very common that a view will grab substatially more columns thyan you need, as well as build a sub optimal plan when you join it to yet more tables and views.
remove the view and do the same work on the base tables themselves.
would immediatle update statistics on all tables used by this stored proc, and the tables the view actually references:
UPDATE STATISTICS ON tblMy_Data WITH FULL SCAN;
UPDATE STATISTICS ON tblMy_Relation WITH FULL SCAN;
--these tables need stats too!
--the view InnoTrak.dbo.vw_TRK_MetricQuestionList references what tables?
--the view vw_Return_Entity_Ops references what tables?
Lowell
February 3, 2014 at 2:07 pm
The estimated rows being way off is either getting you a big table scan and hash join which is killing your time and possibly opening you to blocking too, OR the difference is the other way and you are getting a seek/nested loop join plan for lots of rows and the logical IOs are killing you. Either way, when there is a huge difference between estimated/actual you (and your server) lose. :crying:
I note a view - lord only knows what that is pulling it, and a linked server too possibly? Badness there likely if external.
What about all the other things I said? Did you do them?
And how did the sproc run with OPTION (RECOMPILE) after the SELECT statement in it?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2014 at 5:27 am
I have it running properly this morning, and have no real clue why, other than I ran the query with the view replacement as was suggested, and the procedure ran with the same time as the actual query.
The ps_SpeedTest was a copy of the actual procedure, and when I executed that procedure with the update table join, the original procedure ran properly.
I wonder if the updated execution plan from speedTest affected it.
How can I check statistics?
I really appreciate the code to update them, but how do I know it changed if I don't know what it was first, or is that something that we don't see?
I tried the recompile but what I did was exec ps_speedTest with recompile; and it didn't make a difference yesterday.
But, it said it was successfully marked for recompile.
Maybe that is why it is running properly this morning...
I am going to get permission to put the whoisactive procedure on the server today, and learn to use it.
We have other things that get slow for unknown reasons, and it should help us troubleshoot those.
Tonya
February 4, 2014 at 6:54 am
I said to put the OPTION (RECOMPILE) INSIDE the sproc attached to the SELECT statement. What you had here was likely a form of parameter sniffing where the first call to a sproc caches a plan for that set of input parameters. Then the rest of the executions use the same plan - even if it is disastrously inefficient for the current parameter values. Putting recompile on the sproc got you a new plan.
Binoogle is your friend. A search on [when were sql server statistics last updated] gets several top links that have queries to find when stats were last updated.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply