| Author |
Topic |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2011-03-28 : 06:28:40
|
| Hello,When I run some certain complex queries they usually appears to run very slowly unless I rebuild the clustered index on the main table referenced just before, even if I have not inserted any data since the last index rebuild.Just to give you an idea of how different the speed is after a rebuild 200,000 rows are returned in about half a minute. After, in the same time, about 200 rows will be returned!I don't understand why rebuilding the index would have any impact on performance when no data has been modified in the whole database since the last rebuild. Also, no fragmentation of the index is reported. Re-organising does not seem to speed up the queries either.As the results of these queries are for reporting I have resorted to rebuilding the index and then storing the results in a table. This is not ideal though. I'm sure the query could be optimised in some way, but if it runs in a reasonable time with rebuilt indexes, why shouldn't it run okay if I keep fragmentation to a minimum? I'm sure whatever is going on also affects other queries performance, it's just this particular queries are such that they show the performance issue the most.Has anyone experience a similar problem or know why one could occur?Thanks,Michael |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-28 : 07:05:52
|
| Have you tried updating the statistics instead of rebuilding? It'll go a lot faster and probably have the same effect.Are you running the same queries, or are they mostly ad-hoc/single use? It's possible you're also caching too many ad-hoc plans. You can check that with Glenn Berry's DMV queries:http://sqlserverperformance.wordpress.com/2010/10/08/sql-server-memory-related-queries/If your data is larger than available RAM, and you're on 64-bit, the bloated plan cache could be using too much RAM and not leaving enough for data. If you do have a lot of ad-hoc plans and can't change them to stored procedures, you can try the "Optimize for ad-hoc workloads" setting (sp_configure) and ALTER DATABASE...SET PARAMETERIZATION FORCED on your affected databases. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-28 : 07:17:07
|
| Can you post the queries?Rebuilding an index invalidates all plans that use that index, so could be that you have a bad plan (for some reason) and what the rebuild is actually doing is forcing the optimiser to recompile it.--Gail ShawSQL Server MVP |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2011-03-28 : 10:49:08
|
| Thanks robvolk. We run lots of ad-hoc queries if by that's you mean queries not with in a sproc, view or function, although a lot of these ad-hoc queries use pre-defined views and functions. We're using 64-bit sql server. It seems a lot of data for a single query I guess, but I'm sure it can fit in ram (200,000 rows by 50 columns, mostly varchar(255) but probably a lot smaller). I'll have a look into this "optimize for ad-hoc workloads" setting.I haven't tried updating the stats. I'll try this once it starts running slowly again. I don't really want to do this every time before running the query though, even if it is faster.Gail, unfortunately the query is built on several views which in themselves are complex and built on other views so really too much to post here. The query itself is not run with in a stored procedure or view so that would mean a plan isn't pre-compiled anyway I'd imagine. Or does SQL Server "remember" old ad-hoc queries and use the same plan? Is that what robvolk meant by caching plans?I try to not get too into the database admin side if I can help it so sorry if my knowledge isn't great! Thanks for the help. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-28 : 11:01:30
|
| ad-hoc queries follow the same rules as for stored procedures for caching of plans.With a mess like that it could very likely be a bad plan. Hard to tell. See if statistics update helps. If so, try running the query with the OPTION (recompile) hint (just once). Let's identify exactly what does and does not help.--Gail ShawSQL Server MVP |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2011-03-28 : 12:03:15
|
| At the moment it seems to be running fast so I'll have to wait for it to start running slowly again before I can try the OPTION (recompile) hint.Interestingly, if I create a sproc out the query it runs slow! Why would the sproc have a bad query plan and the ad-hoc query still run fast?I'm not sure if this is relevant, but there's the usual stat objects for each index for these tables, but also 10-20 of these stange looking _WA_Sys ones. Not very familiar with this area as I've said, but thought I should mention it. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-28 : 12:12:54
|
| Those are auto-created stats. |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2011-04-07 : 12:24:24
|
| Hello again, sorry it's taken me so long to get back. When I put the option (recompile) at the end of the query it seems to run fast every time. Still don't quite understand how the cached plan can get so slow and why the plan is different when a sproc is created using the same query.Gail, you called my query a mess! Well actually I won't deny that, but this query is not something anything else is built on and is for reporting so performance doesn't have to be very fast, just want it to run in something under 5 mins really. Thanks for the help. |
|
|
|
|
Clustered index rebuild strange behaviour