Troubleshooting Custom Report Issues

Hi Beth

Sometimes I find it easier to use the most basic debugging tool, which is to add Print statements to the stored procedure with the time it has taken to get there and a statement saying where it is in the code.

It is one way of identifying which queries are causing the delays.

Add this to start of proc
declare @debugdt datetime

before each query add
select @debugdt=GETDATE()

after each query add
print 'finish query N ' + convert(varchar(10),datediff(ms,@debugdt,getdate()))

Then in message tab you get

(1179 row(s) affected)
finish query 1 910

(117 row(s) affected)
finish query 2 496

(129 row(s) affected)
finish query 3 1616

Will drop you an email as am happy to take a lok at the code too

Mark

If the forums had a like button, I would click it on Mark's post.  I use print statements all the time to figure out where things are going wrong, both from the perspectives of too much time and unexpected data being returned.

Depending on the report and the tables you are referenceing, using NOLOCK (if you are not already doing that) could potentially shave time, though I doubt it would affect anything to that degree.

John

I usually don't like (NOLOCK) hints since people tend not to realize that it's synonymous with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; (see Aaron Bertrand's article on the matter), but John raises a really important point -- there aren't a whole lot of reasons for a query to run slower the second time on the same connection, but a "random"-seeming pattern of execution times could indeed be the result of blocking happening as a result of other connections on the server.

To troubleshoot this, you would run your report until you got one of the "long execution time" runs, and then in SSMS run queries on DMVs or system procedures (or other fun things like Adam Machanic's sp_whoisactive) to find out what your query is waiting on. I'm not an expert on this, so I would recommend that you do some googling on your favorite SQL Server blogger or web resource and see if they can point you in the right direction.

Hi Beth,

You might be dealing with parameter sniffing when SQL Server creates the execution plan for this stored procedure. In a nutshell, what SQL Server tends to do is build a query plan when a stored procedure is first excuted and then it caches that plan for future use. That might work great for some parameter values, but might be a horrible plan for other values. It can result in the behavior you are describing.

One way to test to see if this is possibly impacting you is to use WITH RECOMPILE; that will tell SQL Server to build a fresh plan each time the procedure is run. After you add that, try some of your tests in SSMS after adding this to see how that impacts your results.

If it helps, you can keep WITH RECOMPILE, but there could be a performance hit due to regenerating the plan each time. If this procedure is run many times throughout the day, then you might want to target the parts of the stored procedure that are taking the longest with query hints, such as OPTIMIZE FOR. 

This article describes some of those strategies: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

I hope this helps and good luck!

Thanks,
David

> or other fun things like Adam Machanic's sp_whoisactive

Adding another vote for this. Everyone in the Tess community that is a SSMS user should install this.

You guys are all so smart and fantastic! I love it.

I'm going to take a look at your suggestions and see what I can figure out. Lots of new things to learn!