SQL Server Performance Forum – Threads Archive
Inconsistent Execution Times for Stored Procedures
I have the following scenario: 2 SQL 2000 boxes (Enterprise Edition)– One is a production box. (Multiple applications access it.)
– One is dedicated to a single application (except during a "download window"). During most of any given day, the "dedicated" server is accessed by a single application that makes one stored procedure call (through ODBC). This stored procedure needs to return in under 300MS ALWAYS. Normally (84.07% of the time), the stored procedure executes in less than 10MS. 99.89% of the time it executes in under 300MS. The times when it doesn’t execute under 300MS, it executes in anywhere from 300MS-1600MS. These times coincide with the "download window" (not surprising). The "download" consists of an import process on the production box calling a remote procedure (that inserts one record) on the dedicated box. This "insert" procedure also USUALLY executes in under 10MS but spikes in the same pattern/dispersion as the stored procedure called through ODBC. So, during the "download window," I end up with ~99.9% of the stored procedure executions completing in under 300MS. I need to get this number closer to 100%. I have tracked every pattern I can think of, and I have noticed that the "burst" completion times are never isolated. They are always bunched in groups of 10-20 calls. The execution times seem to "burst" every 5-8 minutes. I don’t believe there is any way to "prioritize" one stored procedure over another. I am out of ideas as to potential places I can look to figure out some way to improve the execution times of the delinquent stored procedures. The "dedicated" box is a quad processor with 4GIG of memory, so I don’t believe this is a hardware or memory limitation. Other than the two processes I have described above, there is nothing running on the "dedicated" box during this time. SQL Server is the only software installed besides the operating system and (I think.) a backup software. Any ideas as to things to look into would be greatly, greatly appreciated.
One of the first things I would do is to use Profiler to capture the run of the stored procedure in question during a good time (fast) and a slow time. At this same time, I would want to capture the duration, reads, writes, CPU, and the execution plan. Once I did this, then I would want to compare what I saw. If the execution plans and other measures are identical, then we need to look elsewhere. But if there is some differences in the execution plans and the other measures, then we need to determine what is causing them (such as outdated statistics, etc.) and see if we can fix them. If the execution plan and other measures are identical, then I would look into locking, and what is going on there. Again, I would use Profiler to trace what is going on during locking during a fast and slow SP execution to see if locking is an issue. If locking is not an issue, then I would probably use Performance Monitor to capture detailed activity during both a fast and slow SP run to see if that provides any clues, and then go from there. Of course, there are other possibilities, but the ones I have already discussed are probably the best place to start. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Brad’s suggestions are the way to go, but one question I did have. Is the table you are inserting into a heap? If so, then it would be beneficial to you to maybe add a clustered index.
"How do you expect to beat me when I am forever?"
Brad, I will set up traces as you suggested and run them Monday morning. royv, You’ll have to excuse my ignorance here (PLEASE! I’m a bit new to getting this deep into performance analysis.), but could you tell me why adding a clustered index would help? I thought about that but though that it would actually slow down the remote procedure call that inserts data into the table. Am I way off base there? Rachel
Do refer to this link http://www.sql-server-performance.com/clustered_indexes.asp] in this website and corresponding tips on the homepage for a better idea. HTH Satya SKJ
There has been a lot of discussion of the benefits of clustered indexes in this forum, besides what is available on my website. You might want to do a search on clusterd index in the forum and read the relevant posts. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>