|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Performance problem with SQL Server 2005I am trying to optimise a number of slow running stored procedures and I'm having a strange problem. As soon as I create or alter a stored procedure it runs really slowly. Then at some point it suddenly speeds up. The problem is that I can't tell when and if it's going to speed up; it's quite unpredicable. The speeding up is not due to caching of data (which can make a stored procedure run somewhat faster when you run it repeatedly). For instance: - I compile a stored procedure - I run the first time. It takes 20 seconds - I run it another 3 times. It takes 18 seconds (thanks to caching) - I leave it for 30 minutes while I do other unrelated things - I run it again. It now takes 5 seconds. - I run it another 3 times. It take 4 seconds each time. Does anyone know what is happening? Is SQL Server doing some background optimisation? If so, is there any way to force it to happen when I want so that I don't get unpredicable results? I have tried updating the statistics in all the tables that are used in the stored proc but this has no impact. Besides statistics are at table level whereas what I am experiencing is at stored procedure level (e.g. if I make a copy of the stored procedure when it takes 5 seconds and call it a different name, the original version will take 5 seconds as previously, and the copy will take 20 seconds again). Kostas (Kos***@discussions.microsoft.com) writes:
Show quote > I am trying to optimise a number of slow running stored procedures and The only background processing from SQL Server is auto-statistics. (Well,> I'm having a strange problem. As soon as I create or alter a stored > procedure it runs really slowly. Then at some point it suddenly speeds > up. The problem is that I can't tell when and if it's going to speed up; > it's quite unpredicable. The speeding up is not due to caching of data > (which can make a stored procedure run somewhat faster when you run it > repeatedly). > > For instance: > - I compile a stored procedure > - I run the first time. It takes 20 seconds > - I run it another 3 times. It takes 18 seconds (thanks to caching) > - I leave it for 30 minutes while I do other unrelated things > - I run it again. It now takes 5 seconds. > - I run it another 3 times. It take 4 seconds each time. > > Does anyone know what is happening? Is SQL Server doing some background > optimisation? If so, is there any way to force it to happen when I want so > that I don't get unpredicable results? > > I have tried updating the statistics in all the tables that are used in > the stored proc but this has no impact. Besides statistics are at table > level whereas what I am experiencing is at stored procedure level (e.g. > if I make a copy of the stored procedure when it takes 5 seconds and > call it a different name, the original version will take 5 seconds as > previously, and the copy will take 20 seconds again). it's not really background, but it could be invoked by another process while you have your coffee.) But it seems from your description that we can rule out that possibility. The only other thing I can think of is parameter sniffing. That is, when you run a procedure and there is no plan in the cache, the optimizr will optimize the procedure for the given parameter values. If the plan goes out of cache while you do something else, and you then rerun with different parameters you could get a different plan. It could help if you could post a sample procedure, so we have any idea of what's in them. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx "Erland Sommarskog" wrote: Hi Erland, and thanks for the reply.> > The only other thing I can think of is parameter sniffing. That is, > when you run a procedure and there is no plan in the cache, the optimizr > will optimize the procedure for the given parameter values. If the > plan goes out of cache while you do something else, and you then rerun > with different parameters you could get a different plan. > > It could help if you could post a sample procedure, so we have any > idea of what's in them. > You might be on the right track about a different plan being used. I wasn't aware that the plan was calculated when you run the stored proc and then cached - I thought the plan was calculated when you compile the stored proc. However I am always calling the stored procedure with the same parameter (I have created 1 set of test data that I am using for benchmarking). Is it possible for the optimiser to generate different plans for the same parameter? I think what I need to do is to examine what indices are being used in the fast running version of the stored procedure and add hints so that these indices are always used. What do you think of this approach? Forgot to say that unfortunately I am not permitted to post any code as it
contains a lot of sensitive business logic. Kostas (Kos***@discussions.microsoft.com) writes:
> You might be on the right track about a different plan being used. I Yes, if statistics have changed.> wasn't aware that the plan was calculated when you run the stored proc > and then cached - I thought the plan was calculated when you compile the > stored proc. However I am always calling the stored procedure with the > same parameter (I have created 1 set of test data that I am using for > benchmarking). Is it possible for the optimiser to generate different > plans for the same parameter? The optimizer looks at the load when it comes to determine whether to use a parallel plan. In fact, if memory serves, I think a parallel may be executed on fewer processeors than originally intended if there is a lack of resource. > I think what I need to do is to examine what indices are being used in the Yes, comparing the query plans between fast and slow version is a must> fast running version of the stored procedure and add hints so that these > indices are always used. What do you think of this approach? in a situation like this. > Yes the WITH RECOMPILE has made a difference, but I'm not sure if it's the When you have WITH RECOMPILE in the procedure definition, the plan is> right one. > - If I run the slow running version of the sproc using WITH RECOMPILE it > continues to run slowly. > - If I run the fast running version of the sproc using WITH RECOMPILE it > will run slowly. When I run it again without WITH RECOMPILE it continues > to run quickly as previously. Then when I run the slow running version > (without WITH RECOMPILE), it speeds up and runs at the same speed as the > fast version! not cache. The same applies with you use WITH RECOMPILE when uou invoke the procedure. Then again, if you change the procedurem all plans are flushed and it's as if the procedure was new and fresh. Too bad that you cannot post the procedure - but please tell us one thing: is there any dynamic SQL in it? Do you called nested procedures? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Kostas,
For starters, I would consider concurrency. Start performance monitor and see if the server is busy with something else when you see slow execution. In addition to Alex suggestion.., there is another feature which you may want
to use.. > For starters, I would consider concurrency. Start performance monitor I would say you can run the profiler along with the perfmon, import the > and see if the server is busy with something else when you see slow > execution. perfmon result in your profiler and you will get the perfmon graph time-tagged with your profiler trace. Now if you click on the hikes on the graph, it will give you the corresponding command text in your profiler... In fact in 2005 you can get the execution plan traced too.. Thanks for the reply Alexander. I really don't think it's a concurrency
issue. I have 2 stored procedures that are identical. One I compiled yesterday and takes 5 seconds to run, the other I compiled today and takes 20 seconds. It doesn't matter when I run them or in which one I run first, the performance is always the same. Show quote "Alexander Kuznetsov" wrote: > Kostas, > > For starters, I would consider concurrency. Start performance monitor > and see if the server is busy with something else when you see slow > execution. > > Hi Kostas,
Try recreating the stored procedure with a WITH RECOMPILE option and see. Maybe the execution plan stored is poor for the records you are selecting. "Omnibuzz" wrote: Hi Omnibuzz and thank you too.> Hi Kostas, > Try recreating the stored procedure with a WITH RECOMPILE option and see. > Maybe the execution plan stored is poor for the records you are selecting. Yes the WITH RECOMPILE has made a difference, but I'm not sure if it's the right one. - If I run the slow running version of the sproc using WITH RECOMPILE it continues to run slowly. - If I run the fast running version of the sproc using WITH RECOMPILE it will run slowly. When I run it again without WITH RECOMPILE it continues to run quickly as previously. Then when I run the slow running version (without WITH RECOMPILE), it speeds up and runs at the same speed as the fast version! So it looks like the optimiser is not calculating the best execution plan, but at least it's clever enough to see that there is a better performing one and use that instead. This doesn't seem like fix to the problem though. If the plans are cached, there is always the chance the cache will be purged and all the sprocs will start running slowly. Am I right to think that the proper way to solve this is to add hints in all the queries? That's something that I'd really like to avoid as I trust myself less than the optimiser, and it would make the code less easily maintainable when the schema and/or business logic needs to change in the future. Kostas wrote:
> Am I right to think that the proper way to solve this is to add hints in all In my experience (with SQL Server 2000, not 2005), there are times> the queries? That's something that I'd really like to avoid as I trust myself > less than the optimiser, and it would make the code less easily maintainable > when the schema and/or business logic needs to change in the future. where I have had to force the optimizer to use the correct indexes and also to process tables in a specific order (using SET FORCEPLAN ON). It tends to happen whenever querying against large tables (with millions of rows) and joining onto serveral other smaller tables. Examine the execution plans using Profiler, identify the ones that are not always optimal, and try using the optimizer hints on those queries only. When using FORCEPLAN, it's critical you write your query with the tables in the order you want them processed. e.g (assuming you have an index on TransactionDate) SELECT * FROM Transaction t INNER JOIN TransactionItem ti ON ti.TransactionID = t.TransactionID AND ti.ProductCode = 1 INNER JOIN Customer c ON c.CustomerID = t.CustomerID WHERE t.TransactionDate > '1 Jan 2006' AND t.TransactionDate < '2 Jan 2006' instead of SELECT * FROM Customer c INNER JOIN Transaction t ON t.CustomerID = c.CustomerID INNER JOIN TransactionItem ti ON ti.TransactionID = t.TransactionID AND ti.ProductCode = 1 WHERE t.TransactionDate > '1 Jan 2006' AND t.TransactionDate < '2 Jan 2006' Chris Hi Kosta,
From what you writing it is not clear whether you saw different execution plans produced between the different experiments. I would suggest that you enable profiler trace (statistics profile) and compare the execution plans between the two iterations to eliminate/verify this possibility. Based on what you are reporting: > - If I run the slow running version of the sproc using WITH RECOMPILE ....I would conclude that the difference that you experience is because of it > continues to run slowly. > - If I run the fast running version of the sproc using WITH RECOMPILE it > will run slowly. When I run it again without WITH RECOMPILE it continues to > run quickly as previously. Then when I run the slow running version (without > WITH RECOMPILE), it speeds up and runs at the same speed as the fast version! the overhead of compilation: each time you execute either of the SPs with "WITH RECOMPILE" they execute slower than without (the plan(s) are cached) and that would make sense. However, this observation contradicts your original posting in which the 5th subsequent execution after some time was much faster than the 2nd,3d,etc. Finally, it's not clear if your tests are made on an idle system or a busy server. If you are the only user on the system, plan generation should be deterministic and repeatable. The same should be more or less true for the execution time and "statistics io", assuming that there is no other noise created by other users or other applications on the box. Please check the above, repeat the experiment and get back to us with the results. Leo Show quote "Kostas" <Kos***@discussions.microsoft.com> wrote in message news:E58C0C06-CAA3-450E-9A3E-5716B3C826DB@microsoft.com... > "Omnibuzz" wrote: >> Hi Kostas, >> Try recreating the stored procedure with a WITH RECOMPILE option and >> see. >> Maybe the execution plan stored is poor for the records you are >> selecting. > > Hi Omnibuzz and thank you too. > > Yes the WITH RECOMPILE has made a difference, but I'm not sure if it's the > right one. > - If I run the slow running version of the sproc using WITH RECOMPILE it > continues to run slowly. > - If I run the fast running version of the sproc using WITH RECOMPILE it > will run slowly. When I run it again without WITH RECOMPILE it continues > to > run quickly as previously. Then when I run the slow running version > (without > WITH RECOMPILE), it speeds up and runs at the same speed as the fast > version! > > So it looks like the optimiser is not calculating the best execution plan, > but at least it's clever enough to see that there is a better performing > one > and use that instead. > > This doesn't seem like fix to the problem though. If the plans are cached, > there is always the chance the cache will be purged and all the sprocs > will > start running slowly. > > Am I right to think that the proper way to solve this is to add hints in > all > the queries? That's something that I'd really like to avoid as I trust > myself > less than the optimiser, and it would make the code less easily > maintainable > when the schema and/or business logic needs to change in the future.  |
|||||||||||||||||||||||
Other interesting topics